Oracle 之表空间常用查询命令

1. 查看所有表空间空间大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------            --------------------
UNDOTBS1                                        200
SYSAUX                                             120
TEST_DATA                                      100
FGA_TEST                                        100
USERS                                                  5
TESTDATA                                        100
SYSTEM                                             300

7 rows selected.

 

2. 已经使用的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;                                     

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------             --------------------
SYSAUX                                          20.125
UNDOTBS1                                   175.6875
TEST_DATA                                   99.875
FGA_TEST                                     98
USERS                                           4.9375
TESTDATA                                     98
SYSTEM                                         52.5

7 rows selected.

 
3. 所有表空间使用空间多少可以这样计算

SQL> select a.tablespace_name,total,free,total-free used from 
   ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
   group by tablespace_name) a,
   ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
   group by tablespace_name) b
   where a.tablespace_name=b.tablespace_name;

TABLESPACE_NAME                     TOTAL       FREE        USED
------------------------------                ----------      ----------      ----------
SYSAUX                                           120          20.125       99.875
UNDOTBS1                                      200        175.6875     24.3125
TEST_DATA                                    100          99.875           .125
FGA_TEST                                      100          98                2
USERS                                                 5            4.9375         .0625
TESTDATA                                      100          98                 2
SYSTEM                                           300          52.5          247.5

7 rows selected.

4、查看表空间和数据文件

SQL> col FILE_NAME format a50
SQL> set linesize 400
SQL> select file_name,blocks,tablespace_name from dba_data_files;

FILE_NAME                                                           BLOCKS        TABLESPACE_NAME
--------------------------------------------------                 ----------          ------------------------------
/u01/app/oracle/ora11g/test_data01.dbf                  12800            TEST_DATA
/u01/app/oracle/ora11g/users01.dbf                            640            USERS
/u01/app/oracle/ora11g/undotbs01.dbf                    25600            UNDOTBS1
/u01/app/oracle/ora11g/sysaux01.dbf                     15360             SYSAUX
/u01/app/oracle/ora11g/system01.dbf                     38400             SYSTEM
/u01/app/oracle/ora11g/testdata01.dbf                   12800             TESTDATA
/u01/app/oracle/ora11g/fga_test01.dbf                   12800             FGA_TEST

7 rows selected.

 

5、查看表空间、数据文件状态

SQL> set linesize 300
SQL> col FILE_NAME format a50
SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME, STATUS,ONLINE_STATUS from DBA_DATA_FILES;

   FILE_ID FILE_NAME                                              TABLESPACE_NAME          STATUS      ONLINE_STATUS
---------- -------------------------------------------------- -   -----------------------------      ---------------   -------------------
         5 /u01/app/oracle/ora11g/test_data01.dbf           TEST_DATA                 AVAILABLE    ONLINE
         4 /u01/app/oracle/ora11g/users01.dbf                 USERS                          AVAILABLE    ONLINE
         3 /u01/app/oracle/ora11g/undotbs01.dbf             UNDOTBS1                   AVAILABLE    ONLINE
         2 /u01/app/oracle/ora11g/sysaux01.dbf               SYSAUX                        AVAILABLE    ONLINE
         1 /u01/app/oracle/ora11g/system01.dbf               SYSTEM                       AVAILABLE    SYSTEM
         6 /u01/app/oracle/ora11g/testdata01.dbf             TESTDATA                   AVAILABLE    ONLINE
         7 /u01/app/oracle/ora11g/fga_test01.dbf             FGA_TEST                   AVAILABLE    ONLINE

7 rows selected.

 

6、查询哪个表属于哪个表空间
SQL> select table_name,tablespace_name from dba_tables
   where table_name='FGA_TEST001';

TABLE_NAME                     TABLESPACE_NAME
------------------------------    ------------------------------
FGA_TEST001                    FGA_TEST

 

7、查询某个表空间下有哪些表
SQL> select tablespace_name,table_name from dba_tables
  where tablespace_name='FGA_TEST';

TABLESPACE_NAME                TABLE_NAME
------------------------------ -      -----------------------------
FGA_TEST                                FGA_TEST001

8、查询FGA_TEST表空间下每个表占用的空间
SQL> select a.tablespace_name,a.table_name,Sum(Bytes)/1024/1024
   from dba_tables a,dba_Extents b
   Where a.Tablespace_Name=a.Tablespace_Name
   AND   a.tablespace_name='FGA_TEST'
   group by a.tablespace_name, a.table_name  ;

TABLESPACE_NAME        TABLE_NAME                     SUM(BYTES)/1024/1024
------------------------------     ------------------------------                    --------------------
FGA_TEST                          FGA_TEST001                                373.5625