Oracle 12c CDB 和 PDB 表空间管理和配置 说明

时间:2022-09-30 21:35:01



 注:本文谢绝转载。

 

1  管理CDB中的表空间

    CDB表空间的管理和non-CDB 表空间的管理一样,CDB 也有一个实例,在安装的时候指定。

 

 

--查看表空间:

SQL> select instance_name fromv$instance;

INSTANCE_NAME

----------------

Cndba

 

SQL> col file_name for a50

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME                   FILE_ID FILE_NAME

------------------------------ ------------------------------------------------------------

USERS                                   6 /u01/app/oracle/oradata/cndba/users01.dbf

UNDOTBS1                                4/u01/app/oracle/oradata/cndba/undotbs01.dbf

SYSAUX                                  3/u01/app/oracle/oradata/cndba/sysaux01.dbf

SYSTEM                                  1 /u01/app/oracle/oradata/cndba/system01.dbf

 

--切换container

SQL> set lin 140                 

SQL> select con_id, dbid, guid, name ,open_mode from v$pdbs;

 

   CON_ID       DBID GUID                             NAME         OPEN_MODE

---------- ------------------------------------------ ------------ ----------

        2 4088301206 F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEED     READ ONLY

        3  426143573F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA    READ WRITE

        4 1231796139 F812DE1B6A8F363AE0430A01A8C0C759 PCNDBA2      READ WRITE

 

SQL> alter session setcontainer=pcndba2;

 

Session altered.

 

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME      FILE_ID FILE_NAME

----------------- ------------------------------------------------------------

SYSTEM                    12/u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

SYSAUX                    13/u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

USERS                     14/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

 

 

--表空间具体操作示例

SQL> alter session setcontainer=CDB$ROOT;

Session altered.

 

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

 

SQL> CREATE TABLESPACE dave

 2    DATAFILE'/u01/app/oracle/oradata/cndba/pcndba2/dave01.dbf' SIZE 1M

 3    AUTOEXTEND ON NEXT 1M;

Tablespace created.

 

SQL> ALTER TABLESPACE dave ADD

 2    DATAFILE'/u01/app/oracle/oradata/cndba/pcndba2/dave02.dbf' SIZE 1M

 3    AUTOEXTEND ON NEXT 1M;

 

Tablespace altered.

 

--查看:

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME                   FILE_ID FILE_NAME

------------------------------ ------------------------------------------------------------

SYSTEM                                 1/u01/app/oracle/oradata/cndba/system01.dbf

SYSAUX                                  3/u01/app/oracle/oradata/cndba/sysaux01.dbf

UNDOTBS1                                4/u01/app/oracle/oradata/cndba/undotbs01.dbf

USERS                                  6/u01/app/oracle/oradata/cndba/users01.dbf

DAVE                                   16/u01/app/oracle/oradata/cndba/pcndba2/dave02.dbf

DAVE                                   15/u01/app/oracle/oradata/cndba/pcndba2/dave01.dbf

 

 

--drop 表空间:

SQL> drop tablespace dave includingcontents and datafiles;

Tablespace dropped.

 

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME                   FILE_ID FILE_NAME

------------------------------ ------------------------------------------------------------

SYSTEM                                  1/u01/app/oracle/oradata/cndba/system01.dbf

SYSAUX                                  3/u01/app/oracle/oradata/cndba/sysaux01.dbf

UNDOTBS1                                4/u01/app/oracle/oradata/cndba/undotbs01.dbf

USERS                                   6/u01/app/oracle/oradata/cndba/users01.dbf

 

 

2  管理PDB的表空间

 

PDB 表空间的管理和CDB一样,只需要切换到正确的container即可。

 

 

 

SQL> select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

PDBCNDBA                       READ WRITE

PCNDBA2                        READ WRITE

 

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

 

SQL> alter session setcontainer=pcndba2;

Session altered.

 

SQL> show con_name

CON_NAME

------------------------------

PCNDBA2

 

 

SQL> col tablespace_name for a15

SQL> col file_name for a60

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME    FILE_ID FILE_NAME

--------------- ----------------------------------------------------------------------

SYSTEM                  12/u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

SYSAUX                  13 /u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

USERS                   14/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

 

SQL> CREATE TABLESPACE dave

 2    DATAFILE'/u01/app/oracle/oradata/cndba/pcndba2/dave01.dbf' SIZE 1M

 3    AUTOEXTEND ON NEXT 1M;

Tablespace created.

 

SQL> ALTER TABLESPACE dave ADD

 2    DATAFILE'/u01/app/oracle/oradata/cndba/pcndba2/dave02.dbf' SIZE 1M

 3    AUTOEXTEND ON NEXT 1M;

 

Tablespace altered.

 

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

 

TABLESPACE_NAME    FILE_ID FILE_NAME

--------------- ----------------------------------------------------------------------

SYSAUX                  13/u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

SYSTEM                  12 /u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

USERS                   14/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

DAVE                    17/u01/app/oracle/oradata/cndba/pcndba2/dave01.dbf

DAVE                    18/u01/app/oracle/oradata/cndba/pcndba2/dave02.dbf

 

