Showing posts with label Listing partitions. Show all posts
Showing posts with label Listing partitions. Show all posts

Thursday, June 17, 2021

How to list partitions and their LOB segment sizes

SELECT  ts.table_owner "owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob part name",
        lp.column_name "lob name",
        lp.compression "lob compression",
        lp.partition_name "table part name",
        ts.num_rows "num rows",
        lp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_partitions lp
ON (s.partition_name = lp.lob_partition_name)  
   JOIN DBA_TAB_PARTITIONS ts
        ON (TS.PARTITION_NAME = lp.PARTITION_NAME) 
WHERE lp.table_name='MYTABLE'
AND   ts.table_name='MYTABLE'
-- To limit the output to a specific tablespace, uncomment line below
AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'MYTABLE' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lp.COMPRESSION,/*lp.subpartition_name*/lp.partition_name, lp.column_name,ts.num_rows,lp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY 12 DESC;
Output:
owner table name segment name segment type lob type lob part name lob name lob compression table part name num rows tablespace size GB
JIM MYTABLE SYS_LOB0000079505C00019$$ LOB PARTITION SECUREFILE SYS_LOB_P3273 MYLOB1 NO SYS_P3270
864051
DATA1
20
JIM MYTABLE SYS_LOB0000079505C00019$$ LOB PARTITION SECUREFILE SYS_LOB_P4684 MYLOB1 NO SYS_P4681
593520
DATA1
17
JIM MYTABLE SYS_LOB0000079505C00019$$ LOB PARTITION SECUREFILE SYS_LOB_P5692 MYLOB1 NO SYS_P5689
2021989
DATA1
16


For queries against a subpartitioned table, see this post.

Friday, November 8, 2019

How to list subpartitions and their LOB segment sizes




The query below will list the lob segment names, the lob subpartition names, the table subpartition names and sizing information for any subpartitioned tables using LOB columns:

SELECT  ts.table_owner "table owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob subpart name",
        lsp.column_name "lob name",
        lsp.compression "lob compression",
        lsp.subpartition_name "table subpartition name",
        ts.num_rows "num rows",
        lsp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_subpartitions lsp
ON (s.partition_name = lsp.lob_subpartition_name)  
   JOIN DBA_TAB_SUBPARTITIONS ts
        ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME) 
WHERE lsp.table_name='DOCUMENTS'
AND   ts.table_name='DOCUMENTS'
-- To limit the output to a specific tablespace, uncomment line below
-- AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lsp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'DOCUMENTS' ) )

GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY lsp.subpartition_name DESC;


Example output:


TABLE_NAME segment name segment type lob type lob subpart name lob compression table subpartition name num rows tablespace size GB
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_2018_01 MEDIUM KLM_SP_2018_01
164497
DOCS2018
235
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE VISA_DOCS_2018_01 MEDIUM VISA_SP_2018_01
72405
DOCS2018
76
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_2017_10 MEDIUM KLM_SP_2017_10
16256
DOCS2017
19
MYTABLE MYTABLE_SMALL_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_SMALL_2018_01 MEDIUM KLM_SP_2018_01
164497
DOCS2018
18
MYTABLE MYTABLE_SMALL_LOB7 LOB SUBPARTITION SECUREFILE VISA_DOCS_SMALL_2018_01 MEDIUM VISA_SP_2018_01
72405
DOCS2018
8

Friday, August 2, 2019

How to list subpartitions and their sizes



First, find the number of partitions and subpartitions in a specific tablespace:
SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_OWNER='SCOTT'
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;

TABLE_NAME TABLESPACE_NAME num partitions num subpartitions
MYTABLE1 DATA1
75
450
MYTABLE2 DATA2
73
219
MYTABLE3 DATA1
74
222
MYTABLE4 DATA2
74
222
MYTABLE5 DATA1
81
243

For a specific table, all partitions and their subpartitions:
SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;

PARTITION_NAME SUBPARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_P177766 SYS_SUBP177763 TABLE SUBPARTITION DATA2 DISABLED  
26021384
25.06.2020 22:55:36
12
SYS_P177766 SYS_SUBP177764 TABLE SUBPARTITION DATA2 DISABLED  
21531914
26.06.2020 23:32:34
9,1
SYS_P177766 SYS_SUBP177765 TABLE SUBPARTITION DATA2 DISABLED  
0
02.03.2020 00:56:19
0
SYS_P177774 SYS_SUBP177771 TABLE SUBPARTITION DATA2 DISABLED  
285
16.07.2020 23:21:12
0
SYS_P177774 SYS_SUBP177772 TABLE SUBPARTITION DATA1 DISABLED  
259
16.07.2020 23:21:30
0

