11g deferred_segment_creation

最新推荐文章于 2021-04-12 22:25:56 发布

原创最新推荐文章于 2021-04-12 22:25:56 发布 · 827 阅读

0 ·

CC 4.0 BY-SA版权

体系结构专栏收录该内容

7 篇文章

订阅专栏

本文探讨了Oracle 11g中Deferred Segment Creation的功能，包括其对不同用户表的影响、导出工具的支持情况以及如何使用DBMS_SPACE_ADMIN包来管理和操作这类段。演示了sys用户与普通用户下表的行为差异，以及EXP与EXPDP工具的不同表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

11g deferred_segment_creation

1.普通用户下的表适用。

2.exp不能导出，expdp可以导出。

3.exec dbms_space_admin.DROP_EMPTY_SEGMENTS(schema_name => 'SCOTT',table_name => 'GGG'); 是11.2.0.2才有的新特性

1.sys表:

SQL> create table sys (id number);

Table created.

SQL> select COUNT(*) from dba_segments where segment_name='SYS';

COUNT(*)
----------
1

普通用户表：

SQL> create table scott.ggg (id number);

Table created.

SQL> select count(*) from dba_segments where segment_name='GGG';

COUNT(*)
----------
0

[oracle@localhost ~]$ exp 'scott/tiger' tables=ggg file=1.dmp

Export: Release 11.2.0.1.0 - Production on Sat May 25 15:27:26 2013

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

About to export specified tables via Conventional Path ...
EXP-00011: SCOTT.GGG does not exist
Export terminated successfully with warnings

[oracle@localhost ~]$ expdp 'scott/tiger' tables=ggg dumpfile=1.dmp

Export: Release 11.2.0.1.0 - Production on Sat May 25 15:37:34 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=ggg dumpfile=1.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."GGG" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:38:20

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> exec dbms_space_admin.materialize_deferred_segments(schema_name => 'SCOTT',table_name => 'GGG');

begin dbms_space_admin.materialize_deferred_segments(schema_name => 'SCOTT',table_name => 'GGG'); end;

ORA-06550: line 2, column 24:
PLS-00302: component 'MATERIALIZE_DEFERRED_SEGMENTS' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

SQL> exec dbms_space_admin.DROP_EMPTY_SEGMENTS(schema_name => 'SCOTT',table_name => 'GGG');

begin dbms_space_admin.DROP_EMPTY_SEGMENTS(schema_name => 'SCOTT',table_name => 'GGG'); end;

ORA-06550: line 2, column 24:
PLS-00302: component 'DROP_EMPTY_SEGMENTS' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored