Exporting a Table Using Oracle Data Pump in Oracle Database

    

Note: You can export/import data by any application user or sys/system user. You need to mention <ownername>.<tablename> in expdp command. #Check if read, write permissions are granted to the user on respective logical directory or not. SQL> set lines 300 pages 3000 SQL> col grantee for a14 SQL> col owner for a14 SQL> col table_name for a15 SQL> select grantee,owner,table_name,privilege from dba_tab_privs where table_name='DIR_TEST'; GRANTEE   OWNER  TABLE_NAME   PRIVILEGE --------- ------ ------------ --------- TEST      SYS    DIR_TEST     READ TEST      SYS    DIR_TEST     WRITE #Execute expdp command to take table export. By application user: C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Export: Release 19.0.0.0.0 - Production on Thu Mar 7 19:07:27 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: test Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log 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 "TEST"."EMP"                                6.531 KB       5 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is:   C:\BACKUP\TEST.DMP Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 7 19:07:55 2024 elapsed 0 00:00:24 OR By sys or system user: C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Export: Release 19.0.0.0.0 - Production on Thu Mar 7 20:51:02 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log 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 "TEST"."EMP"                                6.531 KB       5 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is:   C:\BACKUP\TEST.DMP Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 7 20:51:15 2024 elapsed 0 00:00:09 #You can execute the below query to monitor the export while running. SQL> set lines 300 pages 3000 SQL> col OWNER_NAME for a12 SQL> col JOB_NAME for a20 SQL> col JOB_MODE for a12 SQL> col STATE for a12 SQL> col OPERATION for a16 SQL> select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE,DEGREE from dba_datapump_jobs; OWNER_NAME JOB_NAME             OPERATION  JOB_MODE STATE     DEGREE ---------- -------------------- ---------- -------- --------- ------ TEST       SYS_EXPORT_TABLE_01  EXPORT     TABLE    EXECUTING      1