- 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
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 ...
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_name, ui.uniqueness, ui.tablespace_name tablespace, uic.index_name, uic.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_name, uic.index_name, uic.column_position;
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...
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 ...
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.
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 !!!!