link之家
链接快照平台
- 输入网页链接，自动生成快照
- 标签化管理网页链接
相关文章推荐
|
拉风的电池 · 统计信息简介 | PingCAP 文档中心· 10 月前 · |
|
|
刀枪不入的马铃薯 · oracle ...· 1 年前 · |
|
|
千杯不醉的冰棍 · Power BI: ...· 1 年前 · |
|
|
暗恋学妹的毛豆 · queryDsl多表联合查询,任意查询联合字 ...· 1 年前 · |
|
|
活泼的椰子 · power bi动态条形图改颜色 - 抖音· 1 年前 · |
答案：在
Oracle 11.2
中，当创建一个空表或者空分区时，为了加快创建速度，
Oracle
并不会立即分配初始段和空间，实际的表段（
Table Segement
）被延迟到第一行数据插入时创建。延迟段创建特性通过
DEFERRED_SEGMENT_CREATION
参数控制，默认为
TRUE
，表示开启该功能。延迟段创建可以节省空间，加快初始化过程，是面向性能和资源的一个优化。可以通过修改参数
DEFERRED_SEGMENT_CREATION
来关闭这个特性：
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FLASE;
该参数为动态参数，不需要重启数据库。可以通过如下的
SQL
语句找到所有的空表（未分配任何区的表）：
SELECT SEGMENT_CREATED,TABLE_NAME FROM USER_TABLES WHERE SEGMENT_CREATED = 'NO';
延迟段创建的限制条件有：
①
延迟段创建不支持的表类型包括：索引组织表（
Index-Organized Tables
）、簇表（
Clustered Tables
）、全局临时表（
Global Temporary Tables
）、会话级临时表（
Session-Specific Temporary Tables
）、内部表（
Internal Tables
）、
Typed
表（
Typed Tables
）、
AQ
表（
AQ Tables
）和外部表（
External Tables
）。
②
用户
SYS
、
SYSTEM
、
PUBLIC
、
OUTLN
和
XDB
下的表不支持延迟段创建。
③
延迟段创建不支持位图连接索引（
Bitmap Join Indexes
）和域索引（
Domain Indexes
）。
④ 延迟段创建不支持
字典管理表空间（
Dictionary-Managed Tablespace
，
DMT
）
。
⑤
延迟段创建不支持
SYSTEM
表空间。
⑥
延迟段创建从
Oracle 11.2.0.2
版本开始才开始支持分区和子分区。
在创建表的时候，当
SEGEMENT CREATION
为
IMMEDIATE
时，
Oracle
会为表建立段（
SEGMENT
），当
SEGEMENT CREATION
为
DEFERRED
时，
Oracle
不会为空表建立段，如下所示：
SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10))
SEGMENT CREATION IMMEDIATE
;
表已创建。
SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10))
SEGMENT CREATION DEFERRED;
表已创建。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';
SEGMENT_NAME
-------------
T_TEST_2
SQL> INSERT INTO T_TEST_1 VALUES(1,'LHRDB');
已创建
1
行。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';
SEGMENT_NAME
------------
T_TEST_1
T_TEST_2
可以看到，在
SEGEMENT CREATION
为
IMMEDIATE
的情况下，
Oracle
为
T_TEST_2
建立了段，在
SEGEMENT CREATION
为
DEFERRED
的情况下，
Oracle
没有为表
T_TEST_3
建立段，当向没有分配段的空表中插入信息时，
Oracle
会自动为空表建立段。需要注意的是，未分配任何区（
EXTENT
）的表，在
DBA_SEGMENTS
视图中是查不到的。若使用不加
DROP ALL STROAGE
的
TRUNCATE
语句，则分配的段只是缩小到最初分配的大小，并没有完全回收段，如下所示：
SQL> TRUNCATE TABLE TEST1; ------
使用不加子句的
truncate
Table truncated.
SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS; -----
只是删除到最小的区
SEGMENT_NAME BYTES
------------------------------ ----------
TEST1 65536
SQL> TRUNCATE TABLE TEST1 DROP ALL STORAGE; -----
使用
11g
中新加的子句
drop all stroage
Table truncated.
SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS; -----
段全部被回收
no rows selected
需要关注的是，这个新特性会带来一个问题：在使用
exp/imp
进行导出导入时，不会包含这些空表（数据泵
expdp
不存在这个问题，
expdp
可以导出空表），所以，在迁移数据的时候可能会导致遗漏部分空表。针对这个问题的解决方法有：
1
、最原始最笨的办法（不推荐）：
INSERT
一行，再
ROLLBACK
或者删除就可以产生
SEGMENT
了。该方法是在空表中插入数据，再删除，则会产生
SEGMENT
，此时再导出时就可以导出空表。
2
、设置
DEFERRED_SEGMENT_CREATION
参数。设置
DEFERRED_SEGMENT_CREATION
参数为
FALSE
来禁用“延迟段创建”，无论是空表还是非空表，都会分配
SEGMENT
。需要注意的是，该值设置后只对后面新增的表产生作用，对之前建立的空表（已经存在的）不起作用，仍不能导出。
3
、使用
ALLOCATE EXTENT
。使用
ALLOCATE EXTENT
可以为数据库的每一张表分配
EXTENT
。批量生成脚本：
SELECT 'ALTER TABLE '||D.OWNER||'.'||D.TABLE_NAME||' ALLOCATE EXTENT;' EXEC_SQL FROM DBA_TABLES D WHERE D.SEGMENT_CREATED='NO' AND D.OWNER IN ('LHR','ABC'); --
注意修改用户名
执行以上
SQL
产生的脚本后即可为每一个空表分配段，然后执行
exp
命令即可。
总结一下，
当数据库服务器的版本为
11.2.0.1
或使用
exp 11.2.0.1
工具进行导出的时候，需要注意空表的问题。由于延迟段创建的特性，会导致空表不能正常导出。从
Oracle 11.2.0.2
开始，已经不存在该问题了。
在数据库服务器版本为Oracle 11.2.0.1中，
当采用
exp（无论版本为多少）导出数据时，需要先判断数据库中是否含有空表。因为若基于SCHEMA
级别或
FULL
级别导出数据时不会报错
，所以，可能会漏掉一些空表定义。当数据库中含有空表时，需要先设置参数DEFERRED_SEGMENT_CREATION为FALSE，然后使用ALLOCATE EXTENT为每一个空表分配段，最后再导出所需要的数据。另外，
对于分区表而言，即使没有创建段
（无论是整个分区还是个别子分区没有创建段，可以使用DBA_TAB_PARTITIONS.SEGMENT_CREATED来查询是否创建了段），
也不存在丢失分区表或丢失某个子分区的问题
。
In Oracle Database 11
g
release 2 (11.2), the
DEFERRED_SEGMENT_CREATION
parameter is set to
TRUE
by default. This means that any tables you create do not have any segments until the first row of data is inserted into the table. Original Export ignores tables without segments. Therefore, if you create any new tables and do not insert any data before performing an export, those tables are not exported. (Note that Data Pump Export does not have this limitation because it does not ignore tables without segments.)
DEFERRED_SEGMENT_CREATION specifies the semantics of deferred
segment creation. If set to true, then segments for tables and
their dependent objects (LOBs, indexes) will not be created until the first row
is inserted into the table.
Before creating a set of tables, if it is known that a significant number of
them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.
Restrictions on Deferred Segment Creation
This clause is
subject to the following restrictions:
You cannot defer segment creation for the following types of tables:
index-organized tables, clustered tables, global temporary tables,
session-specific temporary tables, internal tables, typed tables, AQ tables,
external tables, and tables owned by SYS, SYSTEM, PUBLIC, OUTLN, or XDB.
Deferred segment creation is supported on partitions and subpartitions
beginning with Oracle Database 11
g
Release 2
(11.2.0.2).
Serializable transactions do not work with deferred segment creation. Trying
to insert data into an empty table with no segment created causes an
error.
当 SEGEMENT CREATION 为 IMMEDIATE 的情况下， ORACLE 在建表的时候，会为表建立段（ SEGMENTS ），当 SEGEMENT CREATION 为 DEFERRED 的情况下， ORACLE 不会为空表建立段 , 下面分别演示下这两种情况的效果。 SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION IMMEDIATE; 表已创建。 SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION DEFERRED; 表已创建。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------- T_TEST_2 可以看到，在 SEGEMENT CREATION 为 IMMEDIATE 的情况下， ORACLE 为 T_TEST_2 建立了段，在 SEGEMENT CREATION 为 DEFERRED 的情况下， ORACLE 没有为表 T_TEST_3 建立段，当向没有分配段的空表中插入信息时， ORACLE 会自动为空表建立段。 SQL> INSERT INTO T_TEST_1 VALUES(1,'STREAM'); 已创建 1 行。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 也可以用 ALLOCATE EXTENT 的方式来为空表建立段信息。 SQL> ALTER TABLE T_TEST_3 ALLOCATE EXTENT; 表已更改。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 T_TEST_3 虽然延迟段创建避免了空表占用空间的问题，但是也为 DBA 带点小麻烦，这就是在 EXP 导出数据的时候，虽然空表的信息也存在数据库字典内，但是 ORACLE 不会导出未分配段的空表，这样在使用 EXP 做数据迁移的时候，就会遇到点小问题。 SQL> CREATE USER dbdream IDENTIFIED BY dbdream DEFAULT TABLESPACE USERS; 用户已创建。 SQL> GRANT CONNECT,RESOURCE TO DBDREAM; 授权成功。 SQL> CREATE TABLE T_TEST_1(ID NUMBER,NAME VARCHAR2(10)); 表已创建。 SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION IMMEDIATE; 表已创建。 D:\ >exp dbdream/dbdream file=d:\dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:35:22 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的用户 ... . . 正在导出表 T_TEST_2 导出了 0 行 成功终止导出 , 没有出现警告。 EXP 只能导出已经分配段的表，要导出未分配段的空表 EXP 就无能为力了，要导出未分配段的空表就需要使用数据泵（ EXPDP ），使用 EXPDP 可以导出未分配段的空表。 SQL> CREATE DIRECTORY D_TEST AS 'D:\T_TEST'; 目录已创建。 SQL> GRANT READ,WRITE ON DIRECTORY D_TEST TO DBDREAM; 授权成功。 D:\ >expdp dbdream/dbdream directory=D_TEST dumpfile=dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:50:00 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 正在使用 BLOCKS 方法进行估计 ... . . 导出了 "DES"."T_TEST_1" 0 KB 0 行 . . 导出了 "DES"."T_TEST_2" 0 KB 0 行 作业 "DES"."SYS_EXPORT_SCHEMA_01" 已于 11:50:47 成功完成 如果非要用 EXP 做迁移，而且所有空表也都需要迁移，那么就需要使用上文提到的利于 ALLOCATE EXTENT 创建段的方法。在做 EXP 操作之前，先使用 ALLOCATE EXTENT 的方法为空表分配段信息。 SQL> DECLARE 2 V_COUNT NUMBER; 3 BEGIN 4 FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP 5 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_COUNT; 6 IF V_COUNT = 0 THEN 7 EXECUTE IMMEDIATE 'ALTER TABLE ' || I.TABLE_NAME || ' ALLOCATE EXTENT'; 8 END IF; 9 END LOOP; 10 END; 11 / PL/SQL 过程已成功完成。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 然后在用 EXP 导出数据，这样空表就可以被导出了。 D:\ >exp dbdream/dbdream file=d:\dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:58:03 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的用户 ... . . 正在导出表 T_TEST 导出了 0 行 . . 正在导出表 T_TEST_2 导出了 0 行 成功终止导出 , 没有出现警告。 ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})] ----------- 故，需要构建如下样子简单的SQL命令： ----------- alter table TableName allocate extent ----------- 但要是每一张表写一条语句的话太过麻烦，为了方便我们使用SQL命令拼写出每一张表的alter语句。 SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null（注意：很多教程没有这里，这里是有可能位空的） 上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条)，我们只需要将其生成的所有sql代码全部执行，就可以给每一张已经存在的表来分配segment，就OK了。 最后： 这时再用exp导出就没有问题了。 但是： 数据库本身的deferred_segment_creation属性还是TRUE,也是就是说如果再创建新表的话，默认还是不分配segment的。所以还是需要更改deferred_segment_creation的参数，以便以后创建的新表自动分配segment。 如果你的数据库还没有创建任何数据表，那么直接修改deferred_segment_creation属性，以后创建的表无论是不是为空都会自动分配segment，就不会出现导不出空表的情况。然而如果你的数据库中已经有很多空表，并且需要导出来，那么光修改deferred_segment_creation属性则没有用的，因为它只对之后创建的表有作用。你需要给已存在的空表分配segment以便可以导出存在的空表，就用到上面讲的allocate extent方法，但此方法只针对已经存在的表的segment属性，所以最好就是：先给已存在的空表分配segment，方便其可以直接导出，然后设定deferred_segment_creation参数以便以后每张表无论是否为空都自动分配segment。 SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null; 批量输出上述生成的SQL语句并写入到一个.sql的脚本文件中。 1. 创建执行脚本文件：我创建一个E:\sql_script.sql文件。内容如下： set heading off; set echo off; set feedback off; set termout on; spool E:\sql_allocate.sql; Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null ; spool off; 这个脚本的作用就是创建一个E:\sql_allocate.sql脚本文件，将Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null 的执行结果（就是给每张表生成segment的SQL代码）批量输出，存储到一个E:\sql_allocate.sql的脚本文件中。 APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.
PURPOSE This bulletin provides information about the database version 11.2 new feature called Deferred Segment Creation also referred to as Segment Creation On-Demand. SCOPE Database Administrators and Developers. DETAILS![]()
Video -
![]( "Trouble seeing this video?")
The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION set to TRUE.
The advantages of this new space allocation method are:
? A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
? The application installation time is reduced, because the creation of a table is a data dictionary operation only.
When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance.
Note: With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. For more details, see the Oracle Database Administrator's Guide. SQL> connect / as sysdba
Connected.
SQL> show parameter deferred
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> connect imran/imran
Connected.
SQL> create table t1(no number);
Table created.
SQL> select * from user_segments;
no rows selected
SQL> insert into t1 values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> select table_name,segment_created from user_tables;
TABLE_NAME SEG
------------------------------ ---
T1 YES
? This slide shows you how to verify the segment creation on demand. First you check the parameter DEFERRED_SEGMENT_CREATION . Then a table is created without segments, which you can verify by querying the USER_SEGMENTS data dictionary view. After the insert of a row, you query this view again, to see that the segment now exists.
? You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created. Note that when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
? Another addition to the data dictionary is the SYS.SEG$ table that stores the storage parameters which you specified during the table or index creation.
You can control segment creation in two ways:
1) With the DEFERRED_SEGMENT_CREATION initialization parameter set to TRUE or FALSE. This parameter can be set in the initialization file. You can also control it via the ALTER SESSION or ALTER SYSTEM commands.
Example: SQL> connect / as sysdba
Connected.
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation=FALSE;
System altered.
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
SQL> connect imran/imran
Connected.
SQL> create table t2(no number);
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> alter session set deferred_segment_creation=true;
Session altered.
SQL> create table t3( no number);
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
2) With the SEGMENT CREATION clause of the CREATE TABLE command:
SEGMENT CREATION DEFERRED: If specified, segment creation is deferred until the first row is inserted into the table. This is the default behavior for the Oracle Database 11gR2.
SEGMENT CREATION IMMEDIATE: If specified, segments are materialized during table creation. This is the default behavior in Oracle databases prior to the Oracle Database 11gR2.
Example SQL> select * from user_segments;
no rows selected
SQL> CREATE TABLE SEG_TAB3(C1 number, C2 number)
SEGMENT CREATION IMMEDIATE;
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3
SQL>CREATE TABLE SEG_TAB4(C1 number, C2 number)
SEGMENT CREATION DEFERRED;
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3
This clause takes precedence over the DEFERRED_SEGMENT_CREATION parameter. It is possible to force creation of segments for an already created table with the ALTER TABLE …MOVE command. However, it is not possible to directly control the deferred segment creation for dependant objects like indexes. They inherit this characteristic from their parent object, in this case, the table.
Restrictions and Exceptions :- ?To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.
? In the Oracle Database 11.2.0.1, deferred segment creation is restricted to non-partitioned tables and non-partitioned indexes. Deferred segment creation is not supported for partitioned indexes, bitmap join indexes, and domain indexes.
? IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables are not supported. Tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.
? Segment creation on demand is not supported for tables created in dictionary-managed tablespaces and for clustered tables. If you try creating these tables, segments ARE created.
? Also please note: If you create a table with deferred segment creation on a locally managed tablespace, then it has no segments. If at a later time, you migrate the tablespace to dictionary-managed, then any attempt to create segments produces errors. In this case you must drop the table and recreate it. Note:
In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables. This restriction is removed in release 11.2.0.2 and later.
The following rules apply to a table whose segment has not yet been materialized:
If you create this table with CREATE TABLE ... AS subquery, then if the source table has no rows, segment creation of the new table is deferred. If the source table has rows, then segment creation of the new table is not deferred.
If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized, then the segment is materialized and then an extent is allocated. However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.
During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment, segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.
When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.
In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored.
ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE.
Parallel DML operations on tables with no segments are disabled.
Additional Automatic Functionality:-
Additional enhancements in the Oracle Database 11gR2 (unrelated to the deferred segment creation) are implemented to save space: all UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you. It is enabled by default with the COMPATIBILITY initialization parameter set to 11.2.0.0. ? Creating an index without a segment
CREATE INDEX test_i1 ON seg_test(c) UNUSABLE;
? Removing any allocated space for an index
ALTER INDEX test_i UNUSABLE;
? Creating the segment for an index:
ALTER INDEX test_i REBUILD;
The following slide shows some SQL commands which you might find useful for this new functionality:
? You can create an index without segment with the CREATE INDEX … UNUSABLE clause.
? You can remove any allocated space with the ALTER INDEX … UNUSABLE clause.
? And finally, you can create a segment for an index with the ALTER INDEX … REBUILD
Example SQL> select * from user_segments ;
no rows selected
SQL> create table test_table(no number);
Table created.
SQL> insert into test_table values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
SQL> create index test_index on test_table(no) unusable;
Index created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
SQL> alter index test_index rebuild;
Index altered.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
TEST_INDEX
SQL> alter index test_index unusable;
Index altered.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162/abstract/1/ ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人微信公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： ............................................................................................................................................. ● QQ群号： 230161599 （满） 、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2017-08-01 06:00 ~ 2017-08-31 24:00 在魔都完成 ● 最新修改时间： 2018-03-22 18:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ............................................................................................................................................. ● 小麦苗的微店 ： ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 好消息：小麦苗OCP、OCM开班啦，详情请点击 ： http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用 微信客户端 扫描下面的二维码来关注小麦苗的微信公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）， 学习最实用的数据库技术。 【QQ：646634621】【微信：db_bao】【微信公众号：DB宝】【QQ群：230161599】【Oracle OCP、OCM、高可用（RAC+DG+OGG）、MySQL OCP、PGCA+PGCE+PGCM等都可以找麦老师了】【个人网站：xmmup.com】
下面测试Oracle 11g开始的新特性truncate的增强和延迟段空间创建。 Oracle从11g开始，当用户创建一张空表的时候不会先分配段和空间，只有当对这张表插入第一行数据的时候才分配段和空间。 这就解决了有很多表是空的但是占用大量的磁盘空间。 测试11g延迟段创建和truncate 用户下有一张新表 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- TEST1 TABLE SQL> select segment_name,bytes from user_segments; ----还没有给分配空间 no rows selected SQL> insert into test1 select * from dba_objects where rownum<2; -----插入一行记录 1 row created. SQL> select segment_name,bytes from user_segments; ----分配空间 SEGMENT_NAME BYTES ------------------------------ ---------- TEST1 65536 SQL> commit; Commit complete. SQL> alter table test1 allocate extent; ------手动给表分配一个区 Table altered. SQL> select segment_name,bytes from user_segments; SEGMENT_NAME BYTES ------------------------------ ---------- TEST1 131072 SQL> truncate table test1; ------使用不加子句的truncate Table truncated. SQL> select segment_name,bytes from user_segments; -----只是删除到最小的区 SEGMENT_NAME BYTES ------------------------------ ---------- TEST1 65536 SQL> truncate table test1 drop all storage; -----使用11g中新加的子句drop all stroage Table truncated. SQL> select segment_name,bytes from user_segments; -----段全部被回收 no rows selected很多数据库都有存在空表的情况，较多的空表会占用大量的磁盘空间， ORACLE 在 11gR2 版本推出延迟段创建新特性，所谓延迟段创建，顾名思义就是在创建一张新空表的时候， ORACLE 默认不会为这张空表分配段（ SEGMENTS ），也就是不会为这张空表分配空间，这样就避免了空表占用空间的情况，如下实验： SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> CREATE TABLE T_TEST_1(ID NUMBER,NAME VARCHAR2(10)); 表已创建。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='T_TEST_1'; 默认情况下 ORACLE 没有为空表（ T_TEST_1 ）分配空间，如果查看过 ORACLE11gR2 官方文档关于 CREATE TABLE 语法的人可能会看到 SEGEMENT CREATION 信息，如下：
当 SEGEMENT CREATION 为 IMMEDIATE 的情况下， ORACLE 在建表的时候，会为表建立段（ SEGMENTS ），当 SEGEMENT CREATION 为 DEFERRED 的情况下， ORACLE 不会为空表建立段 , 下面分别演示下这两种情况的效果。 SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION IMMEDIATE; 表已创建。 SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION DEFERRED; 表已创建。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------- T_TEST_2 可以看到，在 SEGEMENT CREATION 为 IMMEDIATE 的情况下， ORACLE 为 T_TEST_2 建立了段，在 SEGEMENT CREATION 为 DEFERRED 的情况下， ORACLE 没有为表 T_TEST_3 建立段，当向没有分配段的空表中插入信息时， ORACLE 会自动为空表建立段。 SQL> INSERT INTO T_TEST_1 VALUES(1,'STREAM'); 已创建 1 行。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 也可以用 ALLOCATE EXTENT 的方式来为空表建立段信息。 SQL> ALTER TABLE T_TEST_3 ALLOCATE EXTENT; 表已更改。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 T_TEST_3 虽然延迟段创建避免了空表占用空间的问题，但是也为 DBA 带点小麻烦，这就是在 EXP 导出数据的时候，虽然空表的信息也存在数据库字典内，但是 ORACLE 不会导出未分配段的空表，这样在使用 EXP 做数据迁移的时候，就会遇到点小问题。 SQL> CREATE USER dbdream IDENTIFIED BY dbdream DEFAULT TABLESPACE USERS; 用户已创建。 SQL> GRANT CONNECT,RESOURCE TO DBDREAM; 授权成功。 SQL> CREATE TABLE T_TEST_1(ID NUMBER,NAME VARCHAR2(10)); 表已创建。 SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) 2 SEGMENT CREATION IMMEDIATE; 表已创建。 D:\ >exp dbdream/dbdream file=d:\dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:35:22 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的用户 ... . . 正在导出表 T_TEST_2 导出了 0 行 成功终止导出 , 没有出现警告。 EXP 只能导出已经分配段的表，要导出未分配段的空表 EXP 就无能为力了，要导出未分配段的空表就需要使用数据泵（ EXPDP ），使用 EXPDP 可以导出未分配段的空表。 SQL> CREATE DIRECTORY D_TEST AS 'D:\T_TEST'; 目录已创建。 SQL> GRANT READ,WRITE ON DIRECTORY D_TEST TO DBDREAM; 授权成功。 D:\ >expdp dbdream/dbdream directory=D_TEST dumpfile=dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:50:00 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 正在使用 BLOCKS 方法进行估计 ... . . 导出了 "DES"."T_TEST_1" 0 KB 0 行 . . 导出了 "DES"."T_TEST_2" 0 KB 0 行 作业 "DES"."SYS_EXPORT_SCHEMA_01" 已于 11:50:47 成功完成 如果非要用 EXP 做迁移，而且所有空表也都需要迁移，那么就需要使用上文提到的利于 ALLOCATE EXTENT 创建段的方法。在做 EXP 操作之前，先使用 ALLOCATE EXTENT 的方法为空表分配段信息。 SQL> DECLARE 2 V_COUNT NUMBER; 3 BEGIN 4 FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP 5 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_COUNT; 6 IF V_COUNT = 0 THEN 7 EXECUTE IMMEDIATE 'ALTER TABLE ' || I.TABLE_NAME || ' ALLOCATE EXTENT'; 8 END IF; 9 END LOOP; 10 END; 11 / PL/SQL 过程已成功完成。 SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE'T_TEST%'; SEGMENT_NAME ------------ T_TEST_1 T_TEST_2 然后在用 EXP 导出数据，这样空表就可以被导出了。 D:\ >exp dbdream/dbdream file=d:\dbdream.dmp Export: Release 11.2.0.1.0 - Production on 星期一 2 月 13 11:58:03 2012 连接到 : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的用户 ... . . 正在导出表 T_TEST 导出了 0 行 . . 正在导出表 T_TEST_2 导出了 0 行 成功终止导出 , 没有出现警告。 ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})] ----------- 故，需要构建如下样子简单的SQL命令： ----------- alter table TableName allocate extent ----------- 但要是每一张表写一条语句的话太过麻烦，为了方便我们使用SQL命令拼写出每一张表的alter语句。 SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null（注意：很多教程没有这里，这里是有可能位空的） 上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条)，我们只需要将其生成的所有sql代码全部执行，就可以给每一张已经存在的表来分配segment，就OK了。 最后： 这时再用exp导出就没有问题了。 但是： 数据库本身的deferred_segment_creation属性还是TRUE,也是就是说如果再创建新表的话，默认还是不分配segment的。所以还是需要更改deferred_segment_creation的参数，以便以后创建的新表自动分配segment。 如果你的数据库还没有创建任何数据表，那么直接修改deferred_segment_creation属性，以后创建的表无论是不是为空都会自动分配segment，就不会出现导不出空表的情况。然而如果你的数据库中已经有很多空表，并且需要导出来，那么光修改deferred_segment_creation属性则没有用的，因为它只对之后创建的表有作用。你需要给已存在的空表分配segment以便可以导出存在的空表，就用到上面讲的allocate extent方法，但此方法只针对已经存在的表的segment属性，所以最好就是：先给已存在的空表分配segment，方便其可以直接导出，然后设定deferred_segment_creation参数以便以后每张表无论是否为空都自动分配segment。 SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null; 批量输出上述生成的SQL语句并写入到一个.sql的脚本文件中。 1. 创建执行脚本文件：我创建一个E:\sql_script.sql文件。内容如下： set heading off; set echo off; set feedback off; set termout on; spool E:\sql_allocate.sql; Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null ; spool off; 这个脚本的作用就是创建一个E:\sql_allocate.sql脚本文件，将Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null 的执行结果（就是给每张表生成segment的SQL代码）批量输出，存储到一个E:\sql_allocate.sql的脚本文件中。 APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.
PURPOSE This bulletin provides information about the database version 11.2 new feature called Deferred Segment Creation also referred to as Segment Creation On-Demand. SCOPE Database Administrators and Developers. DETAILS
The advantages of this new space allocation method are:
? A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
? The application installation time is reduced, because the creation of a table is a data dictionary operation only.
When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance.
Note: With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. For more details, see the Oracle Database Administrator's Guide. SQL> connect / as sysdba
Connected.
SQL> show parameter deferred
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> connect imran/imran
Connected.
SQL> create table t1(no number);
Table created.
SQL> select * from user_segments;
no rows selected
SQL> insert into t1 values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> select table_name,segment_created from user_tables;
TABLE_NAME SEG
------------------------------ ---
T1 YES
? This slide shows you how to verify the segment creation on demand. First you check the parameter DEFERRED_SEGMENT_CREATION . Then a table is created without segments, which you can verify by querying the USER_SEGMENTS data dictionary view. After the insert of a row, you query this view again, to see that the segment now exists.
? You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created. Note that when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
? Another addition to the data dictionary is the SYS.SEG$ table that stores the storage parameters which you specified during the table or index creation.
You can control segment creation in two ways:
1) With the DEFERRED_SEGMENT_CREATION initialization parameter set to TRUE or FALSE. This parameter can be set in the initialization file. You can also control it via the ALTER SESSION or ALTER SYSTEM commands.
Example: SQL> connect / as sysdba
Connected.
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation=FALSE;
System altered.
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
SQL> connect imran/imran
Connected.
SQL> create table t2(no number);
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> alter session set deferred_segment_creation=true;
Session altered.
SQL> create table t3( no number);
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
2) With the SEGMENT CREATION clause of the CREATE TABLE command:
SEGMENT CREATION DEFERRED: If specified, segment creation is deferred until the first row is inserted into the table. This is the default behavior for the Oracle Database 11gR2.
SEGMENT CREATION IMMEDIATE: If specified, segments are materialized during table creation. This is the default behavior in Oracle databases prior to the Oracle Database 11gR2.
Example SQL> select * from user_segments;
no rows selected
SQL> CREATE TABLE SEG_TAB3(C1 number, C2 number)
SEGMENT CREATION IMMEDIATE;
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3
SQL>CREATE TABLE SEG_TAB4(C1 number, C2 number)
SEGMENT CREATION DEFERRED;
Table created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3
This clause takes precedence over the DEFERRED_SEGMENT_CREATION parameter. It is possible to force creation of segments for an already created table with the ALTER TABLE …MOVE command. However, it is not possible to directly control the deferred segment creation for dependant objects like indexes. They inherit this characteristic from their parent object, in this case, the table.
Restrictions and Exceptions :- ?To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.
? In the Oracle Database 11.2.0.1, deferred segment creation is restricted to non-partitioned tables and non-partitioned indexes. Deferred segment creation is not supported for partitioned indexes, bitmap join indexes, and domain indexes.
? IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables are not supported. Tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.
? Segment creation on demand is not supported for tables created in dictionary-managed tablespaces and for clustered tables. If you try creating these tables, segments ARE created.
? Also please note: If you create a table with deferred segment creation on a locally managed tablespace, then it has no segments. If at a later time, you migrate the tablespace to dictionary-managed, then any attempt to create segments produces errors. In this case you must drop the table and recreate it. Note:
In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables. This restriction is removed in release 11.2.0.2 and later.
The following rules apply to a table whose segment has not yet been materialized:
If you create this table with CREATE TABLE ... AS subquery, then if the source table has no rows, segment creation of the new table is deferred. If the source table has rows, then segment creation of the new table is not deferred.
If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized, then the segment is materialized and then an extent is allocated. However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.
During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment, segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.
When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.
In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored.
ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE.
Parallel DML operations on tables with no segments are disabled.
Additional Automatic Functionality:-
Additional enhancements in the Oracle Database 11gR2 (unrelated to the deferred segment creation) are implemented to save space: all UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you. It is enabled by default with the COMPATIBILITY initialization parameter set to 11.2.0.0. ? Creating an index without a segment
CREATE INDEX test_i1 ON seg_test(c) UNUSABLE;
? Removing any allocated space for an index
ALTER INDEX test_i UNUSABLE;
? Creating the segment for an index:
ALTER INDEX test_i REBUILD;
The following slide shows some SQL commands which you might find useful for this new functionality:
? You can create an index without segment with the CREATE INDEX … UNUSABLE clause.
? You can remove any allocated space with the ALTER INDEX … UNUSABLE clause.
? And finally, you can create a segment for an index with the ALTER INDEX … REBUILD
Example SQL> select * from user_segments ;
no rows selected
SQL> create table test_table(no number);
Table created.
SQL> insert into test_table values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
SQL> create index test_index on test_table(no) unusable;
Index created.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
SQL> alter index test_index rebuild;
Index altered.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
TEST_INDEX
SQL> alter index test_index unusable;
Index altered.
SQL> select segment_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162/abstract/1/ ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人微信公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： ............................................................................................................................................. ● QQ群号： 230161599 （满） 、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2017-08-01 06:00 ~ 2017-08-31 24:00 在魔都完成 ● 最新修改时间： 2018-03-22 18:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ............................................................................................................................................. ● 小麦苗的微店 ： ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 好消息：小麦苗OCP、OCM开班啦，详情请点击 ： http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用 微信客户端 扫描下面的二维码来关注小麦苗的微信公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）， 学习最实用的数据库技术。 【QQ：646634621】【微信：db_bao】【微信公众号：DB宝】【QQ群：230161599】【Oracle OCP、OCM、高可用（RAC+DG+OGG）、MySQL OCP、PGCA+PGCE+PGCM等都可以找麦老师了】【个人网站：xmmup.com】
注册时间： 2012-09-23
10636321
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
推荐文章
|
|
拉风的电池 · 统计信息简介 | PingCAP 文档中心 10 月前 |
|
|
刀枪不入的马铃薯 · oracle cdc报错:NoSuchMethodError: org.apache.kafka.common.utils.Utils.isBlank(Ljava/lang/String；)Z_nos 1 年前 |
|
|
活泼的椰子 · power bi动态条形图改颜色 - 抖音 1 年前 |