未完待續……未完待續……未完待續……未完待續……

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

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計算某個表空間所包含對象的大小

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

?

ORACLE數據庫、表空間、表的容量相關查詢--1