Creating UNDO tablespace on root is fine but creating it on PDB, it got created successfully but yet NOT. Witht he understanding all PDBs sharing the same UNDO tablespace at the root, I would expect that accidentally creating the UNDO tablespace while in a PDB container would fail but this is not the case.
Scenario 1: Creating UNDO at root level.
SQL> col file_name format a60
SQL> set pagesize 120
SQL> set linesize 1000
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)
2 max_mb from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces) order by file_id;
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
---------- ------------------------------------------------------------ ---------- --------- --- ----------
1 /u01/data/ORCL/datafile/o1_mf_system_8xy1y65o_.dbf 790 AVAILABLE YES 32767.98
3 /u01/data/ORCL/datafile/o1_mf_sysaux_8xy1tly9_.dbf 4500 AVAILABLE YES 32767.98
4 /u01/data/ORCL/datafile/o1_mf_undotbs1_8xy20ykp_.dbf 235 AVAILABLE YES 32767.98
6 /u01/data/ORCL/datafile/o1_mf_users_8xy20vdz_.dbf 5 AVAILABLE YES 32767.98
11 /u01/data/ORCL/datafile/vpxadmin.dbf 1024 AVAILABLE YES 32767.98
12 /u01/data/ORCL/datafile/vcdman.dbf 1024 AVAILABLE YES 32767.98
15 /u01/data/ORCL/datafile/imds.dbf 1024 AVAILABLE YES 32767.98
7 rows selected.
SQL> select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
2 df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
3 from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
4 dba_data_files group by tablespace_name) df,(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace from dba_free_space
5 6 group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 4285 215 4500 5
UNDOTBS1 22 213 235 91
VPXADMIN 1 1023 1024 100
VCDMAN_TBSP 1 1023 1024 100
USERS 1 4 5 80
SYSTEM 787 3 790 0
IMDS_DATEN 4 1020 1024 100
7 rows selected.
SQL> create undo tablespace undotb02 datafile '/u01/data/ORCL/datafile/undo02_test.dbf' size 50M autoextend on;
Tablespace created.
SQL> select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
2 3 from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
4 dba_data_files group by tablespace_name) df,(select tablespace_name,
5 round(sum(bytes) / 1048576) FreeSpace from dba_free_space
6 group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 4285 215 4500 5
UNDOTBS1 22 213 235 91
VPXADMIN 1 1023 1024 100
VCDMAN_TBSP 1 1023 1024 100
USERS 1 4 5 80
SYSTEM 787 3 790 0
IMDS_DATEN 4 1020 1024 100
UNDOTB02 2 48 50 96
8 rows selected.
SQL> col file_name format a60
SQL> set pagesize 120
SQL> set linesize 1000
SQL> select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)
2 max_mb from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces) order by file_id;
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
---------- ------------------------------------------------------------ ---------- --------- --- ----------
1 /u01/data/ORCL/datafile/o1_mf_system_8xy1y65o_.dbf 790 AVAILABLE YES 32767.98
3 /u01/data/ORCL/datafile/o1_mf_sysaux_8xy1tly9_.dbf 4500 AVAILABLE YES 32767.98
4 /u01/data/ORCL/datafile/o1_mf_undotbs1_8xy20ykp_.dbf 235 AVAILABLE YES 32767.98
6 /u01/data/ORCL/datafile/o1_mf_users_8xy20vdz_.dbf 5 AVAILABLE YES 32767.98
11 /u01/data/ORCL/datafile/vpxadmin.dbf 1024 AVAILABLE YES 32767.98
12 /u01/data/ORCL/datafile/vcdman.dbf 1024 AVAILABLE YES 32767.98
15 /u01/data/ORCL/datafile/imds.dbf 1024 AVAILABLE YES 32767.98
16 /u01/data/ORCL/datafile/undo02_test.dbf 50 AVAILABLE YES 32767.98
8 rows selected.
Scenario 2: Creating UNDO at pdb level.
SQL> SELECT CDB FROM V$DATABASE;
CDB
---
YES
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 PDBVCDB READ WRITE NO
5 PDBNEWDB READ WRITE NO
SQL> alter session set container=PDBNEWDB;
Session altered.
SQL>
select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
dba_data_files group by tablespace_name) df,(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace from dba_free_space
group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 607 33 640 5
SYSTEM 255 5 260 2
SQL> col file_name format a60
SQL> select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)
max_mb from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces) order by file_id; 2
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
---------- ------------------------------------------------------------ ---------- --------- --- ----------
18 /u01/data/pdbnewdb/ORCL/0943022849962048E053CB41830ABCA3/dat 260 AVAILABLE YES 32767.98
afile/o1_mf_system_b7wb28m7_.dbf
19 /u01/data/pdbnewdb/ORCL/0943022849962048E053CB41830ABCA3/dat 640 AVAILABLE YES 32767.98
afile/o1_mf_sysaux_b7wb28w6_.dbf
SQL>
Created successfully!
SQL> create undo tablespace undotbs02 datafile '/u01/data/pdbnewdb/ORCL/undo02_test.dbf' size 50M autoextend on;
Tablespace created.
But the new UNDO tablespace was nowhere to be found.
SQL> select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)
max_mb from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces) order by file_id; 2
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
---------- ------------------------------------------------------------ ---------- --------- --- ----------
18 /u01/data/pdbnewdb/ORCL/0943022849962048E053CB41830ABCA3/dat 260 AVAILABLE YES 32767.98
afile/o1_mf_system_b7wb28m7_.dbf
19 /u01/data/pdbnewdb/ORCL/0943022849962048E053CB41830ABCA3/dat 640 AVAILABLE YES 32767.98
afile/o1_mf_sysaux_b7wb28w6_.dbf
SQL> select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
2 df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
3 from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
4 dba_data_files group by tablespace_name) df,(select tablespace_name,
5 round(sum(bytes) / 1048576) FreeSpace from dba_free_space
6 group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 607 33 640 5
SYSTEM 255 5 260 2
SQL> !ls -las /u01/data/pdbnewdb/ORCL/undo02_test.dbf
ls: /u01/data/pdbnewdb/ORCL/undo02_test.dbf: No such file or directory
SQL> !ls -las /u01/data/pdbnewdb/ORCL/*
total 24
8 drwxrwx--- 3 oracle oracle 4096 Dec 2 2014 .
8 drwxrwx--- 3 oracle oracle 4096 Dec 2 2014 ..
8 drwxrwx--- 2 oracle oracle 4096 Dec 2 2014 datafile
SQL>
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)
max_mb from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces) order by file_id; 2
FILE_ID FILE_NAME MB STATUS AUT MAX_MB
---------- ------------------------------------------------------------ ---------- --------- --- ----------
1 /u01/data/ORCL/datafile/o1_mf_system_8xy1y65o_.dbf 790 AVAILABLE YES 32767.98
3 /u01/data/ORCL/datafile/o1_mf_sysaux_8xy1tly9_.dbf 6030 AVAILABLE YES 32767.98
4 /u01/data/ORCL/datafile/o1_mf_undotbs1_8xy20ykp_.dbf 165 AVAILABLE YES 32767.98
6 /u01/data/ORCL/datafile/o1_mf_users_8xy20vdz_.dbf 5 AVAILABLE YES 32767.98
11 /u01/data/ORCL/datafile/vpxadmin.dbf 1024 AVAILABLE YES 32767.98
12 /u01/data/ORCL/datafile/vcdman.dbf 1024 AVAILABLE YES 32767.98
6 rows selected.
SQL> select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
2 df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
3 from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
4 dba_data_files group by tablespace_name) df,(select tablespace_name,
5 round(sum(bytes) / 1048576) FreeSpace from dba_free_space
6 group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 5735 295 6030 5
UNDOTBS1 18 147 165 89
VPXADMIN 1 1023 1024 100
VCDMAN_TBSP 1 1023 1024 100
USERS 1 4 5 80
SYSTEM 788 2 790 0
6 rows selected.
SQL>
No new UNDO that I was trying/accidentally created while I was in a PDB.
When the current container is a PDB, an attempt to create an undo tablespace fails without an error as per Oracle Documentation
ReplyDelete(http://docs.oracle.com/database/121/ADMIN/E41484-15.pdf).
Vasilios Moschos