[oracle@test12c Desktop]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 19:05:10 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> show con_name
CON_NAME
——————-
CDB$ROOT
Where
CON_NAME : Displays the name of the Container to which you are connected when connected to a Consolidated Database. For non-consolidated database, it will return “Non Consolidated”.
SQL> show con_id
CON_ID
————
1
Where
CON_ID : Displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0.
Now I want to check how Name for my my pluggable database , while installation i chosen five container with prefix db_
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
db_1 3
db_2 4
db_3 5
db_4 6
db_5 7
9 rows selected.
Listener will look like the below :
SQL> conn sys/sys@localhost:1521/db_1 as sysdba
Connected.
SQL> show con_name
CON_NAME
—————-
DB_1
SQL> show con_id
CON_ID
————
3
[oracle@test12c db12c]$ mkdir test
[oracle@test12c db12c]$ chmod -R 775 test
Sqlplus / as sysdba
SQL > create pluggable database TEST admin user admin identified by admin
file_name_convert= (‘/u01/app/oracle/oradata/db12c/pdbseed/’,’/u01/app/oracle/oradata/db12c/test/’);
Pluggable database created.
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME Status
———————– ————
PDB$SEED NORMAL
TEST NEW
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
TEST 3
Now Con_id=3 , Most of Oracle Data Dictionary contains new_column called con_id , to check datafile related to new pluaggable database :
SQL> select name from v$datafile where con_id=3 ;
NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> shutdown ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup ;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
SQL>select name, open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED
SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST READ WRITE
SQL> alter pluggable database TEST close immediate ;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED
SQL > Alter pluggable database all Open;
SQL > Alter pluggable database all close ;
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=3 ;
TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3
SQL> select file_name, con_id from cdb_data_files where con_id=3 ;NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf
SQL> select file_name, con_id from cdb_temp_files where con_id=3;
FILE_NAME CON_ID
——————————————
/u01/app/oracle/oradata/db12c/gls/pdbseed_temp01.dbf 3
If you do the below query to create tablespace, it will not be created under TEST database, therefore it will be created on root :
SQL > create tablespace cdata datafile ‘/u01/app/oracle/oradata/db12c/gls/test.dbf’ SIZE 30M;
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSTEM 2
TEMP 2
SYSAUX 2
SYSTEM 3
SYSAUX 3
TEMP 3
12 rows selected.
SQL> create temporary tablespace root_temp tempfile ‘/u01/app/oracle/oradata/db12c/temp_01.dbf’ SIZE 30M;
If you need to create Tablespace in pluggable database follow the below , you have to options
- connect to pluggable database in our case test using tnsnames.ora or easy connect
- connect sys/sys@localhost:1521/test
- alter session command line
- alter session set container=
SQL> alter session set container=TEST;Session altered.
SQL> create tablespace pdb_test datafile ‘/u01/app/oracle/oradata/db12c/TEST/test_pdb.dbf’ SIZE 20M;
Tablespace created.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3
PDB_TEST 3
Reference :
1- oracle Documentation Here
2- Oracle Documentation Here