导入转换分区表为非分区表
# 以sh.sales表为例 SQL@dbabd> select table_owner,table_name,partition_name from dba_tab_partitions where table_owner = 'SH' and table_name = 'SALES'; TABLE_OWNER TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SH SALES SALES_1995 SH SALES SALES_1996 SH SALES SALES_H1_1997 SH SALES SALES_H2_1997 SH SALES SALES_Q1_1998 SH SALES SALES_Q1_1999 SH SALES SALES_Q1_2000 SH SALES SALES_Q1_2001 SH SALES SALES_Q1_2002 SH SALES SALES_Q1_2003 SH SALES SALES_Q2_1998 SH SALES SALES_Q2_1999 SH SALES SALES_Q2_2000 SH SALES SALES_Q2_2001 SH SALES SALES_Q2_2002 SH SALES SALES_Q2_2003 SH SALES SALES_Q3_1998 SH SALES SALES_Q3_1999 SH SALES SALES_Q3_2000 SH SALES SALES_Q3_2001 SH SALES SALES_Q3_2002 SH SALES SALES_Q3_2003 SH SALES SALES_Q4_1998 SH SALES SALES_Q4_1999 SH SALES SALES_Q4_2000 SH SALES SALES_Q4_2001 SH SALES SALES_Q4_2002 SH SALES SALES_Q4_2003 28 rows selected SQL@dbabd> select count(*) from sh.sales; COUNT(*) ---------- 918843 SQL@dbabd> select owner,index_name,table_owner,table_name from dba_indexes where table_name = 'SALES'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME --------------- ---------------------- --------------- --------------- SH SALES_PROD_BIX SH SALES SH SALES_CUST_BIX SH SALES SH SALES_CHANNEL_BIX SH SALES SH SALES_PROMO_BIX SH SALES SH SALES_TIME_BIX SH SALES SQL@dbabd> select owner,constraint_name,table_name from dba_constraints where table_name = 'SALES'; OWNER CONSTRAINT_NAME TABLE_NAME ---------------- ------------------------------ ------------------------------ SH SYS_C0011124 SALES SH SYS_C0011123 SALES SH SYS_C0011122 SALES SH SYS_C0011121 SALES SH SYS_C0011120 SALES SH SYS_C0011119 SALES SH SYS_C0011118 SALES SH SALES_PROMO_FK SALES SH SALES_CHANNEL_FK SALES SH SALES_TIME_FK SALES SH SALES_PRODUCT_FK SALES SH SALES_CUSTOMER_FK SALES 12 rows selected # 导入到SCOTT下并重命名为sales_imp，忽略相关的外键约束 $ impdp "'/ as sysdba'" TABLES=sh.sales DIRECTORY=datapump DUMPFILE=expfull.dmp LOGFILE=scott_sales_imp.log JOB_NAME=scott_sales_imp REMAP_SCHEMA=sh:scott REMAP_TABLE=sales:sales_imp PARTITION_OPTIONS=merge EXCLUDE=constraint,statistics # 查询验证 SQL@dbabd> select count(*) from scott.sales_imp; COUNT(*) ---------- 918843 SQL@dbabd> select owner,index_name,table_owner,table_name from dba_indexes where table_name = 'SALES_IMP'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME --------------- ---------------------- --------------- --------------- SCOTT SALES_PROD_BIX SCOTT SALES_IMP SCOTT SALES_CUST_BIX SCOTT SALES_IMP SCOTT SALES_CHANNEL_BIX SCOTT SALES_IMP SCOTT SALES_PROMO_BIX SCOTT SALES_IMP SCOTT SALES_TIME_BIX SCOTT SALES_IMP SQL@dbabd> select owner,constraint_name,table_name from dba_constraints where table_name = 'SALES_IMP'; OWNER CONSTRAINT_NAME TABLE_NAME ---------------- ----------------------------- ------------------------------ SCOTT SYS_C0012351 SALES_IMP SCOTT SYS_C0012350 SALES_IMP SCOTT SYS_C0012349 SALES_IMP SCOTT SYS_C0012348 SALES_IMP SCOTT SYS_C0012347 SALES_IMP SCOTT SYS_C0012346 SALES_IMP SCOTT SYS_C0012345 SALES_IMP 7 rows selected导入表空间
# 创建一个表空间USERS_IMP SQL@dbabd> create tablespace users_imp datafile '/data/app/oracle/data/dbabd/users_imp.dbf' size 50M; Tablespace created SQL@dbabd> select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- USERS /data/app/oracle/data/dbabd/users01.dbf UNDOTBS1 /data/app/oracle/data/dbabd/undotbs01.dbf SYSAUX /data/app/oracle/data/dbabd/sysaux01.dbf SYSTEM /data/app/oracle/data/dbabd/system01.dbf EXAMPLE /data/app/oracle/data/dbabd/example01.dbf USERS_IMP /data/app/oracle/data/dbabd/users_imp.dbf 6 rows selected # 表空间USERS_IMP添加表 SQL@dbabd> create table scott.table_imp as select * from all_objects; Table created SQL@dbabd> select count(*) from scott.table_imp; COUNT(*) ---------- 85005 SQL@dbabd> alter table scott.table_imp move tablespace users_imp; Table altered SQL@dbabd> select table_name,tablespace_name from dba_tables where owner = 'SCOTT'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SALGRADE USERS BONUS USERS DEPT USERS EMP USERS TABLE_IMP USERS_IMP # 基于USERS_IMP表空间模式导出 $ expdp "'/ as sysdba'" TABLESPACES=users_imp DIRECTORY=datapump DUMPFILE=users_imp.dmp LOGFILE=users_imp.log JOB_NAME=users_imp # 删除USERS_IMP表空间 SQL@dbabd> drop tablespace users_imp including contents and datafiles; Tablespace dropped SYS@dbabd> select count(*) from scott.table_imp; select count(*) from scott.table_imp * ERROR at line 1: ORA-00942: table or view does not exist # 导入USERS_IMP表空间 :' 参考impdp参数TABLESPACES的用法，该方式需提前创建USERS_IMP表空间。 ' SQL@dbabd> create tablespace users_imp datafile '/data/app/oracle/data/dbabd/users_imp.dbf' size 20M; Tablespace created $ impdp "'/ as sysdba'" TABLESPACES=users_imp DIRECTORY=datapump DUMPFILE=users_imp.dmp LOGFILE=users_imp.log JOB_NAME=users_imp # 查询验证 SQL@dbabd> select count(*) from scott.table_imp; COUNT(*) ---------- 85005 SQL@dbabd> select table_name,tablespace_name from dba_tables where owner = 'SCOTT'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SALGRADE USERS BONUS USERS DEPT USERS EMP USERS TABLE_IMP USERS_IMP 总结