对于测试数据库，经常会涉及到用户数据重新导入的情况，在不赋予用户创建和删除用户的前提下，普通用户也可以通过脚本来删除自己模式下的表、视图等对象。
下面是一个删除用户所有表的脚本：
DECLARE
CURSOR MYCURSOR IS SELECT TABLE_NAME FROM USER_TABLES;
VTABLE_NAME VARCHAR2(60);
sql_stmt varchar2(100);
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR INTO VTABLE_NAME;
EXIT WHEN MYCURSOR%NOTFOUND;
sql_stmt := 'drop table "'||VTABLE_NAME||'" cascade constraints';
EXECUTE IMMEDIATE sql_stmt;
END LOOP;
CLOSE MYCURSOR;
END;
/删除存储过程等对象：
DECLARE
CURSOR MYCURSOR IS SELECT NAME,TYPE FROM USER_SOURCE;
vsource_name VARCHAR2(60);
vsource_type VARCHAR2(60);
sql_stmt VARCHAR2(100);
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR INTO vsource_name,vsource_type;
EXIT WHEN MYCURSOR%NOTFOUND;
sql_stmt := 'drop '||vsource_type||' "'||vsource_name||'"';
EXECUTE IMMEDIATE sql_stmt;
--dbms_output.put_line(sql_stmt);
END LOOP;
CLOSE MYCURSOR;
END;
/
quit;通过光标循环操作，可以实现其它对象的删除。
DECLARE
CURSOR MYCURSOR IS SELECT SYNONYM_NAME FROM USER_SYNONYMS;
VTABLE_NAME VARCHAR2(60);
sql_stmt varchar2(100);
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR INTO VTABLE_NAME;
EXIT WHEN MYCURSOR%NOTFOUND;
sql_stmt := 'drop SYNONYM "'||VTABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt;
END LOOP;
CLOSE MYCURSOR;
END;
/
Comments
There are currently no comments
New Comment