数据库自动导入脚本_impdp通过dblink 一键导入 for19c

#!/bin/bash

#源   SID
S_PDB_SID=$1

#目标   SID  
T_PDB_SID=$2

#源 ip
IPP=$3

#例子： sh  impdpt.sh  spdb tpdb sip  ，在目标端执行，可以实现在目标端tpdb 下，建立dblink，导入spdb


echo $S_PDB_SID 
echo $IPP
echo $T_PDB_SID
export ORACLE_PDB_SID=$T_PDB_SID
mkdir /home/oracle/ip/
mkdir /u01/app/oracle/dump
rm /home/oracle/ip/$T_PDB_SID.sh
sqlplus -s / as sysdba<<eof
drop public database link link0705; 
create public database link link0705 connect to system identified by password using '${IPP}:1521/${S_PDB_SID}';
select * from dual@link0705;
drop directory exp0705;
create directory exp0705 as '/u01/app/oracle/dump';
eof
sqlplus -s / as sysdba<<eof >/home/oracle/ip/$T_PDB_SID.sh
set heading off
set feed off
set line 999
select 'export ORACLE_PDB_SID=$T_PDB_SID' from dual;
select 'sqlplus -s / as sysdba<<eof' from dual; 
select DISTINCT 'CREATE TABLESPACE '||tablespace_name||' DATAFILE SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED, SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED, SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;' from DBA_DATA_FILES@link0705 where TABLESPACE_name NOT IN ('SYSTEM','SYSAUX') and tablespace_name not like '%UND%' GROUP BY TABLESPACE_NAME;
select DISTINCT 'ALTER TABLESPACE '||tablespace_name||' ADD DATAFILE SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED, SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED, SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,SIZE 10M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;' from DBA_DATA_FILES where TABLESPACE_name NOT IN ('SYSTEM','SYSAUX') and tablespace_name not like '%UND%' GROUP BY TABLESPACE_NAME;
select distinct 'create temporary tablespace '|| tablespace_name||' tempfile size 1G autoextend on;' from v\$TEMP_EXTENT_POOL@link0705; 
select 'alter user '||USERNAME||' account lock;' from dba_users where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX') AND USERNAME NOT IN ('NFT') and username in (select username from dba_users@link0705 where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX')); 
select 'alter system kill session '''||sid||','||SERIAL#||',@'||inst_id||''' immediate;' from gv\$session where USERNAME IN (select username from dba_users where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX') AND USERNAME NOT IN ('NFT')and username in (select username from dba_users@link0705 where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX')));
select 'alter pluggable database $T_PDB_SID close immediate instances=all;' from dual;
select 'alter pluggable database $T_PDB_SID open instances=all;' from dual;
select 'DROP USER '||USERNAME||' CASCADE;' from dba_users where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX') AND USERNAME NOT IN ('NFT')
and username in (select username from dba_users@link0705 where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX'));
select 'eof' from dual;
select 'impdp \"/ as sysdba \" network_link=link0705 directory=exp0705 schemas='||listagg(USERNAME,',') Within group(Order By USERNAME Desc) ||' logfile=$T_PDB_SID.log parallel=8 cluster=no exclude=statistics ' from dba_users@link0705 where account_status='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX')AND USERNAME NOT LIKE '%NFT%' and USERNAME not like 'C##%';
select 'sqlplus -s / as sysdba<<eof' from dual; 
select 'exec utl_recomp.recomp_parallel(8);' from dual;
select 'eof' from dual;

eof
cat /home/oracle/ip/$T_PDB_SID.sh
nohup sh /home/oracle/ip/$T_PDB_SID.sh &