Part II
There are some tables need copy from one oracle user User1 to another user User2.
1. The structure of the two users are same;
两个用户的结构是相同
2. No DB Link or privilege granted between those two users;
两个用户之间没有DB连接或权限
3. No primary key or unique key conflict between those two users' data;
两个用户之间没有主键和唯一的冲突
4. The records number is not very much (at most 10000 for one table)。
数字的记录不能超过很大
Study the solution, and write another copy scripts from User1 to User2 with below criteria :
1. The structure of the two users are same;
2. Can create DB Link or grant priv between those two users;
--*******************************
--File main.sql
--*******************************
set echo off serveroutput on feedback off trimspool on line 10000 verify off head off
@@ one_table.sql 'COMPONENT';
set head on line 100 feedback on termout on echo on
--*******************************
--File one_table.sql
--*******************************
prompt *** Process &1 
;
set serveroutput on echo off feedback off trimspool on line 10000 verify off feedback off head off termout off
define tab_name = '&1'
spool ./data/&tab_name..sql
declare
cursor c1(v_owner varchar2, v_tab_name varchar2) is
select column_name, data_type
from all_tab_columns
where table_name = v_tab_name
and owner = v_owner;
field_list varchar2(4000);
field_list_sel varchar2(4000);
fmt_field varchar2(200);
date_format varchar2(30) := 'yyyymmddhh24miss';
v_owner varchar2(30) := user;
v_tab_name varchar2(30) := upper(trim('&tab_name'));
begin
dbms_output.enable(1000000);
dbms_output.put_line('set echo off feedback off');
dbms_output.put_line('spool ./data/&tab_name' || '_data.sql');
-- Print the insert field
dbms_output.put_line('variable field_list varchar2(4000);');
dbms_output.put_line('begin');
dbms_output.put_line(' :field_list := ''insert into &tab_name (''' );
for rec1 in c1(v_owner, v_tab_name) loop
if c1%rowcount = 1 then
field_list := ' || '' ' || rec1.column_name || '''';
else
field_list := ' || '',' || rec1.column_name || '''';
end if;
dbms_output.put_line(field_list);
end loop;
dbms_output.put_line('||'')''||chr(10)||''values ('';');
dbms_output.put_line('end;');
dbms_output.put_line('/');
-- For select SQL to generate insert statement
dbms_output.put_line('select :field_list || chr(10) ||');
for rec1 in c1(v_owner, v_tab_name) loop
-- For insert values clause
if rec1.data_type = 'DATE' then
fmt_field := '''to_date(''''''||to_char(' || rec1.column_name || ', ''' ||
date_format || ''')||'''''',''''' || date_format || ''''')''';
elsif rec1.data_type in ('CHAR', 'VARCHAR2') then
fmt_field := ''''''''' || replace(' || rec1.column_name || ','''''''', '''''''''''')|| ''''''''';
else
fmt_field := 'to_char(' || rec1.column_name || ')';
end if;
if c1%rowcount = 1 then
field_list_sel := fmt_field;
else
field_list_sel := ' ||'','' || ' || fmt_field;
end if;
dbms_output.put_line(field_list_sel);
end loop;
dbms_output.put_line('|| '');''');
dbms_output.put_line('from &tab_name ;');
dbms_output.put_line('spoo off;');
end;
/
spool off;
@ ./data/&tab_name..sql
set head on line 100 termout on