Oracle查询来查找总大小与已消耗与可用大小的关系，包括各种模式11g [英] Oracle Query to find total size vs consumed vs available size including various schemas 11g

问题描述

我需要对以下内容进行分解

I have a requirement to come up with a break down of the below

1. 总DWH大小

1. overall DWH SIZE

已用空间

可用空间

分解每个模式的空间(例如STAGE，EDW/CORE，MART....)(必须覆盖第1,2,3点)

break down of space per schema (example STAGE,EDW/CORE,MART....) (point 1,2,3 must be covered)

4.1-我很感兴趣地看到所有模式都按表总大小与已用大小进行了细分(表格)，我尝试了以下查询(1到5)

在检查各种帖子时会感到困惑

upon checking various posts it's bit confusing

有人建议检查下表

• DATA_SIZE(用于检查数据文件)
• TEMP_SIZE(用于检查临时文件)
• SYS.V_ $ LOG(检查重做日志文件)
• V $ CONTROLFILE(以检查重做日志文件)DBA_SEGMENTS

我已经测试了以下查询

查询1-数据库的实际大小

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

结果-GB

GB900-仅示例

查询2-给出此数据库中数据占用的大小或数据库使用情况详细信息

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

结果-GB

GB900-仅示例

查询3-总体数据库大小(以TB为单位)

SELECT 
( SELECT SUM(BYTES)/1024/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) +
( SELECT NVL(SUM(BYTES),0)/1024/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +
( SELECT SUM(BYTES)/1024/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +
( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE) "SIZE IN TB"
FROM
DUAL

结果结核病

TB大小100-仅示例

SIZE IN TB 100 - only example

查询4-使用空间和可用空间的Oracle DB的数据库大小(以TB为单位)

select round(sum(used.bytes) / 1024 / 1024 / 1024/1024 ) || 'TB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024/1024 ) -
round(free.p / 1024 / 1024 / 1024/1024) || 'TB' "Used space"
, round(free.p / 1024 / 1024 / 1024/1024) || 'TB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

结果结核病

数据库大小已用空间可用空间100 90 10

Database Size Used space Free space 100 90 10

查询5-查询以获取Oracle数据库架构中所有表的大小

SELECT * FROM 
(
SELECT
OWNER, 
OBJECT_NAME, 
OBJECT_TYPE, 
TABLE_NAME, 
--ROUND(BYTES)/1024/1024 AS MB,
ROUND(BYTES) / 1024 / 1024 / 1024 AS GB,
--ROUND(100*RATIO_TO_REPORT(ROUND(BYTES) / 1024 / 1024 / 1024) OVER(),2) AS GB_PERCENT,
ROUND(100*RATIO_TO_REPORT(BYTES) OVER (), 2) PERCENTAGE,
TABLESPACE_NAME, 
EXTENTS, 
INITIAL_EXTENT,
ROUND(SUM(BYTES/1024/1024/1024) OVER (PARTITION BY TABLE_NAME)) AS TOTAL_TABLE_GB
--ROUND(SUM(BYTES)/1024/1024/1024) OVER (PARTITION BY TABLE_NAME)) AS TOTAL_TABLE_GB
FROM 
(
--TABLES
SELECT OWNER, SEGMENT_NAME AS OBJECT_NAME, 'TABLE' AS OBJECT_TYPE,
SEGMENT_NAME AS TABLE_NAME, BYTES,
TABLESPACE_NAME, EXTENTS, INITIAL_EXTENT
FROM DBA_SEGMENTS /*DBA_SEGMENTS*/
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
--INDEXES
SELECT I.OWNER, I.INDEX_NAME AS OBJECT_NAME, 'INDEX' AS OBJECT_TYPE,
I.TABLE_NAME, S.BYTES,
S.TABLESPACE_NAME, S.EXTENTS, S.INITIAL_EXTENT
FROM DBA_INDEXES I /*DBA_INDEXES*/
, DBA_SEGMENTS S /*DBA_SEGMENTS*/
WHERE S.SEGMENT_NAME = I.INDEX_NAME
AND S.OWNER = I.OWNER
AND S.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
--LOB SEGMENTS
UNION ALL
SELECT L.OWNER, L.COLUMN_NAME AS OBJECT_NAME, 'LOB_COLUMN' AS OBJECT_TYPE,
L.TABLE_NAME, S.BYTES,
S.TABLESPACE_NAME, S.EXTENTS, S.INITIAL_EXTENT
FROM DBA_LOBS L, /*DBA_LOBS*/
DBA_SEGMENTS S /*DBA_SEGMENTS*/
WHERE S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE = 'LOBSEGMENT'
--LOB INDEXES
UNION ALL
SELECT L.OWNER, L.COLUMN_NAME AS OBJECT_NAME, 'LOB_INDEX' AS OBJECT_TYPE,
L.TABLE_NAME, S.BYTES,
S.TABLESPACE_NAME, S.EXTENTS, S.INITIAL_EXTENT
FROM DBA_LOBS L, /*DBA_LOBS*/
DBA_SEGMENTS S /*DBA_SEGMENTS*/
WHERE S.SEGMENT_NAME = L.INDEX_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE = 'LOBINDEX'
)
WHERE OWNER IN UPPER('&SCHEMA_NAME')
)
--WHERE TOTAL_TABLE_MB > 10
ORDER BY TOTAL_TABLE_GB DESC, GB DESC
/

EXPECTED_RESULTS

OWNER     OBJECT_TYPE TOTAL_SPACE_GB TOTAL_SPACE_USED_GB PERCENTAGE_GB 
DWH_STAGE  TABLE      400            200                  50            
DWH_EDW    TABLE      800            400                  50    
DWH_MART   TABLE      1600           800                  50   

请问您如何实现此目标?

could you please suggest how to achieve this ?

推荐答案

如果需要，这将显示表段中的空间，但我认为您可能希望按表空间来显示可用空间.

This shows space within table segments if that is what you want but I think you probably want free space by tablespace instead.

select t.owner,'TABLE' OBJECT_TYPE ,t.TOTAL_SPACE_GB, u.TOTAL_SPACE_USED_GB,(u.TOTAL_SPACE_USED_GB/t.TOTAL_SPACE_GB)*100 PERCENTAGE_USED
from
(select owner,sum(bytes)/(1024*1024*1024) TOTAL_SPACE_GB
from dba_segments
where segment_type like 'TABLE%'
group by owner) t,
(select owner, sum(NUM_ROWS*AVG_ROW_LEN)/(1024*1024*1024) TOTAL_SPACE_USED_GB
from dba_tables
group by owner) u
where
t.owner=u.owner
order by owner;

鲍比

这篇关于Oracle查询来查找总大小与已消耗与可用大小的关系，包括各种模式11g的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！