（一）查看表空间的信息
1）从控制文件中得到的所有表空间的名称
SQL> select * from V$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
已选择6行。
2）查看所有的（或者用户可以访问的）表空间
SQL> select * from dba_tablespaces;
SQL> select * from user_tablespaces;
3）查看所有的（或者用户可以访问的）表空间内的空闲区间的信息
SQL> select * from dba_free_space;
SQL> select * from user_free_space;
SQL> desc dba_free_space;
名称 是否为空? 类型
----------------------------------------- -------- -----------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
dba_free_space 各字段的定义如下：（以下是oracle官方网的定义）
DBA_FREE_SPACE
DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
Related View
USER_FREE_SPACE describes the free extents in the tablespaces accessible to the current user.
4）查看所有数据文件（临时文件）的信息
SQL> select * from V$datafile;
SQL> select * from V$tempfile;
5)查看所有属于表空间的数据（或临时）文件
SQL> select * from dba_data_files;
SQL> select * from dba_temp_files;
6)查看临时文件的使用/剩余空间
SQL> select * from V$temp_space_header;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE
------------------------------ ---------- ---------- ----------- ----------
BLOCKS_FREE RELATIVE_FNO
----------- ------------
TEMP 1 19922944 2432 1048576
128 1
7）查看用户的默认和临时表空间
SQL> select * from dba_users;
8）查看所有用户的表空间配额
SQL> select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
SYSAUX DMSYS 262144 209715200 32 25600 NO
SYSAUX SYSMAN 52101120 -1 6360 -1 NO
SYSAUX OLAPSYS 16318464 -1 1992 -1 NO
dba_ts_quotas 各个字段的解释如下：
DBA_TS_QUOTAS
DBA_TS_QUOTAS describes tablespace quotas for all users.
Related View
USER_TS_QUOTAS describes tablespace quotas for the current user. This view does not display the USERNAME column.
（二）创建表空间
SQL> create tablespace test datafile 'C:\oradata\testdb\test1.dat' size 1M;
表空间已创建。（permernant tablespace)
SQL> create temporary tablespace test_temp tempfile 'C:\oradata\testdb\testtemp.dat' size 2M;
表空间已创建。(temporary tablespace)
SQL> create undo tablespace testundo datafile 'C:\oradata\testdb\testundo.dat' size 2M;
表空间已创建。(undo tablespace)
详细的语法表参照官网的讲解：http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_7003.htm#SQLRF01403
（三）修改表空间：
修改表空间的语法如下：
alter tablespace 表空间名字
{ [coalesce]
[temporary|permanent]
[read only|read write]
[online]
[offline [normal|temporary|immediate|for recover]]
[minimum extent 数目[k|M]]
[default 存储参数语句]
[datafile 路径]
}
例子：
SQL> alter tablespace test read write;
SQL> alter tablespace test online;
(四)删除表空间
SQL> drop tablespace TEST_TEMP;
表空间已删除。