oracle 表空间_Oracle技术教程_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       &n
文章出处：http://www.diybl.com/course/7_databases/oracle/oraclejs/2008727/134187.html