For the latter, there are some techniques to convert a schema in a PDB.<\/p>\n
- \n
- export\/import (obviously), with eventually Golden Gate to do it online<\/li>\n
- Transportable tablespaces (if the schemas follow strict 1-to-1 tablespace separation<\/li>\n
- partial PDB cloning<\/li>\n<\/ul>\n
We will focus on the last one for this blog post.<\/p>\n
Situation<\/strong><\/p>\n
![\"\"](\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2020\/05\/pdb_partial_clone-1024x533.png\")
<\/a><\/p>\nHere we have a PDB with some schemas, each of them has a dedicated tablespace, but accidentally, two of them have also some objects on a common tablespace.<\/p>\n
This happens frequently when all the users have quota on the default database tablespace and they do not have necessarily a personal default tablespace.<\/p>\n
connect \/ as sysdba\r\nCREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdbadmin IDENTIFIED BY manager;\r\nALTER PLUGGABLE DATABASE PDB1OPEN;\r\nALTER SESSION SET CONTAINER=PDB1;\r\n\r\nCREATE TABLESPACE ABC DATAFILE SIZE 50M;\r\nCREATE TABLESPACE DEF DATAFILE SIZE 50M;\r\nCREATE TABLESPACE GHI DATAFILE SIZE 50M;\r\n\r\nCREATE USER ABC IDENTIFIED BY ABC DEFAULT TABLESPACE ABC QUOTA UNLIMITED ON ABC;\r\nCREATE USER DEF IDENTIFIED BY DEF DEFAULT TABLESPACE DEF QUOTA UNLIMITED ON DEF;\r\nCREATE USER GHI IDENTIFIED BY GHI DEFAULT TABLESPACE GHI QUOTA UNLIMITED ON GHI;\r\n\r\nALTER USER ABC QUOTA UNLIMITED ON DATA;\r\nALTER USER DEF QUOTA UNLIMITED ON DATA;\r\nALTER USER GHI QUOTA UNLIMITED ON DATA;\r\n\r\nGRANT CREATE SESSION, ALTER SESSION, CREATE TABLE TO ABC, DEF, GHI;\r\n\r\n\r\nCONNECT abc\/abc@srcsrv:1521\/pdb1\r\n\r\nCREATE TABLE T1 (A VARCHAR2(50));\r\nINSERT INTO T1 VALUES ('TOTO');\r\nCOMMIT;\r\n\r\nCREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;\r\nINSERT INTO T2 VALUES ('TOTO');\r\nCOMMIT;\r\n\r\n\r\nCONNECT def\/def@srcsrv:1521\/pdb1\r\n\r\nCREATE TABLE T1 (A VARCHAR2(50));\r\nINSERT INTO T1 VALUES ('TOTO');\r\nCOMMIT;\r\n\r\nCREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;\r\nINSERT INTO T2 VALUES ('TOTO');\r\nCOMMIT;\r\n\r\n\r\nCONNECT ghi\/ghi@srcsrv:1521\/pdb1\r\n\r\nCREATE TABLE T1 (A VARCHAR2(50));\r\nINSERT INTO T1 VALUES ('TOTO');\r\nCOMMIT;<\/pre>\nThis is the typical situation where transportable tablespaces become hard to achieve without some upfront segment movement, as tablespaces are not self-contained.<\/p>\n
Thankfully, Oracle Multitenant allows us to clone a PDB from a remote one and specify only a subset of tablespaces.<\/p>\n
CREATE PLUGGABLE DATABASE ABC FROM PDB1.ORCL1_PDB1_tempclone \r\n USER_TABLESPACES=('ABC','DATA');<\/pre>\nHere is a full example script with some checks and fancy parameters:<\/p>\n
--------------------------\r\n-- Script: clone_pdb.sql\r\n-- Purpose: (partial) clone a PDB from remote PDB\r\n-- Author: Ludovico Caldara (Trivadis)\r\n--\r\n-- Execute as: DBA in CDB$ROOT\r\n--\r\n-- Requirements: The remote user for the DBlink must have SELECT privileges on a few fixed views:\r\n-- --\r\n-- create user c##remote_user identified by manager container=all;\r\n-- grant create session, sysoper, create pluggable database to c##remote_user container=all;\r\n-- -- granting extra privileges to get file names and total size\r\n-- grant select on sys.v_$pdbs to c##remote_user container=all;\r\n-- grant select on sys.v_$datafile to c##remote_user container=all;\r\n-- grant select on sys.v_$database to c##remote_user container=all;\r\n-- alter user c##remote_user set container_data=all for v_$pdbs container=current;\r\n-- alter user c##remote_user set container_data=all for v_$datafile container=current;\r\n-- alter user c##remote_user set container_data=all for v_$database container=current;\r\n--\r\n--\r\n--\r\n-- Script variables:\r\ndefine SourceCDB = 'ORCL1'\r\ndefine SourcePDB = 'PDB1'\r\ndefine SourceUser = 'C##REMOTE_USER'\r\ndefine SourcePassword = 'manager'\r\ndefine SourceAlias = 'srcsrv:1521\/PDB1'\r\ndefine TargetCDB = 'ORCL2'\r\ndefine TargetPDB = 'ABC'\r\ndefine UserTBSPClause = 'USER_TABLESPACES=(''ABC'',''DATA'')'\r\n-- in case of non-OMF\r\ndefine FileNameConvertClause = 'FILE_NAME_CONVERT=(''\/&SourceCDB\/&SourcePDB\/'',''\/&TargetCDB\/&TargetPDB\/'')'\r\n\r\nSET ECHO ON SERVEROUTPUT ON\r\n\r\n-- Temporary database link name. We try to drop it if it exists but nevermind if it gives error\r\nWHENEVER SQLERROR CONTINUE\r\nDROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;\r\n\r\n-- The other tasks shouls succeed, otherwise exit\r\nWHENEVER SQLERROR EXIT 1\r\nCREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';\r\n\r\n-- in this PL\/SQL block we make sure that we do not copy if\r\n-- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY\r\nDECLARE\r\n l_log_mode sys.v_$database.log_mode%TYPE;\r\n l_local_undo database_properties.property_value%TYPE;\r\n l_open_mode sys.v_$pdbs.open_mode%TYPE;\r\n\r\nBEGIN\r\n BEGIN\r\n SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';\r\n EXCEPTION\r\n WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');\r\n END;\r\n\r\n -- Check if NOARCHIVELOG and not READ ONLY\r\n SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;\r\n IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN\r\n raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');\r\n END IF;\r\n\r\n -- Check if not LOCAL_UNDO_ENABLED and not READ ONLY\r\n SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';\r\n IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN\r\n raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');\r\n END IF;\r\nEND;\r\n\/\r\n\r\n-- let's add some intelligence... Parallel degree depending on PDB size\r\nCOLUMN parallel new_value parallel\r\nSELECT CASE\r\n WHEN ROUND(total_size\/POWER(1024,3)) <= 1 THEN 1\r\n WHEN ROUND(total_size\/POWER(1024,3)) >= 5 THEN 4\r\n ELSE 2 END AS parallel\r\nFROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';\r\n\r\n-- Real clone PDB\r\nCREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL ¶llel &FileNameConvertClause;\r\n\r\nALTER PLUGGABLE DATABASE &TargetPDB OPEN;\r\nALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;\r\n-- drop temporary db link: leaving it there exposes some risks\r\nDROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone\r\n\r\nSET ECHO OFF SERVEROUTPUT OFF<\/pre>\nThis is an example output:<\/p>\n
SQL> @\/tmp\/clone\r\nSQL>\r\nSQL> -- Temporary database link name. We try to drop it if it exists but nevermind if it gives error\r\nSQL> WHENEVER SQLERROR CONTINUE\r\nSQL> DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;\r\nold 1: DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone\r\nnew 1: DROP DATABASE LINK ORCL1_PDB1_tempclone\r\n\r\nDatabase link dropped.\r\n\r\nSQL>\r\nSQL> -- The other tasks shouls succeed, otherwise exit\r\nSQL> WHENEVER SQLERROR EXIT 1\r\nSQL> CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';\r\nold 1: CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias'\r\nnew 1: CREATE DATABASE LINK ORCL1_PDB1_tempclone connect to C##REMOTE_USER identified by manager using 'srcsrv:1521\/PDB1'\r\n\r\nDatabase link created.\r\n\r\nSQL>\r\nSQL> -- in this PL\/SQL block we make sure that we do not copy if\r\nSQL> -- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY\r\nSQL> DECLARE\r\n 2 l_log_mode sys.v_$database.log_mode%TYPE;\r\n 3 l_local_undo database_properties.property_value%TYPE;\r\n 4 l_open_mode sys.v_$pdbs.open_mode%TYPE;\r\n 5\r\n 6 BEGIN\r\n 7 BEGIN\r\n 8 SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';\r\n 9 EXCEPTION\r\n 10 WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');\r\n 11 END;\r\n 12\r\n 13 -- Check if NOARCHIVELOG and not READ ONLY\r\n 14 SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;\r\n 15 IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN\r\n 16 raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');\r\n 17 END IF;\r\n 18\r\n 19 -- Check if not LOCAL_UNDO_ENABLED and not READ ONLY\r\n 20 SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';\r\n 21 IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN\r\n 22 raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');\r\n 23 END IF;\r\n 24 END;\r\n 25 \/\r\nold 8: SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';\r\nnew 8: SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE name='PDB1';\r\nold 14: SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;\r\nnew 14: SELECT log_mode INTO l_log_mode FROM sys.v_$database@ORCL1_PDB1_tempclone ;\r\nold 20: SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';\r\nnew 20: SELECT property_value INTO l_local_undo FROM database_properties@ORCL1_PDB1_tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> -- let's add some intelligence... Parallel degree depending on PDB size\r\nSQL> COLUMN parallel new_value parallel\r\nSQL> SELECT CASE\r\n 2 WHEN ROUND(total_size\/POWER(1024,3)) <= 1 THEN 1\r\n 3 WHEN ROUND(total_size\/POWER(1024,3)) >= 5 THEN 4\r\n 4 ELSE 2 END AS parallel\r\n 5 FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';\r\nold 5: FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB'\r\nnew 5: FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE NAME='PDB1'\r\n\r\n PARALLEL\r\n----------\r\n 2\r\n\r\nSQL>\r\nSQL> -- Real clone PDB\r\nSQL> CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL ¶llel &FileNameConvertClause;\r\nold 1: CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL ¶llel &FileNameConvertClause\r\nnew 1: CREATE PLUGGABLE DATABASE ABC FROM PDB1@ORCL1_PDB1_tempclone USER_TABLESPACES=('ABC','DATA') PARALLEL 2 FILE_NAME_CONVERT=('\/ORCL1\/PDB1\/','\/ORCL2\/ABC\/')\r\n\r\nPluggable database created.\r\n\r\nSQL> ALTER PLUGGABLE DATABASE &TargetPDB OPEN;\r\nold 1: ALTER PLUGGABLE DATABASE &TargetPDB OPEN;\r\nnew 1: ALTER PLUGGABLE DATABASE ABC OPEN;\r\n\r\nPluggable database altered.\r\n\r\nSQL>ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE; \r\nold 1: ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;\r\nnew 1: ALTER PLUGGABLE DATABASE ABC SAVE STATE;\r\n\r\nPluggable database altered.\r\n\r\n\r\nSQL>\r\nSQL> -- drop temporary db link: leaving it there exposes some risks\r\nSQL> DROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone\r\n 2\r\nSQL> SET ECHO OFF SERVEROUTPUT OFF\r\n<\/pre>\nIf the clone process succeeds, at the end we should have the new ABC pluggable database with ABC and DATA tablespaces only.<\/p>\n
SQL> select tablespace_name from dba_tablespaces;\r\n\r\nTABLESPACE_NAME\r\n------------------------------\r\nSYSTEM\r\nSYSAUX\r\nUNDOTBS1\r\nTEMP\r\nDATA\r\nABC\r\n<\/pre>\n
Yeah!<\/p>\n
Any Cleanup needed?<\/strong><\/p>\n
What happened to the users? Actually, they are all still there:<\/p>\n
SQL> select username from dba_users where oracle_maintained='N';\r\n\r\nUSERNAME\r\n--------------------------------------------------------------------------------\r\nPDBADMIN\r\nC##REMOTE_CLONE\r\nABC\r\nDEF\r\nGHI\r\n\r\n5 rows selected.\r\n<\/pre>\n
And the segments in the two skipped tablespaces are not there:<\/p>\n
SQL> select owner, segment_name, tablespace_name from dba_segments \r\n2> where owner in ('ABC','DEF','GHI');\r\n\r\nOWNER SEGMENT_NAME TABLESPACE_NAME\r\n-------------------- -------------------- ------------------------------\r\nABC T2 DATA\r\nDEF T2 DATA\r\nABC T1 ABC<\/pre>\nSo the table definitions are also gone?<\/p>\n
SQL> select owner, table_name, tablespace_name from dba_tables\r\n 2 where owner in ('ABC','DEF','GHI');\r\n\r\nOWNER TABLE_NAME TABLESPACE_NAME\r\n-------------------- -------------------- ------------------------------\r\nABC T2 DATA\r\nABC T1 ABC\r\nDEF T2 DATA\r\nDEF T1 DEF\r\nGHI T1 GHI\r\n<\/pre>\nNot at all! The tables are still there and reference to tablespaces that do not exist anymore. Possible?<\/p>\n
Actually, the tablespaces definition are still there if we look at v$tablespace:<\/p>\n
SQL> select name from v$tablespace;\r\n\r\nNAME\r\n------------------------------\r\nSYSTEM\r\nSYSAUX\r\nUNDOTBS1\r\nTEMP\r\nDATA\r\nABC\r\nDEF\r\nGHI\r\n\r\n8 rows selected.\r\n<\/pre>\n
If we give a look at the DBA_TABLESPACES view definition, there are a few interesting filters:<\/p>\n
create or replace view DBA_TABLESPACES\r\n...\r\nwhere ts.online$ != 3\r\nand bitand(flags,2048) != 2048\r\nand bitand(ts.flags, 16777216) != 16777216\r\n...\r\n\/<\/pre>\n
What is their meaning?<\/p>\n
online$ \/* status (see KTT.H): *\/\r\n \/* 1 = ONLINE, 2 = OFFLINE, 3 = INVALID *\/\r\n\r\n flags \/* various flags: see ktt3.h *\/\r\n \/* 0x800 = this actually describes a group *\/ -- 2048\r\n \/* 0x1000000 = bad transport of ts in pdb *\/ -- 16777216<\/pre>\n
So the first WHERE clause skips all the INVALID TABLESPACES (when you drop a tablespace it is still stored in ts$ with this state), the second skips the definition of TEMPORARY TABLESPACE GROUPS, the third one is actually our candidate.<\/p>\n
Indeed, this is what we get from ts$ for these tablespaces:<\/p>\n
SQL> select name, decode(online$,1,'ONLINE',2,'OFFLINE',3,'INVALID',4,'READ ONLY','UNDEFINED') as STATUS, bitand(flags,2048), bitand(flags,16777216) from ts$;\r\n\r\nNAME STATUS BITAND(FLAGS,2048) BITAND(FLAGS,16777216)\r\n------------------------------ --------- ------------------ ----------------------\r\nSYSTEM ONLINE 0 0\r\nSYSAUX ONLINE 0 0\r\nUNDOTBS1 ONLINE 0 0\r\nTEMP ONLINE 0 0\r\nDATA ONLINE 0 0\r\nABC ONLINE 0 0\r\nDEF OFFLINE 0 16777216\r\nGHI OFFLINE 0 16777216\r\n\r\n8 rows selected.\r\n<\/pre>\n
So the two tablespaces are filtered out because of this new multitenant flag.<\/p>\n
If we try to drop the tablespaces, it succeeds:<\/p>\n
SQL> drop tablespace def including contents and datafiles;\r\n\r\nTablespace dropped.\r\n\r\nSQL> drop tablespace ghi including contents and datafiles;\r\n\r\nTablespace dropped.\r\n<\/pre>\n
But the user GHI, who has no objects anymore, is still there.<\/p>\n
SQL> select username from dba_users\r\n 2 where username in ('ABC','DEF','GHI') ;\r\n\r\nUSERNAME\r\n-------------------------\r\nGHI\r\nDEF\r\nABC<\/pre>\nSo we need to drop it explicitly.<\/p>\n
SQL> drop user ghi cascade;\r\n\r\nUser dropped.\r\n\r\nSQL>\r\n<\/pre>\n
Automate the cleanup<\/strong><\/p>\n
This is an example PL\/SQL that is aimed to automate the cleanup.<\/p>\n
Actually:<\/p>\n
- \n
- Users that had segments in one of the excluded tablespaces but do not have any segments left are just LOCKED (for security reasons, you can guess why).<\/li>\n
- Tablespaces that meet the “excluded PDB” criteria, are just dropped<\/li>\n<\/ul>\n
set serveroutput on\r\nDECLARE\r\n\r\n -- get the users that had tables in the non-cloned TBSP that do not have any other segments...\r\n CURSOR c_users IS\r\n SELECT DISTINCT owner as username FROM dba_tables t\r\n WHERE t.tablespace_name IN (\r\n SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216\r\n ) AND NOT EXISTS (\r\n SELECT 1 FROM dba_segments s WHERE s.owner=t.owner\r\n );\r\n\r\n -- get the list of non-cloned TBSP\r\n CURSOR c_tbsps IS\r\n SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216;\r\n\r\n r_users c_users%ROWTYPE;\r\n r_tbsps c_tbsps%ROWTYPE;\r\n\r\n e_drop_def_tbsp EXCEPTION;\r\n PRAGMA EXCEPTION_INIT (e_drop_def_tbsp, -12919);\r\n\r\n\r\nBEGIN\r\n -- check if currently in a PDB\r\n IF sys_context('userenv','con_name') = 'CDB$ROOT' THEN\r\n raise_application_error(-20101,'The current container is CDB$ROOT. It must be a PDB.');\r\n END IF;\r\n\r\n -- loop all candidate users and lock them out\r\n OPEN c_users;\r\n LOOP\r\n FETCH c_users INTO r_users;\r\n EXIT WHEN c_users%NOTFOUND;\r\n dbms_output.put_line ('ALTER USER '||r_users.username||' ACCOUNT LOCK');\r\n execute immediate 'ALTER USER '||r_users.username||' ACCOUNT LOCK';\r\n dbms_output.put_line ('User '||r_users.username||' locked.');\r\n END LOOP;\r\n\r\n -- loop all candidate tbsps and drop them\r\n OPEN c_tbsps;\r\n LOOP\r\n FETCH c_tbsps INTO r_tbsps;\r\n EXIT WHEN c_tbsps%NOTFOUND;\r\n dbms_output.put_line ('DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES');\r\n BEGIN\r\n execute immediate 'DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES';\r\n dbms_output.put_line ('Tablespace '||r_tbsps.name||' dropped.');\r\n EXCEPTION\r\n WHEN e_drop_def_tbsp THEN\r\n dbms_output.put_line ('Cannot drop default tablespace '||r_tbsps.name||'. Please change the default tablespace and drop it afterwards.');\r\n END;\r\n END LOOP;\r\nEND;\r\n\/\r\n<\/pre>\nThis is the output for the clone procedure we have just seen:<\/p>\n
SQL> @cleanup_partial_pdb.sql\r\nALTER USER GHI ACCOUNT LOCK\r\nUser GHI locked.\r\nDROP TABLESPACE DEF INCLUDING CONTENTS AND DATAFILES\r\nTablespace DEF dropped.\r\nDROP TABLESPACE GHI INCLUDING CONTENTS AND DATAFILES\r\nTablespace GHI dropped.\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n
The PL\/SQL block can be quite slow depending on the segments and tablespaces, so it might be a good idea to have a custom script instead of this automated one.<\/p>\n
What about user DEF?<\/strong><\/p>\n
The automated procedure has not locked the account DEF. Why?<\/p>\n
Actually, the user DEF still has some segments in the DATA tablespace. Hence, the procedure cannot be sure what was the original intention: copy the user ABC ? The clone procedure allows only to specify the tablespaces, so this is the only possible result.<\/p>\n
Promo: If you need to migrate to Multitenant and you need consulting\/training, just contact me, I can help you \ud83d\ude42<\/a><\/strong><\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
When consolidating to multitenant, there are several consolidation patterns. Big, complex databases usually have special requirements for which it might be a good choice to go to single-tenant (a single PDB in one CDB) Small, relatively easy databases are the … Continue reading
![\"\"](\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2020\/05\/pdb_partial_clone.png\")
<\/a><\/p>\n