#常用的几个代码
--查询临时表空间 select name from v$tempfile; --查询表空间 select name from v$datafile; 修改用户的密码 alter user 用户名 identified by 密码;
昨天部署好oracle19c后，用以前oracle11g的笔记来创建表空间遇到了坑。这里写一下总结。
其实之所以遇到坑是因为相比于oracle11g,oracle19c多了一个cdb和pdb的概念（从12c开始出现）。
#确定表空间文件存储目录
[oracle@localhost ~]$ su - oracle [oracle@localhost ~]$ cd /opt/oracle/oradata/ [oracle@localhost oradata]$ ls orclcdb [oracle@localhost oradata]$ cd orclcdb/ [oracle@localhost orclcdb]$ ls control01.ctl control02.ctl orclpdb1 pdbseed redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf [oracle@localhost orclcdb]$ mkdir anytxn_v2_dev [oracle@localhost orclcdb]$ cd anytxn_v2_dev/ [oracle@localhost anytxn_v2_dev]$ pwd /opt/oracle/oradata/orclcdb/anytxn_v2_dev
#创建表空间文件
[oracle@localhost anytxn_v2_dev]$ sqlplus / as sysdba sql*plus: release 19.0.0.0.0 - production on fri feb 21 13:38:42 2020 version 19.3.0.0.0 copyright (c) 1982, 2019, oracle. all rights reserved. connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production version 19.3.0.0.0 sql> create temporary tablespace anytxn_dev_data_temp tempfile '/opt/oracle/oradata/orclcdb/anytxn_v2_dev/anytxn_v2_dev_temp.dbf' size 32m autoextend on next 32m maxsize 20480m extent management local; tablespace created. sql> create tablespace anytxn_v2_dev_data logging datafile '/opt/oracle/oradata/orclcdb/anytxn_v2_dev/anytxn_v2_dev_data.dbf' size 100m autoextend on next 100m maxsize 30480m autoallocate extent management local segment space management auto; 2 3 4 5 6 7 8 9 tablespace created.
#创建用户
sql> create user anytxn_v2_dev identified by "jrx12345" default tablespace anytxn_v2_dev_data temporary tablespace anytxn_dev_data_temp profile default;
create user anytxn_v2_dev identified by "jrx12345" default tablespace anytxn_v2_dev_data temporary tablespace anytxn_dev_data_temp profile default
*
error at line 1:
ora-65096: invalid common user or role name
此错误是因为用户名称不符合规范，oracle 12c开始引入了cdb与pdb的新特性。sqlplus / as sysdba命令默认登陆的是cdb数据库，而cdb数据库中要求所有新建用户用户名必须以c##开头，否则就会报以上错误，在pdb内创建用户则没有此要求
#修改用户名后创建用户
sql> create user c##anytxn_v2_dev identified by "jrx12345" default tablespace anytxn_v2_dev_data temporary tablespace anytxn_dev_data_temp profile default; create user c##anytxn_v2_dev identified by "jrx12345" default tablespace anytxn_v2_dev_data temporary tablespace anytxn_dev_data_temp profile default * error at line 1: ora-65048: error encountered when processing the current ddl statement in pluggable database orclpdb1 ora-00959: tablespace 'anytxn_v2_dev_data' does not exist
原因是在cdb内创建用户分配表空间时，所分配的表空间必须在pdb和cdb中同时存在，否则会报错。如果是在pdb与cdb有相同表空间的情况下给cdb用户分配表空间，则会分配cdb的表空间，给用户pdb的表空间并不受影响。所以要在pdb内创建相同的表空间，然后再回cdb创建用户
查询当前数据库名称 sql> show con_name con_name ------------------------------ cdb$root 查询pdb数据库名称 sql> select name,open_mode from v$pdbs; name -------------------------------------------------------------------------------- open_mode ------------------------------ pdb$seed read only orclpdb1 read write 切换数据库 sql> alter session set container=orclpdb1; session altered. sql> create temporary tablespace anytxn_dev_data_temp tempfile '/opt/oracle/oradata/orclcdb/orclpdb1/anytxn_v2_dev/anytxn_v2_dev_temp.dbf' size 32m autoextend on next 32m maxsize 20480m extent management local; tablespace created. sql> create tablespace anytxn_v2_dev_data logging datafile '/opt/oracle/oradata/orclcdb/orclpdb1/anytxn_v2_dev/anytxn_v2_dev_data.dbf' size 100m autoextend on next 100m maxsize 30480m autoallocate extent management local segment space management auto; 2 3 4 5 6 7 8 9 tablespace created. sql> alter session set container=cdb$root; session altered. sql> create user c##anytxn_v2_dev identified by "jrx12345" default tablespace anytxn_v2_dev_data temporary tablespace anytxn_dev_data_temp profile default; user created. sql> grant connect,resource to c##anytxn_v2_dev; grant succeeded.
如上所示，创建成功，尝试用新用户连接数据库
[oracle@localhost anytxn_v2_dev]$ sqlplus c##anytxn_v2_dev/jrx12345 sql*plus: release 19.0.0.0.0 - production on fri feb 21 20:46:04 2020 version 19.3.0.0.0 copyright (c) 1982, 2019, oracle. all rights reserved. last successful login time: fri feb 21 2020 15:33:39 +08:00 connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production version 19.3.0.0.0
到此这篇关于oracle19c 创建表空间的文章就介绍到这了,更多相关oracle19c 创建表空间内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！