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