SQL> DROP TABLESPACE dave INCLUDINGCONTENTS AND DATAFILES;

Tablespace dropped.

 

SQL> selecttablespace_name,file_id,file_name from dba_data_files;

TABLESPACE_NAME    FILE_ID FILE_NAME

--------------- ----------------------------------------------------------------------

SYSTEM                  12/u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

SYSAUX                  13/u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

USERS                   14/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

 

 

 

3  Undo 表空间

 

CDB中的undo 表空间只能从CDB中管理,不能从non-CDB中管理。

PDB 没有自己的undo 表空间,PDB 中直接使用CDB中的undo 表空间。

 

如果连上PDB,是看不到undo 表空间的。

 

SQL> show con_name

 

CON_NAME

------------------------------

PCNDBA2

SQL> select tablespace_name fromdba_tablespaces;

 

TABLESPACE_NAME

---------------

SYSTEM

SYSAUX

TEMP

USERS

 

 

--切换到CDB中:

SQL> alter session setcontainer=CDB$ROOT;

Session altered.

 

SQL> show con_name

 

CON_NAME

------------------------------

CDB$ROOT

 

SQL> select tablespace_name fromdba_tablespaces;

 

TABLESPACE_NAME

---------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

 

SQL> select name from v$datafile;

 

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/cndba/system01.dbf

/u01/app/oracle/oradata/cndba/sysaux01.dbf

/u01/app/oracle/oradata/cndba/undotbs01.dbf

/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf

/u01/app/oracle/oradata/cndba/users01.dbf

/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf

/u01/app/oracle/oradata/cndba/pdbcndba/system01.dbf

/u01/app/oracle/oradata/cndba/pdbcndba/sysaux01.dbf

/u01/app/oracle/oradata/cndba/pdbcndba/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/cndba/pdbcndba/example01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

 

13 rows selected.

 

--查看TEMP 表空间:

SQL> select name from v$tempfile;

 

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/cndba/temp01.dbf

/u01/app/oracle/oradata/cndba/pdbseed/pdbseed_temp01.dbf

/u01/app/oracle/oradata/cndba/pdbcndba/pdbcndba_temp01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/temp01.dbf

SQL>

 

4  Temporary 表空间

CDB 中的temporary 表空间只从在CDB中管理。

 

PDB 可以有自己的temporary表空间,也可以不用,在创建PDB的时候如果没有指定temporary表空间,那么就会公用CDB的temporary 表空间。

 

SQL> select name from v$pdbs;

 

NAME

----------------------------------------------------------------------

PDB$SEED

PDBCNDBA

PCNDBA2

 

SQL> alter session set container=pcndba2;

 

Session altered.

 

SQL> show con_name

 

CON_NAME

------------------------------

PCNDBA2

 

SQL> select file_name fromdba_data_files;

 

FILE_NAME

------------------------------------------------------------

/u01/app/oracle/oradata/cndba/pcndba2/sysaux01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/system01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/pcndba2_users01.dbf

 

 

SQL> CREATE TEMPORARY TABLESPACE temp2

 2    TEMPFILE'/u01/app/oracle/oradata/cndba/pcndba2/temp02.dbf' SIZE 5M

 3    AUTOEXTEND ON NEXT 1M;

 

Tablespace created.

 

SQL> select name from v$tempfile;

 

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/cndba/pcndba2/temp01.dbf

/u01/app/oracle/oradata/cndba/pcndba2/temp02.dbf

 

SQL> drop tablespace temp2 includingcontents and datafiles;

Tablespace dropped.

 

SQL> select name from v$tempfile;

 

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/cndba/pcndba2/temp01.dbf

 

 

5  Default Tablespaces

 

CDB 中的defaulttablespace 和 default temporary tablespace 只能在CDB中操作。

 

 

PDB 的中defaulttablespace 和 default temporary tablespace有两种修改方法:

 

(1)  使用ALTER PLUGGABLE DATABASE命令

推荐使用这种方法,法语如下:

 

CONN pdb@pdb1

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACEusers;

ALTER PLUGGABLE DATABASE DEFAULT TEMPORARYTABLESPACE temp;

 

(2)为了向后的兼容性,也可以使用ALTERDATABASE 命令修改:

CONN pdb@pdb1

ALTER DATABASE DEFAULT TABLESPACE users;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACEtemp;

 

注意:

不管使用哪种方法,在修改之前,主要要保证container是正确的。

 

 

 

 

 

 

 

--------------------------------------------------------------------------------------------

版权所有,文章禁止转载,否则追究法律责任!

 

AboutDave:

--------------------------------------------------------------------------------------------

QQ:      251097186

Email:    tianlesoftware@gmail.com

Blog:     http://blog.csdn.net/tianlesoftware

Weibo:    http://weibo.com/tianlesoftware

Twitter:  http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

Dave 的QQ群:

--------------------------------------------------------------------------------------------

注意:加群必须注明表空间和数据文件关系 | 不要重复加群

CNDBA_1: 104207940 (满)    CNDBA_2: 62697716 (满)   CNDBA_3: 283816689

CNDBA_4: 391125754   CNDBA_5: 62697850    CNDBA_6: 62697977   CNDBA_7: 142216823(满)