oracle表空间和数据量查看

–查看数据量
set linesize 222 pagesize 999
col owner for a15
col segment_name for a50
col segment_type for a25
select owner,segment_name,tablespace_name,sum(bytes)/1024/1024 MB from dba_segments where segment_name like upper( ‘&segment_name%’ ) group by owner,segment_name,tablespace_name order by 2;

–查看表空间

set linesize 200 pagesize 999 long 20000
col name format a30
col contents format a9
col extent_management format a8
col size_mb format a12
col Used_MB format a12
col free_mb format a12
col “Used%” format a8
col dt format a20

SELECT d.tablespace_name Name,
d.status,
d.contents,
d.extent_management,
d.segment_space_management,
a.file_cnt,
TO_CHAR(NVL(a.bytes/1048576, 0), ‘999G999G999’) SIZE_MB,
TO_CHAR(DECODE(d.contents,‘UNDO’,NVL(u.bytes, 0)/1048576,NVL(a.bytes - NVL(f.bytes, 0), 0)/1048576),‘999G999G999’) Used_MB,
TO_CHAR(DECODE(d.contents,‘UNDO’,NVL(a.bytes - NVL(u.bytes, 0), 0)/1048576,NVL(f.bytes, 0)/1048576), ‘999G999G999’) FREE_MB,
TO_CHAR(DECODE(d.contents,‘UNDO’,NVL(u.bytes/a.bytes * 100, 0),NVL((a.bytes - NVL(f.bytes, 0))/a.bytes * 100, 0)),‘990D00’) “Used%”,
TO_CHAR(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) dt
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) file_cnt
from dba_data_files
GROUP BY tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f,
(SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = ‘ACTIVE’
group by tablespace_name, status
UNION ALL
SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = ‘UNEXPIRED’
group by tablespace_name, status)
group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND NOT (d.extent_management = ‘LOCAL’ AND d.contents = ‘TEMPORARY’)
UNION ALL
SELECT d.tablespace_name,
d.status,
d.contents,
d.extent_management,
d.segment_space_management,
a.file_cnt,
TO_CHAR(NVL(a.bytes/1048576, 0), ‘999G999G999’) SIZE_MB,
TO_CHAR(NVL(t.bytes, 0)/1048576, ‘999G999G999’) Used_MB,
TO_CHAR((NVL(a.bytes, 0)/1048576 - NVL(t.bytes, 0)/1048576),‘999G999G999’) FREE_MB,
TO_CHAR(NVL(t.bytes/a.bytes * 100, 0), ‘990D00’) “Used%”,
TO_CHAR(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) dt
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes, count(file_id) file_cnt
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) bytes
from gv$sor{t}_{s}egmentss,sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = ‘LOCAL’
AND d.contents = ‘TEMPORARY’
ORDER BY “Used%”;