Shulou(Shulou.com)06/01 Report--

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 (+)

)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.