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

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 extent 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博客

创建表空间

最新文章