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