[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的出现。使用新特性要注意。
参考链接：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的出现。使用新特性要注意。
