Home » RDBMS Server » Server Administration » How to enable/On recyclebin in 10g db
How to enable/On recyclebin in 10g db [message #325838] Mon, 09 June 2008 02:43 Go to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Dear all!

I m facing trouble to enable recyclebin feature in my 10g DB, when i

drop any table,it does not placed in logical container and drops from

database permanantly .
I cant flash it back from recyclebin.I expect that my recyc.. feature

is diable in 10g db.
Can any body tell me any command to enable it in my db so tat i can

recover my droped object from rec.. to db.

I tried commands dynamicaly and regularly,as follows but,failed with

error ora 02065 "illegal alter option comand"
alter system set recyclebin on;
alter session set recyclebin on;
need help unrgantly

Thax in advance
Best regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #325843 is a reply to message #325838] Mon, 09 June 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version with 4 decimals?

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #325860 is a reply to message #325843] Mon, 09 June 2008 04:47 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Thax alot!
Mr Michal,

My DB version is 10.1.0.2.0

winxp sp2 32 bit platform.

When i execute command as follows then same response as recyclebin has nothing any dropped object.
commands are as follows

show parameter recyclebin;
show recyclebin;
show dba_recyclebin;
show user_recyclebin;
Me realy confuse whats wrong with DB

Its surely disable but dont know command to enable it.

Best regards,
Malik


Re: How to enable/On recyclebin in 10g db [message #325863 is a reply to message #325838] Mon, 09 June 2008 05:03 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL> alter system set recyclebin=ON scope=both
SQL> /

Session altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON
Re: How to enable/On recyclebin in 10g db [message #325868 is a reply to message #325860] Mon, 09 June 2008 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 10.1 recyclebin is a hidden parameter and so it is "_recyclebin".

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #325874 is a reply to message #325838] Mon, 09 June 2008 05:33 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Thax again;

Mr Michal, I need exact command though which i can enable this feature
Thax alot all
Regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #325878 is a reply to message #325838] Mon, 09 June 2008 05:50 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi ther!
I tried all but faild
any other sugession or exact command
regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #325882 is a reply to message #325878] Mon, 09 June 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried all but faild

What about posting what you tried?

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #326010 is a reply to message #325838] Mon, 09 June 2008 22:49 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

HI ALL!

Sorry for being late too answer back your quiery,Mr Michal .

I mistakenly mistype word "trid"in place of tried.
Anyway
I was saying tat I tried all commands to enable recyclebin in 10g

release 1, but failed.

As u have told me before that parameter in this release is hidden but I

cant understand your clue,
Kindly provide me proper commanad so that I can enable it for placing

logicaly droped objects in and recover that back to my DB in case of

mistakenly deletion,
Waiting your urgant response;
Best regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #326012 is a reply to message #326010] Mon, 09 June 2008 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Is this database running with archived redo logfiles enabled?
Re: How to enable/On recyclebin in 10g db [message #326013 is a reply to message #325838] Mon, 09 June 2008 23:01 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Thax,
yea my db is running with archive mode ,
regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #326016 is a reply to message #325868] Mon, 09 June 2008 23:11 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Michel Cadot wrote on Mon, 09 June 2008 05:14
In 10.1 recyclebin is a hidden parameter and so it is "_recyclebin".

Regards
Michel


Re: How to enable/On recyclebin in 10g db [message #326017 is a reply to message #325838] Mon, 09 June 2008 23:14 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Confused
Mr Michal,
can u explore your answer , I mean how to enable this feature with proper command in sqlplus

Best regards,
Malik

[Updated on: Mon, 09 June 2008 23:17]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #326020 is a reply to message #326017] Mon, 09 June 2008 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was saying tat I tried all commands to enable recyclebin in 10g

release 1, but failed.

COPY AND PASTE WHAT YOU TRIED.

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #326024 is a reply to message #325838] Mon, 09 June 2008 23:32 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

alter system set "user_recyclebin"=on scope=both;
then

alter system set "_recyclebin"=on scope=both;
then
alter system set "dba_recyclebin"=on scope=both;
alter system set recyclebin=on scope=both;


With apologise, please tell me sound,exact command to enable ,if I m on mistake,
best regards,
Malik


[Updated on: Tue, 10 June 2008 00:07]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #326047 is a reply to message #326024] Tue, 10 June 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The second one.
But why don't refer to documentation before trying all commands you can imagine containing "recyclebin"?

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #326568 is a reply to message #325838] Thu, 12 June 2008 00:31 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi Mr Michal!

I tried my best to serch 'how to enable it'but failed,
please y are you not telling me if u know how one can enable recyclebin,
If I know abt it then y I post my issue to forum,
so please if any body has any sound command please write that down exactly
WATING 4 your POSITIVE RESPONSE
Thax in ADVANCE,
BEST REGARDS,
MALIK
Re: How to enable/On recyclebin in 10g db [message #326578 is a reply to message #326568] Thu, 12 June 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please y are you not telling me if u know how one can enable recyclebin,

Quote:
WATING 4 your POSITIVE RESPONSE

please read OraFAQ Forum Guide, especially what is said about IM/SMS speak.

Metalink note 265253.1 "10g Recyclebin Features And How To Disable it( _recyclebin )"

As you didn't copy and paste what you exactly did I can't help more in telling what you did wrong.

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #326873 is a reply to message #325838] Thu, 12 June 2008 22:42 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi again!
Mr Michal,

Plzz I need exact command only from which i can enable
this feature,
I serch all documents but not found solution for my prob.
If u have any hyper link abt it,plz post that out over here,or provide me web address of that metalink. Confused Confused Sad

Thax alot,
best regards,
Malik

[Updated on: Thu, 12 June 2008 22:46]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #326877 is a reply to message #325838] Thu, 12 June 2008 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

By the way, "u" is not a member of this forum, so I wonder if you will ever get the response you desire.
Re: How to enable/On recyclebin in 10g db [message #326880 is a reply to message #325838] Thu, 12 June 2008 23:15 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi there!

sorry for mistake ,actualy i was realy confuse regarding my problem,
I will try my best not to do so in future,
Thax alot .
My system has altered with the follwing comand,

Alter system set "_recyclebin"=true scope=both;

Thax alot all of members,that you have solved my problem

you all are senior ,so kindly forgive all my mistakes

Best regards to all
specialy Mr Michal,
Malik

Re: How to enable/On recyclebin in 10g db [message #326980 is a reply to message #325838] Fri, 13 June 2008 05:11 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Dear all! Confused

I am still facing trouble to place dropped object in recyclebin feature in my 10g release1 DB, when i drop any table,it does not placed in logical container and drops from database permanantly while I have enabled this feature in my DB.
when use command as follows

show parameters recyclebin;

then database confirms that this feature is enable

but when i drop any object then it does not show in recyclebin using

select * from recyclebin;
select * from dba_recyclebin;

same response as stated
"no rows selected".

I cant recover dropped object back to my DB using

flashback table t1 to before drop rename to t2;

while when i use above flashback query response is as follows
"object not in recyclebin"

when i quiery to DB
show parameter recyclebin ;

then it shows that its value is true and its name is _recyclebin,

Please keep in mind
I have enable it using commanad as follows

alter system set "_recyclebin"=true scope=both;

which stated 'system altered'.

Can any body tell me any command to utlise it in my DB,
or from where I am on mistake



need some more help...

Thax in advance
Best regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #326999 is a reply to message #326980] Fri, 13 June 2008 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

AS YOU HAVE ALREADY BEEN ASKED SEVERAL TIMES YOU HAVE TO COPY AND PASTE YOUR SESSION.

Regards
Michel

[Updated on: Fri, 13 June 2008 06:00]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #327111 is a reply to message #325838] Fri, 13 June 2008 22:43 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi there!

I am sorry to say that I have conn /as sysdba user with all sys privs.What you more need to understand ground info.

best regards,
Malik
Re: How to enable/On recyclebin in 10g db [message #327113 is a reply to message #325838] Fri, 13 June 2008 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You seem incapable or unwilling to follow Posting Guidelines.
http://www.orafaq.com/forum/t/88153/0/

So You're On Your Own (YOYO)!
Re: How to enable/On recyclebin in 10g db [message #327116 is a reply to message #325838] Fri, 13 June 2008 23:46 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi again!

Please tell me what kind of information you need to detect real cause of my issue,Actualy I could not unserstand your clue,
Please give me any example so that I can follow your guidline to provide you concerning information.
Best regard,
Malik
Re: How to enable/On recyclebin in 10g db [message #327121 is a reply to message #327116] Sat, 14 June 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 13 June 2008 12:59

AS YOU HAVE ALREADY BEEN ASKED SEVERAL TIMES YOU HAVE TO COPY AND PASTE YOUR SESSION.


See varu123 post above for an example or read other topics in this site.

Regards
Michel

[Updated on: Sat, 14 June 2008 00:44]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #327125 is a reply to message #325838] Sat, 14 June 2008 01:26 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi there!
Sorry to disturb.
here is my connection information ,I have connected externaly in sqlplus not in sqlpus worksheet,because it is a faulty.

SQL>CONN /AS SYSDBA;
Connected
SQL>alter system set "_recyclebin"=true scope=both;
System altered.
SQL>show parameter recyclebin;


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string true
------------------------------------------------------------

SQL>CREATE TABLE t1 (ID NUMBER(24));
Table created.
SQL>DROP TABLE t1;
Table dropped.
SQL>FLASHBACK TABLE t1 TO BEFORE DROP RENAME TO t2;
EEROR ORA38305.
Object not in recyclebin.
SQL>alter session set "_recyclebin"=true scope=both;
session altered.
SQL>CREATE TABLE t1 (ID NUMBER(24));
Table created.
SQL>DROP TABLE t1;
Table dropped.
SQL>FLASHBACK TABLE t1 TO BEFORE DROP RENAME TO t2;
EEROR ORA38305.
Object not in recyclebin.


I dont know from where I am on mistake,
Best regards,
Malik

[Updated on: Sat, 14 June 2008 01:31]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #327127 is a reply to message #327125] Sat, 14 June 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now
1/ Use genuine SQL*Plus not worksheet and use copy and paste
2/ Use code tags
3/ Do the same thing with another user:
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel

[Updated on: Sat, 14 June 2008 01:50]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #327209 is a reply to message #325838] Sun, 15 June 2008 00:41 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Thax alot!


Mr Micheal for your proper step by step guidance.
and timely response.
Anyway my real Sql*plus description is as follows

SQL> conn system
Enter password:
Connected.
SQL> show parameter recyclebin;

NAME TYPE VALUE
------------------------------------ ----------- ------------
_recyclebin boolean FALSE

SQL> show parameter recycle;

NAME TYPE VALUE
------------------------------------ ----------- ------------
_recyclebin boolean FALSE
buffer_pool_recycle string
db_recycle_cache_size big integer 0
SQL>
SQL> CONN SYSTEM
Enter password:
Connected.
SQL> ALTER SYSTEM SET "_RECYCLEBIN"=TRUE SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ---------------
_recyclebin boolean TRUE

SQL> SHOW PARAMETER RECYCLE;
NAME TYPE VALUE
------------------------------------ ----------- ---------------
_recyclebin boolean TRUE
buffer_pool_recycle string
db_recycle_cache_size big integer 0


SQL> SHOW PARAMETER RECYC;


NAME TYPE VALUE
------------------------------------ ----------- ---------------
_recyclebin boolean TRUE
buffer_pool_recycle string
db_recycle_cache_size big integer 0

SQL> CREATE TABLE T1
2 (ID NUMBER(33));

Table created.

SQL> SELECT * FROM T1;

no rows selected

SQL> DESC T1
Name Null? Type
----------------------------------------- -------- ------------

ID NUMBER(33)

SQL> INSERT INTO T1 VALUES (786);

1 row created.

SQL> SELECT * FROM T1;


ID
----------
786




SQL> DROP TABLE T1;

Table dropped.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;


FLA
---
YES

SQL> SELECT * FROM RECYCLEBIN;

no rows selected


SQL> FLASHBACK TABLE T1 TO BEFORE DROP RENAME TO T2;
FLASHBACK TABLE T1 TO BEFORE DROP RENAME TO T2
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN.

SQL> SHOW USER
USER is "SYSTEM"
SQL> SHOW PARAMETER PFILE;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORAHOME_1\DATABASE\SPFILEA.
ORA
SQL>
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 a
MALIK
10.1.0.2.0 14-JUN-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL


SQL> select * from dba_recyclebin;

no rows selected

SQL> select * from recyclebin;

no rows selected


SQL> select * from user_recyclebin;

no rows selected

SQL>

I hope above sql*plus description which is made by copy paste,will help
you to detect my issue.

Best regards,

Malik Mad

[Updated on: Sun, 15 June 2008 01:02]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #327210 is a reply to message #327209] Sun, 15 June 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be better if you'd follow what I said about code tags and the example of varu123 as I pointed you.
Once again read OraFAQ Forum Guide, "How to format your post?" section there is an example of how to do so.

There are bugs on recyclebin, set it up at instance level with alter system scope=spfile and restart the database.
Then replay your test case and post it WITH CODE TAGS.

Also migrate to 10gR2 and forget 10.1.

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #332606 is a reply to message #325838] Wed, 09 July 2008 02:48 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi again!

Sorry for being late to continue my issue regarding above target.

please Mr Michel tell me exact command thrugh which I can enable recycle bin at instance level.
with complete details

Best Regards.
Malik
Re: How to enable/On recyclebin in 10g db [message #332613 is a reply to message #332606] Wed, 09 July 2008 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is in your last post.

Regards
Michel

[Updated on: Wed, 09 July 2008 03:19]

Report message to a moderator

Re: How to enable/On recyclebin in 10g db [message #332628 is a reply to message #325838] Wed, 09 July 2008 03:58 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Sorry for disturbance
Mr Michel!

you mean to say that

Alter system set "_recyclebin"=true scope=spfile;

If above coomand is the answer for my query then It does not work

If any other solution for activate recyclebin then still help requred becouse system displays that it altered but object does not go to this logical container at deletion
Best regards;
Malik
Re: How to enable/On recyclebin in 10g db [message #332629 is a reply to message #325838] Wed, 09 July 2008 04:02 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi Mr Michel!

if I have to migrate my version 10.1.0 to any other release than what is the best and how many toal releases in 10g till today.

best regards;
Malik
Re: How to enable/On recyclebin in 10g db [message #332636 is a reply to message #332629] Wed, 09 July 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Migrate to 10.2.0.4.

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #332639 is a reply to message #325838] Wed, 09 July 2008 04:48 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Hi again!
how many toal releases in 10g till today?
Best regards and very thakns.,
Malik
Re: How to enable/On recyclebin in 10g db [message #332644 is a reply to message #325838] Wed, 09 July 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10.1 and 10.2 and there will be no other.

Regards
Michel
Re: How to enable/On recyclebin in 10g db [message #332651 is a reply to message #325838] Wed, 09 July 2008 05:07 Go to previous message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Bundle of thans Mr Michal!

I am realy impressed by you regarding your knolege about oracle admin track.

I will touch you I hope in future withnew issues

Best regards;
Malik jee
Previous Topic: Updating oracle9i Db with the Updation of Oracle10g DB
Next Topic: Drop tablespace can't delete datafile
Goto Forum:
  


Current Time: Sun Dec 04 22:55:08 CST 2016

Total time taken to generate the page: 0.05171 seconds