Re: Easy question

Detlev Goebel wrote:
>
> Hugo Juan Jordan wrote:
> >
> > In order to document the structure of a database, they have asked me to
> > obtain a text file with all the SQL statements needed to build the same
> > database that I am running in a Oracle server 7.3.. I'm sure it must be
> > very easy to obtain, but I have no idea.
> > Any suggestions?
> >
> > I'm very sorry to ask silly questions, but I'm still very far from really
> > good ones.
> >
> > Thanks in advance.
> >
> > Hugo Juan
>
> Hugo,
>
> I never heard of silly questions did you?
> Your requirements are not trivial at all. You have to look up several
> system views to get your information :
> sys.all_tables - to get all information about tables like storage and
> tablespace where they reside.
> sys.dba_tab_columns - for al columns of those tables (names, datatype,
> length and default values).
> sys.all_indexes - for indexes on those tables.
> sys.dba_index_columns - for the columns of those indexes.
> sys.all_constraints - for the constraints on those tables.
> sys.all_views - for the views.
>
> Maybe the tablespaces, the user informations, datafiles and so on.
>
> if you'll write scripts take care about it.
>
> Otherwise I'm sure there are some tools anywhere in the net to do this.
> Perhaps anyone else can provide some URL's.

Okey dokey - while other's suggestions of tools to recreate the entire DB are useful, http://www.oramag.com (Oracle Magazine) has a script which, when run as sys or system, can do this for a single table. (look in the top tips or codebase sections at that site). Here's how its done for indexes: (I wrote this: no warranty!)

• genidx <table_name>
• Generates the code necessary to recreate all of the indexes
• on a given table. Some fields have been ignored in producing
• this SQL: namely
• CLUSTER Not generally used here
• ASC/DESC Not generally used and unavailable in user_ind_columns?
• NOSORT Not generally used here --
• Brian Ewins, Thu May 22 12:04:21 BST 1997 -- set verify off set feedback off set serveroutput ON size 1000000 set pages 0 set lines 80 declare max_col integer:=0; tbl_name varchar2(32); begin tbl_name:=upper('&&1'); for idx in ( select * from user_indexes ui where ui.table_name=tbl_name ) loop dbms_output.put_line('CREATE INDEX '||idx.index_name); dbms_output.put_line('ON '||idx.table_owner ||'.'||idx.table_name); dbms_output.put_line('('); select max(column_position) into max_col from user_ind_columns uic where idx.index_name=uic.index_name and idx.table_name=uic.table_name; for col in ( select * from user_ind_columns uic where uic.index_name=idx.index_name and idx.table_name=uic.table_name ) loop dbms_output.put(' '||col.column_name); if (col.column_position<max_col) then dbms_output.put_line(','); end if; end loop; dbms_output.put_line(')'); dbms_output.put_line('TABLESPACE '||idx.tablespace_name); dbms_output.put_line('PCTFREE '||idx.pct_free); dbms_output.put_line('INITRANS '||idx.ini_trans); dbms_output.put_line('MAXTRANS '||idx.max_trans); dbms_output.put_line('STORAGE'); dbms_output.put_line('('); dbms_output.put_line(' INITIAL '||idx.initial_extent); dbms_output.put_line(' NEXT '||idx.next_extent); dbms_output.put_line(' MINEXTENTS '||idx.min_extents); dbms_output.put_line(' MAXEXTENTS '||idx.max_extents); dbms_output.put_line(' PCTINCREASE '||idx.pct_increase); dbms_output.put_line(');'); end loop; end; . /

  ---

I find this script useful when someone's being adding indexes to a table during development and they're going to be dropped for a data load - I don't need to rely on knowing where all the index creation scripts were at (a couple of times they havent even existed).

Anyway, its trivial to combine the gentab script (suitably hacked to be usable by users) and genidx above, to recreate all tables and indexes.

The awkward bit arises when you have constraints which were created 'using index'... these indexes shouldn't be dropped, and the stuff above should instead go towards an 'alter table enable constraint <foo> using index...' statement.

        Hope this helped.

-- 
****====----                                              Brian Ewins. 
Fax: (44) 141 220 6100                          Tel: (44) 141 220 6121
 "It's time we face reality, my friends...
      We're not exactly rocket scientists." --Gary Larson ----====****