创建表空间

我在这里只写几个比较常用的表空间创建方法

1.创建自动扩展的表空间

SQL> create tablespace tab01

  2  datafile '/u01/tab01.dbf' size 10m  

  3  extent management local autoallocate;

Tablespace created.

2.创建等量扩展的表空间

SQL> create tablespace tab02

  2  datafile '/u01/tab02.dbf' size 10m

  3  extent management local uniform. size 1m;

Tablespace created.

3.创建临时表空间

SQL> create temporary tablespace temp01

  2  tempfile '/u01/temp01.dbf' size 2m; 

Tablespace created.

创建temporay tablespace的时候出现了问题：

SQL>  create temporary tablespace temp01

  2   tempfile'/u01/temp01.dbf' size 2m

  3  extent management local autoallocate;

extent management local autoallocate

                        *

ERROR at line 3:

ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

SQL> create temporary tablespace temp02

  2   tempfile '/u01/temp02.dbf' size 2m

  3  e xtent management local uniform. size 1m;

Tablespace created.

表明创建temporary tablespace 的时候必须 uniform

SQL> create temporary tablespace temp03

  2  tempfile '/u01/temp03.dbf' size 2m

  3  segment space management auto;

segment space management auto

                         *

ERROR at line 3:

ORA-30573: AUTO segment space management not valid for this type of tablespace

SQL> create temporary tablespace temp03

  2  tempfile '/u01/temp03.dbf' size 2m

  3   segment space management manual;

Tablespace created.

表明temporary tablespace 的时候必须 segment space management manual;

4.创建undo tablespace

SQL> create undo tablespace undo01

  2  datafile '/u01/undo.dbf' size 1m;

Tablespace created.

5.创建bigfile tablespace

什么是bigfile tablespace和smallfile?

BigFile创建的TableSpace可以达到2的32次方个blocks,只能创建一个Datafile。 而SmallFile最大到2的22次方，SmallFile最多可以创建1022个Datafile，总和稍少于BigFile。

Bigfile tablespaces are intended for very large databases. When a very large database has thousands of read/write datafiles, operations that must update the datafile headers, such as checkpoints, can take

a relatively long time. If you reduce the number of datafiles, these operations can complete faster.Smallfile tablespace

is the new name for the old Oracle tablespace datafile option. With a smallfile tablespace, you can have multiple datafiles for a tablespace.（引自administration I）

SQL> create bigfile tablespace bfspace    

  2  datafile '/u01/bfspace.dbf' size 2m;

Tablespace created.

简单的把几种命令创建表空间的方法写出来了。当然还有其他方法，比如用TOAD。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22821701/viewspace-631688/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22821701/viewspace-631688/