[oracle] Oracle에서 테이블 크기를 계산하는 방법

MSSQL에 익숙하고 잠재적으로 망가 졌기 때문에 Oracle 10g에서 테이블 크기를 얻는 방법이 궁금합니다. 나는 그것을 구글 검색 했으므로 sp_spaceused만큼 쉬운 옵션이 없을 수도 있음을 알고 있습니다. 여전히 내가 얻을 수있는 잠재적 인 답변은 대부분 구식이거나 작동하지 않습니다. 아마도 내가 작업하고있는 스키마에 대한 DBA가 없기 때문일 것입니다.

누구든지 해결책이나 권장 사항이 있습니까?

답변

이 쿼리에 관심이있을 수 있습니다. 인덱스와 테이블의 LOB를 고려하여 각 테이블에 할당 된 공간의 양을 알려줍니다. 종종 테이블 자체가 아닌 “구매 주문 테이블이 인덱스를 포함하여 얼마나 많은 공간을 차지하는지”를 알고 싶어합니다. 당신은 항상 세부 사항을 탐구 할 수 있습니다. 이를 위해서는 DBA_ * 뷰에 액세스해야합니다.

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner,
   table_name,
   TRUNC(sum(bytes)/1024/1024) Meg,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

답변

-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;


--Tables + Rows
select owner, table_name, num_rows
 from all_tables
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;

참고 : 다음은 수집 통계를 통해보다 정확한 추정치입니다.

exec dbms_utility.analyze_schema(user,'COMPUTE');

답변

우선, 공간 분석을 수행하기 위해 테이블 통계를 수집하는 것은 잠재적으로 위험한 일이라는 점을 일반적으로 경고합니다. 통계 수집은 특히 DBA가 호출에서 사용하지 않는 기본이 아닌 매개 변수를 사용하는 통계 수집 작업을 구성한 경우, Oracle이 해당 테이블을 사용하는 쿼리를 재분석하여 성능이 될 수있는 경우 쿼리 계획을 변경할 수 있습니다. 히트. DBA가 의도적으로 통계없이 일부 테이블을 남긴 경우 (통계 OPTIMIZER_MODE가 CHOOSE 인 경우 일반적 임) 통계 수집으로 인해 Oracle은 규칙 기반 최적화 프로그램 사용을 중지하고 주요 성능이 될 수있는 일련의 쿼리에 대해 비용 기반 최적화 프로그램 사용을 시작할 수 있습니다. 프로덕션에서 예기치 않게 수행되는 경우 두통. 통계가 정확하면 쿼리USER_TABLES 하거나ALL_TABLES 또는DBA_TABLES) 직접 전화하지 않고GATHER_TABLE_STATS. 통계가 정확하지 않으면 그 이유가있을 수 있으며 현상 유지를 방해하고 싶지 않습니다.

둘째, SQL Server sp_spaceused프로 시저 와 가장 가까운 것은 Oracle DBMS_SPACE패키지 일 가능성이 높습니다 . Tom Kyte는 이 패키지에 간단한 인터페이스를 제공하고 출력되는 것과 유사한 정보를 출력 하는 훌륭한 show_space절차 를 가지고 sp_spaceused있습니다.

답변

먼저 테이블에서 옵티 마이저 통계를 수집하십시오 (아직없는 경우).

begin
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/

경고 : Justin의 답변에서 옵티 마이저 통계 수집은 쿼리 최적화에 영향을 미치며 적절한주의와 고려없이 수행하면 안됩니다. !

그런 다음 생성 된 통계에서 테이블이 차지하는 블록 수를 찾으십시오.

select blocks, empty_blocks, num_freelist_blocks
from   all_tables
where  owner = 'MYSCHEMA'
and    table_name = 'MYTABLE';

테이블에 할당 된 총 블록 수는 블록 + empty_blocks + num_freelist_blocks입니다.
블록은 실제로 데이터를 포함하는 블록의 수입니다.

사용 된 공간 (예 : 17 블록 x 8KB = 136KB)을 얻으려면 사용중인 블록 크기 (일반적으로 8KB)를 블록 수에 곱하십시오.

한 번에 스키마의 모든 테이블에 대해이를 수행하려면 다음을 수행하십시오.

begin
    dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/

select table_name, blocks, empty_blocks, num_freelist_blocks
from   user_tables;

참고 : 이 AskTom 스레드를 읽은 후 위의 변경 사항

답변

더 자세한 정보를 제공하기 위해 WW 쿼리를 수정했습니다.

SELECT * FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
    tablespace_name, extents, initial_extent,
    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  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
    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_segments s
    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_segments s
    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_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner = UPPER('&owner')
)
WHERE total_table_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/

답변

서브 파티션 된 테이블 및 인덱스의 경우 다음 쿼리를 사용할 수 있습니다



    SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB
    FROM
    (SELECT segment_name table_name, owner, bytes
     FROM dba_segments
     WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
     UNION ALL
     SELECT i.table_name, i.owner, s.bytes
     FROM dba_indexes i, dba_segments s
     WHERE s.segment_name = i.index_name
     AND   s.owner = i.owner
     AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
     UNION ALL
     SELECT l.table_name, l.owner, s.bytes
     FROM dba_lobs l, dba_segments s
     WHERE s.segment_name = l.segment_name
     AND   s.owner = l.owner
     AND   s.segment_type = 'LOBSEGMENT'
     UNION ALL
     SELECT l.table_name, l.owner, s.bytes
     FROM dba_lobs l, dba_segments s
     WHERE s.segment_name = l.index_name
     AND   s.owner = l.owner
     AND   s.segment_type = 'LOBINDEX')
    WHERE owner in UPPER('&owner')
    GROUP BY table_name, owner
    HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
    ORDER BY SUM(bytes) DESC
    ;

답변

IIRC 필요한 테이블은 DBA_TABLES, DBA_EXTENTS 또는 DBA_SEGMENTS 및 DBA_DATA_FILES입니다. 머신에 대한 관리 권한이없는 경우 확인할 수있는 테이블에 대한 USER_ 및 ALL_ 버전도 있습니다.