oracle19c创建表空间,Oracle19c 创建表空间

昨天部署好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