[20131109]deferred segment creation與12c的exp命令.txt

[20131109]deferred segment creation與12c的exp命令.txt

參考連結：http://space.itpub.net/267265/viewspace-713311


昨天想匯出一些資料在自己的12c測試環境，發現具有段延遲建立特性的表使用exp也能匯出。

例子如下：
SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t ( a number);
Table created.

SCOTT@test01p> select DBMS_METADATA.get_ddl ('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "A" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

--SEGMENT CREATION DEFERRED說明表具有段延遲建立特性。

d:\tools\rlwrap>exp scott/tiger@test01p tables=(t) file=t.dmp
Export: Release 12.1.0.1.0 - Production on Sat Nov 9 22:26:23 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.


--說明12c可以匯出，這樣看來前面的版本應該算BUG。

做一個move看看
參考連結：http://space.itpub.net/267265/viewspace-713311
SCOTT@test01p> alter table t move tablespace users;
Table altered.

SCOTT@test01p> select  table_name ,segment_created from user_tables where table_name='T';
TABLE_NAME SEG
---------- ---
T          NO

SCOTT@test01p> select  segment_name ,segment_type ,bytes from user_segments where segment_name='T' and segment_type='TABLE';
no rows selected

--看來11G遇到的問題都是bug。看看11.2.0.3問題還存在嗎？

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t ( a number);

Table created.

SCOTT@test> select DBMS_METADATA.get_ddl ('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "A" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

$ exp scott/btbtms tables=(t) file=t.dmp
Export: Release 11.2.0.3.0 - Production on Mon Nov 11 09:09:08 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.

--可以發現t也被匯出！

--做一個move看看
SCOTT@test> alter table t move tablespace users;
Table altered.

SCOTT@test> select  table_name ,segment_created from user_tables where table_name='T';
TABLE_NAME SEG
---------- ---
T          NO

SCOTT@test> select  segment_name ,segment_type ,bytes from user_segments where segment_name='T' and segment_type='TABLE';
no rows selected

--做一個imp匯入看看。
SCOTT@test> rename t to tx;
Table renamed.

$ imp scott/btbtms full=y file=t.dmp
Import: Release 11.2.0.3.0 - Production on Mon Nov 11 09:12:24 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"          0 rows imported
Import terminated successfully without warnings.


SCOTT@test> desc t;
Name  Null?    Type
----- -------- ---------
A              NUMBER


SCOTT@test> select  segment_name ,segment_type ,bytes from user_segments where segment_name='TX' and segment_type='TABLE';
no rows selected

SCOTT@test> select  segment_name ,segment_type ,bytes from user_segments where segment_name='T' and segment_type='TABLE';
no rows selected

--可以發現t表結構匯入（沒有記錄).但是沒有建立資料段。

總結：
可以發現每一個新特性的出現，總是意味著一堆bug的出現。使用新特性要注意。