OK, it is a fairly trivial task. But when you have to prepare for workshops etc. it may take a lot of time to purge/cleanse/empty all student’s schemas without completely deleting them. Of course deleting them is an option but: it requires DBA privileges and it also removes all privileges granted to the users. What I want to do is simply drop every single object in the user’s schema, without losing the schema altogether. There may be excellent (GUI) tools for this, but my colleague Anton wrote this SQL script (in 1 minute and 44 seconds):
set pages 0 set feedback off spool del_schema_upx.sql select 'drop ' || object_type || ' ' || user || '.' || object_name || ';' from user_objects where object_type not in ( 'INDEX', 'TRIGGER', 'PACKAGE BODY') / spool off @del_schema_upx.sql
It does not yet cater for the fact that in 10g dropped objects are not gone but are in the recycle bin instead. They still show up in user_objects – with an unrecognizable name that starts with BIN. To get rid of them for good, you have to purge the recycle bin. The statement shown above will generate statements like: drop TABLE UP5.BIN$WE/4n30QTqaR8/1j4+ZDxw==$0; that will fail because tables in the bin can not be dropped. It also does not cater for the references (foreign keys) that may exist between tables. You may either have to run the script several times or add a statement that will first remove all Foreign Keys. The same applies to dropping of types on which (object) tables or columns in tables (VARRAY, NESTED TABLE) still depend. Finally it tried to drop tables that are created to implement a Materialized View whereas the Materialized View takes the table along with it when dropped.
So with a few adaptions, this is a very useful script – it left me time to write this post.
Deploying and undeploying applications in jboss with a oracle 10g database can be a pain. command to empty the recyclebin per tablespace
I use the purge tablespace
What i really want to know, is if I can change the type-mapping of the standardjbosscmp-jdbc.xml to extend the DROP TABLE command with PURGE. That way, you don’t have to manually purge the recycle bin.
Jep you got a point there. Thinking of “training” data – it could help if the constraint was build-in the create statement.
I’m not sure that using the CREATED column makes a difference – the constraints are what would impose some rules about what to try to drop first and they are often added after the tables.
The “cascade constraints” is there to remove the need for ordering the drops and it shouldn’t be necessary to run the script more than once.
Regarding Andrews comments. Drop the objects in order of the CREATED column in view ALL|DBA|USER_OBJECTS and build them in reverse order. This will help regarding multiple executing your script.
Within the bounderies of the problem – a “select from DBA_objects” would not suffice. A simple user can not address this view. Nethertheless i will give you some brainfood. Since Oracle database version 9i (v2?) there was a great new package which appealed me as a DBA greatly: DBMS_METADATA
There are numerous (DBA) scripts around to handle the problem. But now there is a package which could be used to embedded it neatly in your specific solution.
conn system/manager connected SQL> SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') from DUAL; CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','UP7') 2 FROM DUAL; GRANT SELECT ANY DICTIONARY TO "UP7" GRANT CREATE DIMENSION TO "UP7" GRANT CREATE ANY TYPE TO "UP7" GRANT CREATE ANY DIRECTORY TO "UP7" SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','UP7') FROM DUAL; GRANT READ ON DIRECTORY "DIR_DATA" TO "UP7" WITH GRANT OPTION GRANT WRITE ON DIRECTORY "DIR_DATA" TO "UP7" WITH GRANT OPTION SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from DUAL; CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) NOT NULL ENABLE, CONSTRAINT "EMP_PRIMARY_KEY" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "EMP_SELF_KEY" FOREIGN KEY ("MGR") REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE, CONSTRAINT "EMP_FOREIGN_KEY" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"Just some simple examples…to show how powerfull this could be…and its not only limited to dba privs.
On 10g and the Recycle Bin (also see: Flashback Drop)
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until,
– You permanently drop them with the PURGE command.
– Recover the dropped objects with the UNDROP command.
– There is no room in the tablespace for new rows or updates to existing rows.
– The tablespace needs to be extended.
You can view the dropped objects in the recycle bin from two dictionary views:
– user_recyclebin – lists all dropped user objects
– dba_recyclebin – lists all dropped system-wide objects
Some statements in relation to the recycle bin:
Because of the purge keyword, the table test is not just dropped (ending up in the bin) but permanently removed instead
SQL> drop table test purge;
This statement purges an already dropped table from the bin:
SQL> purge table RB$$42514$TABLE$0;
Table purged.
This statement purges all objects from tablespace users in the recycle bin:
SQL> purge tablespace users;
Tablespace purged.
SQL> purge recyclebin;
Recyclebin purged.
The cascade constraints is an important improvement.
However, I feel that a much better, smarter and SQL*Plus independent approach is using EXECUTE IMMEDIATE in a PL/SQL Block.
Something like this??
declare cursor c_obj( b_owner in varchar2) is SELECT object_type , object_name , owner FROM dba_objects where owner = b_owner and object_type not in ( 'PACKAGE BODY','TYPE BODY', 'INDEX', 'TRIGGER', 'UNDEFINED', 'LOB') and ( object_type <> 'TABLE' or object_name not like 'BIN%') -- do not attempt to drop alrea ; l_execute varchar2(2000); begin for r_obj in c_obj( b_owner => upper('&schemaname')) loop l_execute:= 'drop '||r_obj.object_type||' '||r_obj.owner||'.'||r_obj.object_name; if r_obj.object_type = 'TABLE' then l_execute:= l_execute || ' CASCADE CONSTRAINTS'; end if; EXECUTE IMMEDIATE l_execute; end loop; end;I use this slightly improved script:
SELECT ‘drop ‘ ||object_type || ‘ ‘|| owner||’.’ || object_name || ‘;’
FROM dba_objects
where owner in (‘USER1’, ‘USER2’)
and object_type not in ( ‘PACKAGE BODY’, ‘INDEX’, ‘TABLE’, ‘TRIGGER’, ‘UNDEFINED’)
union
SELECT ‘drop ‘ || object_type || ‘ ‘ || owner||’.’||object_name || ‘ cascade constraints;’
FROM dba_objects
where owner in (‘USER1’, ‘USER2’)
and object_type = ‘TABLE’;
I forget what ‘UNDEFINED’ is for, but I needed it for a particular schema. This script doesn’t drop DB links too well because it prefixes them with “user.”