[bbk5119] 第105集 -第13章 - 表空间管理 03

 /*

  小实验:在11g下创建表、索引等段信息,在不插入数据的情况下查看其段信息是否产生,这是Oracle 11g r2的一个改进;对比oracle 10g版本的功能就会清晰可见;

*/ 

SQL> conn test/test
Connected.
SQL> select table_name,min_extents from user_tables;

TABLE_NAME                     MIN_EXTENTS
------------------------------ -----------
EMP1                                     1
SEG_TEST                                 1
EXT_EMP2
EXT_EMP1

SQL> create table emp3
  2  as
  3  select * from emp1 where 1=2;

Table created.

SQL> create index emp3_empid_idx on emp3(employee_id) ;

Index created.

SQL> select table_name,min_extents from user_tables;

TABLE_NAME                     MIN_EXTENTS
------------------------------ -----------
EMP1                                     1
SEG_TEST                                 1
EMP3
EXT_EMP2
EXT_EMP1

SQL> desc user_segments;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 SEGMENT_SUBTYPE                                    VARCHAR2(10)
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 RETENTION                                          VARCHAR2(7)
 MINRETENTION                                       NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
EMP1
SEG_TEST
EMP_EMPLOYEE_ID_IDX

SQL> select index_name,min_extents from user_indexes;

INDEX_NAME                     MIN_EXTENTS
------------------------------ -----------
EMP3_EMPID_IDX
EMP_EMPLOYEE_ID_IDX                      1

SQL> select extent_id from user_extents where segment_name = 'EMP3';


no rows selected

 
SQL> select extent_id from user_extents where segment_name = 'EMP3';

no rows selected

SQL> select extent_id from user_extents where segment_name = 'EMP3_EMPID_IDX';

no rows selected

 

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB

 

在11g 版本中,在user_segments数据字典中,未插入数据的表,是没有段信息存在的,一旦插入数据,则 段信息(表、索引)信息则会在插入的时候触发,并分配段信息.但是在orage 10g的版本中,新创建的表及其所对应的索引信息即使没有往里面插入数据,其段信息(表及索引)也会存在.导致这种差异变化的原因,是参数

SQL> insert into emp3 select * from emp1;

107 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB

 

SQL> show parameter segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

 

假如在创建表时,指定参数选项segment creation immediate,则创建表后立即分配segment信息

SQL> conn test/test
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP1                           TABLE
EMP3                           TABLE
EXT_EMP1                       TABLE
EXT_EMP2                       TABLE
SEG_TEST                       TABLE

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB

SQL> create table emp4
  2  segment creation immediate
  3  as
  4  select * from emp1 where 1=2;

Table created.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB

6 rows selected.

SQL> create index emp4_empid_idx on emp4(employee_id);

Index created.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB
EMP4_EMPID_IDX                 TEST_TAB

7 rows selected.

 

假如将索引修改为unusable状态,其索引段信息将不复存在

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB
EMP4_EMPID_IDX                 TEST_TAB

7 rows selected.

SQL> alter index emp4_empid_idx unusable;

Index altered.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB

6 rows selected.

 

假如将处于unusable状态的索引进行rebuild操作,其段信息将会重新分配

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB

6 rows selected.

SQL> alter index emp4_empid_idx rebuild;

Index altered.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP1                           TEST_TAB
SEG_TEST                       TEST_TAB
EMP3                           TEST_TAB
EMP4                           TEST_TAB
EMP_EMPLOYEE_ID_IDX            TEST_TAB
EMP3_EMPID_IDX                 TEST_TAB
EMP4_EMPID_IDX                 TEST_TAB

7 rows selected.

 

 

 

 

 

posted @ 2013-06-08 17:42  ArcerZhang  阅读(250)  评论(0编辑  收藏  举报