Re: Show the Database

What your doing is in principle okay but remember that all_tab_columns and all_constraints are MASSIVE tables (depending on your database size) and you are basically doing a join on these tables and hence a rather large sort as well. The sort will initially be done in memory but as it gets larger Oracle will swap the sort out to disk which is incredibly slow for large sorts. You may want to get your dba to tune sort_area_size ie (make it larger to reduce the number of runs done to disk) or make sure that your user id is assigned a large well tuned (initial=next=n*sort_area_size) tablespace as your default temporary tablespace ie alter user OPS$ORACLE default tablespace x temporary tablespace large_sort; You have also specified unique/distinct which removes duplicates but also dramatically increases the elapsed time. Heres my SQL for you which does basically what you want it to do.

select distinct a.table_name,decode(b.constraint_type,'C','Check','P','Primary','U','Unique','R','Referential'),b.status,a.column_name,a.data_type,a.data_length,decode(a.nullable,'Y','NULL','N','NOT NULL') from dba_tab_columns a, dba_constraints b where a.table_name = b.table_name ;

You may need to use all_ instead of dba_ Note the use of decode which is real SQL friend and can be used tio make your output more elegant. I applaud your initiate in posting to the board but some people here do get a bit ratty about 'simple' questions. I love em..              Received on Fri Oct 07 1994 - 17:54:59 CET