Skip to Main Content
Forums

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to enable recycle bin & assign Default Tablespace in Ora 10g XE?

senthilmcaNov 30 2007 — edited Nov 30 2007
I am using Oracle 10g Express Edition..
i drop the objects from the database but it does not stored in the recycle bin. how can i resolve this problem.

How to enable recycle bin and PURGE options?

I had followd PaulM reply (1619135

sql>select DEFAULT_TABLESPACE from dba_users where username = 'test';

It shows no data found.
How to assign a Default TableSpace to a user?
While creating a user it takes some default tablespace know?
Why it shows 'No data Found'?

Thanks

Comments

Paul M.

I had followd PaulM reply

Well, glad that someone reads my posts...:-)

sql>select DEFAULT_TABLESPACE from dba_users where
username = 'test';
It shows no data found.

Try 'test' in uppercase.
See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#sthref723

While creating a user it takes some default
tablespace know?

With XE default tablespace is SYSTEM.

How to assign a Default TableSpace to a user?

As dba or sysfba

SQL> alter user <username> default tablespace <new tablespace>;
senthilmca
Thanks

I am having DBA privleges..

sql>ALTER USER TEST DEFAULT_TABLESPACE SYSTEM;

Is the above query is correct?
It gives ORA-00922: missing or invalid option.
Paul M.
DEFAULT_TABLESPACE
It should be "DEFAULT TABLESPACE ".

But, please do not assign SYSTEM as default tablespace for "normal" users.
senthilmca
Thanks Paul..

Now recycle bin carries dropped objects.

How to retrieve that dropped objects?
I am using Ora10g XE.

I think using FlashBack?

If yes means what are the parameters i have to change in init.ora?.
It is available in C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts.

It is having below parameters..

undo_management=AUTO
undo_tablespace=UNDO
DB_RECOVERY_FILE_DEST_SIZE = 10G
DB_RECOVERY_FILE_DEST = C:\oraclexe\app\oracle\flash_recovery_area

Apart from those params if i add anyother in .ora file?

Thanks
Paul M.
How to retrieve that dropped objects?
SQL> show recyclebin

or you can query USER/DBA_RECYCLEBIN views.

what are the parameters i have to change in init.ora?.
You shouldn't need to change anything.
senthilmca
Thanks Paul

I am able to see the dropped object from Recycle bin.
But i am not able to restore that object using FlashBack.

When i am issuning the below command

sql>flashback table <tblname> before drop;

It shows ORA-00911: invalid character.
I think FlashBack is not enabled in my DB..

Is it correct?
How to remove this error?
Thanks
Paul M.
sql>flashback table <tblname> before drop;
Should be

sql>flashback table <tblname> to before drop;

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9012.htm#i2172752
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 28 2007
Added on Nov 30 2007
7 comments
2,220 views