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

0 vote310 views1 answer
Desinstaller mysql sur ubuntu definitivement
SQL
6 votes310 views3 answers
Mysql remove user privileges
SQL
1 vote292 views2 answers
Postgres cast as decimal
SQL
0 vote297 views1 answer
Can we rollback in sql
SQL
10 votes283 views3 answers
How to fetch first 5 characters in sql
SQL
0 vote279 views1 answer
Sql "List the contact methods found in the Contact table.For each contact method, list how many people chose that contact method."
SQL
1 vote283 views2 answers
Mssql show database size
SQL
0 vote268 views1 answer
Sql view index
SQL
0 vote303 views1 answer
T sql dynamic top n query
SQL
1 vote297 views1 answer
Right outer vs left outer join
SQL