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