Oracle Pluggable Database
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Data Dictionary Objects
CDB_FILE$ DBA_PDBS PDB$SEED
CDB_LOCAL_ADMINAUTH$ DBA_PDB_HISTORY PDB_HISTORY$
CDB_PDB_SAVED_STATES DBA_PDB_SAVED_STATES PDB_PLUG_IN_VIOLATIONS
CDB_RESOURCE_PLAN$ DBMS_PDB PDB_SPFILE$
CDB_RESOURCE_PLAN_DIRECTIVE$ DBMS_PDB_EXEC_SQL V$CONTAINERS
CDB_SERVICE$ GV$CONTAINERS V$PDBS
CDB_HIST_PDB_INSTANCE GV$PDBS V$PDB_INCARNATION
CDB_PDBS GV$PDB_INCARNATION WRI$_ADV_ADDM_PDBS
CDB_PDB_HISTORY GV$SESSIONS_COUNT WRM$_PDB_INSTANCE
CONTAINERS INT$DBA_PDB_SAVED_STATES XDB$CDBPORTS
DBA_HIST_PDB_INSTANCE PDB_ALERT$  
Roles
CDB_DBA    
System Privileges
CREATE PLUGGABLE DATABASE    
 
Create Pluggable Database
Creating a PDB from PDB$SEED CREATE PLUGGABLE DATABASE <pdb_name>
[AS APPLICATION CONTAINER]

ADMIN USER <administrative_user> IDENTIFIED BY <password>
FILE_NAME_CONVERT = ('<pdbseed_path>', '<new_pdb_path>')
ROLES = (<comma_separated_role_list>)
DEFAULT TABLESPACE <tablespace_name> [DATAFILE <datafile_tempfile_spec>]
EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>
STORAGE <UNLIMITED | MAXSIZE | MAX_SHARED_TEMP_SIZE> <UNLIMITED | bytes>]
conn / as sysdba

CREATE PLUGGABLE DATABASE pdbprod
ADMIN USER uwclass IDENTIFIED BY uwclass
FILE_NAME_CONVERT = ('/pdbseed/', '/pdbprod/');

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status
FROM v$pdbs v, cdb_pdbs d
WHERE v.guid = d.guid
ORDER BY 1;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         READ WRITE NO         NORMAL
PDBTEST                        MOUNTED    n/a        NORMAL
PDBPROD                        MOUNTED    n/a        NEW

-- see ALTER PLUGGABLE DATABASE below to continue this demo
Creating a PDB from an existing PDB CREATE PLUGGABLE DATABASE <new pdb> FROM <existing_pdb>
FILE_NAME_CONVERT=('<file_path>', '<file_path');
conn / as sysdba

SELECT name, open_mode
FROM v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBDEV                         READ WRITE
PDBTEST                        MOUNTED

ALTER PLUGGABLE DATABASE pdbdev CLOSE;

SELECT name, open_mode
FROM v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBDEV                         MOUNTED
PDBTEST                        MOUNTED

ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY;

SELECT name, open_mode
FROM v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBDEV                         READ ONLY
PDBTEST                        MOUNTED

CREATE PLUGGABLE DATABASE pdbprod FROM pdbdev
FILE_NAME_CONVERT = ('/pdbdev/', '/pdbprod/');

SELECT name, open_mode
FROM v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBDEV                         READ ONLY
PDBTEST                        MOUNTED
PDBPROD                        MOUNTED

ALTER PLUGGABLE DATABASE pdbdev CLOSE;
ALTER PLUGGABLE DATABASE pdbdev OPEN;
ALTER PLUGGABLE DATABASE pdbprod OPEN;
Creating an Application Container  
 
Creating an Application Seed Container  
CREATE PLUGGABLE DATABASE AS SEED ADMIN USER MX_ADMIN IDENTIFIED BY "password" USER_TABLESPACES=('MX_DATA','MX_INDEX','MX_LARGE_DATA', 'MX_LARGE_INDEX'); ALTER PLUGGABLE DATABASE MX$SEED OPEN;
 
Alter Pluggable Database
Application Clause ALTER PLUGGABLE DATABASE <application_clauses>
 
Change State ALTER PLUGGABLE DATABASE <change_state>
 
Change State from Root ALTER PLUGGABLE DATABASE <change_state_from_root_>
 
Datafile Clause ALTER PLUGGABLE DATABASE <datafile_clauses>
 
Recovery Clauses ALTER PLUGGABLE DATABASE <recovery_clauses>
 
