Oracle 11.2.0.1客户端导出Oracle 11.2.0.2数据库时有分区表的分区段错误问题解决方案

http://www.itpub.net/thread-1478468-1-1.html

http://tomszrp.itpub.net/post/11835/520574

 

从11.2.0.1版本开始,Oracle又提供了一种新的空间分配方法:当你Create一个非分区表时,这个Table Segment并没有立刻创建,而是直到有第一行记录插入的时候才去创建这个Segment,这和我们以前的Segment的创建和空间分配方法是不一样.这样的段也被称为延迟段.从11.2.0.2版本开始,增加了对分区表LOB字段的支持这个特性由初始化参数deferred_segment_creation来控制,该参数默认是true.下面先看一个11.2.0.1上的例子[oracle@rac01] /home/oracle> sqlplus study/study@demoSQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 19:13:19 2010Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> show parameter deferred_segment_creationNAME                                 TYPE        VALUE------------------------------------ ----------- ------deferred_segment_creation            boolean     TRUESQL> SQL> create table test1 as select * from dual;Table created.SQL> create table test2 as select * from dual where 0=1;Table created.SQL> create table test3 as select * from dual where 0=1;Table created.SQL> select table_name from tabs;TABLE_NAME---------------TEST1TEST2TEST3SQL> select segment_name from user_segments;SEGMENT_NAME------------TEST1可以看到只有TEST1这个表有对应的segment.SQL> select table_name      from tabs t      where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);TABLE_NAME------------------TEST3TEST2SQL>  而TEST2,TEST3没有创建对应的segment,因为创建时表里没有任何记录。下面看看传统的exp/imp对这种情况下的test1,test2,test3的支持:[oracle@rac01] /home/oracle> exp study/study tables=(test1,test2,test3) file=test.dmp            Export: Release 11.2.0.1.0 - Production on Sun Nov 7 19:19:37 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table                          TEST1          1 rows exportedEXP-00011: STUDY.TEST2 does not existEXP-00011: STUDY.TEST3 does not existExport terminated successfully with warnings.[oracle@rac01] /home/oracle> 可以看到因为test2,test3没有对应的segment,而出现EXP-00011d的错误提示。

但在11.2.0.2版本中,exp又可以完成这个动作了
[oracle@vmora] /home/oracle> exp study/study tables=test1,test2,test3 file=test.dmp  
Export: Release 11.2.0.2.0 - Production on Wed Jul 13 23:33:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                          TEST1          1 rows exported
. . exporting table                          TEST2          0 rows exported
. . exporting table                          TEST3          0 rows exported
Export terminated successfully without warnings.
[oracle@vmora] /home/oracle> 
那么在11.2.0.1中如何为这些对象创建对应的segment呢?

方法有很多, 我这里采用直接分配一个extent的方法:
[oracle@rac01] /home/oracle> sqlplus study/study@demo

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 19:21:47 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select 'alter table '||table_name||' allocate extent(size 64k);' 
     from tabs t 
     where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT(SIZE64K);'
---------------------------------------------------------------------
alter table TEST3 allocate extent(size 64k);
alter table TEST2 allocate extent(size 64k);

SQL> alter table TEST3 allocate extent(size 64k);
Table altered.

SQL> alter table TEST2 allocate extent(size 64k);
Table altered.

SQL> select table_name 
     from tabs t 
     where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);
no rows selected

SQL> 
可以看到,现在test1,test2,test3都有对应的segment了,下面再看看传统的exp/imp

[oracle@rac01] /home/oracle> exp study/study tables=(test1,test2,test3) file=test.dmp       

Export: Release 11.2.0.1.0 - Production on Sun Nov 7 19:22:50 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing o
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          TEST1          1 rows exported
. . exporting table                          TEST2          0 rows exported
. . exporting table                          TEST3          0 rows exported
Export terminated successfully without warnings.
[oracle@rac01] /home/oracle> 
和我们以前得到的结果是一样的了。
:expdp/impdp对deferred segment是支持的,所以大家在11gr2及后续的版本中尽量不要使用传统的exp/imp,
    传统的exp/imp在新版本中很有很多的问题,本文不再介绍,Oracle官方也不建议大家使用传统的exp/imp.

11.2.0.2中deferred segment的增强
11.2.0.2中对Deferred Segment Creation的增强,参见我之前的博文
    
本节中主要针对DBMS_SPACE_ADMIN中新增加的
    MATERIALIZE_DEFERRED_SEGMENTS
    DROP_EMPTY_SEGMENTS
两个过程进行一个体验

[oracle@vmora] /home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 11 22:11:56 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size                  1347396 bytes
Variable Size             213909692 bytes
Database Buffers          616562688 bytes
Redo Buffers                5156864 bytes
Database mounted.
Database opened.
SQL> conn study/study
Connected.
SQL> CREATE TABLE t (
  2    id NUMBER,
  3    c CLOB,
  4    CONSTRAINT t_pk PRIMARY KEY (id) USING INDEX LOCAL
  5  )
  6  SEGMENT CREATION DEFERRED
  7  PARTITION BY HASH(id) PARTITIONS 4;

