ORACLE資料庫、資料表空間、表的容量相關查詢--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

主要使用的視圖有：dba_data_files，dba_free_space

col used_% for a8

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;

TABLESPACE_NAME    SPACE_M     USED_M FREE_SPACE used_%
--------------- ---------- ---------- ---------- --------
SYSAUX                 670    637.125     32.875 95
UNDOTBS1               125     30.125     94.875 24
RMANTEST                10     1.0625     8.9375 10
USERS              1703.75     1562.5     141.25 91
SYSTEM                 700   692.3125     7.6875 98
EXAMPLE                100      79.25      20.75 79

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> 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

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

資料檔案大小
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