For specific subpartitions:
SELECT P.PARTITION_NAME "Subpartition name", S.PARTITION_NAME,S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.SUBPARTITION_NAME in ('SYS_SUBP177773','SYS_SUBP177763','SYS_SUBP177764','SYS_SUBP177765');

PARTITION_NAME Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_P177766 SYS_SUBP177763 TABLE SUBPARTITION DATA1 DISABLED  
26021384
25.06.2020 22:55:36
12
SYS_P177766 SYS_SUBP177764 TABLE SUBPARTITION DATA1 DISABLED  
21531914
26.06.2020 23:32:34
9,1
SYS_P177766 SYS_SUBP177765 TABLE SUBPARTITION DATA1 DISABLED  
0
02.03.2020 00:56:19
0
SYS_P177774 SYS_SUBP177773 TABLE SUBPARTITION DATA1 DISABLED  
0
02.03.2020 00:56:20
0

For a specific partition:
SELECT S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_SUBP14672 TABLE SUBPARTITION DATA1 DISABLED   127103150 29.06.2019 51,4
SYS_SUBP14673 TABLE SUBPARTITION DATA1 DISABLED   89059917 29.06.2019 34,1
SYS_SUBP14674 TABLE SUBPARTITION DATA1 DISABLED   0 29.06.2019 0

For a specific tablespace:
SELECT S.PARTITION_NAME "Subpartition name",S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.OWNER = 'SCOTT'
AND S.TABLESPACE_NAME='DATA1'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY S.BYTES DESC; 

Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_SUBP27395 TABLE SUBPARTITION SCOTT DISABLED  
100835414
27.10.2018 19:06:53
44,6
SYS_SUBP29902 TABLE SUBPARTITION SCOTT DISABLED  
88951135
27.10.2018 21:23:24
41,6
SYS_SUBP29332 TABLE SUBPARTITION SCOTT DISABLED  
83142250
27.10.2018 23:40:00
38,3
SYS_SUBP29395 TABLE SUBPARTITION SCOTT DISABLED  
78610455
27.10.2018 19:00:05
37,1
SYS_SUBP28115 TABLE SUBPARTITION SCOTT DISABLED  
75810738
07.07.2018 15:54:52
35,7

Tuesday, September 13, 2016

How to list partitions and their sizes


Using an inline view with a correlated subquery:
set lines 200
col partition_name format a40
SELECT P.PARTITION_NAME, (  SELECT ROUND(SUM(BYTES)/1024/1024/1024)
                            FROM DBA_SEGMENTS S
                            WHERE S.PARTITION_NAME = P.PARTITION_NAME
                            AND SEGMENT_NAME='&&TABLE_NAME') "size GB"
FROM DBA_TAB_PARTITIONS P
WHERE P.TABLE_NAME = '&&TABLE_NAME'
ORDER BY P.PARTITION_POSITION ASC;

Example output:
sqlplus / as sysdba @get_size.sql
Enter value for table_name: ARCHIVED_DOCUMENTS


PARTITION_NAME                      size GB
------------------------------   ----------
DOKARCHIVE1                           2.875
DOKARCHIVE2                               3
DOKARCHIVE3                               3
DOKARCHIVE4                               3
DOKARCHIVE5                               3
DOKARCHIVE6                          2.8125
DOKARCHIVE7                            2.75

Or, limit the output to specific partitions:
select s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='SALES_ARCHIVE'
and   s.owner='SH'
and s.partition_name in ( 'P_2015_01','P_2016_01','P_2017_01')
group by s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 1;

Example output:
PARTITION_NAME TABLESPACE_NAME COMPRESSION NUM_ROWS GB
P_2015_01 SALES_2015 ENABLED
22077874
2,3
P_2016_01 SALES_2016 ENABLED
394694450
42,3
P_2017_01 SALES_2017 ENABLED
481708328
48,9
If your partitions involve lob columns, make sure you fetch the sizes of the lob partitions, too.