oracle tablespace maxsize,查oracle表空间总大小 剩余大小

根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大小

SELECT

A.TABLESPACE_NAME,B.TOTAL/1024/1024||'M',(B.TOTAL-A.USE)/1024/1024||'M'

FREE FROM

(

select

c.tablespace_name,(c.p_use-d.p_free) as use from

(

select

tablespace_name,sum(bytes) as p_use from

dba_data_files

where tablespace_name

NOT IN

('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

group by

tablespace_name

) C,

(

select

tablespace_name,sum(bytes) as p_free from

dba_free_space

where tablespace_name

NOT IN

('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

group by

tablespace_name

) D

where

C.tablespace_name=D.tablespace_name

) A ,

(

WITH TABLESPACE_TOTAL

AS

(

SELECT

tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES

T

WHERE T.TABLESPACE_NAME NOT IN

('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T. group by tablespace_name

UNION

ALL

SELECT

tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN

('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T. group by tablespace_name

)

SELECT

TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY

TABLESPACE_NAME

) B

WHERE

A.TABLESPACE_NAME=B.TABLESPACE_NAME