BIN$ in Tablespace but not Recyclebin. How to Drop? [message #321534] |
Tue, 20 May 2008 11:22 |
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 #321541 is a reply to message #321536] |
Tue, 20 May 2008 12:36 |
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 #321547 is a reply to message #321546] |
Tue, 20 May 2008 12:53 |
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 #321574 is a reply to message #321552] |
Tue, 20 May 2008 14:22 |
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.
|
|
|