oracle 常用查询

• 查看各个表空间的使用率（包括临时表空间）

SELECT d.tablespace_name "Name", d.status "Status",

TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",

TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,

'99999999.99'

) USE,

TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),

'990.00'

) "Used %"

FROM SYS.dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')

UNION ALL

SELECT d.tablespace_name "Name", d.status "Status",

TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",

TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.99') USE,

TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"

FROM SYS.dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (bytes_cached) BYTES

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY';

• 查看物理文件的大小和使用率

select 

b.file_id 物理文件号,

b.file_name 物理文件名,

b.tablespace_name 表空间,

b.bytes/1024/1024 大小M,

(b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,

substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率 

from dba_free_space a,dba_data_files b 

where a.file_id=b.file_id 

group by b.tablespace_name,b.file_id,b.file_name,b.bytes 

order by b.tablespace_name

• 文件大小与水位线大小

select   'alter database datafile '''||a.file_name||''' resize '    ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',  
a.filesize,c.hwmsize 
from  ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a,
( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c 
where a.file_id = c.file_id   and a.filesize - c.hwmsize > 100

• 物理文件大小与可释放的空间（包含resize语句）

select a.file#,

  a.name,

          a.bytes / 1024 /1024 CurrentMB,

trunc(ceil(HWM * a.block_size) / 1024 / 1024 +1) ResizeTo,

trunc((a.bytes - HWM * a.block_size) / 1024  / 1024  + 1) ReleaseMB,

 'alter database datafile ''' || a.name || ''' resize ' ||

trunc(ceil(HWM * a.block_size) / 1024 / 1024 +1) || 'M;' ResizeCmd

from v$datafile a,

(select file_id,  max(block_id + blocks -1) HWM

 FROM dba_extents

  group by file_id) b

where a.file# = b.file_id(+) and (a.bytes - HWM* a.block_size) > 0

• 表空间使用率

SELECT d.tablespace_name "Name", d.status "Status",

TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",

TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,

'99999999.99'

) USE,

TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),

'990.00'

) "Used %"

FROM SYS.dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')

UNION ALL

SELECT d.tablespace_name "Name", d.status "Status",

TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",

TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.99') USE,

TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"

FROM SYS.dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (bytes_cached) BYTES

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY';

• 查看某用户下所有表（或索引）的大小

select OWNER "用户名", t.segment_name "表名", t.segment_type "表类型", sum(t.bytes / 1024 / 1024) "表大小M"
from dba_segments t
where t.owner = 'HEVF'
and t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type
order by "表大小M" desc;

• 数据文件的实用大小

select

'alter database datafile ''' || a.file_name || ''' resize '

||

round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) ||

'M;',

a.filesize || 'M' as "数据文件的总大小",

c.hwmsize || 'M' as

"数据文件的实用大小"

from (select file_id, file_name, round(bytes / 1024 / 1024) as

filesize

from dba_data_files) a,

(select file_id, round(max(block_id) * 8

/ 1024) as HWMsize

from dba_extents

group by file_id) c

where a.file_id

= c.file_id

and a.filesize - c.hwmsize > 100;

• 收缩表空间

-- 收缩表空间
select a.tablespace_name,
a.file_name,
a.totalsize as totalsize_MB,
b.freesize as freesize_MB,
'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' ||
round((a.totalsize - b.freesize) + 500) || 'M;' as "alter datafile"
from (select a.file_name,
a.file_id,
a.tablespace_name,
a.bytes / 1024 / 1024 as totalsize
from dba_data_files a) a,
(select b.tablespace_name,
b.file_id,
sum(b.bytes / 1024 / 1024) as freesize
from dba_free_space b
group by b.tablespace_name, b.file_id) b
where a.file_id = b.file_id
and b.freesize > 100
and a.tablespace_name  in ('APPS_TS_TX_DATA')
order by a.tablespace_name

• 查看表空间碎片

select tablespace_name,sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))) fsfi
    from dba_free_space
    group by tablespace_name order by 1;