功能:
1、先删除数据库用户,本地dmp文件。
2、FTP实现自动下载DMP文件到本地。
3、自动还原DMP文件到数据。
4、编译数据库失效对象。
run.bat
set oracle_sid=test
sqlplus system/future@test @d:\test\clear.sql
del D:\test\pos01.dmp
del D:\test\pos01.log
ftp -s:ftp.txt
IMP system/future@test full=y ignore=y commit=y file=D:\test\pos01.dmp log=D:\test\pos01.log
sqlplus system/future@test @d:\test\sql.sql
exit
clear.sql
Begin
For vr in (Select * From dba_Users Where UserName Like 'DBUSR%') Loop
Execute Immediate 'Drop User '||Vr.UserName||' cascade';
End Loop;
End;
/
quit
ftp.txt
open 192.168.199.42
test
test
lcd D:\test
bin
mget pos01.dmp
Y
quit
sql.sql
declare str varchar2(500);
begin
for invalid_indexs in (select 'call dbms_utility.compile_schema('''||username||''')' invalstr from dba_users where username <>'SYS'
union
select 'call dbms_utility.compile_schema(''PUBLIC'')' invalstr from dual)
loop
execute immediate invalid_indexs.invalstr;
end loop;
end;
/
quit