oracle12c、18c、19c表空间使用率查询-创新互联
查询临时表空间
在德阳等地区,都构建了全面的区域性战略布局,加强发展的系统性、市场前瞻性、产品创新能力,以专注、极致的服务理念,为客户提供成都网站建设、成都网站设计 网站设计制作按需定制设计,公司网站建设,企业网站建设,成都品牌网站建设,成都全网营销推广,外贸网站建设,德阳网站建设费用合理。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(+)
查询undo和数据表空间
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(+)
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
网站题目:oracle12c、18c、19c表空间使用率查询-创新互联
文章网址:http://ybzwz.com/article/jcijp.html