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

3 votes134 views1 answer
How to calculate number of days between two dates excluding weekends in oracle
SQL
0 vote148 views2 answers
Return the number of records in a single table mysql
SQL
0 vote133 views1 answer
2020 new year
SQL
3 votes144 views2 answers
Truncate table
SQL
1 vote142 views1 answer
Failed to stop mysqld.service: Unit mysqld.service not loaded.
SQL
3 votes141 views3 answers
Postgresql random number
SQL
1 vote159 views1 answer
Tsql create unique index composite
SQL
0 vote153 views2 answers
Amazon linux postgresql client
SQL
-1 vote187 views1 answer
Get month and year from date in mysql sequelize
SQL
0 vote137 views1 answer
How to set all the min and sec data to zero in sql server
SQL