Clone Metadata Only while creating a new PDB in Oracle Database 12c Release 1 (12.1.0.2)

Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)

The 12.1.0.2 patchset introduced the ability to do a metadata-only clone. Adding the NO DATA clause when cloning a PDB signifies that only the metadata for the user-created objects should be cloned, not the data in the tables and indexes.

Create a clean PDB, then add a new user and a test table with some data.



CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb10 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb10/');


ALTER PLUGGABLE DATABASE pdb10 OPEN;


ALTER SESSION SET CONTAINER = pdb10;


CREATE TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb10/users01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;


CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;


CREATE TABLE test.t1 (  id NUMBER);
INSERT INTO test.t1 VALUES (1);
COMMIT;

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         1

SQL>





#################################################################################################
Perform a metadata-only clone of the PDB using the NO DATA clause.

CONN / AS SYSDBA


ALTER PLUGGABLE DATABASE pdb10 CLOSE;

ALTER PLUGGABLE DATABASE pdb10 OPEN READ ONLY;



CREATE PLUGGABLE DATABASE pdb11 FROM pdb10
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb10/','/u01/app/oracle/oradata/cdb1/pdb11/')
  NO DATA;



ALTER PLUGGABLE DATABASE pdb11 OPEN READ WRITE;



-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ WRITE;



Checking the contents of the test table in the new PDB show the table is present, but it is empty.



CONN / AS SYSDBA


ALTER SESSION SET CONTAINER = pdb11;

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         0


SQL>
#################################################################################################


Restrictions;

The NO DATA clause is only valid is the the source PDB doesn't contain any of the following.

    Index-organized tables
    Advanced Queue (AQ) tables
    Clustered tables
    Table clusters

If it does, you will get the following type of error.

SQL> CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb1/pdb11/')
   NO DATA;
CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
*
ERROR at line 1:
ORA-65161: Unable to create pluggable database with no data

SQL>