oracle實驗記錄 （bigfile tablespace）

關於建立tablespace的幾個問題(其中有很多引數就不寫了)
SQL> create bigfile tablespace testbig datafile 'd:\bigtestfile.ora' size 2m;

Tablespace created.

segment space management  default為manual,而bigfile tablespace 段空間管理方式必須為auto(用bitmap)
問題來了 上面建立的 default 應該為 manual 但卻成功建立了

SQL> create bigfile tablespace testbig2 datafile 'd:\bigtestfile2.ora' size 2m s
egment space management manual;
create bigfile tablespace testbig2 datafile 'd:\bigtestfile2.ora' size 2m segmen
t space management manual
*
ERROR at line 1:
ORA-32772: BIGFILE is invalid option for this type of tablespace

這個例子中寫為manual卻不成功(defalut值)
SQL> create bigfile tablespace testbig2 datafile 'd:\bigtestfile2.ora' size 2m s
egment space management auto;

Tablespace created.          auto後可以

SQL>

SQL> select tablespace_name, extent_management,segment_space_management from use
r_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
UNDOTBS1                       LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO
EXAMPLE                        LOCAL      AUTO
TEST                           LOCAL      MANUAL
TEST3                          LOCAL      MANUAL
TESTBIG                        LOCAL      AUTO*****************
TESTBIG2                       LOCAL      AUTO
看出 這個建立時候未用default manual 是BUG?還是oracle太智慧根據SQL字面 自動改成AUTO?

SQL> alter tablespace testbig add datafile 'd:\big1.dbf' size 1m;
alter tablespace testbig add datafile 'd:\big1.dbf' size 1m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

小結:bigfile tablespace 段空間管理必為auto 且 只能有一個datafile

建立temptablespace

 

temptable  區分配必須為 uniform
SQL> create temporary tablespace temptest tempfile 'd:\testtemp.dbf' size 10m un
iform. size 64k ;

Tablespace created.

SQL> create temporary tablespace temptest2 tempfile 'd:\testtemp2.dbf' size 10m
autoallocate ;
create temporary tablespace temptest2 tempfile 'd:\testtemp2.dbf' size 10m autoa
llocate
                                                                           *
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

SQL> create temporary tablespace temptest2 tempfile 'd:\testtemp2.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name, next_extent,segment_space_management,allocation_typ
e from user_tablespaces;

TABLESPACE_NAME                NEXT_EXTENT SEGMEN ALLOCATIO
------------------------------ ----------- ------ ---------
SYSTEM                                     MANUAL SYSTEM
UNDOTBS1                                   MANUAL SYSTEM
SYSAUX                                     AUTO   SYSTEM
TEMP                               1048576 MANUAL UNIFORM
USERS                                      AUTO   SYSTEM
EXAMPLE                                    AUTO   SYSTEM
TEST                                       MANUAL SYSTEM
TEST3                                      MANUAL SYSTEM
TESTBIG                                    AUTO   SYSTEM
TESTBIG2                                   AUTO   SYSTEM
TEMPTEST                             65536 MANUAL UNIFORM

TABLESPACE_NAME                NEXT_EXTENT SEGMEN ALLOCATIO
------------------------------ ----------- ------ ---------
TEMPTEST2                          1048576 MANUAL UNIFORM

12 rows selected.
SQL> create temporary tablespace temptest3 tempfile 'd:\testtemp3.dbf' size 10m
segment space management auto;
create temporary tablespace temptest3 tempfile 'd:\testtemp3.dbf' size 10m segme
nt space management auto

                    *
ERROR at line 1:
ORA-30573: AUTO segment space management not valid for this type of tablespace

SQL> create temporary tablespace temptest3 tempfile 'd:\testtemp3.dbf' size 10m
segment space management manual;必須為manual

Tablespace created.
分析 :分割槽 必須uniform,當不寫時候沒用default值 default uniform. 1m ,segment space management 必須為manual (default)

 

 

bigfile tempspace
段管理方式 必須為manual 用freelist(default)
 分割槽大小必須 uniform
SQL> create bigfile temporary tablespace temptest4 tempfile 'd:\testtemp4.dbf' s
ize 10m segment space management manual;

Tablespace created.

SQL> create bigfile temporary tablespace temptest5 tempfile 'd:\testtemp5.dbf' s
ize 10m segment space management auto;
create bigfile temporary tablespace temptest5 tempfile 'd:\testtemp5.dbf' size 1
0m segment space management auto

                            *
ERROR at line 1:
ORA-30573: AUTO segment space management not valid for this type of tablespace

dufault 區分配方式為autoallocate

SQL> create bigfile temporary tablespace temptest5 tempfile 'd:\temptest5.dbf' s
ize 10m EXTENT MANAGEMENT LOCAL autoallocate   ;
create bigfile temporary tablespace temptest5 tempfile 'd:\temptest5.dbf' size 1
0m EXTENT MANAGEMENT LOCAL autoallocate

                           *
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

SQL> create bigfile temporary tablespace temptest5 tempfile 'd:\temptest5.dbf' s
ize 10m EXTENT MANAGEMENT LOCAL uniform   ;

Tablespace created.

總結:bigfile tempspace 也必須uniform

SQL> create tablespace test6 datafile 'd:\test6b.dbf' size 1m,'d:\test6a.dbf' si
ze 1m;

Tablespace created.建立時候多datafile ,分開

 

SQL> drop tablespace test3 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace test5 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace test6 including contents and datafiles;

Tablespace dropped.

SQL> alter tablespace test rename to xhtest;~10G

Tablespace altered.

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-610592/，如需轉載，請註明出處，否則將追究法律責任。