Save States Clauses ALTER PLUGGABLE DATABASE <pdb_name> SAVE STATE INSTANCES = <ALL | pdb_name>;
 
Settings Clauses ALTER PLUGGABLE DATABASE <settings_clauses>
 
Unplug Set Default Edition ALTER PLUGGABLE DATABASE <pdb_names> UNPLUG INTO '<file_name>' '<pdb_name>' DEFAULT EDITION = <edition_name>;
ALTER PLUGGABLE DATABASE pdbdev UNPLUG INTO '/home/oracle/pdbdev.xml'

(or)

ALTER PLUGGABLE DATABASE pdbdev UNPLUG INTO '/home/oracle/pdbdev.xml'
'PDBDEV' DEFAULT EDITION = demo_ed;
Opening and Closing a PDB ALTER PLUGGABLE DATABASE <pdb_name> [OPEN | CLOSE];
ALTER PLUGGABLE DATABASE pdbprod OPEN;

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status
FROM v$pdbs v, cdb_pdbs d
WHERE v.guid = d.guid
ORDER BY 1;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         READ WRITE NO         NORMAL
PDBTEST                        MOUNTED    n/a        NORMAL
PDBPROD                        READ WRITE NO         NORMAL
Changing the status of a PDB ALTER PLUGGABLE DATABASE <pdb_name> [OPEN | CLOSE];
ALTER PLUGGABLE DATABASE pdbprod OPEN;

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status
FROM v$pdbs v, cdb_pdbs d
WHERE v.guid = d.guid
ORDER BY 1;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         READ WRITE NO         NORMAL
PDBTEST                        MOUNTED    n/a        NORMAL
PDBPROD                        READ WRITE NO         NORMAL
Altering a pluggable database CREATE PLUGGABLE DATABASE <pdb_name>
ADMIN USER <administrative_user> IDENTIFIED BY <password>
FILE_NAME_CONVERT = ('<pdbseed_path>', '<new_pdb_path>')
ROLES = (<comma_separated_role_list>)
DEFAULT TABLESPACE <tablespace_name> [DATAFILE <datafile_tempfile_spec>]
EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>
STORAGE <UNLIMITED | MAXSIZE | MAX_SHARED_TEMP_SIZE> <UNLIMITED | bytes>]
col con_name format a10
col restricted format a10

SELECT v.name, v.open_mode, v.restricted, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         MOUNTED    NO         NORMAL

ALTER PLUGGABLE DATABASE pdbdev OPEN;

SELECT v.name, v.open_mode, v.restricted, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         READ WRITE NO         NORMAL

SELECT * FROM dba_pdbs;

-- 12.1.0.1 version
-- SELECT * FROM cdb$view(dba_pdbs);
-- 12.1.0.2 version
SELECT * FROM containers(dba_pdbs);

-- 12.1.0.1 version
-- SELECT * FROM cdb$view(cdb_pdbs);
-- 12.1.0.2 version
SELECT * FROM containers(cdb_pdbs);

SELECT table_name, tablespace_name, con_id
FROM dba_tables
WHERE rownum < 6;
SELECT table_name, tablespace_name, con_id
                                    *
ERROR at line 1:
ORA-00904: "CON_ID": invalid identifier

SELECT table_name, tablespace_name, con_id
FROM cdb$view(dba_tables)
WHERE rownum < 6;
 
Drop Pluggable Database
Dropping a pluggable database DROP PLUGGABLE DATABASE <pdb_name> <KEEP | INCLUDING> DATAFILES;
SELECT name, open_mode
FROM v$containers
ORDER BY 1;

NAME                           OPEN_MODE
------------------------------ ----------
CDB$ROOT                       READ WRITE
PDB$SEED                       READ ONLY
PDBDEV                         READ WRITE
PDBTEST                        MOUNTED
PDBPROD                        READ WRITE

DROP PLUGGABLE DATABASE pdbprod INCLUDING DATAFILES;
*
ERROR at line 1:
ORA-65025: Pluggable database PDBPROD is not closed on all instances.

ALTER PLUGGABLE DATABASE pdbprod CLOSE;

DROP PLUGGABLE DATABASE pdbprod INCLUDING DATAFILES;
 
 
   
Explore Users -- continuing in the PDBDEV container

desc dba_users

desc cdb_users

SELECT username, account_status, lock_date, expiry_date
FROM dba_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
WHERE username like 'UW%'
ORDER BY 1;

ALTER PLUGGABLE DATABASE orabase OPEN;

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBDEV                         MOUNTED    n/a        NORMAL
ORADEV                         READ WRITE NO         NORMAL

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
WHERE username like 'UW%'
ORDER BY 1;
Getting connected  
 
Create Pluggable Database From Clone
 
PDB Creation with UNPLUG/PLUG
Unplug Clause
Unplug Clause

Create from unplugged		 ALTER PLUGGABLE DATABASE <pdb_name> UNPLUG INTO '<file_name>';
conn sys@pdbtest as sysdba

-- from inside a pdb

ALTER PLUGGABLE DATABASE pdbtest CLOSE;

ALTER PLUGGABLE DATABASE pdbtest UNPLUG INTO 'pdbtest.xml';
ALTER PLUGGABLE DATABASE pdbtest UNPLUG INTO 'pdbtest.xml'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

-- now correctly from inside pdb$root

conn / as sysdba

ALTER PLUGGABLE DATABASE pdbtest CLOSE;

ALTER PLUGGABLE DATABASE pdbtest UNPLUG INTO 'pdbtest.xml';

-- ftp files to new server
-- the xml file is located at $ORACLE_HOME/database

SQL> CREATE PLUGGABLE DATABASE pdbprod USING 'pdbtest.xml'
  2  SOURCE_FILE_NAME_CONVERT = ('\pdbtest\', '\pdbprod\')
  3  NOCOPY
  4  STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 20M)
  5  TEMPFILE REUSE;
CREATE PLUGGABLE DATABASE pdbprod USING 'pdbtest.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

CREATE PLUGGABLE DATABASE pdbprod AS CLONE USING 'pdbtest.xml'
FILE_NAME_CONVERT = ('\pdbtest\', '\pdbprod\')
STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 20M);

SELECT con_id, name, open_mode
FROM v$containers
ORDER BY con_id;

SELECT tablespace_name, file_name
FROM cdb_data_files
WHERE con_id = 5
UNION
SELECT tablespace_name, file_name
FROM cdb_temp_files
WHERE con_id = 5;
 
ALTER PLUGGABLE DATABASE
Settings Clauses
Default Edition ALTER PLUGGABLE DATABASE <pdb_name>
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

CREATE EDITION demo_ed;

ALTER PLUGGABLE DATABASE pdbdev DEFAULT EDITION = demo_ed;
Default Tablespace ALTER PLUGGABLE DATABASE <pdb_name> DEFAULT TABLESPACE <tablespace_name>;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

SELECT tablespace_name
FROM dba_tablespaces;

ALTER PLUGGABLE DATABASE orabase DEFAULT TABLESPACE oradata;
Default Tablespace Type ALTER PLUGGABLE DATABASE <pdb_name> SET DEFAULT <BIGFILE | SMALLFILE> TABLESPACE;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

ALTER PLUGGABLE DATABASE orabase SET DEFAULT BIGFILE TABLESPACE;
Default Temporary Tablespace ALTER PLUGGABLE DATABASE <pdb_name> DEFAULT TEMPORARY TABLESPACE <tablespace_or_group>;;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

ALTER PLUGGABLE DATABASE orabase DEFAULT TEMPORARY TABLESPACEtemp_grp;
Rename ALTER PLUGGABLE DATABASE <pdb_name> RENAME GLOBAL_NAME TO <database>[.domain];
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

SELECT *
FROM global_name;

-- shutdown
-- reopen in restricted mode

ALTER PLUGGABLE DATABASE pdbdev RENAME GLOBAL_NAME TO pdb$dev;
Set Timezone only in a PDB: Must be done while connected to a PDB ... cannot be done in CDB$ROOT ALTER PLUGGABLE DATABASE [<pdb_name>] SET TIME_ZONE = '<timezone_region>';
conn sys@pdbdev as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

SELECT value$
FROM props$
WHERE name = 'DBTIMEZONE';

ALTER PLUGGABLE DATABASE pdbdev SET TIME_ZONE = 'Japan';
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

ALTER SESSION SET CONTAINER = pdbdev;

SELECT value$
FROM props$
WHERE name = 'DBTIMEZONE';

ALTER PLUGGABLE DATABASE pdbdev SET TIME_ZONE = 'Japan';

SELECT value$
FROM props$
WHERE name = 'DBTIMEZONE';

ALTER PLUGGABLE DATABASE pdbdev SET TIME_ZONE = '00:00';

SELECT value$
FROM props$
WHERE name = 'DBTIMEZONE';
Set Timezone only in a PDB: Must be done while connected to a PDB ... cannot be done in CDB$ROOT ALTER PLUGGABLE DATABASE <pdb_name> SET TIME_ZONE = '< | -> HH:MI';
See Demo Above
Database Files Rename ALTER PLUGGABLE DATABASE <pdb_name> RENAME FILE '<file_name>' TO '<file_name>';
conn sys@pdbprod as sysdba

ALTER PLUGGABLE DATABASE CLOSE;

ALTER PLUGGABLE DATABASE pdbprod
RENAME FILE
'C:\APP\ORACLE\ORADATA\ORABASE\PDBPROD\PDBSEED_TEMP012014-12-04_10-11-17-PM.DBF' TO 'C:\APP\ORACLE\ORADATA\ORABASE\PDBPROD\PDBPROD_TEMP.DBF';

-- the original file remains in the file system and must be manually removed.
Database Files Create Datafile ALTER PLUGGABLE DATABASE <pdb_name>
CREATE DATAFILE <'<datafile_name>' | filenumber> [AS <file_specification> | NEW]
TBD
Database Files Alter Datafile ALTER PLUGGABLE DATABASE <pdb_name>
DATAFILE <'<datafile_name>' | filenumber> <ONLINE | OFFLINE [FOR DROP] | RESIZE <size_clause> | <autoextend_clause> | END BACKUP>;
TBD
Database Files Alter Tempfile ALTER PLUGGABLE DATABASE <pdb_name>
TEMPFILE <'<datafile_name>' | filenumber> <RESIZE | <autoextend_clause> | DROP [INCLUDING DATAFILES] | ONLINE | OFFLINE>;
TBD
Database Files Move Datafile ALTER PLUGGABLE DATABASE <pdb_name> <move_datafile_clause>
MOVE DATAFILE <'<file_name>' | ASM_file_name | filenumber>
TO '<file_name | ASM_file_name>' [REUSE] [KEEP]
TBD
Supplemental Logging ALTER PLUGGABLE DATABASE <pdb_name> <ADD | DROP>
SUPPLEMENTAL LOG <DATA | <supplemental_id_key_clause> | <supplemental_plsql_clause>>
ALTER PLUGGABLE DATABASE pdbdev ADD SUPPLEMENTAL LOG DATA;
Storage ALTER PLUGGABLE DATABASE <pdb_name> STORAGE <UNLIMITED | <size_clause>>;
ALTER PLUGGABLE DATABASE pdbdev STORAGE UNLIMITED;
Storage ALTER PLUGGABLE DATABASE <pdb_name> STORAGE (MAX_SHARED_TEMP_SIZE <UNLIMITED | <size_clause>);
ALTER PLUGGABLE DATABASE pdbdev STORAGE (MAX_SHARED_TEMP_SIZE UNLIMITED);
Datafile Clause
Datafile Online / Offline ALTER PLUGGABLE DATABASE <pdb_name> DATAFILE ALL <ONLINE | OFFLINE>;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

ALTER PLUGGABLE DATABASE pdbdev CLOSE;

SELECT open_mode
FROM v$pdbs;

SELECT file#, status, enabled
FROM v$datafile;

ALTER PLUGGABLE DATABASE pdbdev DATAFILE ALL OFFLINE;
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

ALTER PLUGGABLE DATABASE pdbdev DATAFILE ALL ONLINE;

SELECT file#, status, enabled
FROM v$datafile;

