]]>By: barthttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1082
Thu, 07 Apr 2005 13:46:07 +0000/?p=248#comment-1082Deploying and undeploying applications in jboss with a oracle 10g database can be a pain.
I use the purge tablespace command to empty the recyclebin per 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.
]]>By: Marcohttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1081
Tue, 09 Nov 2004 23:05:54 +0000/?p=248#comment-1081Jep you got a point there. Thinking of “training” data – it could help if the constraint was build-in the create statement.
]]>By: andrewhttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1080
Tue, 09 Nov 2004 17:21:03 +0000/?p=248#comment-1080I’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.

]]>By: Marco Gralikehttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1079
Tue, 09 Nov 2004 16:32:13 +0000/?p=248#comment-1079Regarding 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.
]]>By: Marco Gralikehttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1078
Tue, 09 Nov 2004 16:03:45 +0000/?p=248#comment-1078Within 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.

Just some simple examples…to show how powerfull this could be…and its not only limited to dba privs.

]]>By: Lucashttps://technology.amis.nl/2004/11/08/sql-script-to-empty-outcleanse-a-user-schema/#comment-1077
Tue, 09 Nov 2004 15:58:19 +0000/?p=248#comment-1077On 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: