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

SELECT DB_NAME ,con_id ,RES_NAME ,TABLE_SIZE ,USED_SIZE ,USE_PERCENT ,AVA_SIZE ,AUTOEXTENSIBLE ,CONTENTSFROM ( 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(+) )

相关推荐: mongodb+分片集群+ycsb测试一例

简介mongodb是很出名的nosql数据库了,属于集合-文档型的特有架构nosql数据库,也是被誉为最像关系型数据库的非关系型数据库,但是不支持事务.由于mongodb原生就支持分布式架构,所以部署简单,灵活,可伸缩,自动平衡数据结构等优点,继而性能也非常高…