在平时的应用开发及数据库环境准备的过程中,经常会遇到清理当前用户下数据库对象的需求,以便满足数据库环境准备。本文给出一种使用PL/SQL实现清理当前用户下数据库对象的方法。
【提醒】:由于脚本中涉及到DROP这种危险的DDL命令,千万不要在生产环境及系统用户(SYSTEM及SYS用户等)下进行尝试。
1.环境准备
这里模拟创建表、视图以及同名。
sec@ora11g> create table t1 (x int);
Table created.
sec@ora11g> create table t2 (x int);
Table created.
sec@ora11g> create view v_t1 as select * from t1;
View created.
sec@ora11g> create view v_t2 as select * from t2;
View created.
sec@ora11g> create synonym syn_t1 for t1;
Synonym created.
sec@ora11g> create synonym syn_t2 for t2;
Synonym created.
sec@ora11g> select OBJECT_NAME,OBJECT_TYPE from obj order by CREATED;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
T1 TABLE
T2 TABLE
V_T1 VIEW
V_T2 VIEW
SYN_T1 SYNONYM
SYN_T2 SYNONYM
6 rows selected.
OK,具有三种数据库对象的sec用户准备完毕,此处为每一种数据库对象创建了多个。
2.准备清理当前用户数据库对象的PL/SQL脚本
由于脚本结构和内容均比较简单,不进行注释。具体脚本内容如下。
DECLARE
CURSOR l_tables IS
select table_name from user_tables;
CURSOR l_views IS
select view_name from user_views;
CURSOR l_synonyms IS
select synonym_name from user_synonyms;
BEGIN
-- drop tables
FOR rec_tables IN l_tables LOOP
dbms_output.put_line('Dropping table ' rec_tables.table_name );
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' rec_tables.table_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop table ' rec_tables.table_name);
END;
END LOOP;
-- drop views
FOR rec_views IN l_views LOOP
dbms_output.put_line('Dropping view ' rec_views.view_name);
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' rec_views.view_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop view ' rec_views.view_name);
END;
END LOOP;
-- drop synonyms
FOR rec_synonyms IN l_synonyms LOOP
dbms_output.put_line('Dropping synonym ' rec_synonyms.synonym_name);
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM ' rec_synonyms.synonym_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop synonym ' rec_synonyms.synonym_name);
END;
END LOOP;
END;
/
3.执行效果验证
1)执行脚本过程中的输出信息
Dropping table T1
Dropping table T2
Dropping view V_T1
Dropping view V_T2
Dropping synonym SYN_T1
Dropping synonym SYN_T2
PL/SQL procedure successfully completed.
如果在删除的过程中出现问题,这里也会给出简单的报错提示。
2)测试脚本执行效果
sec@ora11g> select OBJECT_NAME,OBJECT_TYPE from obj order by CREATED;
no rows selected
可见,当前sec用户下的表、视图以及同名清理完毕。
4.小结
这里给出的仅仅是删除当前用户下表、视图以及同名的PL/SQL脚本。根据具体的环境,可以按照脚本的结构自行定制。
--转自