if [ ! -f /export/home/oracle/nas/scott21.dmp.gz ]
then
# mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com
echo "FILE does not exist"
exit 1
fi
echo "FILE EXIST"
size=-1
set $(du /export/home/oracle/nas/scott21.dmp.gz)
while [ $size -ne $1 ]
do
echo "Inside the loop"
size=$1
set $(du /export/home/oracle/nas/scott21.dmp.gz)
sleep 1
done
echo "FTP DONE"
gunzip /export/home/oracle/nas/scott21.dmp.gz > /dev/null
if [ $? -ne 0 ]
then
#mail -s "Import failed file exist but corrupted" sanjay.jaiswal@xyz.com
echo "FILE corrupted"
exit 1
fi
echo "starting import"
sqlplus /nolog << EOF
connect system/oracle
@/export/home/oracle/nas/zero_test_create.sql
exit
EOF
`
echo "On linux Prompt"
imp system/oracle file=/export/home/oracle/nas/scott21.dmp grants=n rows=y ignore=y log=/export/home/oracle/nas/zero_test.log
touser=prakash,sanjay fromuser=scott,system
echo "Import Completed"
cat /export/home/oracle/nas/zero_test.log | grep -v IMP-00041 | grep 5 IMP- > import_error.log
# mail -s "Export failed " sanjay.jaiswal@xyz.com < import_error.log
exit 0
==========
declare
cursor username_c is SELECT TARGET_ADMIN FROM impdp;
cursor kill_session_c(username_v varchar2) is SELECT sid,serial# FROM V$session WHERE username=username_v;
username_vc username_c%rowtype;
kill_session_vc kill_session_c%rowtype;
l_kill_count NUMBER DEFAULT 0;
l_user_exist number default 0;
run varchar2(80);
BEGIN
open username_c;
LOOP
fetch username_c INTO username_vc;
EXIT WHEN username_c%notfound;
open kill_session_c(username_vc.TARGET_ADMIN);
LOOP
fetch kill_session_c INTO kill_session_vc;
EXIT WHEN kill_session_c%notfound;
DBMS_OUTPUT.PUT_LINE('The '|| username_vc.TARGET_ADMIN || ' session is being killed ');
run := 'alter system kill session ''' || kill_session_vc.sid || ','
||kill_session_vc.serial# || ''' immediate ' ;
execute immediate run;
DBMS_OUTPUT.PUT_LINE('The '|| username_vc.TARGET_ADMIN || ' session with sid,serial#' ||kill_session_vc.sid ||' , ' || kill_session_vc.serial# );
END LOOP;
close kill_session_c;
LOOP
SELECT nvl(count(*),0) INTO l_kill_count FROM v$session WHERE username = username_vc.TARGET_ADMIN;
EXIT WHEN l_kill_count = 0 ;
dbms_backup_restore.sleep(0.1);
END LOOP;
run := 'drop user ' || username_vc.TARGET_ADMIN || ' cascade' ;
BEGIN
execute immediate run;
DBMS_OUTPUT.PUT_LINE('Droping ' || username_vc.TARGET_ADMIN);
EXCEPTION
WHEN others then
DBMS_OUTPUT.PUT_LINE('Oracle Error ' ||substr(sqlerrm,1,250));
END;
LOOP
SELECT nvl(count(*),0) INTO l_user_exist FROM dba_users WHERE username = username_vc.TARGET_ADMIN;
EXIT WHEN l_user_exist = 0 ;
dbms_backup_restore.SLEEP(1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' dropped');
DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' is being created ');
run := 'create user ' || username_vc.TARGET_ADMIN || ' identified by advantage ' ;
execute immediate run;
run := 'grant connect,resource to ' || username_vc.TARGET_ADMIN ;
DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' is created ' );
execute immediate run;
-- update impdp set CREATE_USER=1 where TARGET_ADMIN=username_vc.TARGET_ADMIN;
commit;
-- datapump_admin_api(username_vc.TARGET_ADMIN);
DBMS_OUTPUT.put_line(username_vc.TARGET_ADMIN);
END LOOP;
END;
/
I am sorry. what i want is that the shelll should wait till hte pl/sql(called from shell) porcedure is completed and after completion shell should start the rest with rest of code.
Please also let me know if hte shell has program has any othere problem
Thanks and Regards