Oracle Database DataPump EXPDP/IMPDP

Create the datapump directory and grant privileges.

-- Query datapump directory.
set linesize 200 pages 100
col privilege for a10
col directory_path for a40
select t.owner, privilege, directory_name, directory_path
  from user_tab_privs t, all_directories d
  where t.table_name(+) = d.directory_name
  order by 3, 2;

select * from dba_directories;

-- Create directory.
create directory dpdir as '/home/oracle/dpdir';
grant read,write on directory dpdir to sys,system;

-- Drop directory.
drop directory dpdir;

Export Mode

# Full
expdp system/oracle directory=dpdir dumpfile=his%u.dmp filesize=8g parallel=4 logfile=hisfull.log full=y cluster=n

# Schema
expdp system/oracle directory=dpdir dumpfile=spacs%u.dmp filesize=8g parallel=1 schemas=spacs logfile=spacs.log

# Table
expdp sd_zy/sd_zy directory=dpdir tables=zy_yfstz0,zy_dfstz0 dumpfile=zy%u.dmp filesize=8g parallel=4 logfile=zynew.log

Import Mode

# Full
impdp system/oracle directory=dpdir full=y dumpfile=his%u.dmp parallel=4 logfile=hisfull.log

# Schema
impdp system/oracle directory=dpdir schemas=itpux01,itpux02,itpux03 dumpfile=expdp_itpux010203.dmp logfile=impdp_itpux010203.log

# Table
impdp sd_zs/sd_zs directory=dpdir tables=zs_blws00 dumpfile=sd%u.dmp logfile=zs_blws00.log

# Remap
impdp system/oracle directory=datapump dumpfile=newtopbox%u.dump \
exclude=statistics,constraint,index remap_schema=users:topbox remap_tablespace=tbs_users:tbs_topbox parallel=2

DataPump Jobs

-- running jobs
select * from dba_datapump_jobs;

-- attach job
expdp system/oracle attach=SYS_EXPORT_TABLE_01

-- stop job
Export> stop_job=immeidate

-- detach and delete job
Export> kill_job

ZHS16GBK -> AL32UTF8

Source: AMERICAN_AMERICA.ZHS16GBK
Target: AMERICAN_AMERICA.AL32UTF8

Symptoms:

KUP-11007: conversion error loading table "USER01"."ITPUX01_M1K"
ORA-12899: value too large for column RECOMMEND (actual: 12, maximum: 10)

Solution:

Import table metadata only.

impdp system/oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_itpux010203.dmp CONTENT=metadata_only \
REMAP_SCHEMA=itpux01:user01,itpux02:user02,itpux03:user03 REMAP_TABLESPACE=itpux01:user01,itpux02:user02,itpux03:user03 \
logfile=impdp_itpux010203.log

Expand column char/varchar length.

set pagesize 0;
spool /home/oracle/char1.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' char(' || (t_column.data_length + ceil(t_column.data_length * 0.5)) || ');' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length <= 1300
         AND t_column.data_type = 'CHAR';
spool off

set pagesize 0;
spool /home/oracle/char2.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||t_column.column_name || ' char(2000);' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length > 1300
         AND t_column.data_length < 2000
         AND t_column.data_type = 'CHAR';
spool off

set pagesize 0;
spool /home/oracle/char3.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' varchar2(' || (t_column.data_length + ceil(t_column.data_length * 0.5)) || ');' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length <= 2600
         AND t_column.data_type = 'VARCHAR2';
spool off

set pagesize 0;
spool /home/oracle/char3.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' varchar2(4000);' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length > 2600
         AND t_column.data_length < 4000
         AND t_column.data_type = 'VARCHAR2';
spool off

Import table data only.

impdp system/oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_itpux010203.dmp CONTENT=data_only TABLE_EXISTS_ACTION=TRUNCATE \
REMAP_SCHEMA=itpux01:user01,itpux02:user02,itpux03:user03 REMAP_TABLESPACE=itpux01:user01,itpux02:user02,itpux03:user03 \
logfile=impdp_itpux010203.log