測試alter table storage及dbms_space_admin包

  ###測試alter table storage及dbms_space_admin包(本地管理表空間local及auto segment space management)
SQL> create table test_storage(a int,b int) storage(initial 40k next 40k pctincrease 5 minextents 1 maxextents 1000);--顯式指定storage儲存屬性建表

Table created.

SQL> desc user_segments;
 Name                                                                                                                                                                          Null?    Type
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------

----------------------------------------------------------------------------------------------
 SEGMENT_NAME                                                                                                                                                                           VARCHAR2(81)
 PARTITION_NAME                                                                                                                                                                         VARCHAR2(30)
 SEGMENT_TYPE                                                                                                                                                                           VARCHAR2(18)
 TABLESPACE_NAME                                                                                                                                                                        VARCHAR2(30)
 BYTES                                                                                                                                                                                  NUMBER
 BLOCKS                                                                                                                                                                                 NUMBER
 EXTENTS                                                                                                                                                                                NUMBER
 INITIAL_EXTENT                                                                                                                                                                         NUMBER
 NEXT_EXTENT                                                                                                                                                                            NUMBER
 MIN_EXTENTS                                                                                                                                                                            NUMBER
 MAX_EXTENTS                                                                                                                                                                            NUMBER
 PCT_INCREASE                                                                                                                                                                           NUMBER
 FREELISTS                                                                                                                                                                              NUMBER
 FREELIST_GROUPS                                                                                                                                                                        NUMBER
 BUFFER_POOL                                                                                                                                                                            VARCHAR2(7)

SQL> select segment_name,extents,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_segments where segment_name='TEST_STORAGE';--oracle會自動對相關建表引數進行調整，比如max_extents（從

                                                                                                                                                       1000到2147483645)

SEGMENT_NAME                                                                         EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
--------------------------------------------------------------------------------- ---------- -------------- ----------- ----------- ----------- ------------
TEST_STORAGE                                                                               1          40960                       1  2147483645

SQL> alter table test_storage storage(next 2000k);--不允許對錶儲存引數進行變更
alter table test_storage storage(next 2000k)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> alter table test_storage storage(pctincrease 20);---同上
alter table test_storage storage(pctincrease 20)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,pct_increase  from user_tablespaces;--檢視錶所屬的表空間的extent及segment管理方式，各為local及auto

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN PCT_INCREASE
------------------------------ ---------- --------- ------ ------------
SYSTEM                         LOCAL      SYSTEM    MANUAL
UNDOTBS1                       LOCAL      SYSTEM    MANUAL
SYSAUX                         LOCAL      SYSTEM    AUTO
TEMP                           LOCAL      UNIFORM   MANUAL            0
USERS                          LOCAL      SYSTEM    AUTO
EYE                            LOCAL      SYSTEM    AUTO

6 rows selected.

#######摘錄相關錯誤如下
SQL> host oerr ora 25150
25150, 00000, "ALTERING of extent parameters not permitted"
// *Cause: An attempt was made to alter the extent parameters for a segment   ###說明對於assm方式且以統一分式或自動分配extent的表空間，不能進行變更屬性操作的
//         in a tablespace with autoallocate or uniform. extent allocation
//         policy.
// *Action: Remove the appropriate extent parameters from the command.

 

SQL> exec dbms_space_admin.tablespace_migrate_from_local('EYE');---用包dbms_space_admin把表空間eye從本地管理方式轉化為字典管理方式，失敗
BEGIN dbms_space_admin.tablespace_migrate_from_local('EYE'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> host oerr ora 10616 --查閱以上錯誤,提示不能對auto segment空間管理方式進行以上轉化操作
10616, 00000, "Operation not allowed on this tablespace"
// *Cause:  Cannot perform. the operation on tablespace with AUTO
//          SEGMENT SPACE MANAGEMENT
// *Action:  Recheck the tablespace name and re-issue the statement

 

SQL> exec dbms_space_admin.tablespace_migrate_from_local(upper('undotbs1'));--不允許對undotbs表空間進行轉化操作
BEGIN dbms_space_admin.tablespace_migrate_from_local(upper('undotbs1')); END;

*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> create tablespace test_tbs datafile '/oracle/oradata/test_tbs.dbf' size 100m segment space management manual;--無奈，我就新建一個auto segment space management方式的本地local管理表空間

Tablespace created.

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,pct_increase  from user_tablespaces where tablespace_name='TEST_TBS';--查新建的表空間test_storage

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN PCT_INCREASE
------------------------------ ---------- --------- ------ ------------
TEST_TBS                       LOCAL      SYSTEM    MANUAL

SQL> exec dbms_space_admin.tablespace_migrate_from_local('TEST_TBS');--再次轉化還是不成
BEGIN dbms_space_admin.tablespace_migrate_from_local('TEST_TBS'); END;

*
ERROR at line 1:
ORA-12914: Cannot migrate tablespace to dictionary managed type
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> host oerr ora 12914  ##查閱錯誤，說明只要system表空間是本地管理方式，就不能把表空間轉化為字典管理方式，暈
12914, 00000, "Cannot migrate tablespace to dictionary managed type"
// *Cause: Attemp to migrate locally managed tablespace to  dictionary managed
//         type when the database has locally managed system tablespace.
// *Action: Command cannot be issued.

SQL>