CODEWITHSUNDEEP
databaseoracle-databasepartitioning
Damianos17
Damianos17

Reputation: 37

How to count the partitions for particular table?

In Postgres I may use something like

select count(p1.*) from pg_partitions p1
where  p1.tablename = 'table_name';

How to write query that counts NO of partitions for specific table in oracle? What is oracle equivalent of pg_partitions ?

Upvotes: 1

Views: 2700

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Depending on which privileges you have

Partitions of a table you own

select count(*) from user_tab_partitions where table_name = yourtable;

Partitions of a table you own or you have privilege to see it

select count(*) from all_tab_partitions where table_name = yourtable and table_owner = ownerofthetable ;

If you have privilege select any dictionary or select catalog role, or a granted privilege over the specific dba_tab_partitionsview , then

select count(*) from dba_tab_partitions where table_name = yourtable and table_owner = ownerofthetable ;

Upvotes: 2

Related Questions