oralce 12c 表空间查询(包含pdb)

发布时间:2020-08-09 21:28:41 作者:18620626259
来源:网络 阅读:651


SELECT DB_NAME

,con_id

,RES_NAME

,TABLE_SIZE

,USED_SIZE

,USE_PERCENT

,AVA_SIZE

,AUTOEXTENSIBLE

,CONTENTS

FROM (

SELECT tt.con_id

,nvl(x.name, 'CDB$ROOT') AS DB_NAME

,ts1.tablespace_name AS "RES_NAME"

,round(nvl(tt.tmp_max_size, 0) / 1024 / 1024, 2) AS "TABLE_SIZE"

,round(nvl(tu.tmp_used_size, 0) / 1024 / 1024, 2) AS "USED_SIZE"

,CASE 

WHEN tt.tmp_space = 0

THEN 0

ELSE ROUND((nvl(tu.tmp_used_size, 0) * 100 / tt.tmp_max_size), 2)

END AS "USE_PERCENT"

,round((nvl(tt.tmp_max_size, 0) - nvl(tu.tmp_used_size, 0)) / 1024 / 1024, 2) AS "AVA_SIZE"

,ts1.CONTENTS AS "CONTENTS"

,ts1.STATUS AS "STATUS"

,ts1.ALLOCATION_TYPE AS "ALLOCATION_TYPE"

,tt.tmp_file_count AS "FILE_COUNT"

,CASE 

WHEN tt.tmp_auto_extens_c > 0

THEN 'YES'

ELSE 'NO'

END AS "AUTOEXTENSIBLE"

FROM cdb_tablespaces ts1

,v$pdbs x

,(

SELECT tablespace_name

,sum(nvl(bytes, 0)) / 1024 tmp_space

,con_id

,SUM(decode(AUTOEXTENSIBLE, 'YES', nvl(MAXBYTES, 0), nvl(bytes, 0))) / 1024 / 1024 tmp_max_size

,count(*) tmp_file_count

,sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) tmp_auto_extens_c

FROM cdb_temp_files

GROUP BY tablespace_name

,con_id

) tt

,(

SELECT tablespace_name

,SUM(nvl(bytes_cached, 0)) / 1024 / 1024 tmp_used_size

FROM gv$temp_extent_pool

GROUP BY tablespace_name)tu

WHERE tt.tablespace_name = tu.tablespace_name

AND ts1.extent_management LIKE 'LOCAL'

AND ts1.contents LIKE 'TEMPORARY'

AND tt.tablespace_name = ts1.TABLESPACE_NAME

AND tt.con_id = ts1.CON_ID

AND ts1.con_id = x.con_id(+)

UNION ALL

SELECT d.con_id

,nvl(x.name, 'CDB$ROOT') AS DB_NAME

,d.tablespace_name AS "RES_NAME"

,round(d.max_size / 1024 / 1024, 2) AS "TABLE_SIZE"

,round((d.SPACE - NVL(f.FREE_SPACE, 0)) / 1024 / 1024, 2) AS "USED_SIZE"

,CASE 

WHEN d.space = 0

THEN 0

ELSE ROUND(((d.SPACE - NVL(f.FREE_SPACE, 0)) * 100 / d.max_size), 2)

END AS "USE_PERCENT"

,round((d.max_size - d.space + NVL(f.FREE_SPACE, 0)) / 1024 / 1024, 2) AS "AVA_SIZE"

,ts.CONTENTS AS "CONTENTS"

,CASE 

WHEN ts.STATUS = 'READ ONLY'

AND d.offline_c = d.file_count

THEN 'OFFLINE(READ_ONLY)'

ELSE ts.STATUS

END AS "STATUS"

,ts.ALLOCATION_TYPE AS "ALLOCATION_TYPE"

,d.file_count AS "FILE_COUNT"

,CASE 

WHEN d.auto_extens_c > 0

THEN 'YES'

ELSE 'NO'

END AS "AUTOEXTENSIBLE"

FROM cdb_tablespaces ts

,v$pdbs x

,(

SELECT TABLESPACE_NAME

,con_id

,SUM(nvl(BYTES, 0)) / 1024 SPACE

,sum(decode(autoextensible, 'YES', nvl(maxbytes, 0), nvl(bytes, 0))) / 1024 max_size

,sum(decode(ONLINE_STATUS, 'OFFLINE', 1, 0)) offline_c

,count(*) file_count

,sum(decode(autoextensible, 'YES', 1, 0)) auto_extens_c

FROM cdb_DATA_FILES

GROUP BY TABLESPACE_NAME

,con_id

) d

,(

SELECT TABLESPACE_NAME

,SUM(nvl(BYTES, 0)) / 1024 FREE_SPACE

,con_id

FROM cdb_FREE_SPACE

GROUP BY TABLESPACE_NAME

,con_id

) f

WHERE d.TABLESPACE_NAME = f.TABLESPACE_NAME

AND d.con_id = f.con_id

AND ts.TABLESPACE_NAME = d.TABLESPACE_NAME

AND ts.con_id = d.con_id

AND ts.con_id = x.con_id(+)

)


推荐阅读:
  1. Oracle Database 12cR2多租户权威指南
  2. Oracle 12c怎么安装

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle 表空间 ce %d

上一篇:python函数教程:Python 字符串操作(string替换、截取等)

下一篇:升级psu引发的新bugORACLE ORA-07445

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》