#1
  1. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,186
    Rep Power
    2265

    SHOW TABLES in Oracle


    In MySQL, I can get a list of all tables w/ this query:
    "SHOW TABLES"

    Whats the syntax w/ Oracle 9i ?

    Looked through the docs online, but w/o knowing what you're trying to look up, you can't really look anything up (kind of a catch22).

    Thx in advance for the knowledge.
    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  2. #2
  3. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,186
    Rep Power
    2265
    nevermind, its:

    select * from user_objects where object_type = 'TABLE';

    gotta love brevity.

    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    28
    Rep Power
    0
    how about user _tables?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    2
    Rep Power
    0
    you have to select from USER_TABLES or DBA_TABLES
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    27
    Rep Power
    0
    u can use
    select * from tabs;
  10. #6
  11. No Profile Picture
    Permanently Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    37
    Rep Power
    0

    Lightbulb


    Hi,

    Use user_tables or all_all_tables, but not use dba_tables this need some privileges.

    Regards,
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    1
    Rep Power
    0
    SELECT table_name FROM tabs;
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    32
    Rep Power
    8
    lol...i think he/she got the point...!!

    my two cents...if you want all the metadata 'tables', then do a:

    select * from dict;
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    1
    Rep Power
    0
    select * from dict;
    amazing!
    so with that, its safe to say if you want to see what tables
    the user has access to the query is.

    select TABLE_NAME from ALL_ALL_TABLES;

    ALL_ALL_TABLES
    Description of all object and relational tables accessible to the user

    Just a note to fellow mysql'ers who run across this venturing into oracle for the first time.

    Oracle doesn't have a universal username storage system like mysql.
    show databases works cause if you have a user whom you created and gave some high privileges to, it will see most databases created by lesser powered logins.
    Oracle however has users on a per database level.

    So when you create a new database, unless you create another user login thats the same from DB1, user will not be able to see stuff on DB2.

    I could be wrong, this is just the findings a person who's been trying to understand oracle for the past few weeks
    so if anyone better words of wisdom or wants to correct a few things... please by all means.

    Best Regards,
    Curtis Cage(d_Penguin)
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by Caged_Penguin
    So when you create a new database, unless you create another user login thats the same from DB1, user will not be able to see stuff on DB2.
    Oracle does not have the concept of a "Database" as MySQL (or SQL Server) has. The usual "mapping" of databases from MySQL (or SQL Server) to Oracle is a users. So if you have several databases in MySQL you would have several users in Oracle. In Oracle a schema is (more or less) equivalent to a user.

IMN logo majestic logo threadwatch logo seochat tools logo