导入SCHEMA
# 先查询原先数据库SCOTT用户SCHEMA对象信息 SQL@dbabd> select owner,object_name,object_type from all_objects where owner = 'SCOTT'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ------------------- SCOTT PK_DEPT INDEX SCOTT DEPT TABLE SCOTT EMP TABLE SCOTT PK_EMP INDEX SCOTT BONUS TABLE SCOTT SALGRADE TABLE 6 rows selected # 删除SCOTT用户及所有SCHEMA对象 SQL@dbabd> drop user scott cascade; User dropped SYS@dbabd> select owner,object_name,object_type from all_objects where owner = 'SCOTT'; no rows selected SYS@dbabd> select * from all_users where username = 'SCOTT'; no rows selected # 执行SCHEMA模式导入SCOTT $ impdp "'/ as sysdba'" SCHEMAS=scott DIRECTORY=datapump DUMPFILE=expfull.dmp LOGFILE=imp_scott.log JOB_NAME=imp_scott # 查询验证 SQL@dbabd> select owner,object_name,object_type from all_objects where owner = 'SCOTT'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ------------------- SCOTT SALGRADE TABLE SCOTT BONUS TABLE SCOTT EMP TABLE SCOTT DEPT TABLE SCOTT PK_DEPT INDEX SCOTT PK_EMP INDEX 6 rows selected导入表数据(包含表的对象)
# 以scott.emp表为例 :' 1.表行数 SYS@dbabd> select count(*) from scott.emp; COUNT(*) ---------- 14 2.表索引 SQL@dbabd> select owner,index_name,table_owner,table_name from dba_indexes where table_name = 'EMP'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME -------------------- -------------------- -------------------- -------------------- SCOTT PK_EMP SCOTT EMP 3.表约束 SQL@dbabd> select owner,constraint_name,table_name from dba_constraints where table_name = 'EMP'; OWNER CONSTRAINT_NAME TABLE_NAME -------------------- ------------------------------ ------------------------------ SCOTT FK_DEPTNO EMP ' # 删除表scott.emp SQL@dbabd> drop table scott.emp purge; Table dropped # 执行表scott.emp的导入 $ impdp "'/ as sysdba'" TABLES=scott.emp DIRECTORY=datapump DUMPFILE=expfull.dmp LOGFILE=imp_scott.emp.log JOB_NAME=imp_scott.emp # 查询验证 SQL@dbabd> select count(*) from scott.emp; COUNT(*) ---------- 14只导入表数据(不包含其它表对象)
# 先导入表创建元数据 $ impdp "'/ as sysdba'" TABLES=scott.emp DIRECTORY=datapump DUMPFILE=expfull.dmp LOGFILE=imp_scott.emp.log JOB_NAME=imp_scott.emp CONTENT=metadata_only EXCLUDE=index,constraint,statistics # 验证表是创建成功 SYS@dbabd> select * from scott.emp; no rows selected SYS@dbabd> select owner,index_name,table_owner,table_name from dba_indexes where table_name = 'EMP'; no rows selected SYS@dbabd> select owner,constraint_name,table_name from dba_constraints where table_name = 'EMP'; no rows selected # 再导入表数据 $ impdp "'/ as sysdba'" TABLES=scott.emp DIRECTORY=datapump DUMPFILE=expfull.dmp LOGFILE=imp_scott.emp.log JOB_NAME=imp_scott.emp CONTENT=data_only # 最后验证表数据 SYS@dbabd> select count(*) from scott.emp; COUNT(*) ---------- 14