'PostgreSQL'에 해당되는 글 1건

2019.06.15 PostgreSQL Partition table 조회 스크립트 2

PostgreSQL Partition table 조회 스크립트

1. IT Story/Scripts 2019. 6. 15. 16:07

with recursive t (inhrelid, inhparent, depth)
as (
        select  inhrelid, inhparent, 1
        from    pg_inherits pi
        union all
        select  pi.inhrelid, pi.inhparent, depth + 1
        from    t, pg_inherits pi
        where   pi.inhparent = t.inhrelid
)
select pa.rolname,
       (select relname from pg_class where oid = pi.inhparent) as table_name,
       pcs.relname as partition_name,
       pt.spcname as tablespace_name,
       case ppt.partstrat when 'l' then 'list' when 'r' then 'range' else 'not partition' end as partition_type,
       dr.DEGREE,
       pc.reltuples::int as row_nums,
       pg_size_pretty(pg_table_size(pc.oid)) as size,
       pg_get_expr(pc.relpartbound, pc.oid) as partition_value
from    pg_class pcs
join    pg_inherits pi
        on pc.oid = pi.inhrelid
join    pg_partitioned_table ppt
        on ppt.partrelid = pi.inhparent
join    pg_authid pa
        on pc.relowner = pa.oid
join    pg_tablespace pt
        on pt.oid = case
                        when pc.reltablespace = 0
                            then (
                                    select  dattablespace
                                    from    pg_database
                                    where   datname=current_database()
                                 )
                        else pc.reltablespace
                    end
join (
        select inhrelid, inhparent, max(depth) as degree
        from t
        group by inhrelid, inhparent
     ) dr
     on dr.inhrelid = pc.oid
WHERE   DEGREE='1'
order by degree, table_name, partition_name;

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

오라클 운영중인 테이블 Loop Commit 작업 스크립트 (0)	2020.12.24
Oracle 동일 SQL_ID의 실행 변경사항 계획 비교 스크립트 (0)	2019.05.17
Oracle/Oragrid Relink 작업 스크립트 (0)	2019.05.11
Oracle 실시간 Session 조회 스크립트 (0)	2019.04.29
Oracle ASH TOP Activity (0)	2019.04.28
Oracle Oradebug Ashdump를 통한 Trace 생성 스크립트 (0)	2019.04.27
PostgreSQL Vaccum(XID) 관리 스크립트 (0)	2019.04.25
Oracle Awr Generator (0)	2019.04.22