Home » RDBMS Server » Server Administration » BIN$ in Tablespace but not Recyclebin. How to Drop? (10g 10.2 Windows)
BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321534] Tue, 20 May 2008 11:22 Go to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

I have an object:
Segment Name: BIN$EzGCHy7qTbWygugJf6yMjw==$0
Segment Type: Table
in a tablespace I'm trying to resize.

The recycle bin is empty.

How can I remove this object from the tablespace, without recreating the tablespace?

I tried drop table "fpps"."BIN$EzGCHy7qTbWygugJf6yMjw==$0"; but it returns 'table does not exist'.

Thx.
Brian
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321535 is a reply to message #321534] Tue, 20 May 2008 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is the owner really "fpps" (lower case?)?
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321536 is a reply to message #321534] Tue, 20 May 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The recycle bin is empty.

Which one? Yours or the owner's one?
Query dba_recyclebin.

Quote:
I tried drop table "fpps"."BIN$EzGCHy7qTbWygugJf6yMjw==$0"; but it returns 'table does not exist'.

Of course, the table was already dropped. Try PURGE.

Regards
Michel
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321541 is a reply to message #321536] Tue, 20 May 2008 12:36 Go to previous messageGo to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

Thx. The schema name should have been in uppercase.

But after the following the BIN$ still exists.

SQL> drop table "FPPS"."BIN$EzGCHy7qTbWygugJf6yMjw==$0";
drop table "FPPS"."BIN$EzGCHy7qTbWygugJf6yMjw==$0"
                  *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> select * from recyclebin;

no rows selected

SQL> purge recyclebin;

Recyclebin purged.

SQL> select S.tablespace_name, s.owner, s.segment_name, s.segment_type,
  2  sum(s.bytes) size_in_bytes,
  3  round(sum(s.bytes) / 1024 / 1024, 2) size_in_m,
  4  f.file_name
  5  from sys.dba_segments s, sys.dba_data_files f
  6  where f.tablespace_name = s.tablespace_name
  7  and f.file_id = s.header_file
  8  and s.tablespace_name in ('FPPS_INSTR')
  9  and f.file_name = 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\FPPSDBA\FPPS_INSTR01'
 10  group by s.tablespace_name, s.owner, s.segment_name, s.segment_type, f.file_name
 11  Order by s.tablespace_name, s.owner, s.segment_name;

TABLESPACE_NAME                OWNER                          SEGMENT_NAME                              
------------------------------ ------------------------------ --------------------------------------
FPPS_INSTR                     FPPS                           BIN$EzGCHy7qTbWygugJf6yMjw==$0  
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321542 is a reply to message #321541] Tue, 20 May 2008 12:40 Go to previous messageGo to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Sorry Michel. Please disregard my last reply. Purge tablename worked. Thx.

Brian
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321545 is a reply to message #321542] Tue, 20 May 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bpeasey wrote on Tue, 20 May 2008 19:40
Sorry Michel. Please disregard my last reply. Purge tablename worked. Thx.

Brian

You could also query dba_recyclebin and use PURGE statement if you are a DBA.

Regards
Michel

Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321546 is a reply to message #321541] Tue, 20 May 2008 12:49 Go to previous messageGo to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi Joy,

I was logged in a system.

The command was
PURGE table "FPPS"."BIN$EzGCHy7qTbWygugJf6yMjw==$0"
/


Brian
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321547 is a reply to message #321546] Tue, 20 May 2008 12:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Oh crap, I just deleted my message. Sorry.

I just wanted to clarify what you meant by purge tablename as there is no such command. As you have already posted, the proper command is purge table {tablename}.

and what I accidentally deleted was to say that the table was in someone else's schema, so a purge recyclebin will not work. As Michel stated, you would have to usepurge dba_recyclebin.
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321552 is a reply to message #321547] Tue, 20 May 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I badly express myself, it was 2 points:
- query dba_recyclebin to find the droppped table (instead of user_recyclebin
- use PURGE (table) statement, I didn't mean "purge dba_recyclebin" as it purges all objects and not just the offending one.

Regards
Michel
Re: BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321574 is a reply to message #321552] Tue, 20 May 2008 14:22 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Whoops, I didn't want to put words in Michel's mouth. I didn't even think about what I was saying in purging the dba_recyclebin. It was really meant just to point out that you have to use the dba_recyclebin as opposed to recyclebin when objects are not in your own schema.
Previous Topic: get create table statement
Next Topic: Alter Initial Extent Size
Goto Forum:
  


Current Time: Sat Jul 20 11:39:04 CDT 2024