Oracle表数据导出导入脚本

天成

732

3页

3次

2020-03-27

10墨值下载

--yangtiancheng

--202003

set linesize 200 pages 80

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

col inst_id format a10

col HOST_NAME format a16

col VERSION format a10

col username format a28

col default_tbs format a15

col temporary_tbs format a15

select INSTANCE_NAME inst_id,HOST_NAME,VERSION, username,default_tablespace

default_tbs,temporary_tablespace temporary_tbs,created

from dba_users,v$instance

--where account_status='OPEN'

order by created;

DROP TABLE ds_user_tables;

CREATE TABLE ds_user_tables

(

OWNER VARCHAR2(70 BYTE),

TABLE_NAME VARCHAR2(70 BYTE),

COLUMN_COUNT INTEGER DEFAULT 0,

RECORD_COUNT INTEGER DEFAULT 0,

SYSTEM_ DATE DEFAULT SYSDATE,

COLUMN_FLAG INTEGER DEFAULT 1,

RECORD_FLAG INTEGER DEFAULT 1,

bytes_MB INTEGER DEFAULT 0,

vexp VARCHAR2(500 BYTE),

vimp VARCHAR2(500 BYTE),

vexpdp VARCHAR2(500 BYTE),

vimpdp VARCHAR2(500 BYTE),

vdblink VARCHAR2(500 BYTE)

);

--truncate table ds_user_tables;

insert into ds_user_tables(OWNER,table_name)

select owner,table_name

from dba_tables

where owner = upper('&owner');

--AND (table_name LIKE 'TB20%');

commit;

declare

CURSOR table_cursor

SELECT owner,table_name

FROM ds_user_tables

WHERE record_flag = 1;

--AND table_name LIKE 'TB%';

v_owner VARCHAR2 (40);

v_ename VARCHAR2 (40);

i_count NUMBER;

str_sql VARCHAR2 (1500);

v_exp VARCHAR2 (1500);

v_imp VARCHAR2 (1500);

v_expdp VARCHAR2 (1500);

v_impdp VARCHAR2 (1500);

v_dblink VARCHAR2 (1500);

BEGIN

OPEN table_cursor;

LOOP

FETCH table_cursor INTO v_owner,v_ename;

EXIT WHEN table_cursor%NOTFOUND;

str_sql := 'select /*+ parallel(4) */ count(1) from ' || TRIM (v_owner) ||

'.' || TRIM (v_ename);

EXECUTE IMMEDIATE str_sql INTO i_count;

str_sql :=

'update ds_user_tables tb set tb.record_count=:1 where tb.owner=:2 and

tb.table_name=:3';

EXECUTE IMMEDIATE str_sql USING i_count, v_owner,v_ename;

COMMIT;

if i_count>=0 then

--exp/imp

v_exp :='exp sys/xxx@xx.xx.xx.xx:1521/orcl TABLES=(\"'||v_owner||'.'||

v_ename||'\") ROWS=Y INDEXES =Y CONSTRAINTS =Y GRANTS=Y DIRECT=N BUFFER=819200

file='||v_ename||'.dmp log='||v_ename||'_exp.log';

v_imp :='imp ''sys/xxx@orcl as sysdba'' fromuser='||v_owner||' touser='||

v_owner||' IGNORE=Y GRANTS=N INDEXES=N CONSTRAINTS=N file='||v_ename||'.dmp

log='||v_ename||'_imp.log';

str_sql :=

'update ds_user_tables tb set tb.vexp=:1,tb.vimp=:2 where tb.owner=:3 and

tb.table_name=:4';

EXECUTE IMMEDIATE str_sql USING v_exp,v_imp,v_owner,v_ename;

commit;

--expdp/impdp

v_expdp :='expdp sys/xxx@xx.xx.xx.xx:1521/orcl directory=tcdmpbak

