Neste artigo vamos realizar um export e import de SCHEMA de um PDB para outro PDB, dentro do mesmo CDB.
Criando DIRECTORY nos dois PDBs:
[oracle@quiasma ~]$ mkdir STAGE
[oracle@quiasma ~]$ chmod 775 STAGE/
[oracle@quiasma ~]$ cd STAGE/
[oracle@quiasma STAGE]$ pwd
/home/oracle/STAGE
[oracle@quiasma STAGE]$ sqlplus system/oracle@HIPOFISE1
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 23 05:33:34 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Jun 23 2021 05:17:48 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> CREATE DIRECTORY BSS AS '/home/oracle/STAGE';
Directory created.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
[oracle@quiasma STAGE]$ sqlplus system/oracle@HIPOFISE2
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 23 05:34:05 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Jun 23 2021 05:33:34 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> CREATE DIRECTORY BSS AS '/home/oracle/STAGE';
Directory created.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
[oracle@quiasma STAGE]$
Vamos realizar o export do owner BSS1 do PDB HIPOFISE1 para o PDB HIPOFISE2:
SQL> SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='BSS1';
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS DEFAULT_TABLESPACE
-------------------------------- ------------------------------
BSS1
OPEN USERS
SQL> conn system/oracle@HIPOFISE2
Connected.
SQL> SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='BSS1';
no rows selected
Parâmetros para export:
[oracle@quiasma STAGE]$ cat exp.par
USERID=system/oracle@HIPOFISE1
SCHEMAS=BSS1
DIRECTORY=BSS
DUMPFILE=exp.dm
LOGFILE=exp.log
CONSISTENT=Y
Processo de export:
[oracle@quiasma STAGE]$ expdp parfile=exp.par
Export: Release 18.0.0.0.0 - Production on Wed Jun 23 05:38:26 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Parameter File, Replaced with: "flashback_time=TO_TIMESTAMP('2021-06-23 05:38:26', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@HIPOFISE1 parfile=exp.par reuse_dumpfiles=true
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported "BSS1"."TESTE1" 5.078 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/STAGE/exp.dm
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 23 05:38:48 2021 elapsed 0 00:00:21
Parâmetros de import:
[oracle@quiasma STAGE]$ cat imp.par
USERID=system/oracle@HIPOFISE2
SCHEMAS=BSS1
DIRECTORY=BSS
DUMPFILE=exp.dm
LOGFILE=imp.log
Processo de import:
[oracle@quiasma STAGE]$ impdp parfile=imp.par
Import: Release 18.0.0.0.0 - Production on Wed Jun 23 05:41:57 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@HIPOFISE2 parfile=imp.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BSS1"."TESTE1" 5.078 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Jun 23 05:42:13 2021 elapsed 0 00:00:15
Validando novo owner no PDB de destino:
[oracle@quiasma STAGE]$ sqlplus system/oracle@HIPOFISE2
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 23 05:42:45 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Jun 23 2021 05:41:57 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='BSS1';
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
BSS1
OPEN
SQL> SELECT * FROM BSS1.TESTE1;
DESCRICAO
--------------------
PDB HIPOFISE
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish