-----------------------------------------------------
导出与导入表数据 For Oracel
-----------------------------------------------------

1.建立导出目录

SQL> create directory dmpdir as '/archivelog/dmpdir';

Directory created.

2.查询目录是否建立

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR
/orahome/10.2/ccr/state

SYS DMPDIR
/archivelog/dmpdir

SYS DATA_PUMP_DIR
/orahome/10.2/rdbms/log/[@more@]

3.导出表数据

SQL> host expdp system/xxx directory=dmpdir tables=xxxx_SN_SITE_STATISTICS dumpfile=xxxx_sn_site_statistics.dmp logfile=xxxx_sn_site_statistics.log job_name=expdp;



4.启动到非归档模式,不让其产生ARCHIVELOG

SQL> shutdown immeidate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;

5.进行数据的导入

SQL> host impdp system/xxxxx directory=dmpdir dumpfile=xxxx_sn_site_statistics.dmp logfile=impdp_xxxx_sn_site_statistics.log job_name=impdp full=y;



6.打开数据归档
SQL> shutdown immeidate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog/STCSMES
Oldest online log sequence 26560
Next log sequence to archive 26565
Current log sequence 26565
SQL> alter database open;

--------------------------

导出、导入用户下所有数据

--------------------------

SQL> host expdp system/xxxx directory=dmpdir dumpfile=xxx.dmp logfile=expdp_xxx.log schemas=xxx job_name=expdp;

SQL> drop user smp cascade;

SQL> host impdp system/xxx directory=dmpdir dumpfile=xxx.dmp logfile=impdp_xxx.log job_name=impdp full=y;

-------------------------------------------------------------------

从一个导出schema dmp文件中，导入一个table到另一个schema

-------------------------------------------------------------------

impdp system/system schemas=xxx directo
ry=xxx_dir remap_schema=xxx:xxxx dumpfile=xxx.dmp include = TABLE:" in ('EMP') " logfile=xxxx.log;