背景
oracle 段延迟分配是oracle 11g的一个新的特性,当你创建表的时候,数据库不分配空间,只有你插入记录的时候才真正分配空间,我觉得这个特性就和net app存储的存储分配特性很相似,net app存储就是划分空间的时候可以划分很大,但是,如果没有使用是不分配空间的
oracle 出发点是好的,这样可以占用更少的空间,当使用的时候才分配,但是,一个新的比较严重的问题引发了。
现象
客户进行异构平台数据迁移,由于数据量很小,客户没有使用expdp,而是使用了exp,当迁移完数据后,应用无法正常使用,通过排查,迁移过来的数据少了一些表。到oracle support知识库,发现oracle 11g增加了一个新特性段延迟分配,引起了次问题
分析过程
1.查看oracle的参数
SQL> show parameters segment
NAME TYPE VALUE
------------------------------------ ----------- ------------
deferred_segment_creation boolean TRUE
rollback_segments string
transactions_per_rollback_segment integer 5
上边的deferred_segment_creation设置默认为true,就是说明段延迟分配
2. 创建一个表
SQL> create table test(id number);
Table created.
SQL> select segment_name from user_segments where segment_type='TABLE';
QYL4
QYL
QYL1
QYL2
查看不到test表
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
查看有test表
3 .exp导出数据
C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 08:44:09 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table QYL 1 rows exported
. . exporting table QYL1 1 rows exported
. . exporting table QYL2 0 rows exported
. . exporting table QYL4 3 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
大家看到导出没有test表,这样我们导出就丢失了没有数据的表
4.解决办法
1,更改参数,但是更改参数只是影响后边创建的表,原来的表还是需要重新创建一下
sql>alter system set deferred_segment_creation =false scope=both;
创建表
sql>create table test(id number);
查看段和表
SQL> select segment_name from user_segments where segment_type='TABLE';
QYL4
QYL
QYL1
QYL2
TEST
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
结果显示一样了
导出数据
C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:02:23 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table QYL 1 rows exported
. . exporting table QYL1 1 rows exported
. . exporting table QYL2 0 rows exported
. . exporting table QYL4 3 rows exported
. . exporting table TEST 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
上边导出已经包含了test表
2,使用expdp导出
过程如下
SQL> alter system set deferred_segment_creation=true;
System altered.
SQL> connect test/test
Connected.
SQL> create table test(id number);
Table created.
SQL> select segment_name from user_segments where segment_type='TABLE';
SEGMENT_NAME
--------------------------------------------------------------------------------
QYL4
QYL
QYL1
QYL2
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看没有test表
expdp导出数据
C:\Documents and Settings\oracle>expdp system/sys dumpfile=test%U.dmp schemas=te
st directory=imp_data
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:08:44 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** dumpfile=test%U.dmp s
chemas=test directory=imp_data
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMA
P/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."QYL" 5.007 KB 1 rows
. . exported "TEST"."QYL1" 5.007 KB 1 rows
. . exported "TEST"."QYL4" 5.820 KB 3 rows
. . exported "TEST"."QYL2" 0 KB 0 rows
. . exported "TEST"."TEST" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
F:\TEST01.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 09:09:34
上边的test表导了出来
3,往表中插入一条记录
sql>insert into test values(1);
commit;
查看表和段
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments where segment_type='TABLE';
SEGMENT_NAME
--------------------------------------------------------------------------------
QYL4
QYL
QYL1
QYL2
TEST
5 rows selected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
两个结果一样
oracle 段延迟分配是oracle 11g的一个新的特性,当你创建表的时候,数据库不分配空间,只有你插入记录的时候才真正分配空间,我觉得这个特性就和net app存储的存储分配特性很相似,net app存储就是划分空间的时候可以划分很大,但是,如果没有使用是不分配空间的
oracle 出发点是好的,这样可以占用更少的空间,当使用的时候才分配,但是,一个新的比较严重的问题引发了。
现象
客户进行异构平台数据迁移,由于数据量很小,客户没有使用expdp,而是使用了exp,当迁移完数据后,应用无法正常使用,通过排查,迁移过来的数据少了一些表。到oracle support知识库,发现oracle 11g增加了一个新特性段延迟分配,引起了次问题
分析过程
1.查看oracle的参数
SQL> show parameters segment
NAME TYPE VALUE
------------------------------------ ----------- ------------
deferred_segment_creation boolean TRUE
rollback_segments string
transactions_per_rollback_segment integer 5
上边的deferred_segment_creation设置默认为true,就是说明段延迟分配
2. 创建一个表
SQL> create table test(id number);
Table created.
SQL> select segment_name from user_segments where segment_type='TABLE';
QYL4
QYL
QYL1
QYL2
查看不到test表
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
查看有test表
3 .exp导出数据
C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 08:44:09 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table QYL 1 rows exported
. . exporting table QYL1 1 rows exported
. . exporting table QYL2 0 rows exported
. . exporting table QYL4 3 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
大家看到导出没有test表,这样我们导出就丢失了没有数据的表
4.解决办法
1,更改参数,但是更改参数只是影响后边创建的表,原来的表还是需要重新创建一下
sql>alter system set deferred_segment_creation =false scope=both;
创建表
sql>create table test(id number);
查看段和表
SQL> select segment_name from user_segments where segment_type='TABLE';
QYL4
QYL
QYL1
QYL2
TEST
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
结果显示一样了
导出数据
C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:02:23 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table QYL 1 rows exported
. . exporting table QYL1 1 rows exported
. . exporting table QYL2 0 rows exported
. . exporting table QYL4 3 rows exported
. . exporting table TEST 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
上边导出已经包含了test表
2,使用expdp导出
过程如下
SQL> alter system set deferred_segment_creation=true;
System altered.
SQL> connect test/test
Connected.
SQL> create table test(id number);
Table created.
SQL> select segment_name from user_segments where segment_type='TABLE';
SEGMENT_NAME
--------------------------------------------------------------------------------
QYL4
QYL
QYL1
QYL2
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看没有test表
expdp导出数据
C:\Documents and Settings\oracle>expdp system/sys dumpfile=test%U.dmp schemas=te
st directory=imp_data
Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:08:44 2016
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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** dumpfile=test%U.dmp s
chemas=test directory=imp_data
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMA
P/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."QYL" 5.007 KB 1 rows
. . exported "TEST"."QYL1" 5.007 KB 1 rows
. . exported "TEST"."QYL4" 5.820 KB 3 rows
. . exported "TEST"."QYL2" 0 KB 0 rows
. . exported "TEST"."TEST" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
F:\TEST01.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 09:09:34
上边的test表导了出来
3,往表中插入一条记录
sql>insert into test values(1);
commit;
查看表和段
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments where segment_type='TABLE';
SEGMENT_NAME
--------------------------------------------------------------------------------
QYL4
QYL
QYL1
QYL2
TEST
5 rows selected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
两个结果一样