Oracle 12c CDB and PDB table space management and configuration instructions

Note: This article declined to reprint.

1 managing table spaces in a CDB

CDB table space management and NON-CDB table space management, CDB also has an instance, specified at the time of installation.

-- To view a table space:

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

-- Toggle 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

-- table Space Specific operations examples

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.

-- View:

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 Table Space:

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 table space for managing PDB

PDB table space Management and CDB, just need to switch to the correct 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 Table Space

The undo tablespace in CDB can only be managed from CDB and cannot be managed from NON-CDB.

The PDB does not have its own undo table space, and the undo table space in the CDB is used directly in the PDB.

If you are connected to the PDB, you cannot see the Undo table space.

Sql> Show Con_name

Con_name

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

PCNDBA2

sql> Select Tablespace_name fromdba_tablespaces;

Tablespace_name

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

SYSTEM

Sysaux

TEMP

USERS

-- Switch to 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

Rows selected.

-- To View the temp table space:

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 table space

The temporary table space in CDB is only managed from the CDB.

The PDB can have its own temporary table space, or it can not, if the PDB is created without specifying the temporary table space, then the temporary tablespace of the CDB is common.

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

The Defaulttablespace and default temporary tablespace in CDB can only be operated in CDB.

There are two ways to modify the Defaulttablespace and default temporary tablespace in the PDB:

(1) using the ALTER Pluggable database command

It is recommended to use this method in French as follows:

CONN [email protected]

ALTER pluggable DATABASE DEFAULT tablespaceusers;

ALTER pluggable DATABASE DEFAULT temporarytablespace temp;

(2) For backwards compatibility, you can also use the Alterdatabase command to modify:

CONN [email protected]

ALTER DATABASE DEFAULT tablespace users;

ALTER DATABASE DEFAULT Temporary tablespacetemp;

Attention:

Regardless of which method you use, it is important to ensure that container is correct before you modify it.

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

All rights reserved, the article prohibits reprint, otherwise investigates the legal liability!

Aboutdave:

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

qq:251097186

Email: [email protected]

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's QQ Group:

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

Note: Add group must indicate tablespace and data file relationship | Do not repeat the addition group

cndba_1:104207940 (Full) cndba_2:62697716 (full) cndba_3:283816689

cndba_4:391125754 cndba_5:62697850 cndba_6:62697977 cndba_7:142216823 (full)

Oracle 12c CDB and PDB table space management and configuration instructions