Table created.
创建一个deferred的表t
SQL> SELECT segment_name, segment_type, bytes, extents
  2   FROM user_segments
  3   WHERE segment_name IN ('T','T_PK')
  4   OR segment_name IN (SELECT segment_name
  5                       FROM user_lobs
  6                       WHERE table_name = 'T')
  7   ORDER BY 1,2;

no rows selected
可以看到确实没有对应的segment

然后我用materialize_deferred_segments对该分区表的segment进行实例化
SQL> BEGIN
  2    sys.dbms_space_admin.materialize_deferred_segments(
  3      schema_name => 'STUDY',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.

再去检查一下:
SQL> SELECT segment_name, segment_type, bytes, extents
  2   FROM user_segments
  3   WHERE segment_name IN ('T','T_PK')
  4   OR segment_name IN (SELECT segment_name
  5                       FROM user_lobs
  6                       WHERE table_name = 'T')
  7   ORDER BY 1,2;

SEGMENT_NAME                     SEGMENT_TYPE            BYTES    EXTENTS
-------------------------------- ------------------ ---------- ----------
SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1
SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1
SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1
SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1
T                                TABLE PARTITION       8388608          1
T                                TABLE PARTITION       8388608          1
T                                TABLE PARTITION       8388608          1
T                                TABLE PARTITION       8388608          1
T_PK                             INDEX PARTITION         65536          1
T_PK                             INDEX PARTITION         65536          1
T_PK                             INDEX PARTITION         65536          1
T_PK                             INDEX PARTITION         65536          1

12 rows selected.
SQL> 
对应的segment都创建好了,下面我插入一行记录
SQL> insert into t values(1,'a');
1 row created.
SQL> commit;
Commit complete.
然后调用drop_empty_segments将空的segment删除
SQL> BEGIN
  2    dbms_space_admin.drop_empty_segments(
  3      schema_name => 'STUDY',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.
再检查一下
SQL> SELECT segment_name, segment_type, bytes, extents
  2   FROM user_segments
  3   WHERE segment_name IN ('T','T_PK')
  4   OR segment_name IN (SELECT segment_name
  5                       FROM user_lobs
  6                       WHERE table_name = 'T')
  7   ORDER BY 1,2;

SEGMENT_NAME                     SEGMENT_TYPE            BYTES    EXTENTS
-------------------------------- ------------------ ---------- ----------
SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1
T                                TABLE PARTITION       8388608          1
T_PK                             INDEX PARTITION         65536          1

SQL>     
果然,除了有数据的分区,其他的空segment都被drop掉了。
更多关于 Deferred Segment ,请参阅官方手册
Oracle Instant Client 11.2.0.1.0是轻量级Oracle客户端,用于连接访问Oracle 9i、10g、11g 11.2.0.1.0版本的Oracle数据库Oracle Instant Client11.2.0.1.0 安装程序包含OCI/ OCCI、JDBC-OCI SDK(软件开发工具包),软件开发时所需的全部头文件和链接库。 此版本不支持 Pro*C/C++和.NET程序所需的Oracle Provider for OLE DB,如需支持上述功能,请访问并下载ODAC(Oracle Data Access Components,其包含 ODP.NET, Oracle Services for MTS, Oracle Providers for ASP.NET):  http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html  http://www.oracle.com/technetwork/cn/database/windows/downloads/index-101312-zhs.html 此版本集成了Oracle 11g 11.2.0.1.0中的实用工具tnsping、exp/imp,支持 Object Browser、TOAD等 Oracle 管理工具,并在Windows XP、Windows Vista、Windows 7平台下测试通过。 注意: 1. 请勿使用高版本的exp/imp工具对低版本的Oralce数据库进行导出,导入操作。 2. 安装完成后需手工配置 %TNS_ADMIN%\tnsnames.ora 文件,内容如下: ??? = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxx) ) ) 1. 将 ??? 改为任意名字,这个名字将在你的程序或程序配置中用到。 2. 将 xxx.xxx.xxx.xxx 改为要连接的 Oracle 服务器的机器名或IP地址。 3. 将 xxxxxx 改为要连接的 Oracle 服务器的 SID,如不知道 SID,请询问安装 Oracle 数据库的管理人员。 相关网站: Oracle Instant Client 主页 http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html Oracle Instant Client 常见问题 http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A3661 SQL*Plus http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/apd.htm#sthref3169 SDK: OCI(Oracle Call Interface) http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10646/oci01int.htm#i423362 OCCI(Oracle C++ Call Interface) http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10764/install.htm#CACFFGHF 如需源码或技术支持: Email: gosearcher@gmail.com
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值