Oracle 之表空间常用查询命令 -优快云博客

本文链接：https://blog.youkuaiyun.com/Tomcat_19890903/article/details/84683852

本文介绍如何在Oracle数据库中管理表空间，包括查看表空间大小、使用情况、数据文件及其状态等。同时，提供了查询表及表空间归属的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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