11g deferred_segment_creation

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

 

2.

[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

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 - 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

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 - 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

 

3

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