Display all schemas that have partition table in Oracle Database

select distinct owner schema_name

from dba_segments

where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))

and owner in(select table_owner from dba_tab_partitions)

order by owner;

 

Or


select distinct owner schema_name

from dba_segments

where owner not in ('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP')

and owner in(select table_owner from dba_tab_partitions)

order by owner;

 

Comments