ORACLE数据库、表空间、表的容量相关查询--1

未完待续……未完待续……未完待续……未完待续……

1.查询某个表所占空间大小

col tablespace_name for a15
col segment_name for a15
col segment_type for a15

select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST%';

结果如下：

SEGMENT_NAME    SEGMENT_TYPE    TABLESPACE_NAME    EXTENTS         KB
--------------- --------------- --------------- ---------- ----------
TEST            TABLE           USERS                    1         64
TEST1           TABLE           USERS                    1         64
TEST1           TABLE           USERS                  168     794624
TEST5           TABLE           RMANTEST                 1         64
TEST9           TABLE           USERS                  169     800768

3.某个用户下的表所占空间前三位:

select * from (select segment_name,bytes/1024 KB from dba_segments where owner = 'BYS' order by bytes desc ) where rownum <= 3;
SEGMENT_NAME            KB
--------------- ----------
TEST9               800768
TEST1               794624
EMP                     64
用SQL计算出某个用户下所有对象的大小，给出SQL语句和结果。
SQL> show user
User is "bys"
 
SQL> select sum(a.m) from (select segment_name,segment_type,bytes/1024/1024 M from user_segments) a;
 
  SUM(A.M)
----------
         4

2.查询表空间大小及空闲空间大小，使用率等

主要使用的视图有：dba_data_files，dba_free_space

col used_% for a8

col TABLESPACE_NAME for a15

select df.tablespace_name,df.sum_df_m as space_m,df.sum_df_m-fs.sum_fs_m as used_m,fs.sum_fs_m as free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%",100-to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) "unused_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name;

结果如下：
TABLESPACE_NAME                   SPACE_M     USED_M FREE_SPACE used_%     unused_%
------------------------------ ---------- ---------- ---------- -------- ----------
SYSAUX                            625.625    595.625         30 95                5
UNDOTBS1                              200   137.4375    62.5625 68               32
USERS                            219.8125    121.875    97.9375 55               45
SYSTEM                                500   346.1875   153.8125 69               31
TEST1                                 110          2        108 1                99

select df.tablespace_name,df.sum_df_m as space_m,df.sum_df_m-fs.sum_fs_m as used_m,fs.sum_fs_m as free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name  and df.tablespace_name='USERS';

结果如下：

TABLESPACE_NAME    SPACE_M     USED_M FREE_SPACE used_%
--------------- ---------- ---------- ---------- --------
USERS              1703.75     1562.5     141.25 91

.用SQL计算某个表空间所包含对象的大小

SQL> show user
User is "bys"
SQL> select 'SIZE_TABELSPACE' NAME,sum(user_bytes)/1024/1024 SIZE_M from dba_data_files where tablespace_name='USERS' UNION ALL select 'SIZE_OBJECT' NAME,sum(nvl(bytes,0))/1024/1024 SIZE_M from user_segments where tablespace_name='USERS';
 
NAME                SIZE_M
--------------- ----------
SIZE_TABELSPACE       5.25
SIZE_OBJECT              4

3.查询数据文件大小及文件名

col file_name for a35
select file_name,file_id,tablespace_name,bytes/1024/1024 MB from dba_data_files;

FILE_NAME                              FILE_ID TABLESPACE_NAME         MB
----------------------------------- ---------- --------------- ----------
/u01/oradata/bys1/users01.dbf                4 USERS              1703.75
/u01/oradata/bys1/undotbs01.dbf              3 UNDOTBS1               125
/u01/oradata/bys1/sysaux01.dbf               2 SYSAUX                 670
/u01/oradata/bys1/system01.dbf               1 SYSTEM                 700
/u01/oradata/bys1/example01.dbf              5 EXAMPLE                100
/u01/oradata/bys1/rmantest.dbf               6 RMANTEST                10

4.查询整个数据库的容量

数据文件大小
select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files);
重做日志文件大小
select  sum(a.members*a.m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log) a;
控制文件大小
SQL> select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile);
数据库总容量：
SQL> select sum_d+sum_r+sum_c as sum_database_M,sum_d as sum_datafile,sum_r as sum_redo,sum_c as sum_ctl from (select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files)) a,(select  sum(members*m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log)) b,(select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile)) c;
 
SUM_DATABASE_M SUM_DATAFILE   SUM_REDO    SUM_CTL
-------------- ------------ ---------- ----------
       2733.75      2615.25         90       28.5