users own tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: users own tables

  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    4

    users own tables

    how to list of all users who own tables in more than one tablespace.
    Rajeev

  2. #2
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    cool trumps simple every time!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width