Description:-
In this post we are going to see how to take expdp on pdb database.
Environment:-
PDB Name:-oradbwrpdb1
Schema Name:-Scott
Step:-1 First we need to create PDB service PDB service and registered in the listener
Tnsnames.ora
ORADBWRPDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle21c)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradbwrpdb1)
)
)
Listener Status
[oracle@oracle21c ~]$ lsnrctl status
LSNRCTL for Linux: Version 21.0.0.0.0 – Production on 11-SEP-2022 19:06:36
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle21c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 21.0.0.0.0 – Production
Start Date 11-SEP-2022 18:57:25
Uptime 0 days 0 hr. 9 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle21c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle21c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “c8209f27c6b16005e053362ee80ae60e” has 1 instance(s).
Instance “oradbwr”, status READY, has 1 handler(s) for this service…
Service “e7c13b36e5577371e0531801a8c002ae” has 1 instance(s).
Instance “oradbwr”, status READY, has 1 handler(s) for this service…
Service “oradbwr” has 2 instance(s).
Instance “oradbwr”, status UNKNOWN, has 1 handler(s) for this service…
Instance “oradbwr”, status READY, has 1 handler(s) for this service…
Service “oradbwrXDB” has 1 instance(s).
Instance “oradbwr”, status READY, has 1 handler(s) for this service…
Service “oradbwrpdb1” has 1 instance(s).
Instance “oradbwr”, status READY, has 1 handler(s) for this service…
The command completed successfully
Step:-2 Check pdb database and schema
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 ORADBWRPDB1 READ WRITE NO
SQL> select username from dba_users where username=’SCOTT’;
USERNAME
——————————————————————————–
SCOTT
Step:-3 Create datapump directory
SQL> create directory data_pump_pdb as ‘/u01/datapump’;
Directory created.
SQL> grant read,write on directory data_pump_pdb to scott;
Grant succeeded.
Step:-4 Take export the tables from pdb database
export ORACLE_PDB_SID=oradbwrpdb1
expdp \”/ as sysdba\” tables=scott.emp
or
expdp \”sys/password@oradbwrpdb1 as sysdba\” tables=scott.emp
[oracle@oracle21c ~]$ expdp scott/tiger@oradbwrpdb1 dumpfile=emp_dept.dmp logfile=emp_dept.log directory=data_pump_pdb tables=emp,dept
Export: Release 21.0.0.0.0 – Production on Sun Sep 11 19:30:44 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″: scott/********@oradbwrpdb1 dumpfile=emp_dept.dmp logfile=emp_dept.log directory=data_pump_pdb tables=emp,dept
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported “SCOTT”.”DEPT” 6.023 KB 4 rows
. . exported “SCOTT”.”EMP” 8.773 KB 14 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/datapump/emp_dept.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Sep 11 19:31:24 2022 elapsed 0 00:00:34
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr
