📦 Export / Import (Data Pump)

1exp  user/password@host FULL=Y        # full legacy (binary) export.
2expdp user/password@host FULL=Y DIRECTORY=DUMP DUMPFILE=full.dmp

1# full DB, excluding statistics
2nohup expdp 'system/<password>'@orcl FULL=Y DIRECTORY=DUMP \
3  DUMPFILE=expdp_orcl_full_$(date +%Y-%m-%d).dmp \
4  LOGFILE=expdp_orcl_$(date +%Y-%m-%d).log EXCLUDE=statistics
5
6# one schema
7nohup expdp system/<password>@orcl SCHEMAS=my_schema DIRECTORY=DUMP \
8  DUMPFILE=my_schema_$(date +%Y-%m-%d)_%U.dmp \
9  LOGFILE=my_schema.log EXCLUDE=statistics

1SET LINES 200 PAGES 2000
2SELECT * FROM dba_directories;   -- the paths defined for Oracle.

1CREATE DIRECTORY my_dir AS '/backup/dump';
2GRANT READ, WRITE ON DIRECTORY my_dir TO my_user;
3DROP DIRECTORY my_dir;

1-- see the running jobs
2SELECT * FROM dba_datapump_jobs;

1expdp attach=<job_name>   # enter the prompt of a running job.

1IMPORT> status
2kill_job         # stop the job.
3stop_job         # suspend the job.
4start_job        # resume a stopped job.
5continue_client  # resume an attached client job.

 1# remap a schema
 2impdp '/ as sysdba' DIRECTORY=DUMP DUMPFILE=my_db_2020-04-01.dmp PARALLEL=1 \
 3  REMAP_SCHEMA=src_schema:dst_schema
 4
 5# full import
 6impdp '/ as sysdba' DIRECTORY=DUMP DUMPFILE=expdp_orcl_2020-09-02.dump \
 7  LOGFILE=impdp.log PARALLEL=1 FULL=Y
 8
 9# one schema
10impdp '/ as sysdba' DIRECTORY=DUMP DUMPFILE=exp.dmp SCHEMAS=my_schema

1SET HEAD OFF
2SELECT 'DROP TABLE ' || owner_name || '.' || job_name || ';'
3FROM dba_datapump_jobs WHERE state = 'NOT RUNNING' AND attached_sessions = 0;

1tail -500f /u01/oracle/base/diag/rdbms/orcl/ORCL_1/trace/alert_ORCL_1.log

1SELECT DISTINCT event FROM v$session WHERE status = 'ACTIVE';

1SELECT * FROM dba_registry;

1SHOW PARAMETER resumable;
2ALTER SYSTEM SET resumable_timeout = 7200 SCOPE = BOTH;