java.runtime调用下面的脚本清除用户下面的所有表,存储过程等内容,但是没有效果,请帮看看那里出错了
网上找的例子,自己改了下#!bin/sh
su - oracle
sqlplus /nolog
conn usr/pwd <<EOF
set heading off;
set feedback off;
spool /oraclebak/dropobj.sql;
select 'Drop table '||table_name||' cascade constraints;' from all_tables where owner='usr';
select 'drop view ' || view_name||' cascade constraints;'||chr(13)||chr(10) from all_views where owner='usr';
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from all_sequences where sequence_owner='usr';
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PROCEDURE' and owner='usr';
select 'drop package ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PACKAGE' and owner='usr';
spool off;
set heading on;
set feedback on;
EOF
网上找的例子,自己改了下#!bin/sh
su - oracle
sqlplus /nolog
conn usr/pwd <<EOF
set heading off;
set feedback off;
spool /oraclebak/dropobj.sql;
select 'Drop table '||table_name||' cascade constraints;' from all_tables where owner='usr';
select 'drop view ' || view_name||' cascade constraints;'||chr(13)||chr(10) from all_views where owner='usr';
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from all_sequences where sequence_owner='usr';
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PROCEDURE' and owner='usr';
select 'drop package ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PACKAGE' and owner='usr';
spool off;
set heading on;
set feedback on;
EOF
V_SQL varchar2(200);
BEGIN
for del in (select t.OBJECT_TYPE,t.OBJECT_NAME from dba_objects t where t.OWNER = 'HH7YX') LOOP
begin
V_SQL := 'drop ' || del.object_type || ' ' || del.object_name || ';';
dbms_output.put_line(V_SQL);
EXECUTE IMMEDIATE V_SQL;
exception
when others then
null;
--dbms_output.put_line(sqlerrm);
end;
END LOOP;
END;