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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-610592/