Oracle 之表空间常用查询命令（转）

 

 

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

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
2 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
2 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
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
3 group by tablespace_name) a,
4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
5 group by tablespace_name) b
6 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
2 where table_name='FGA_TEST001';

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

 

7、查询某个表空间下有哪些表
SQL> select tablespace_name,table_name from dba_tables
2 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
2 from dba_tables a,dba_Extents b
3 Where a.Tablespace_Name=a.Tablespace_Name
4 AND a.tablespace_name='FGA_TEST'
5 group by a.tablespace_name, a.table_name
6 ;

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