{"id":179,"date":"2008-11-25T09:59:12","date_gmt":"2008-11-25T08:59:12","guid":{"rendered":"http:\/\/www.askthegerman.com\/?p=179"},"modified":"2023-02-13T07:36:24","modified_gmt":"2023-02-13T06:36:24","slug":"working-with-recyclebin","status":"publish","type":"post","link":"https:\/\/www.askthegerman.com\/?p=179","title":{"rendered":"Working with Recyclebin"},"content":{"rendered":"<p>Oracle Version 10g comes along with a new feature called Recyclebin. It is what it&#8217;s name is promising you &#8211; a bin collecting all the dropped objects and data for you. This recylebin is a prerequisite in order to have FLASHBACK DROP functionality and therefore provided to every Oracle user.<\/p>\n<p><em>SQL&gt; desc recyclebin<br \/>\n&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Null?&nbsp;&nbsp;&nbsp; Type<br \/>\n&nbsp;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;<br \/>\n&nbsp;OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL VARCHAR2(30)<br \/>\n&nbsp;ORIGINAL_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(32)<br \/>\n&nbsp;OPERATION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(9)<br \/>\n&nbsp;TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(25)<br \/>\n&nbsp;TS_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(30)<br \/>\n&nbsp;CREATETIME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(19)<br \/>\n&nbsp;DROPTIME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(19)<br \/>\n&nbsp;DROPSCN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER<br \/>\n&nbsp;PARTITION_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(32)<br \/>\n&nbsp;CAN_UNDROP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(3)<br \/>\n&nbsp;CAN_PURGE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(3)<br \/>\n&nbsp;RELATED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL NUMBER<br \/>\n&nbsp;BASE_OBJECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL NUMBER<br \/>\n&nbsp;PURGE_OBJECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL NUMBER<br \/>\n&nbsp;SPACE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER<\/em><\/p>\n<p>Perhaps important to know is that the recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table and does not work for SYS objects.<\/p>\n<p>Comparing behavior as in  8i \/ 9i, there is no issues with DROPping the table behaviour wise in 10g . The space is not released immediately and is accounted for within the same tablespace \/ schema after the drop, but there is NO recycling of the objects when dropping a tablespace or user.<\/p>\n<p><em>&nbsp;SQL&gt; DROP TABLE EMP;<br \/>\nTable dropped.<\/em><\/p>\n<p><em>SQL&gt; SELECT owner,original_name,operation,type<br \/>\n&nbsp; 2&nbsp; FROM dba_recyclebin<br \/>\n&nbsp; 3&nbsp; \/<\/em><\/p>\n<p><em>OWNER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORIGINAL_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPERATION TYPE<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212; &#8212;&#8212;<br \/>\nSCOTT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EMP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE<\/em><\/p>\n<p><em><strong>Note:<\/strong> We can also create a new table with the same name at this point.<\/em><\/p>\n<p>Freeing used space though:<br \/>\nIn order to completely remove the table from the DB and to release the space the new PURGE command is used.<\/p>\n<p><em>From SCOTT user:<br \/>\nSQL&gt; PURGE TABLE EMP;<br \/>\nTable purged.<\/em><\/p>\n<p><em>SQL&gt; SHOW recyclebin<br \/>\nSQL&gt;<\/em><\/p>\n<p>But we can also DROP and PURGE a table with a single command<\/p>\n<p><em>From SCOTT user:<br \/>\nSQL&gt; DROP TABLE EMP PURGE;<br \/>\nTable dropped.<\/em><\/p>\n<p><em>SQL&gt; SELECT *<br \/>\n&nbsp; 2&nbsp; FROM recyclebin<br \/>\n&nbsp; 3&nbsp; \/<br \/>\nno rows selected<\/em><\/p>\n<p>So there will be no need to PURGE the table out of Recylebin later on and space is directly marked as free again.<\/p>\n<p>Recylebin is for sure a usefully new feature of Oracle 10g, but rarely some Oracle user is used working with it today.&nbsp; Based on your own or customers needs it might appear meaningful to deactive it though.<\/p>\n<p>As for on Oracle 10gR2 recyclebin is an initialization parameter and by default its ON, but you can disable recyclebin by using the following commands:<\/p>\n<p><em>SQL&gt; ALTER SESSION SET recyclebin = OFF;<br \/>\nSQL&gt; ALTER SYSTEM SET recyclebin = OFF;<\/em><\/p>\n<p>Or you are going to change the initialization values within SPFILE or INIT.ORA permanently.<\/p>\n<p><strong>But be aware about&nbsp; the dropped objects, when recyclebin was ON will remain in the recyclebin even if you set the recyclebin parameter to OFF for your session or the even the whole system.<\/strong><\/p>\n<p>For freeing this allocated space also, you can use one of the following two commands:<\/p>\n<p><em>From SCOTT user:<br \/>\nPURGE recyclebin; (Purge all objects in Recyclebin)<br \/>\n<\/em><\/p>\n<p><em>From DBA user:<br \/>\nPURGE dba_recyclebin; (Purge all objects \/ only SYSDBA can)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Version 10g comes along with a new feature called Recyclebin. It is what it&#8217;s name is promising you &#8211; a bin collecting all the dropped objects and data for you. This recylebin is a prerequisite in order to have FLASHBACK DROP functionality and therefore provided to every Oracle user. SQL&gt; desc recyclebin &nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Null?&nbsp;&nbsp;&nbsp; [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5,31],"tags":[172,174,175,257,176,173,103,112],"class_list":["post-179","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle","category-tipsntricks","tag-10gr2","tag-alter-session","tag-alter-system","tag-oracle","tag-purge-recyclebin","tag-recyclebin","tag-sql","tag-sqlplus"],"_links":{"self":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=179"}],"version-history":[{"count":4,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/179\/revisions"}],"predecessor-version":[{"id":363,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=\/wp\/v2\/posts\/179\/revisions\/363"}],"wp:attachment":[{"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.askthegerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}