8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : 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.
Related articles.
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)
Setup
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>
Metadata Clone
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
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>
For more information see:
- Excluding Data When Cloning a PDB
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)
Hope this helps. Regards Tim...