Oracle list partitioned tables

How to see all table partition in oracle

SELECT * FROM ALL_TAB_PARTITIONS;

Oracle list partitioned tables

-- List of partitioned tables
SELECT DISTINCT P.TABLE_OWNER, P.TABLE_NAME, count(*) AS NB_PARTITIONS, 
	SP.NB_SUBPARTITIONS
FROM DBA_TAB_PARTITIONS P
LEFT JOIN (SELECT DISTINCT TABLE_OWNER, TABLE_NAME, count(*) AS NB_SUBPARTITIONS
           FROM DBA_TAB_SUBPARTITIONS
           GROUP BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME
          ) SP
ON P.TABLE_OWNER = SP.TABLE_OWNER AND P.TABLE_NAME = SP.TABLE_NAME
WHERE P.TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
GROUP BY P.TABLE_OWNER, P.TABLE_NAME, SP.NB_SUBPARTITIONS
ORDER BY P.TABLE_OWNER, P.TABLE_NAME;

Similar codes

50 votes249 views6 answers
Install mysql on ubuntu
SQL
0 vote151 views1 answer
Concatenate columns in table
SQL
0 vote150 views1 answer
How to count with except in psql
SQL
0 vote170 views1 answer
Sql value exists in column
SQL
0 vote165 views1 answer
Virtuoso sql query tutorial
SQL
3 votes262 views1 answer
Ssh check mysql version
SQL
0 vote192 views2 answers
Suse Linux restart MySQL
SQL
0 vote152 views1 answer
Codeigniter 3 get best-selling products
SQL
0 vote151 views1 answer
Delete insert record in sql server
SQL
0 vote163 views1 answer
Guid string to binary better
SQL