Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Red face Unanswered: Structure of Table

    Hi All,
    Can anyone tell me a way to get the structure of a table, which should include the indexes, storage parameters etc.,

    Thanks in advance
    qAnand

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Query (alphabetically):
    - USER_CATALOG to see type of the object
    - USER_TABLES for storage parameters
    - USER_COL_COMMENTS for comments on columns
    - USER_COL_PRIVS, USER_COL_PRIVS_MADE, USER_COL_PRIVS_RECD for grants on columns
    - USER_CONSTRAINTS for constraint definitions on user's tables
    - USER_CONS_COLUMNS for columns in constraint definitions
    - USER_INDEXES to see indexes
    - USER_IND_COLUMNS for columns of the user's indexes
    - USER_SYNONYMS to see your private synonyms
    - USER_TABLES contains description of the user's own tables
    - USER_TAB_COLUMNS - columns of user's tables, views and clusters
    - USER_TAB_COMMENTS - comments on the tables and views
    - USER_TAB_PRIVS, USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD - grants
    - USER_TRIGGERS, USER_TRIGGER_COLS - database triggers and usage of columns in triggers

  3. #3
    Join Date
    Jul 2003
    Posts
    70

    Re

    Thanks for the reply,
    BTW is there a way where in we can get the structural information without querying these views.

    Regards,
    qAnand

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure, there is. Find someone who already wrote those queries

    I have one that lists indeses for a desired table(s). Perhaps it'll be good for you too ...
    PHP Code:
    column table_name format a24
    column column_name format a15
    column index_name format a15
    column uniqueness format a9
    column tablespace format a11
    break on table_name on index_name on uniqueness on tablespace
     
    select uic
    .table_nameui.uniquenessui.tablespace_name tablespaceuic.index_nameuic.column_name
    from user_ind_columns uic
    user_indexes ui
    where ui
    .index_name uic.index_name
    and uic.table_name like '%' || upper('&table_name') || '%'
    order by uic.table_nameuic.index_nameuic.column_position
    Last edited by Littlefoot; 05-25-04 at 04:35.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a script you can use to build a "CREATE TABLE" script. It will include
    the structure and the existing storage parameters.... Go to the bottom of
    the script and change the SPOOL statement to match your environment...

    HTH
    Gregg
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gregg,

    there was some garbage in your query, in the last IF-THEN-ELSE block (if (Lv_Cons_Ref_Position = 1) then ...) - lines exeeded 255 characters. I fixed it in my text editor.

    Executed it on my database, but there was no output in the spooled file, nor in TAB_TEMP table. I saw you query DBA_... views so I connected to a privileged (sys) user, but there was no result either.

    What could be wrong?

    P.S. I wish I knew how to write such a script ... magnificent! (never mind it won't work, it's GREAT )

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I'm not sure why you wouldn't be getting data in tab_temp ... I use that script and others like it all the time ... Try this one ... It is the same, except I have commented out -- set echo and set termout ... Change those and watch the execution .... It might be that you are not creating the initial procedure (write_out) ...
    Execute this passing the variables ...

    sql> @c:\...\gen_tbl2 'tablename' 'owner'

    HTH
    Gregg
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It was still not working, but I know one reason: I ran the script on Oracle 7.1, and DBA_TABLES doesn't have Freelists nor Freelist_Groups columns so it terminated unsuccessfully.

    Then I ran it on Oracle 9i. It got stuck in a dead loop somewhere. Debugging it I found that first loop (TAB_CURSOR) works fine (inserts a record into TAB_TEMP) and so do another two, but then the procedure enters a loop and never exits.

    I found that the problem was in the fourth loop (REF_CURSOR). You know where the bug is? WHERE needs another condition: "AND A.R_Owner = C.Owner".

    How come? I created user and imported SCOTT/TIGER's data into it and tested your script on this new user. The REF_CURSOR found two rows, almost the same. The only difference was right there, in R_Owner / Owner columns.

    Now it works just fine. It took me a day to find it out, but I guess it was worth doing it.

    Thank you again for the script, Gregg. Consider including additional WHERE condition into your script too.

    Kindest regards!

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I appreciate the input ... I created/modified, etc ... most of the scripts that I use like that with Oracle 8 ... I have not run and checked most of them againt 9 ... Thanks for doing the legwork on that one !!!!

    Gregg

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The pleasure was mine

  11. #11
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote Originally Posted by qAnand
    Hi All,
    Can anyone tell me a way to get the structure of a table, which should include the indexes, storage parameters etc.,

    Thanks in advance
    qAnand

    You can use:

    - third party tools: DataBee, DBArtisan
    - export with rows=n
    - DBMS_METADATA package Example
    - or custome script


    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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