how to list of all users who own tables in more than one tablespace.
Rajeev
Something like this? Code: select owner from (select owner,count(tablespace_name) from dba_tables group by owner) group by owner having count(*)>1 / HTH, Erik
select owner from (select owner,count(tablespace_name) from dba_tables group by owner) group by owner having count(*)>1 /
Last edited by efrijters; 04-19-2004 at 08:52 AM.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
Code: select owner from dba_tables where partitioned = 'NO' group by owner having count(distinct tablespace)>1 union select owner from dba_tab_partitions group by owner having count(distinct tablespace)>1
select owner from dba_tables where partitioned = 'NO' group by owner having count(distinct tablespace)>1 union select owner from dba_tab_partitions group by owner having count(distinct tablespace)>1
David Aldridge, "The Oracle Sponge" Senior Manager, Business Intelligence Development XM Satellite Radio Washington, DC Oracle ACE
cool trumps simple every time!
Jeff Hunter
Forum Rules