Table Export with Oracle Data Pump (EXPDP)
Perform Data Pump Export for a Table
Now, you can export the table using the following command:
Note: When exporting tables owned by your user, you do not need any special roles. However, to export data from other users’ schemas, you must have the DATAPUMP_EXP_FULL_DATABASE role
expdp discovery/discovery@pdb directory=expbkp dumpfile=employee.dmp logfile=employee.log tables=employee
Example Command and Output Analysis
When executed, the command will export the employee table. The output will confirm the completion of the export job along with details like the size of the data exported.
expdp discovery/discovery@pdb directory=expbkp dumpfile=discovery.dmp logfile=discovery.log tables=employee
Export: Release 21.0.0.0.0 - Production on Fri Oct 25 22:14:03 2024
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 "DISCOVERY"."SYS_EXPORT_TABLE_01": discovery/********@pdb directory=expbkp dumpfile=discovery.dmp logfile=discovery.log tables=employee
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DISCOVERY"."EMPLOYEE" 6.210 KB 10 rows
Master table "DISCOVERY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Exporting or Importing Pluggable Database without using TNS
If you want to export or import tables, schemas, etc., from a pluggable database without using a TNS entry, you can follow these steps:
Set the environment variable for the pluggable database (replace pdb with your actual PDB name) you can connect directly using the pluggable database name. Below is an example command for exporting a table using Oracle Data Pump (EXPDP):
Note:Before running the export command, it’s a good practice to verify your database connection.
export ORACLE_PDB_SID=pdb
sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Oct 25 22:00:30 2024
Version 21.3.0.0.0
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE NO
EXIT
expdp discovery/discovery directory=expbkp dumpfile=discovery.dmp logfile=discovery.log tables=employee
