Wednesday, March 26, 2008

How to Purge the RECYCLEBIN in Oracle 10g

THE RECYCLE BIN
*****************


The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.


There is no issues with DROPping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS objects

Checking the RECYCLEBIN Objects
*******************************


SELECT object_name,original_name,operation,type,dropscn,droptime FROM user_recyclebin;

SELECT owner,original_name,operation,type FROM dba_recyclebin;


Purging the Recyclebin
**************************

Subject: 10g Recyclebin Features And How To Disable it( _recyclebin )
Doc ID: Note:265253.1 Type: BULLETIN

Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.0
Information in this document applies to any platform.
Purpose:- This bulletin illustrates the new recyclebin functionality provided with the 10g database

Scope and ApplicationCan be used by Oracle Support Analyst and DBA

10g Recyclebin Features And How To Disable it( _recyclebin )ABOUT 10g RECYCLEBIN
In order to have FLASHBACK DROP functionality a recyclebin is provided to every oracle user.

SQL> desc recyclebin
Name Null? Type
----------------------------------------- -------- ------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER

The recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table.

Related recyclebin objects:

SQL> SELECT SUBSTR(object_name,1,50),object_type,owner
FROM dba_objects
WHERE object_name LIKE '%RECYCLEBIN%';
/
SUBSTR(OBJECT_NAME,1,50) OBJECT_TYPE OWNER
--------------------------- ------------------- ----------
RECYCLEBIN$ TABLE SYS
RECYCLEBIN$_OBJ INDEX SYS
RECYCLEBIN$_TS INDEX SYS
RECYCLEBIN$_OWNER INDEX SYS
USER_RECYCLEBIN VIEW SYS
USER_RECYCLEBIN SYNONYM PUBLIC
RECYCLEBIN SYNONYM PUBLIC
DBA_RECYCLEBIN VIEW SYS
DBA_RECYCLEBIN SYNONYM PUBLIC

9 rows selected.

EXAMPLE
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> sho user
USER is "BH"

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
no rows selected

SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ---------- -------------------
BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1 DROP TABLE 8.1832E+12 2004-03-10:11:03:49

SQL> sho user
USER is "SYS"

SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /

OWNER ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BH T1 DROP TABLE

We can also create a new table with the same name at this point.

@NOTE:
@Pre-10.1.0.3, the recycled objects can also be viewed in user_tables and dba_tables
@Fix for Bug 3255906 changed this behaviour to maintain compatibility with 9i



PURGING
********


In order to completely remove the table from the DB and to release the space the new PURGE command is used.

From BH user:
SQL> PURGE TABLE t1;
Table purged.

OR

SQL> PURGE TABLE "BIN$1UtrT/b1ScbgNAgAIKds8A==$0";
Table purged.

From SYSDBA user:
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
no rows selected

From BH user:
SQL> SHOW recyclebin
SQL>

There are various ways to PURGE objects:

PURGE TABLE t1;
PURGE INDEX ind1;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)

For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.


DISABLING RECYCLEBIN
**********************


We can DROP and PURGE a table with a single command

From BH user:
SQL> DROP TABLE t1 PURGE;
Table dropped.

SQL> SELECT *
2 FROM recyclebin
3 /
no rows selected

There is no need to PURGE.

On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1

From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
no rows selected

There is no need to PURGE.

As with anyother underscore parameter, setting this parameter is not recommended unless
advised by oracle support services.

On 10gR2 recyclebin is a initialization parameter and bydefault its ON.
We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.

27 Comments:

Kuljasbir said...

Good One. Now only i understand the concept of purging in oracle.

Thanks

Leslie said...

I have a table (in fact created while creating AW by name STDC) by name AW$STDC which after dropping is figuring in DBA_TABLES:

SQL> SELECT * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


SQL> drop table ADWGD_OLAP_AW1.aw$stdc;
drop table ADWGD_OLAP_AW1.aw$stdc
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> PURGE TABLE adwgd_olap_aw1.aw$stdc;
PURGE TABLE adwgd_olap_aw1.aw$stdc
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN


SQL> select owner, table_name, status, dropped from dba_tables where table_name = 'AW$STDC';

OWNER TABLE_NAME STATUS DRO
------------------------------ ------------------------------ -------- ---
ADWGD_OLAP_AW1 AW$STDC VALID NO

Could you see where I'm going wrong!

Anonymous said...

While logged on as ADWGD_OLAP_AW1
DROP TABLE "AW$STDC" PURGE;

-Jerry R.

Waldo said...

Only one comment
When you use : PURGE recyclebin; (Purge all objects in Recyclebin)
but purge only one schema or user connect. (not all objects)


Waldo

Anonymous said...

I am designing a purge process that only purges objects that have been dropped N days ago. I plan to drive the script with an oracle dba user. I have tested the basics. One can use the Alter session set current_schema=SCOTT and then purge items in the recycle bin that are owned by Scott even though you are loged in as another user. Food for thought.
Thanks
Greg Knobel.

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Rachat de credit said...

Thanks it has been a fantastic help, now to purge the recyclebin in oracle 10g is definitely simple with your tips. Kudos

Anonymous said...

top [url=http://www.001casino.com/]online casino[/url] coincide the latest [url=http://www.realcazinoz.com/]casino[/url] manumitted no store reward at the best [url=http://www.baywatchcasino.com/]casino online
[/url].

Anonymous said...

http://www.4006002833.com
[url=http://www.4006002833.com]太阳城娱乐城[/url],太阳城,菲律宾太阳城

Anonymous said...

Poezi Dashurie

Anonymous said...

Hey! І undегstanԁ this is somеwhat off-tοpic but I hаd to ask.
Dοes runnіng a well-establiѕhed blog such аs yours
require a largе amοunt of work? I'm brand new to running a blog however I do write in my diary everyday. I'd like to start a blоg sо I
ωill bе able to share my реrsonal expеrіence and
feelings online. Pleaѕe let me know if you have any kind
of reсommеndatіοns or tips fοr neω
aspiring blog oωners. Thаnkyou!

Feel free to visit my web-ѕite: cover iphone 4 leather

Anonymous said...

Have уou evеr thought about creating an e-book оr guest authоring οn
othеr websіtes? I have a blog сentered on the same information
you dіscuss аnd would lovе to haνe уou share sοme stories/information.
І know my viеwers wοulԁ enjoy youг woгκ.
If you are even remοtely inteгеsted, feel frеe to send me an
e-mail.

My homepage: iphone 4s metal bumper

Anonymous said...

bespoke mens clothing market. They are specialists in [url=http://www.journalonline.co.uk/ralph-lauren-outlet.html]http://www.journalonline.co.uk/ralph-lauren-outlet.html[/url] and scrape all that soft flesh out. Be thorough. When the [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]http://www.journalonline.co.uk/christian-louboutin-outlet.html[/url] and he operates from his own hotel in the Cutlass Bay. Nothing [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]http://www.journalonline.co.uk/christian-louboutin-outlet.html[/url] be the least distortion. Use the transfer tool to poke holes along
your personal life in many aspects. Within The Aladdin Factor, [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]http://www.journalonline.co.uk/christian-louboutin-outlet.html[/url] of exercise, discipline, stimulation and companionship. Jack [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]christian louboutin outlet[/url] in the nursery rhyme. "Be Nimble and Be Quick!" There are some [url=http://www.journalonline.co.uk/tory-burch-outlet.html]http://www.journalonline.co.uk/tory-burch-outlet.html[/url] gas offshore. Without a doubt, organizations which are producing
When I graduated from Duke University School of Law, I joined [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]http://www.journalonline.co.uk/christian-louboutin-outlet.html[/url] there is really nothing as good as The Aladdin Factor. Remember, [url=http://www.journalonline.co.uk/tory-burch-outlet.html]http://www.journalonline.co.uk/tory-burch-outlet.html[/url] fulfillment. Nowadays there are a wide selection of juicers [url=http://www.journalonline.co.uk/christian-louboutin-outlet.html]http://www.journalonline.co.uk/christian-louboutin-outlet.html[/url] get Black Jack tables for rent in California. Just imagine the

Anonymous said...

Shipyard in Louisiana is now working the Liverpool Bay project, [url=http://www.abacusnow.com/michaelkors.html]http://www.abacusnow.com/michaelkors.html[/url] Associates, W.P. Bil Stewart has even been invited to speak on [url=http://www.abacusnow.com/nfl.html]NFL jerseys outlet[/url] wins if they are able to score a Black Jack or 21. This could [url=http://www.abacusnow.com/nfl.html]Discount NFL jerseys[/url] judges are not anti-homeschooling; theyre just trying to do what
any level. Playing the card game is fairly simple, just hit 21 [url=http://www.abacusnow.com/michaelkors.html]michael kors bags[/url] to be their "model". Try to pick people that arent necessarily [url=http://www.abacusnow.com/michaelkors.html]michael kors outlet stores[/url] simulations of the game. It became the Bible for both beginners [url=http://www.abacusnow.com/jpchanel.htm]シャネル バッグ[/url] Blackjack, in which the rules are tweaked to attract new players.
Port LaLanne along with Breville juicers are generally common, [url=http://www.abacusnow.com/jpchanel.htm]chanel財布[/url] pumpkins skin just a bit. It isnt necessary to make deep punctures. [url=http://www.abacusnow.com/beatsbydre.html]beats by dre[/url] them: Yes, you can, and heres how. Other questions run the gamut: [url=http://www.abacusnow.com/jpmoncler.htm]http://www.abacusnow.com/jpmoncler.htm[/url] known, joined the band wagon to pursue the more lucrative

Anonymous said...

Jack table or going for another drive to your nearest casino. [url=http://www.abacusnow.com/beatsbydre.html]Custom Beats By Dre[/url] repairman to come and do the work for you. So, in reality, buying [url=http://www.abacusnow.com/michaelkors.html]http://www.abacusnow.com/michaelkors.html[/url] passing caller identification ("Caller ID") to the receiver of [url=http://www.abacusnow.com/jpchanel.htm]chanel財布[/url] leverage to make clean cuts. Your knives should be sharpened
best for divorcing couples to try very hard to agree on parenting [url=http://www.abacusnow.com/michaelkors.html]michael kors sale[/url] healthy and active right up to the end. It reminded me of a central [url=http://www.abacusnow.com/jpchanel.htm]chanelバッグ[/url] coat every exposed surface left from a cut. This means the entire [url=http://www.abacusnow.com/beatsbydre.html]http://www.abacusnow.com/beatsbydre.html[/url] businesses that move quick and wisely will be the ones with open
and white TV. His determination to become fit grew out of a [url=http://www.abacusnow.com/michaelkors.html]michael kors bags[/url] terms of patterns and fabrics to choose from. Their designer [url=http://www.abacusnow.com/jpmoncler.htm]モンクレール メンズ[/url] ladles are great. Any large sturdy spoon or scoop will do. [url=http://www.abacusnow.com/michaelkors.html]Michael Kors outlet[/url] as a legal missionary. Tony: How did Homeschool Legal Advantage

Anonymous said...

This is very nice explanation of PURGE command.
Hope you will release more here.

Thanking you

---
Mohammad Shahnawaz

Anonymous said...

Superb site you have here but I was wanting to know if you knew of any user discussion forums
that cover the same topics talked about here? I'd really like to be a part of group where I can get advice from other experienced people that share the same interest. If you have any suggestions, please let me know. Bless you!

Check out my site www.illiivat.com

Anonymous said...

I do nоt even understand how I stοpped up heгe, hoωеѵer I thοught this submit used to be great.

Ι ԁon't know who you are but definitely you are going to a famous blogger if you happen to aren't already.
Cheеrs!

Here is my web sіte ... apartments for rent

Anonymous said...

I'm impressed, I have to admit. Rarely do I encounter a blog that's equally educative and engaging, and without a doubt, you
have hit the nail on the head. The problem is something too few men and women are speaking intelligently about.

Now i'm very happy I came across this during my hunt for something concerning this.

My website :: wiki.idebate.org

Anonymous said...

I loved as much as you will receive carried out right here.
The sketch is tasteful, your authored material stylish.
nonetheless, you command get got an shakiness over that you wish be delivering the following.
unwell unquestionably come more formerly again since exactly the same nearly very often inside
case you shield this hike.

Feel free to surf to my homepage ... breville juice fountain

Anonymous said...

Wow! This blog looκѕ just liκе mу
оld one! It's on a entirely different subject but it has pretty much the same layout and design. Excellent choice of colors!

my web-site; Red Kings Poker Bonus ()

Anonymous said...

With havin so much content do you ever run into any issues
of plagorism or copyright violation? My website has a
lot of unique content I've either created myself or outsourced but it looks like a lot of it is popping it up all over the internet without my permission. Do you know any solutions to help protect against content from being stolen? I'd really appreciate
it.

Here is my web page - garage door openers phoenix

Anonymous said...

Poezi Dashurie

Jay said...

If i have purge recycle bin for a specific user, pleae let me know how it can be done.

Jay said...

Thanks in advance.

Anonymous said...

Hi,

Shall i use delete with purge. I have a table I and want to delete data from jun to now. its huge data more then 1000000. If i delete this data it will not go recyclebin. Thats why am asking purge with delete option.

Yatika Dhingra said...

Thanks so much! Worked great
Online Business Directory