Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: how to see the tables of other schema

    can anyone tell me how can i see all the tables of some other schema in oracle.

    i can use 'select * from tab ' for seeing all the tables in my schema but if i want to see all the tables of some say'xyz' schema , how can i do that. what is the sql command for that ?
    i do have the priviledge to see the tables of the schema

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    look at all__tables

  3. #3
    Join Date
    May 2009
    Posts
    2
    u mean 'select * from <schema_name>.all_tables' ????

    i need to see all tables of a specific schema.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select table_name
    from all_tables
    where owner='OTHER_SCHEMA'
    ORDER BY TABLE_NAME;
    Please be aware, you will ONLY see those tables that you have privilege to see. Next time when someone says look at a table, at least do a describe of the table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select Table_name From All_tables Where Owner = 'specific_schema';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2009
    Posts
    46
    The former suggestions are fine but you may also consider this one

    First, It depend on whether your account has select right on other schemas

    If it does, then this may work :

    SELECT TABLE_NAME, OWNER FROM ALL_TABLES;

    OR

    SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER IN ('schema1','schema2' ,...)

    OR
    WHEN LOGGED IN AS SYSDBA
    SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER IN ('schema1','schema2' ,...)

    This statement lists all tables and their owners for you

    Hope it works

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    1. Dredges up a 2 month old post.
    2. Adds nothing to the previous posts.
    3. advises agaist best practices (do not log in as sysdba to perform such tasks. sysdba is a very special type of user and Oracle can and will behave differently when logged in as sysdba. Only log in as sysdba to perform tasks that only sysdba can perform).
    4. Predicate would cause no data to return as Owner column stores data in upper case.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Please read this document:
    How to Use the Data Dictionary
    The Data Dictionary
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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