At this article i will try to show you an easier way of migrating scripts (including table, view, procedure, function, package creation scripts) to another system. Here are the steps for doing this;
1 - Create a table for keeping data temptorarily.
2- create a table. I will name my table as xxtg_kts_engin_deneme
create table xxtg.xxtg_kts_engin_deneme
(
text clob;
);
3 - Choose the prefixes of the tables(or whatever object you want) you desire.
4 - Choose the prefixes of tables you want to exclude.
5 - modify the cursor c_table below upon your needs
cursor c_table is
SELECT *
FROM dba_objects
WHERE object_type = ‘TABLE’
AND object_name LIKE ‘XX%’
AND object_name NOT LIKE ‘XXTG%’
6 - Run the script below to insert your table creation scripts into xxtg_kts_engin_deneme table. After that you can copy your scripts by just opening the table data.
declare
cursor c_table is
SELECT *
FROM dba_objects
WHERE object_type = ‘TABLE’
AND object_name LIKE ‘XX%’
and object_name not like ‘XXXT_PO%’
and object_name not like ‘XXXT_INV%’
and object_name not like ‘XXXT_AP%’
AND owner NOT IN (‘XXTEPE’, ‘XXBR’, ‘XXPYXIS’);
r_table c_table%rowtype;
one_table clob;
tmp_table clob;
tmp_table2 clob;
pos number;
begin
open c_table;
loop
fetch c_table into r_table;
exit when c_table%notfound;
tmp_table2 := ‘drop table ‘ || r_table.table_name;
select DBMS_METADATA.GET_DDL (
’TABLE’,
r_table.table_name,
’XXTG’
) X
into tmp_table
from dual;
tmp_table := ‘drop table xxtg.’ || r_table.table_name || chr(10) || tmp_table;
begin
select ‘drop synonym ‘ || r_table.table_name || chr(10) || DBMS_METADATA.GET_DDL (
’SYNONYM’,
r_table.table_name,
’APPS’
) X
into tmp_table2
from dual;
exception when others then
tmp_table2 := ”;
end;
tmp_table := tmp_table || tmp_table2;
one_table := one_table || tmp_table;
dbms_output.put_line(r_table.table_name);
one_table := one_table || chr(10) || ‘/’ || chr(10);
–one_table := one_table || tmp_table;
end loop;
close c_table;
insert into xxtg.xxtg_kts_engin_deneme
(text)
values(one_table);
end;
