| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: how to extract create table scripts from datadictionary views?
This is from the Oracle Press book SQL & PL/SQL Annotated Archives. Run
cre_tbl.sql after you run this script.
set echo off verify off feedback off pagesize 0 term on select 'Creating table build script...' from DUAL;
accept table_name prompt "Enter the name of the Table: " accept tab_owner prompt "Enter table owner: " set term off
drop table TAB_TEMP;
create table TAB_TEMP (
Lineno NUMBER,
Id_Owner VARCHAR2(30),
Id_Name VARCHAR2(30),
Text VARCHAR2(2000))
declare
cursor TAB_CURSOR is
select Owner,
Table_Name,
Tablespace_Name,
Pct_Free,
Pct_Used,
Ini_Trans,
Max_Trans,
Initial_Extent,
Next_Extent,
Min_Extents,
Max_Extents,
Pct_Increase,
Freelists,
Freelist_Groups,
Degree,
Instances
from DBA_TABLES
where Owner = UPPER('&&tab_owner')
and Table_Name like UPPER('&&table_name')
order by Table_Name;
cursor COL_CURSOR (C_Owner VARCHAR2,
C_Tabname VARCHAR2) is
select Column_Name,
Data_Type,
Data_Length,
Data_Precision,
Data_Scale,
Nullable,
Default_Length,
Data_Default
from DBA_TAB_COLUMNS
where Owner = C_Owner
and Table_Name = C_Tabname
order by Column_ID;
cursor CONS_CURSOR (Cons_Owner VARCHAR2,
Cons_Tablename VARCHAR2) is
select A.Owner,
A.Constraint_Name,
A.Constraint_Type,
A.Table_Name,
A.Search_Condition,
B.Column_Name,
B.Position
from DBA_CONSTRAINTS A,
DBA_CONS_COLUMNS B
where A.Owner = B.Owner
and A.Constraint_Name = B.Constraint_Name
and A.Table_Name = B.Table_Name
and A.Constraint_Type in ('C','P')
and A.Owner = Cons_Owner
and A.Table_Name = Cons_Tablename
order by A.Constraint_Type,
A.Constraint_Name,
B.Position;
cursor REF_CURSOR (R_Owner VARCHAR2,
R_Tablename VARCHAR2) is
select A.Owner,
A.Table_Name,
A.Constraint_Name,
A.R_Constraint_Name,
B.Column_Name,
C.Owner,
C.Table_Name,
C.Column_Name,
C.Position
from DBA_CONSTRAINTS A,
DBA_CONS_COLUMNS B,
DBA_CONS_COLUMNS C
where A.Constraint_Name = B.Constraint_Name
and A.Owner = B.Owner
and C.Constraint_name = A.R_Constraint_Name
and B.Position = C.Position
and A.Owner = R_Owner
and A.Table_Name = R_Tablename
order by A.Constraint_Name,
A.Owner,
C.Position;
Lv_Table_Owner DBA_TABLES.Owner%TYPE;
Lv_Table_Name DBA_TABLES.Table_Name%TYPE;
Lv_Tablespace_Name DBA_TABLES.Tablespace_Name%TYPE;
Lv_Pct_Free DBA_TABLES.Pct_Free%TYPE;
Lv_Pct_Used DBA_TABLES.Pct_Used%TYPE;
Lv_Initial_Trans DBA_TABLES.Ini_Trans%TYPE;
Lv_Max_Trans DBA_TABLES.Max_Trans%TYPE;
Lv_Initial_Extent DBA_TABLES.Initial_Extent%TYPE;
Lv_Next_Extent DBA_TABLES.Next_Extent%TYPE;
Lv_Min_Extents DBA_TABLES.Min_Extents%TYPE;
Lv_Max_Extents DBA_TABLES.Max_Extents%TYPE;
Lv_Pct_Increase DBA_TABLES.Pct_Increase%TYPE;
Lv_Freelists DBA_TABLES.Freelists%TYPE;
Lv_Freelist_Groups DBA_TABLES.Freelist_Groups%TYPE;
Lv_Degree DBA_TABLES.Degree%TYPE;
Lv_Instances DBA_TABLES.Instances%TYPE;
Lv_Column_Name DBA_TAB_COLUMNS.Column_Name%TYPE;
Lv_Column_Data_Type DBA_TAB_COLUMNS.Data_Type%TYPE;
Lv_Column_Data_Length DBA_TAB_COLUMNS.Data_Length%TYPE;
Lv_Column_Data_Precision DBA_TAB_COLUMNS.Data_Precision%TYPE;
Lv_Column_Data_Scale DBA_TAB_COLUMNS.Data_Scale%TYPE;
Lv_Column_Nullable DBA_TAB_COLUMNS.Nullable%TYPE;
Lv_Column_Default_Length DBA_TAB_COLUMNS.Default_Length%TYPE;
Lv_Column_Data_Default DBA_TAB_COLUMNS.Data_Default%TYPE;
Lv_Cons_Owner DBA_CONSTRAINTS.Owner%TYPE;
Lv_Cons_Table_Name DBA_CONSTRAINTS.Table_Name%TYPE;
Lv_Cons_Constraint_Name DBA_CONSTRAINTS.Constraint_Name%TYPE;
Lv_Cons_Constraint_Type DBA_CONSTRAINTS.Constraint_Type%TYPE;
Lv_Cons_Search_Cond DBA_CONSTRAINTS.Search_Condition%TYPE;
Lv_Cons_Column_Name DBA_CONS_COLUMNS.Column_Name%TYPE;
Lv_Cons_R_Constraint_Name DBA_CONSTRAINTS.R_Constraint_Name%TYPE;
Lv_Cons_Ref_Owner DBA_CONSTRAINTS.Owner%TYPE;
Lv_Cons_Ref_Table_Name DBA_CONSTRAINTS.Table_Name%TYPE;
Lv_Cons_Ref_Column_Name DBA_CONS_COLUMNS.Column_Name%TYPE;
Lv_Cons_Ref_Position DBA_CONS_COLUMNS.Position%TYPE;
Lv_Cons_Exists VARCHAR2(1);
Lv_String VARCHAR2(800);
Lv_String2 VARCHAR2(800);
Lv_Lineno NUMBER := 0;
procedure WRITE_OUT(P_Line INTEGER, P_Owner VARCHAR2,
P_Name VARCHAR2, P_String VARCHAR2) is
begin
insert into TAB_TEMP (Lineno, Id_Owner, Id_Name, Text)
values (P_Line,P_Owner,P_Name,P_String);
end;
procedure UPDATE_OUT(P_Line INTEGER, P_Owner VARCHAR2,
P_Name VARCHAR2, P_String VARCHAR2) is
begin
update TAB_TEMP
set Text = P_String
where Lineno = P_Line
and Id_Owner = P_Owner
and Id_Name = P_Name;
begin
open TAB_CURSOR;
loop
fetch TAB_CURSOR into Lv_Table_Owner,
Lv_Table_Name,
Lv_Tablespace_Name,
Lv_Pct_Free,
Lv_Pct_Used,
Lv_Initial_Trans,
Lv_Max_Trans,
Lv_Initial_Extent,
Lv_Next_Extent,
Lv_Min_Extents,
Lv_Max_Extents,
Lv_Pct_Increase,
Lv_Freelists,
Lv_Freelist_Groups,
Lv_Degree,
Lv_Instances;
exit when TAB_CURSOR%NOTFOUND;
Lv_Lineno := 1;
Lv_String:= 'CREATE TABLE ' || LOWER(Lv_Table_Owner)
|| '.'
|| LOWER(Lv_Table_Name);
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_string := '(';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
open COL_CURSOR (Lv_Table_Owner,Lv_Table_Name);
loop
fetch COL_CURSOR into Lv_Column_Name,
Lv_Column_Data_Type,
Lv_Column_Data_Length,
Lv_Column_Data_Precision,
Lv_Column_Data_Scale,
Lv_Column_Nullable,
Lv_Column_Default_Length,
Lv_Column_Data_Default;
exit when COL_CURSOR%NOTFOUND;
Lv_String := ' ' ||
RPAD(LOWER(Lv_Column_Name),35) ||
Lv_Column_Data_Type;
if ( (Lv_Column_Data_Type = 'VARCHAR2' ) or
(Lv_Column_Data_Type = 'RAW' ) or
(Lv_Column_Data_Type = 'CHAR' ) )
then
Lv_String := Lv_String ||
'(' ||
Lv_Column_Data_Length ||
')';
elsif (Lv_Column_Data_Type = 'NUMBER')
then
if Lv_Column_Data_Precision IS NULL
then
Lv_Column_Data_Precision := 38;
Lv_Column_Data_Scale := 0;
end if;
Lv_String := Lv_String ||
'(' ||
Lv_Column_Data_Precision ||
',' ||
Lv_Column_Data_Scale ||
')';
end if;
if (Lv_Column_Data_Default IS NOT NULL)
then
LV_String := Lv_String || ' DEFAULT '
|| SUBSTR(Lv_Column_Data_Default,
1,Lv_Column_Default_Length);
end if;
if (Lv_Column_Nullable = 'N' )
then
Lv_String := Lv_String || ' NOT NULL';
end if;
Lv_String := Lv_String || ',';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
end loop;
close COL_CURSOR;
Lv_Lineno := Lv_Lineno - 1;
Lv_String := SUBSTR(Lv_String,1,(LENGTH(Lv_String) - 1));
UPDATE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ')';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'PARALLEL ( DEGREE ' || Lv_Degree
|| ' INSTANCES '
|| Lv_Instances
|| ' )';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'TABLESPACE ' || Lv_Tablespace_Name ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'PCTFREE ' || Lv_Pct_Free ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'PCTUSED ' || Lv_Pct_Used ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'INITRANS ' || Lv_Initial_Trans ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'MAXTRANS ' || Lv_Max_Trans ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'STORAGE';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := '(';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' INITIAL ' || Lv_Initial_Extent ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' NEXT ' || Lv_Next_Extent ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' MINEXTENTS ' || Lv_Min_Extents ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' MAXEXTENTS ' || Lv_Max_Extents ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' PCTINCREASE ' || Lv_Pct_Increase ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' FREELISTS ' || Lv_Freelists ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ' FREELIST GROUPS ' || Lv_Freelist_Groups ;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := ')';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := '/';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_Cons_Exists := 'N';
open CONS_CURSOR(Lv_Table_Owner, Lv_Table_Name);
loop
fetch CONS_CURSOR into Lv_Cons_Owner,
Lv_Cons_Constraint_Name,
Lv_Cons_Constraint_Type,
Lv_Cons_Table_Name,
Lv_Cons_Search_Cond,
Lv_Cons_Column_Name,
Lv_Cons_Ref_Position;
exit when CONS_CURSOR%NOTFOUND;
if (Lv_Cons_Constraint_Type = 'C') AND
(INSTR(Lv_Cons_Search_Cond,'NOT NULL',1) = 0)
then
Lv_String := 'ALTER TABLE ' || Lv_Cons_Owner
|| '.'
|| Lv_Cons_Table_Name;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'ADD ';
if (INSTR(Lv_Cons_Constraint_Name,'SYS_C',1) = 0)
then
Lv_String := Lv_String || 'CONSTRAINT '
|| Lv_Cons_Constraint_Name;
end if;
Lv_String := Lv_String || ' CHECK ('
|| Lv_Cons_Search_Cond
|| ')';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := '/';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
end if;
if (Lv_Cons_Constraint_Type = 'P')
then
Lv_Cons_Exists := 'Y';
if (Lv_Cons_Ref_Position = 1)
then
Lv_String := 'ALTER TABLE ' || Lv_Cons_Owner
|| '.'
|| Lv_Cons_Table_Name;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'ADD ';
if (INSTR(Lv_Cons_Constraint_Name,'SYS_C') = 0)
then
Lv_String := Lv_String || 'CONSTRAINT ' ||
Lv_Cons_Constraint_Name || ' ';
end if;
Lv_String := Lv_String || 'PRIMARY KEY (' ||
Lv_Cons_Column_Name || ')';
else
Lv_String := REPLACE(Lv_String,')',',' ||
Lv_Cons_Column_Name|| ')' );
end if;
end if;
end loop;
if (Lv_Cons_Exists = 'Y')
then
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := '/';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
end if;
close CONS_CURSOR;
open REF_CURSOR(Lv_Table_Owner, Lv_Table_Name);
loop
fetch REF_CURSOR into Lv_Cons_Owner,
Lv_Cons_Table_Name,
Lv_Cons_Constraint_Name,
Lv_Cons_R_Constraint_Name,
Lv_Cons_Column_Name,
Lv_Cons_Ref_Owner,
Lv_Cons_Ref_Table_Name,
Lv_Cons_Ref_Column_Name,
Lv_Cons_Ref_Position;
exit when REF_CURSOR%NOTFOUND;
if (Lv_Cons_Ref_Position = 1)
then
Lv_String := 'ALTER TABLE '|| Lv_Table_Owner
|| '.'
|| Lv_Table_Name;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String := 'ADD ';
if (INSTR(Lv_Cons_Constraint_Name,'SYS_C',1) = 0)
then
Lv_String := Lv_String || 'CONSTRAINT '
|| Lv_Cons_Constraint_Name
|| ' ';
end if;
Lv_String := Lv_String || 'FOREIGN KEY ('
|| Lv_Cons_Column_Name
|| ')';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String2 := ' REFERENCES ' || Lv_Cons_Ref_Owner
|| '.'
|| Lv_Cons_Ref_Table_Name
|| '('
|| Lv_Cons_Ref_Column_Name
|| ')';
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String2);
Lv_Lineno := Lv_Lineno + 1;
WRITE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name, '/');
Lv_Lineno := Lv_Lineno + 1;
else
Lv_String := REPLACE(Lv_String, ')', ',' ||
Lv_Cons_Column_Name || ')' );
Lv_Lineno := Lv_Lineno - 3;
UPDATE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String);
Lv_Lineno := Lv_Lineno + 1;
Lv_String2 := REPLACE(Lv_String2, ')', ',' ||
Lv_Cons_Ref_Column_Name || ')' );
UPDATE_OUT(Lv_Lineno, Lv_Table_Owner, Lv_Table_Name,
Lv_String2);
Lv_Lineno := Lv_Lineno + 2;
end if;
end loop;
close REF_CURSOR;
end loop;
close TAB_CURSOR;
end;
/
spool cre_tbl.sql
select Text
from TAB_TEMP
order by Id_Owner, Id_Name, Lineno
/
spool off
> With Oracle7, user_tables gives you the storage informations > (tablespace, cluster, pct_free, pct_used, int_trans, ...) > and user_tab_columns informations on the columns (name, type, ...). > > In Oracle8, you have to add the informations on partitions > with user_tab_partitions, user_ind_partitions, user_part_key_columns. > > If you want also constraints you have to watch to user_constraints, > user_cons_columns, user_indexes (for storage informations on the > indexes associated to the primary key, ...). > > If you want also indexes: user_indexes, user_ind_columns. > > ... (synonyms, privileges, cluster, ...) > > -- > Have a nice day > Michel > > Chroma <test_at_chromazone.demon.nl> a écrit dans le message : > 941540763.23203.0.pluto.d4ee2f03_at_news.demon.nl... > > Can anyone tell me, how to extract CREATE TABLE scripts for alluser tables
> > from the Oracle datadictionary views? > > > > Thanx, > > > > Martijn > > > > > >
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 02 1999 - 09:01:29 CST
 |
 |