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 |