ITPub博客

首页 > 数据库 > Oracle > 使用expdp和impdp导出导入——LOB表

使用expdp和impdp导出导入——LOB表

Oracle 作者:daniel1x 时间:2012-05-04 18:40:04 0 删除 编辑

创建表空间和原数据
此处表上存在LOB字段,且存在索引。
通过实验验证使用expdp导出表时,其表上的LOB SEGMENT和索引是否会一起备份出来。

CREATE SMALLFILE TABLESPACE "TRSWCMV65_NEW"
DATAFILE '+PECOVERYDEST/trs/datafile/trswcmv65new' SIZE 5120M  AUTOEXTEND ON  NEXT 1024M MAXSIZE 32767M,
 '+PECOVERYDEST/trs/datafile/trswcmv65new.20120423' SIZE 5120M  AUTOEXTEND ON  NEXT 1024M MAXSIZE 32767M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

create table wcmdocument_test
tablespace TRSWCMV65_NEW
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 336M
    minextents 1
    maxextents unlimited
  )
as select * from wcmdocument where 1 != 1;

插入数据
insert into  WCMDOCUMENT_TEST  select * from wcmdocument where rownum < 91;
commit;

重建索引
-- Create/Recreate primary, unique and foreign key constraints
alter table WCMDOCUMENT_TEST
  add primary key (DOCID)
  using index
  tablespace TRSWCMV65_NEW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index IDX_DOC_TEST1 on WCMDOCUMENT_TEST (DOCCHANNEL, DOCSTATUS, CRTIME)
  tablespace TRSWCMV65_NEW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

此时新表空间内有3个LOBSEGMENT和LOBINDEX,两个正常的索引。


导出wcmdocument_test表的数据。
SQL> col directory_path format a 30
SQL> set linesize 120
SQL> select * from dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            ADMIN_DIR                      /ade/aime_ship_10gR2_050630.0022/oracle/md/admin
SYS                            SUBDIR                         /opt/app/oracle/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS                            DATA_FILE_DIR                  /opt/app/oracle/product/10.2.0/db_1/demo/schema/sales_history/
SYS                            WORK_DIR                       /ade/aime_ship_10gR2_050630.0022/oracle/work
SYS                            LOG_FILE_DIR                   /opt/app/oracle/product/10.2.0/db_1/demo/schema/log/
SYS                            MEDIA_DIR                      /opt/app/oracle/product/10.2.0/db_1/demo/schema/product_media/
SYS                            XMLDIR                         /opt/app/oracle/product/10.2.0/db_1/demo/schema/order_entry/
SYS                            DATA_PUMP_DIR                  /opt/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS                            DPDATA1                        /export/home/oracle/backup
 
9 rows selected
此处选择使用DPDATA1目录

oracle用户登陆数据库节点1主机
cd /export/home/oracle/backup
expdp trswcmv65/trsadmin tables=wcmdocument_test dumpfile=wcmtest.dmp directory=dpdata1

ls -l
-rw-r-----  1 oracle oinstall      696320 May  3 14:06 wcmtest.dmp
-rw-r--r--  1 oracle oinstall        1232 May  3 14:06 export.log

---删除原表
truncate table WCMDOCUMENT_TEST;
drop table WCMDOCUMENT_test;
select * from dba_segments where tablespace_name = 'TRSWCMV65_NEW';
select * from dba_recyclebin;
purge tablespace TRSWCMV65_NEW;

使用dmp文件,导入,并检查结果是否正确
[oracle@rac1 ~]$ impdp trswcmv65/trsadmin directory=dpdata1 dumpfile=wcmtest.dmp  tables=wcmdocument_test

Import: Release 10.2.0.1.0 - 64bit Production on Thursday, 03 May, 2012 18:27:01

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Master table "TRSWCMV65"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TRSWCMV65"."SYS_IMPORT_TABLE_01":  trswcmv65/******** directory=dpdata1 dumpfile=wcmtest.dmp tables=wcmdocument_test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TRSWCMV65"."WCMDOCUMENT_TEST"              501.3 KB      90 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "TRSWCMV65"."SYS_IMPORT_TABLE_01" successfully completed at 18:27:09


 
SQL> select segment_name, segment_type from dba_segments where tablespace_name = 'TRSWCMV65_NEW';
 
SEGMENT_NAME                                                                     SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------
WCMDOCUMENT_TEST                                                                 TABLE
SYS_IL0000092091C00038$$                                                         LOBINDEX
SYS_LOB0000092091C00038$$                                                        LOBSEGMENT
SYS_IL0000092091C00011$$                                                         LOBINDEX
SYS_LOB0000092091C00011$$                                                        LOBSEGMENT
SYS_IL0000092091C00010$$                                                         LOBINDEX
SYS_LOB0000092091C00010$$                                                        LOBSEGMENT
IDX_DOC_TEST1                                                                    INDEX
SYS_C0040887                                                                     INDEX
 
9 rows selected

总结:expdp会导出DDL,并且导出表相关的LOBSEGMENT、LOBINDEX和常规的索引。
      impdp会使用DDL创建表,并且创建表上相关的索引。

本次实验由于trswcmv65用户拥有dba权限,故没有考虑权限的问题。字符集的问题也没有考虑,待研究!

 


 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/21434166/viewspace-1116183/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-20