数据泵expdp 全自动整库导出脚本

陈尚荣

2104

11页

7次

2020-05-31

100墨值下载

#/bin/sh

#创建脚本目录

mkdir -pv /home/oracle/expdp_script

#获取表空间创建语句

echo ====== 1. tablespace information ======

CMDS="

export ORACLE_HOME=$ORACLE_HOME

export ORACLE_SID=$ORACLE_SID

echo

echo ----- SUBSHELL ENV VARIABLES -----

echo

echo

$ENV_COMMANDS

echo ----- STARTING SQL EXECUTION -----

echo

sqlplus / as sysdba"

TABLESPACE_INPUT=" <<\EOF

set long 300000;

set echo off;

set feedback off;

set heading off;

set pagesize 0;

set linesize 30000;

set trimout on;

set trimspool on;

set termout off;

@/home/oracle/expdp_script/get_tablespace_DDL.sql

exit

EOF

TABLESPACE_OUTPUT=" <<\EOF

set long 300000;

set echo off;

set feedback off;

set heading off;

set pagesize 0;

set linesize 30000;

set trimout on;

set trimspool on;

set termout off;

@/home/oracle/expdp_script/1.get_tablespace_SQL.sql

exit

EOF

#获取表空间创建的 DDL 查询语句写入 /home/oracle/expdp_script/get_tablespace_SQL.sql

echo ====== get tablespace DDL ======

cat > /home/oracle/expdp_script/get_tablespace_DDL.sql <<EOF

spool /home/oracle/expdp_script/get_tablespace_SQL.sql

col SQL format a100;

select distinct 'select dbms_metadata.get_ddl(''TABLESPACE'','||Chr(39)||

tablespace_name||Chr(39)||')||'';'' as "SQL" from dual union all' as "SQL"

from dba_data_files

where TABLESPACE_NAME not in('SYSTEM','SYSAUX','USERS','UNDOTBS1','UNDOTBS2')

order by 1;

spool off

EOF

echo ====== done ======

echo ====== exec get_tablespace_DDL ======

#执行 /home/oracle/expdp_script/get_tablespace_DDL.sql 获取查询表空间的 sql 语句

#结果写入 /home/oracle/expdp_script/get_tablespace_SQL.sql

/bin/sh -c "$CMDS $TABLESPACE_INPUT"

echo ====== done ======

echo ====== get_tablespace_SQL ======

echo ====== done ======

echo ====== get 1.get_tablespace_SQL.sql ======

#创建 /home/oracle/expdp_script/1.get_tablespace_SQL.sql

cat > /home/oracle/expdp_script/1.get_tablespace_SQL.sql <<EOF

spool /home/oracle/expdp_script/2.exec_tablespace_SQL.sql

EOF

#把 /home/oracle/expdp_script/get_tablespace_SQL.sql 内容添加到

/home/oracle/expdp_script/1.get_tablespace_SQL.sql 中

cat /home/oracle/expdp_script/get_tablespace_SQL.sql >>

/home/oracle/expdp_script/1.get_tablespace_SQL.sql

#修改 /home/oracle/expdp_script/1.get_tablespace_SQL.sql 文件格式供数据库执行

sed -i '$s/from dual union all/from dual;/'

/home/oracle/expdp_script/1.get_tablespace_SQL.sql

echo "spool off" >> /home/oracle/expdp_script/1.get_tablespace_SQL.sql

echo ====== done ======

echo ====== exec 1.get_tablespace_SQL.sql ======

#执行 /home/oracle/expdp_script/1.get_tablespace_SQL.sql 获得表空间的创建语句

#结果写入 /home/oracle/expdp_script/2.exec_tablespace_SQL.sql

/bin/sh -c "$CMDS $TABLESPACE_OUTPUT"

echo ====== done ======

#修改 /home/oracle/expdp_script/2.exec_tablespace_SQL.sql 文件格式供数据库执行，新数

据库可以直接执行

echo ====== get 2.exec_tablespace_SQL.sql ======

echo ====== done ======

echo ====== 1. tablespace information done ======

#获取用户创建语句

echo ====== 2. users information ======

of 11

100墨值下载

oracle 升级迁移

关注

评论