/*
小实验:在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.