caopom超级在线视频:oracle 表空间

（一）查看表空间的信息
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. Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30)   Name of the tablespace containing the extent
FILE_ID NUMBER   File identifier number of the file containing the extent
BLOCK_ID NUMBER   Starting block number of the extent
BYTES NUMBER   Size of the extent (in bytes)
BLOCKS NUMBER   Size of the extent (in Oracle blocks)
RELATIVE_FNO NUMBER   Relative file number of the file containing the extent
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. Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30) NOT NULL Tablespace name
USERNAME VARCHAR2(30) NOT NULL User with resource rights on the tablespace
BYTES NUMBER
Number of bytes charged to the user
MAX_BYTES NUMBER
User's quota in bytes, or -1 if no limit
BLOCKS NUMBER NOT NULL Number of Oracle blocks charged to the user
MAX_BLOCKS NUMBER
User's quota in Oracle blocks, or -1 if no limit
（二）创建表空间 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; 表空间已删除。