ALTER PLUGGABLE DATABASE pdbdev OPEN;
Recovery Clauses
Backup and Recover a PDB ALTER PLUGGABLE DATABASE <pdb_name> RECOVER [AUTOMATIC] [FROM '<location>'  DATABASE;
-- there are also RECOVERY TABLESPACE, DATAFILE, LOGFILE, and CONTINUE variants.
ALTER PLUGGABLE DATABASE pdbdev RECOVER AUTOMATIC DATABASE;
Backup and Recover a PDB ALTER PLUGGABLE DATABASE <pdb_name> <BEGIN | END> BACKUP;
conn / as sysdba

ALTER PLUGGABLE DATABASE pdbdev BEGIN BACKUP;
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database

ALTER SESSION SET CONTAINER = PDBDEV;

ALTER PLUGGABLE DATABASE pdbdev BEGIN BACKUP;
*
ERROR at line 1:
ORA-01123: cannot start online backup; media recovery not enabled

conn / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE pdbdev OPEN;

ALTER SESSION SET CONTAINER = PDBDEV;

ALTER PLUGGABLE DATABASE pdbdev BEGIN BACKUP;
ALTER PLUGGABLE DATABASE pdbdev END BACKUP;
Change State Clauses
Open Read Write ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ [WRITE] [UPGRADE] [RESTRICTED] [FORCE]
[INSTANCES = <('<instance_name>' | <ALL [EXCEPT ('<instance_name'>)] >);
ALTER PLUGGABLE DATABASE pdbdev OPEN;
Open Read Only ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ ONLY <instances_clause>;
ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY;
Open Resetlogs ALTER PLUGGABLE DATABASE <pdb_name> OPEN RESETLOGS <instances_clause>;
ALTER PLUGGABLE DATABASE pdbdev OPEN RESETLOGS;
Open/Close a single PDB ALTER PLUGGABLE DATABASE <pdb_name> CLOSE [IMMEDIATE];
ALTER PLUGGABLE DATABASE orabase CLOSE;
Open/Close all PDBs ALTER PLUGGABLE DATABASE ALL CLOSE [IMMEDIATE];
ALTER PLUGGABLE DATABASE ALL CLOSE;
Open PDB in READ ONLY mode ALTER PLUGGABLE DATABASE <pdb_name> <CLOSE | OPEN> READ ONLY;
conn / as sysdba

-- must be closed
ALTER PLUGGABLE DATABASE orabase OPEN READ ONLY;
Open PDB in READ ONLY RESTRICTED mode ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ ONLY RESTRICTED;
conn / as sysdba

ALTER PLUGGABLE DATABASE orabase CLOSE;

ALTER PLUGGABLE DATABASE orabase OPEN READ ONLY RESTRICTED;
 
DROP PLUGGABLE DATABASE
Drop a pluggable database from its container DROP PLUGGABLE DATABASE <pdb_name> [INCLUDING DATAFILES];
ALTER PLUGGABLE DATABASE orabase CLOSE;

DROP PLUGGABLE DATABASE orabase INCLUDING DATAFILES;
 
Common Objects
This demo shows undocumented behaviour that, for all I know, might have a negative impact on your relationship with Oracle Support so use this for personal education purposes only and do not replicate this in any supported environment.

You will see from the demo, at right, that what I have done has corrupted the data dictionary. The object appears to be on PDB 3 ... but it is not. A good lesson for those tampering with what is undocumented and unsupported. Interestingly enough, in this case, a database reboot cleared the issue so it, in some manner, related to internal caching.		 CONTAINER_DATA SHARING=<object | metadata>
/* Enable container_data sharing=object */
alter session set "_ORACLE_SCRIPT"=TRUE;

-- create an object wih sharing = OBJECT
CREATE OR REPLACE VIEW morgan1
CONTAINER_DATA SHARING=object
(DBID, INSTANCE_NUMBER, STARTUP_TIME, PARALLEL, VERSION,
DB_NAME, INSTANCE_NAME, HOST_NAME, LAST_ASH_SAMPLE_ID,
PLATFORM_NAME, CON_ID)
AS
SELECT dbid, instance_number, startup_time, parallel, version,
db_name, instance_name, host_name, last_ash_sample_id,
platform_name, con_dbid_to_id(dbid) con_id
FROM wrm$_database_instance;

-- create an object sharing = METADATA
CREATE OR REPLACE VIEW morgan2
CONTAINER_DATA SHARING=metadata
(DBID, INSTANCE_NUMBER, STARTUP_TIME, PARALLEL, VERSION,
DB_NAME, INSTANCE_NAME, HOST_NAME, LAST_ASH_SAMPLE_ID,
PLATFORM_NAME, CON_ID)
AS
SELECT dbid, instance_number, startup_time, parallel, version,
db_name, instance_name, host_name, last_ash_sample_id,
platform_name, con_dbid_to_id(dbid) con_id
FROM wrm$_database_instance;

col view_name format a30

SQL> SELECT con_id, view_name
  2  FROM cdb_views
  3  WHERE view_name LIKE '%MORGAN%';

    CON_ID VIEW_NAME
---------- ------------
         1 MORGAN2
         1 MORGAN1
         3 MORGAN2
         3 MORGAN1

alter session set "_ORACLE_SCRIPT"=FALSE;

SQL> drop view morgan2;

View dropped.

SQL> SELECT con_id, view_name
  2  FROM cdb_views
  3  WHERE view_name LIKE '%MORGAN%';

    CON_ID VIEW_NAME
---------- ------------
         3 MORGAN2
         3 MORGAN1

Related Topics
Built-in Functions
Built-in Packages
Application Containers
Container Database
CONTAINERS
DBMS_PDB
DBMS_PREUP
DBMS_SQL
DBMS_SERVICE
DBMS_SYNC_REFRESH
Dynamic Performance Views
Editions
PDB$SEEDs
Tablespaces
Tablespace Groups
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx