Manage Tablespaces in a CDB:
Management of tablespaces in a container database (CDB) is same as a non-CDB database. Provided you are logged in as a privileged user and pointing to the root container, the usual commands are all available.
SQL> show con_name CON_NAME ----------- CDB$ROOT SQL> select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME --------------- -------------------------------------------- USERS /u01/app/oracle/oradata/DBWR/datafile/o1_mf_ users_fvgqyy96_.dbf UNDOTBS1 /u01/app/oracle/oradata/DBWR/datafile/o1_mf_ undotbs1_fvgqyx60_.dbf SYSTEM /u01/app/oracle/oradata/DBWR/datafile/o1_mf_ system_fvgqv1n6_.dbf SYSAUX /u01/app/oracle/oradata/DBWR/datafile/o1_mf_ sysaux_fvgqxhq9_.dbf
Create tablespace & adding Datafile in CDB:
create tablespace as like as same non-cdb and adding datafile also the same.
SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs01' size 5m; Tablespace created. SQL> alter tablespace tbs01 add datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs02' size 5m; Tablespace altered. SQL> drop tablespace tbs01 including contents and datafiles; Tablespace dropped.
Manage Tablespaces in a PDB
Same tablespace management commands are available from a pluggable database (PDB), provided you are pointing to the correct container. You can connect using a common user then switch to the correct container.
SQL> conn / as sysdba Connected. SQL> alter session set container = pdb5; Session altered. SQL> show con_name CON_NAME ----------------- PDB5 SQL>
Creating Tablespace & Datafile in PDB:
create Tablespace and Datafile as like same in non-cdb.
SQL> create tablespace tbs05 datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs05' size 15m; Tablespace created. SQL> alter tablespace tbs05 add datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs06' size 15m; Tablespace altered. SQL> drop tablespace tbs05 including contents and datafiles; Tablespace dropped.
Temporary Tablespaces
Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database.
A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CDB.
SQL> select name from v$tempfile; NAME ------------- /u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf
Creating , Selecting and dropping Temporary Tablespace as like as non-cdb
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/DBWR/temp1.dbf' size 3m; Tablespace created. SQL> select name from v$tempfile; NAME ----------- /u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf /u01/app/oracle/oradata/DBWR/temp1.dbf SQL> drop tablespace temp1 including contents and datafiles; Tablespace dropped. SQL>
Undo Tablespaces
Management of the undo tablespace in a CDB is unchanged from that of a non-CDB database.
In contrast, a PDB cannot have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible.
SQL> show con_name CON_NAME ----------------------- PDB5 SQL> Select tablespace_name from dba_tablespaces; TABLESPACE_NAME ----------------------- SYSTEM SYSAUX UNDOTBS1 TEMP USERS
But we can see the datafile associated with the CDB undo tablespace.
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------- /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf SQL> SELECT name FROM v$tempfile; NAME ----------------------------------------------------------------- /u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf SQL>
Default Tablespaces
The default tablespace and default temporary tablespace for a CDB is unchanged compared to a non-CDB database.
There are a two ways to set the default tablespace and default temporary tablespace for a PDB. The ALTER PLUGGABLE DATABASE command is the recommended way.
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users; ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;
ALTER DATABASE DEFAULT TABLESPACE users; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
but both methods, you should be pointing to the appropriate container.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
