oracle表空间 Oracle10g学习系列（10）

表空间的四种状态：online，offline，read write和ready only

SQL> select file#,status,enabled from v$datafile;

FILE# STATUS ENABLED

-------- --------- -------------

1 SYSTEM READ WRITE

2 ONLINE READ WRITE

3 ONLINE READ WRITE

4 ONLINE READ WRITE

5 ONLINE READ WRITE

users表空间状态改变：

SQL>alter tablespace users offline;

SQL>alter tablespace users online;

SQL>alter tablespace users read write;

SQL>alter tablespace users read only;

创建表空间：

SQL>create tablespace tb01 datafile '/u01/oracle/oradata/orcl/tb01.dbf' size 10m autoextent on;

表空间重命名：

SQL>alter tablespace tb01 rename to tb02;

表空间自动扩张,AUTOEXTENSIBLE自动扩张字段：

SQL> select tablespace_name,file_name,autoextensible from dba_data_files;

TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE

------------------------ ------------------------- --------------

USERS /home/oracle/oradata/orcl/users01.dbf YES

SYSAUX /home/oracle/oradata/orcl/sysaux01.dbf YES

UNDOTBS1 /home/oracle/oradata/orcl/undotbs01.dbf YES

SYSTEM /home/oracle/oradata/orcl/system01.dbf YES

EXAMPLE /home/oracle/oradata/orcl/example01.dbf YES

SQL> select file#,name from v$datafile;

FILE# NAME

------- ----------------------------------------

1 /home/oracle/oradata/orcl/system01.dbf

2 /home/oracle/oradata/orcl/undotbs01.dbf

3 /home/oracle/oradata/orcl/sysaux01.dbf

4 /home/oracle/oradata/orcl/users01.dbf

5 /home/oracle/oradata/orcl/example01.dbf

SQL>alter database datafile 6 resize autoextend on;

SQL>alter tablespace tb01 add datafile '/u01/oracle/oradata/orcl/tb01.dbf' size 30m autoextent on;

表空间文件重命名：

前提是数据库是归档模式，数据库处于mount或者open模式，open模式的时候，表空间要offline，最后把表空间online：

SQL>alter tablespace tb01 offline;

SQL>ho cp /u01/oracle/oradata/orcl/tb01.dbf /u01/oracle/oradata/orcl/tb02.dbf

SQL>alter tablespace rename datafile '/u01/oracle/oradata/orcl/tb01.dbf' to '/u01/oracle/oradata/orcl/tb02.dbf';

SQL>alter tablespace tb01 online;

需要介质恢复的话：

SQL>recover tablespace tb01;

删除表空间：

SQL>drop tablespace tb01 including contents and datafiles;

查看表空间：

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME

----------------

USERS

SYSAUX

UNDOTBS1

SYSTEM

EXAMPLE

SQL> select name from v$tablespace;

NAME