查看表空間的信息

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）查看所有的（或者用戶可以訪問(wèn)的）表空間
SQL> select * from dba_tablespaces;
SQL> select * from user_tablespaces;
 
 
3）查看所有的（或者用戶可以訪問(wèn)的）表空間內(nèi)的空閑區(qū)間的信息
SQL> select * from dba_free_space;
SQL> select * from user_free_space;
SQL> desc dba_free_space;
 名稱                                      是否為空? 類(lèi)型
 ----------------------------------------- -------- -----------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER
 
dba_free_space 各字段的定義如下：（以下是oracle官方網(wǎng)的定義）

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）查看所有數(shù)據(jù)文件（臨時(shí)文件）的信息
SQL> select * from V$datafile;
SQL> select * from V$tempfile;
 
5)查看所有屬于表空間的數(shù)據(jù)（或臨時(shí)）文件
SQL> select * from dba_data_files;
SQL> select * from dba_temp_files;
 
6)查看臨時(shí)文件的使用/剩余空間
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）相看用戶的默認(rèn)和臨時(shí)表空間
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       &n
文章出處：http://www./course/7_databases/oracle/oraclejs/2008727/134187.html