TABLES=(\"'||v_owner||'.'||v_ename||'\") dumpfile='||v_ename||'.dmp log='||

v_ename||'_expdp.log';

v_impdp :='impdp ''sys/xxx@orcl as sysdba'' directory=tcdmpbak dumpfile='||

v_ename||'.dmp log='||v_ename||'_impdp.log';

str_sql :=

'update ds_user_tables tb set tb.vexpdp=:1,tb.vimpdp=:2 where tb.owner=:3

and tb.table_name=:4';

EXECUTE IMMEDIATE str_sql USING v_exp,v_imp,v_owner,v_ename;

commit;

--dblink

v_dblink :='truncate table '||v_owner||'.'||v_ename||';'||'INSERT INTO

'||v_owner||'.'||v_ename||' SELECT * from '||v_owner||'.'||

v_ename||'@xxx;'||'commit;';

str_sql :=

'update ds_user_tables tb set tb.vdblink=:1 where tb.owner=:2 and

tb.table_name=:3';

EXECUTE IMMEDIATE str_sql USING v_dblink, v_owner,v_ename;

COMMIT;

end if;

-- DBMS_OUTPUT.put_line (TO_CHAR (v_count));

v_dblink VARCHAR2 (1500);

BEGIN

OPEN table_cursor;

LOOP

FETCH table_cursor INTO v_owner,v_ename;

EXIT WHEN table_cursor%NOTFOUND;

str_sql := 'select /*+ parallel(4) */ count(1) from ' || TRIM (v_owner) ||

'.' || TRIM (v_ename);

EXECUTE IMMEDIATE str_sql INTO i_count;

str_sql :=

'update ds_user_tables tb set tb.record_count=:1 where tb.owner=:2 and

tb.table_name=:3';

EXECUTE IMMEDIATE str_sql USING i_count, v_owner,v_ename;

COMMIT;

if i_count>=0 then

--exp/imp

v_exp :='exp sys/xxx@xx.xx.xx.xx:1521/orcl TABLES=(\"'||v_owner||'.'||

v_ename||'\") ROWS=Y INDEXES =Y CONSTRAINTS =Y GRANTS=Y DIRECT=N BUFFER=819200

file='||v_ename||'.dmp log='||v_ename||'_exp.log';

v_imp :='imp ''sys/xxx@orcl as sysdba'' fromuser='||v_owner||' touser='||

v_owner||' IGNORE=Y GRANTS=N INDEXES=N CONSTRAINTS=N file='||v_ename||'.dmp

log='||v_ename||'_imp.log';

str_sql :=

'update ds_user_tables tb set tb.vexp=:1,tb.vimp=:2 where tb.owner=:3 and

tb.table_name=:4';

EXECUTE IMMEDIATE str_sql USING v_exp,v_imp,v_owner,v_ename;

commit;

--expdp/impdp

v_expdp :='expdp sys/xxx@xx.xx.xx.xx:1521/orcl directory=tcdmpbak

TABLES=(\"'||v_owner||'.'||v_ename||'\") dumpfile='||v_ename||'.dmp log='||

v_ename||'_expdp.log';

v_impdp :='impdp ''sys/xxx@orcl as sysdba'' directory=tcdmpbak dumpfile='||

v_ename||'.dmp log='||v_ename||'_impdp.log';

str_sql :=

'update ds_user_tables tb set tb.vexpdp=:1,tb.vimpdp=:2 where tb.owner=:3

and tb.table_name=:4';

EXECUTE IMMEDIATE str_sql USING v_exp,v_imp,v_owner,v_ename;

commit;

--dblink

v_dblink :='truncate table '||v_owner||'.'||v_ename||';'||'INSERT INTO

'||v_owner||'.'||v_ename||' SELECT * from '||v_owner||'.'||

v_ename||'@xxx;'||'commit;';

str_sql :=

'update ds_user_tables tb set tb.vdblink=:1 where tb.owner=:2 and

tb.table_name=:3';

EXECUTE IMMEDIATE str_sql USING v_dblink, v_owner,v_ename;

COMMIT;

end if;

-- DBMS_OUTPUT.put_line (TO_CHAR (v_count));

of 3

10墨值下载

oracle 日常运维

关注

评论