Managing Tablespace in CDB and PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> CREATE TABLESPACE CDB_TABLESPACE DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;

Tablespace created.

SQL> ALTER TABLESPACE CDB_TABLESPACE ADD DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;

Tablespace altered.

SQL> select TABLESPACE_NAME, FILE_NAME from cdb_data_files where con_id=1;

TABLESPACE_NAME      FILE_NAME
-------------------- ------------------------------------------------------------
SYSTEM               +ASM/CSHARK/DATAFILE/system1
SYSAUX               +ASM/CSHARK/DATAFILE/sysaux1
UNDOTBS1             +ASM/CSHARK/DATAFILE/undotbs1
USERS                +ASM/CSHARK/DATAFILE/users1
CDB_TABLESPACE       +ASM/CSHARK/DATAFILE/cdb_tablespace1
CDB_TABLESPACE       +ASM/CSHARK/DATAFILE/cdb_tablespace2

6 rows selected.

SQL> DROP TABLESPACE CDB_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 SHARK                          READ WRITE NO
SQL> ALTER SESSION SET CONTAINER=SHARK;

Session altered.

SQL>
SQL> show con_name

CON_NAME
------------------------------
SHARK

SQL> CREATE TABLESPACE PDB_TABLESPACE DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;

Tablespace created.

SQL> ALTER TABLESPACE PDB_TABLESPACE ADD DATAFILE '+ASM' SIZE 10M AUTOEXTEND ON NEXT 1M;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM CDB_DATA_FILES WHERE TABLESPACE_NAME='PDB_TABLESPACE';

TABLESPACE_NAME      FILE_NAME
-------------------- ------------------------------------------------------------------------------------------
PDB_TABLESPACE       +ASM/CSHARK/ED7B4B/DATAFILE/pdb_tablespace1
PDB_TABLESPACE       +ASM/CSHARK/ED7B3C/DATAFILE/pdb_tablespace2


SQL> DROP TABLESPACE PDB_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

-- Set environment for output display
SET LINESIZE 132 PAGESIZE 100
COL con_name FORMAT A15 HEADING "Container|Name"
COL tablespace_name FORMAT A15
COL fsm FORMAT 999,999,999,999 HEADING "Free|Space (MB)"
COL apm FORMAT 999,999,999,999 HEADING "Alloc|Space (MB)"

-- Compute totals for free space and allocated space
COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name

-- Query to retrieve free and allocated space for tablespaces across containers
WITH free_space AS (
    SELECT
        c1.con_id,
        cf1.tablespace_name,
        SUM(cf1.bytes) / 1024 / 1024 AS fsm
    FROM
        cdb_free_space cf1,
        v$containers c1
    WHERE
        cf1.con_id = c1.con_id
    GROUP BY
        c1.con_id, cf1.tablespace_name
),
allocated_space AS (
    SELECT
        c2.con_id,
        cd.tablespace_name,
        SUM(cd.bytes) / 1024 / 1024 AS apm
    FROM
        cdb_data_files cd,
        v$containers c2
    WHERE
        cd.con_id = c2.con_id
    GROUP BY
        c2.con_id, cd.tablespace_name
)
SELECT
    x.con_id,
    v.name AS con_name,
    x.tablespace_name,
    x.fsm,
    y.apm
FROM
    free_space x
    JOIN allocated_space y ON x.con_id = y.con_id AND x.tablespace_name = y.tablespace_name
    JOIN v$containers v ON v.con_id = x.con_id
UNION
SELECT
    vc2.con_id,
    vc2.name,
    tf.tablespace_name,
    NULL AS fsm,
    SUM(tf.bytes) / 1024 / 1024 AS apm
FROM
    v$containers vc2,
    cdb_temp_files tf
WHERE
    vc2.con_id = tf.con_id
GROUP BY
    vc2.con_id, vc2.name, tf.tablespace_name
ORDER BY
    1, 2;

set lines 300 pages 300
col name format a10
col FILE_NAME format a90
select a.name,b.FILE_ID,b.tablespace_name,b.file_name from V$CONTAINERS a , CDB_TEMP_FILES b where a.con_id=b.con_id;

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME
--------------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------------DESCRIPTION
--------------------------------------------------------------------------------------LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled