#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0

    How do I find out what tables are on the database?


    Hi,

    I have been wondering about this for some time and I can't seem to get it. I want to be able to go

    SELECT sysadm.systables;

    or something and it will show me all the tables in the database. It would also be great to have a way of finding out what fields are in each table, instead of having to go

    SELECT * FROM address;

    and break it halfway through to find out what the fields are.

    I am using and Oracle 8i database (I think).
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    The accessible tables are stored in ALL_TABLES or USER_TABLES

    The column definitions are stored in ALL_TAB_COLUMNS or USER_TAB_COLUMNS

    I don't now what kind of client you are using, but in SQL*Plus you can also do a DESC address to get the table's definition
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0

    Query


    How do i query the last 10 rows from the table?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    How do i query the last 10 rows from the table?
    I'm not sure what you mean with "last 10 rows" as tables don't have a last and a first row. But I'm assuming you can order them somehow, but

    SELECT *
    FROM (SELECT * FROM table ORDER BY column)
    WHERE ROWNUM < 11

    should do what you want.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    Thanks heaps!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    So how did you know that ALL_TABLES exists? Is there a table with the system tables in it? I'm thinking there are other tables like ALL_TABLES that would be useful - where can I find out about them?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    This is all documented in the manuals, it really helps to read them

    For 8.1.7 it's here: http://download-uk.oracle.com/docs/c.../doc/index.htm
    For the current releases you can find it here: http://otn.oracle.com/documentation/index.html

    And of course on your installation CD
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0

    query


    Originally Posted by shammat
    I'm not sure what you mean with "last 10 rows" as tables don't have a last and a first row. But I'm assuming you can order them somehow, but

    SELECT *
    FROM (SELECT * FROM table ORDER BY column)
    WHERE ROWNUM < 11

    should do what you want.
    thanks anyway

    i managed to get it thank you

    actually the above query doesn't really return the last 10 rows it will still return the 10 rows

    it should be

    Select * from table_name where rownum < 11 order by column_name desc

IMN logo majestic logo threadwatch logo seochat tools logo