| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Reverse engineering environments from one schema to another
The following scripts can be used to copy a complete environment from
one schema to another. To commence the run see instructions in attached
script DB_RE.SQL. I have attached a sample environment file at the end
(OWNERS.ENV).
Note that view 'ALL_SORCE' and 'CONSTRAINT_DEFS' (also
included) need to be compiled with public synonyms in the SYS schema
first.
Permission has been obtained from the author (owent_at_vodacom.co.za) to distribute these utilities. If you have any problems using the utilities please mail me (agie_at_ctcc.gov.za) direct.
---*********************************************************************
---*********************************************************************
SET ECHO OFF
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET LINESIZE 110
-- Needed for LONG of trigger/view
SET LONG 10000
-- Needed for LONG of trigger/view
SET LONGC 5000
-- Needed for LONG of trigger/view
SET ARRAYSIZE 3
DEFINE spoolfile = '&1'
-- The name of the spool file which will contain the generated DDL */
DEFINE exec_path = '&2'
-- The path for scripts called by this script
DEFINE copy_schema = '&3'
-- The name of the schema being copied
DEFINE copy_objects = '&4'
--SET TERMOUT OFF
IS
l_exclude_option VARCHAR2(10);
BEGIN
l_exclude_option := p_exclude_option;
INSERT INTO temp_copy_objects
(object_name ,
copy_ch ,
copy_fk ,
copy_uk ,
copy_pk ,
copy_tr ,
copy_nn )
VALUES (UPPER(p_object_name),
decode(instr(l_exclude_option, 'C'), 0, 'C', NULL),
decode(instr(l_exclude_option, 'F'), 0, 'F', NULL),
decode(instr(l_exclude_option, 'U'), 0, 'U', NULL),
'P' ,
decode(instr(l_exclude_option, 'T'), 0, 'T', NULL),
decode(instr(l_exclude_option, 'N'), 0, 'N', NULL));
END;
SET TERMOUT ON
CREATE TABLE temp_dependencies
(name VARCHAR2(30),
referenced_name VARCHAR2(30));
INSERT INTO temp_dependencies
SELECT name, referenced_name
FROM all_dependencies
WHERE referenced_name NOT IN ('STANDARD', 'DBMS_STANDARD')
AND name NOT IN ('STANDARD', 'DBMS_STANDARD')
AND referenced_name <> name
AND type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE','PACKAGE BODY')
AND referenced_type IN
('PROCEDURE','FUNCTION', 'PACKAGE','PACKAGE BODY')
AND owner = upper('©_schema')
AND referenced_owner = upper('©_schema');
INSERT INTO temp_dependencies
SELECT name, referenced_name
FROM all_dependencies
WHERE type = 'VIEW'
AND referenced_type IN ('VIEW','TABLE')
AND owner = upper('©_schema')
AND referenced_owner = upper('©_schema');
INSERT INTO temp_copy_objects
(object_name,
copy_ch,
copy_fk,
copy_uk,
copy_pk,
copy_tr,
copy_nn)
SELECT DISTINCT
referenced_name,
rec.copy_ch,
rec.copy_fk,
rec.copy_uk,
rec.copy_pk,
rec.copy_tr,
rec.copy_nn
FROM temp_dependencies
WHERE referenced_name NOT IN
(select object_name from temp_copy_objects)
START WITH name = rec.object_name
CONNECT BY PRIOR referenced_name = name;
COMMIT;
SET TERMOUT OFF
SPOOL &spoolfile
SELECT 'ALTER TABLE ' || ri.table_name || ' DROP CONSTRAINT ' ||
ri.constraint_name || ';'
FROM constraint_defs ri,
user_constraints ref,
temp_copy_objects copy
WHERE ri.r_constraint_name = ref.constraint_name AND
ref.table_name = copy.object_name AND
ri.owner = user AND
ri.r_owner = user AND
copy.object_type = 'TABLE' AND
ri.constraint_type = 'R';
SELECT 'DROP TABLE ' || user_tables.table_name || ';'
FROM user_tables, temp_copy_objects
WHERE user_tables.table_name = temp_copy_objects.object_name AND
temp_copy_objects.object_type = 'TABLE';
SELECT 'PROMPT Creating selected tables from ©_schema...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'B'
ORDER BY seq_no;
SELECT 'PROMPT Creating selected views from ©_schema...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'V'
ORDER BY seq_no;
SELECT 'PROMPT Creating primary key constraints...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'P'
ORDER BY seq_no;
SELECT 'PROMPT Creating uniqueness constraints...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'U'
ORDER BY seq_no;
SELECT 'PROMPT Creating foreign key constraints...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'R'
ORDER BY seq_no;
SELECT text FROM temp_text
WHERE object_type = 'C'
ORDER BY seq_no;
SELECT 'PROMPT Creating database triggers on selected tables...' FROM DUAL;
SELECT text FROM temp_text
WHERE object_type = 'S'
ORDER BY seq_no;
SPOOL OFF --Now drop temporary tables/procedures - no longer needed
DROP TABLE temp_text; DROP TABLE temp_copy_objects; DROP TABLE temp_dependencies;
DECLARE
l_last_table_name CHAR(30) := '@';
l_seq_no NUMBER(3):= 0; -- Sequences output
l_first_table_name BOOLEAN := TRUE; -- Is this first table?
BEGIN FOR rec IN (
SELECT col.table_name,
col.column_name,
col.data_type,
col.data_length len,
col.data_precision prec,
col.data_scale scale,
col.nullable,
copy.copy_nn
FROM all_tab_columns col,
temp_copy_objects copy
WHERE col.table_name = copy.object_name
AND copy.object_type = 'TABLE'
AND col.owner = upper('&1')
ORDER BY col.table_name, col.column_id)
LOOP
IF RTRIM(rec.table_name) <> RTRIM(l_last_table_name) THEN
-- This is a new table */
l_last_table_name := rec.table_name;
IF l_first_table_name THEN
l_first_table_name := FALSE; -- From here on, not first table
ELSE
-- If not very first table, then output the close bracket for
-- the last table
INSERT INTO temp_text VALUES ('B', l_seq_no, ');');
l_seq_no := l_seq_no + 1;
END IF;
-- Create the table header for this row */
INSERT INTO temp_text VALUES ('B', l_seq_no,
'CREATE TABLE ' || rec.table_name || ' (');
l_seq_no := l_seq_no + 1;
-- Create the first column definition.
INSERT INTO temp_text VALUES ('B', l_seq_no,
rec.column_name || ' ' || rec.data_type ||
decode(rec.data_type, 'CHAR', '(' || to_char(rec.len) || ')',
'RAW', '(' || to_char(rec.len) || ')',
'VARCHAR2', '(' || to_char(rec.len) || ')',
'VARCHAR', '(' || to_char(rec.len) || ')',
'NUMBER', '(' || to_char(rec.prec) || ',' ||
to_char(rec.scale) || ')',
'') ||
decode(rec.copy_nn || rec.nullable, 'NN', ' NOT NULL', '') );
l_seq_no := l_seq_no + 1;
ELSE
-- We are still processing previous table - output this column as
-- part of table */
-- Create the first column definition.
INSERT INTO temp_text VALUES ('B', l_seq_no,
',' || rec.column_name || ' ' || rec.data_type ||
decode(rec.data_type, 'CHAR', '(' || to_char(rec.len) || ')',
'RAW', '(' || to_char(rec.len) || ')',
'VARCHAR2', '(' || to_char(rec.len) || ')',
'VARCHAR', '(' || to_char(rec.len) || ')',
'NUMBER', '(' || to_char(rec.prec) || ',' ||
to_char(rec.scale) || ')',
'') ||
decode(rec.copy_nn || rec.nullable, 'NN', ' NOT NULL', '') );
l_seq_no := l_seq_no + 1;
END IF;
END LOOP;
DECLARE
l_last_view_name CHAR(30) := '@';
l_seq_no NUMBER(5); -- Sequences output no
l_base_count NUMBER(5):= 0;
BEGIN FOR rec IN (
SELECT vw.view_name,
col.column_name,
vw.text
FROM all_tab_columns col,
temp_copy_objects copy,
all_views vw
WHERE col.table_name = copy.object_name
AND vw.view_name = copy.object_name
AND copy.object_type = 'VIEW'
AND col.owner = upper('&1')
AND vw.owner = upper('&1')
ORDER BY col.table_name, col.column_id)
LOOP
IF RTRIM(rec.view_name) <> RTRIM(l_last_view_name) THEN
-- This is a new view */
l_last_view_name := rec.view_name;
l_seq_no := 1; -- Start sequence again for this base number.
-- Create the view header for this row */
INSERT INTO temp_text VALUES ('V', l_base_count + l_seq_no,
'CREATE OR REPLACE VIEW ' || rec.view_name );
l_seq_no := l_seq_no + 1;
-- Create the first column definition.
INSERT INTO temp_text VALUES ('V', l_base_count + l_seq_no,
'(' || rec.column_name);
l_seq_no := l_seq_no + 1;
-- Output the view text for the view, at the end of this base count
INSERT INTO temp_text VALUES ('V', l_base_count + 97, ') AS');
INSERT INTO temp_text VALUES ('V', l_base_count + 98, rec.text);
INSERT INTO temp_text VALUES ('V', l_base_count + 99, '/');
l_base_count := l_base_count + 100;
ELSE
-- We are still processing previous view - output this column as
-- part of previous view
INSERT INTO temp_text VALUES ('V', l_base_count + l_seq_no - 100,
',' || rec.column_name);
l_seq_no := l_seq_no + 1;
END IF;
END LOOP;
COMMIT;
END;
/
DECLARE
l_last_cons CHAR(30) := '@';
l_seq_no NUMBER(3):= 0; -- Sequences output
l_first_cons BOOLEAN := TRUE; -- Is this first constraint?
l_constraint_type CHAR(11);
BEGIN
IF '&1' = 'P' THEN
l_constraint_type := 'PRIMARY KEY';
ELSE
IF ('&1' = 'U') THEN
l_constraint_type := 'UNIQUE';
ELSE
raise_application_error(-20000, 'Parameter must be U or P');
END IF;
SELECT ac.constraint_name cons,
ac.table_name tab,
acc.column_name col
FROM all_constraints ac,
all_cons_columns acc,
temp_copy_objects copy
WHERE ac.constraint_name = acc.constraint_name AND
copy.object_name = ac.table_name AND
copy.object_type = 'TABLE' AND
instr( copy_uk || copy_pk, '&1') > 0 AND
ac.constraint_type = '&1' AND
ac.owner = upper('&2') AND
acc.owner = upper('&2')
ORDER BY ac.constraint_name, acc.position)
LOOP
IF RTRIM(rec.cons) <> RTRIM(l_last_cons) THEN
-- This is a new constraint */
l_last_cons := rec.cons;
IF l_first_cons THEN
l_first_cons := FALSE; -- From here on, not first constraint
ELSE
-- If not very first constraint, then output the close bracket for
-- the last constraint
INSERT INTO temp_text VALUES ('&1', l_seq_no, ');');
l_seq_no := l_seq_no + 1;
END IF;
-- Create the constraint header for this column */
INSERT INTO temp_text VALUES ('&1', l_seq_no,
'ALTER TABLE ' || rec.tab || ' ADD CONSTRAINT ');
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('&1', l_seq_no,
rec.cons || ' ' || l_constraint_type || ' (' || rec.col);
ELSE
-- We are still processing previous constraint - output this column as
-- part of constraint */
INSERT INTO temp_text VALUES ('&1', l_seq_no, ',' || rec.col);
END IF;
l_seq_no := l_seq_no + 1;
DECLARE l_last_cons CHAR(30) := '@';
l_seq_no NUMBER(3):= 0; -- Sequences output
l_first_cons BOOLEAN := TRUE; -- Is this first constraint?
l_prev_ref_tab CHAR(30); -- Stores table name
l_prev_ref_col1 CHAR(30); -- previous 1st referenced col
l_prev_cons_complex BOOLEAN; -- More than one column?
l_prev_ref_cons CHAR(30); -- Previous referenced constraint
l_local_tab NUMBER(3);
BEGIN
FOR rec IN (
SELECT
cd.table_name ri_tab,
cd.constraint_name cons ,
ri_col.column_name ri_col,
ref_cons.table_name ref_tab,
ref_col.column_name ref_col1,
ref_cons.constraint_name ref_cons
FROM constraint_defs cd,
all_cons_columns ri_col,
all_constraints ref_cons,
all_tables ref_tab,
all_cons_columns ref_col,
temp_copy_objects copy
WHERE cd.constraint_name = ri_col.constraint_name AND
copy.object_name = cd.table_name AND
copy.copy_fk IS NOT NULL AND
cd.r_constraint_name = ref_cons.constraint_name AND
ref_cons.constraint_name = ref_col.constraint_name AND
ref_col.position = 1 AND
ref_tab.table_name = ref_cons.table_name AND
cd.constraint_type = 'R' AND
copy.object_type = 'TABLE' AND
cd.owner = upper('&1') AND
cd.r_owner = upper('&1') AND
ri_col.owner = upper('&1') AND
ref_cons.owner = upper('&1') AND
ref_tab.owner = upper('&1') AND
ref_col.owner = upper('&1')
ORDER BY ri_col.constraint_name, ri_col.position)
LOOP
IF RTRIM(rec.cons) <> RTRIM(l_last_cons) THEN
-- This is a new constraint */
l_last_cons := rec.cons;
IF l_first_cons THEN
l_first_cons := FALSE; -- From here on, not first constraint
ELSE
-- If not very first constraint, then output 'references' section
-- of the previous constraint.
-- Note - can't implement as stored procedures, because role does
-- not permit stored procedure to access db views.
-- Determine if referencing table is local or remote.
-- First - determine if a selected table
SELECT count(temp_copy_objects.object_name)
INTO l_local_tab
FROM temp_copy_objects
WHERE RTRIM(temp_copy_objects.object_name) = RTRIM(l_prev_ref_tab);
-- Second - if not a selected table, determine if already local
IF l_local_tab = 0 THEN
SELECT count(user_tables.table_name)
INTO l_local_tab
FROM user_tables
WHERE RTRIM(user_tables.table_name) = RTRIM(l_prev_ref_tab);
END IF;
IF l_local_tab = 0 THEN -- ie, not a local referencing table
INSERT INTO temp_text VALUES ('R', l_seq_no,
') REFERENCES &1..' || l_prev_ref_tab);
ELSE -- is a local referencing table
INSERT INTO temp_text VALUES ('R', l_seq_no,
') REFERENCES ' || l_prev_ref_tab);
END IF;
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('R', l_seq_no,
' (' || l_prev_ref_col1);
l_seq_no := l_seq_no + 1;
-- Now we should output referenced columns..., if more than one
IF l_prev_cons_complex THEN
FOR rec1 IN (
SELECT all_cons_columns.column_name ref_col
FROM all_cons_columns
WHERE RTRIM(all_cons_columns.constraint_name)
= RTRIM(l_prev_ref_cons) AND
all_cons_columns.position > 1 AND
all_cons_columns.owner = upper('&1')
ORDER BY all_cons_columns.position)
LOOP
INSERT INTO temp_text VALUES ('R', l_seq_no, ',' || rec1.ref_col);
l_seq_no := l_seq_no + 1;
END LOOP;
END IF;
-- Output close bracket for the previous constraint
INSERT INTO temp_text VALUES ('R', l_seq_no, ');');
l_seq_no := l_seq_no + 1;
END IF;
-- Remember data for next loop iteration.
l_prev_ref_tab := rec.ref_tab; -- previous referenced table
l_prev_ref_col1 := rec.ref_col1; -- previous 1st referenced col
l_prev_cons_complex:= FALSE; -- Assume one column
l_prev_ref_cons := rec.ref_cons; -- Previous referenced constraint
-- Create the constraint header for this column */
INSERT INTO temp_text VALUES ('R', l_seq_no,
'ALTER TABLE ' || rec.ri_tab || ' ADD CONSTRAINT ');
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('R', l_seq_no,
rec.cons || ' FOREIGN KEY (' || rec.ri_col);
l_seq_no := l_seq_no + 1;
ELSE
-- We are still processing previous constraint - output this column as
-- part of constraint */
l_prev_cons_complex:= TRUE; -- More than one column
INSERT INTO temp_text VALUES ('R', l_seq_no, ',' || rec.ri_col);
l_seq_no := l_seq_no + 1;
END IF;
END LOOP;
IF l_local_tab = 0 THEN -- ie, not a local referencing table
INSERT INTO temp_text VALUES ('R', l_seq_no,
') REFERENCES &1..' || l_prev_ref_tab);
ELSE -- is a local referencing table
INSERT INTO temp_text VALUES ('R', l_seq_no,
') REFERENCES ' || l_prev_ref_tab);
END IF;
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('R', l_seq_no,
' (' || l_prev_ref_col1);
l_seq_no := l_seq_no + 1;
-- Now we should output referenced columns..., if more than one
IF l_prev_cons_complex THEN
FOR rec1 IN (
SELECT all_cons_columns.column_name ref_col
FROM all_cons_columns
WHERE RTRIM(all_cons_columns.constraint_name) =
RTRIM(l_prev_ref_cons) AND
all_cons_columns.position > 1 AND
all_cons_columns.owner = upper('&1')
ORDER BY all_cons_columns.position)
LOOP
INSERT INTO temp_text VALUES ('R', l_seq_no, ',' || rec1.ref_col);
l_seq_no := l_seq_no + 1;
END LOOP;
END IF;
-- Output close bracket for the previous constraint
INSERT INTO temp_text VALUES ('R', l_seq_no, ');');
l_seq_no := l_seq_no + 1;
DECLARE
l_last_cons CHAR(30) := '@';
l_seq_no NUMBER(3):= 0; -- Sequences output
l_first_cons BOOLEAN := TRUE; -- Is this first constraint?
BEGIN FOR rec IN (
SELECT oco_name cons,
td_table tab,
txt_text txt
FROM cdi_constraints,
cdi_text,
sdd_tab_defs,
temp_copy_objects copy
WHERE cdi_constraints.oco_ref = cdi_text.txt_ref AND
copy.object_name = sdd_tab_defs.td_table AND
copy.copy_ch IS NOT NULL AND
oco_constraint_type = 'CHECK' AND
copy.object_type = 'TABLE' AND
td_tid = oco_tab_ref AND
txt_type <> 'CDIDSC'
ORDER BY cdi_constraints.oco_name, cdi_text.txt_seq)
LOOP
IF RTRIM(rec.cons) <> RTRIM(l_last_cons) THEN
-- This is a new constraint */
l_last_cons := rec.cons;
IF l_first_cons THEN
l_first_cons := FALSE; -- From here on, not first constraint
ELSE
-- If not very first constraint, then output the close bracket for
-- the last constraint
INSERT INTO temp_text VALUES ('C', l_seq_no, ');');
l_seq_no := l_seq_no + 1;
END IF;
-- Create the constraint header for this column */
INSERT INTO temp_text VALUES ('C', l_seq_no,
'ALTER TABLE ' || rec.tab || ' ADD CONSTRAINT ');
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('C', l_seq_no,
rec.cons || ' CHECK (');
l_seq_no := l_seq_no + 1;
-- first line of constraint text
INSERT INTO temp_text VALUES ('C', l_seq_no, RTRIM(rec.txt));
l_seq_no := l_seq_no + 1;
ELSE
-- We are still processing previous constraint - output this column as
-- part of constraint */
INSERT INTO temp_text VALUES ('C', l_seq_no, RTRIM(rec.txt));
l_seq_no := l_seq_no + 1;
END IF;
END LOOP;
DECLARE
l_seq_no NUMBER(5):= 0; -- Sequences output
l_first_proc BOOLEAN := TRUE; -- Is this first proc?
BEGIN FOR rec IN (
SELECT line, text
FROM all_sorce,
temp_copy_objects copy
WHERE copy.object_name = all_sorce.name AND
copy.object_type IN
('FUNCTION', 'PROCEDURE','PACKAGE','PACKAGE BODY') AND
all_sorce.owner = upper('&1')
ORDER BY type, name, line)
LOOP
IF rec.line = 1 THEN
-- This is a new procedure */
IF l_first_proc THEN
l_first_proc := FALSE; -- From here on, not first constraint
ELSE
-- If not very first routine, then output the 'compile' slash
-- for the previous stored routine
INSERT INTO temp_text VALUES ('S', l_seq_no, '/');
l_seq_no := l_seq_no + 1;
INSERT INTO temp_text VALUES ('S', l_seq_no, 'show errors');
l_seq_no := l_seq_no + 1;
END IF;
INSERT INTO temp_text VALUES ('S', l_seq_no,
'CREATE OR REPLACE ' || rec.text);
ELSE
INSERT INTO temp_text VALUES ('S', l_seq_no, rec.text);
END IF;
l_seq_no := l_seq_no + 1;
create or replace view ALL_SORCE
(OWNER, NAME, TYPE, LINE, TEXT)
as
select u.name, o.name,
decode(o.type, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 'UNDEFINED'),
s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u
where o.obj# = s.obj# and o.owner# = u.user# and o.type in (7, 8, 9, 11)
o.obj# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# = 12 /* EXECUTE */)
or
exists (select null from sys.sysauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# = -144 /* EXECUTE ANY PROCEDURE */)
)
 |
 |