OBJ_LIST DBA_OBJECTS%ROWTYPE;

JOB_LIST DBA_JOBS%ROWTYPE;

SQLTEXT VARCHAR2(2000);

BEGIN

OPEN CUR_OBJECTS; --循环删除结构关系

LOOP

FETCH CUR_OBJECTS INTO OBJ_LIST;

EXIT WHEN CUR_OBJECTS%NOTFOUND;

SQLTEXT:='DROP '||OBJ_LIST.OBJECT_TYPE||' "'||OBJ_LIST.OBJECT_NAME||'"';

IF (OBJ_LIST.OBJECT_TYPE='TABLE') THEN

SQLTEXT:=SQLTEXT||' CASCADE CONSTRAINTS';

END IF;

EXECUTE IMMEDIATE SQLTEXT;

END LOOP;

CLOSE CUR_OBJECTS;

-------------------------------------------

OPEN CUR_JOBS; --循环删除作业

LOOP

FETCH CUR_JOBS INTO JOB_LIST;

EXIT WHEN CUR_JOBS%NOTFOUND;

DBMS_JOB.REMOVE(JOB_LIST.JOB); --根据作业号删除作业

END LOOP;

CLOSE CUR_JOBS;

--------------------------------------------

IF (TAG=0 OR TAG=10) THEN

EXECUTE IMMEDIATE 'PURGE USER_RECYCLEBIN';--清空回收站

END IF;

END DROP_ALL_OBJECTS;

/

create or replace PROCEDURE DROP_SINGLE_OBJECT

--功能:基于指定用户下删除单个结构(表、视图、函数、类型、包、触发器、序列),但不能删除该过程本身

--参数:DBUSER-数据库用户，pOBJECTNAME-结构名

--调用:EXECUTE DROP_SINGLE_OBJECT('OPENVONE','TB_USERS')

--作者:郭君

--日期:2013-02-10

(

DBUSER IN VARCHAR2,

pOBJECTNAME IN VARCHAR2

)

AS

VCOUNT NUMBER;

OBJ_TYPE DBA_OBJECTS.OBJECT_TYPE%TYPE;

SQLTEXT VARCHAR2(2000);

BEGIN

SELECT COUNT(*) INTO VCOUNT FROM DBA_OBJECTS WHERE owner=DBUSER AND OBJECT_TYPE IN('TABLE','VIEW','FUNCTION','PROCEDURE','TYPE','PACKAGE','TRIGGER','SEQUENCE')

AND OBJECT_NAME<>'DROP_SINGLE_OBJECT' AND OBJECT_NAME=pOBJECTNAME; --不能删除自己

IF VCOUNT=1 THEN

SELECT OBJECT_TYPE INTO OBJ_TYPE FROM DBA_OBJECTS WHERE owner=DBUSER AND OBJECT_NAME=pOBJECTNAME;

SQLTEXT:='DROP '||OBJ_TYPE||' "'||pOBJECTNAME||'"';

IF (OBJ_TYPE='TABLE') THEN

SQLTEXT:=SQLTEXT||' CASCADE CONSTRAINTS';

END IF;

EXECUTE IMMEDIATE SQLTEXT;

END IF;

COMMIT;

END;

/

create or replace PROCEDURE GET_ALL_OBJECTS

--功能:查询用户下所有的结构并返回大字段文段，需要保存到文件进行读取

--参数:DBUSER-数据库用户，TAG-结构类型，见下方说明，TBNAME-模糊匹配的结构名，如果为空或''则生成全库的DDL语句，SQLTEXT-返回生成结构的DDL语句大文本字段

--调用:

--作者:郭君

--日期:2013-02-10

(

DBUSER IN VARCHAR2,

TAG IN NUMBER,

TBNAME IN VARCHAR2,

SQLTEXT OUT CLOB

)

--操作均基于指定用户下进行，其他用户不做处理

--0.全部

--1.序列

--2.表（未解决键/索引对应关系以及创建先后顺序问题，需注意）

--3.索引

--4.触发器

--5.视图

--6.类型

--7.函数

--8.过程

--9.包

AS

CURSOR R_OBJ IS

SELECT * FROM DBA_OBJECTS WHERE OWNER=DBUSER

AND OBJECT_NAME LIKE '%'||TBNAME||'%' --模糊匹配结构名。可生成相同部分结构的DDL语句

AND OBJECT_TYPE IN('SEQUENCE','TABLE','INDEX','TRIGGER','VIEW','TYPE','FUNCTION','PROCEDURE','PACKAGE')

AND OBJECT_TYPE=DECODE(TAG,0,OBJECT_TYPE,1,'SEQUENCE',2,'TABLE',3,'INDEX',4,'TRIGGER',5,'VIEW',6,'TYPE',7,'FUNCTION',8,'PROCEDURE',9,'PACKAGE')

ORDER BY DECODE(OBJECT_TYPE,'SEQUENCE',1,'TABLE',2,'INDEX',3,'TRIGGER',4,'VIEW',5,'TYPE',6,'FUNCTION',7,'PROCEDURE',8,'PACKAGE',9);

R_OBJ_LIST DBA_OBJECTS%ROWTYPE;

DDL_TEXT CLOB;

BEGIN

OPEN R_OBJ;

LOOP

FETCH R_OBJ INTO R_OBJ_LIST;

EXIT WHEN R_OBJ%NOTFOUND;