truncate table sys.recyclebin$; 惹的祸，各个帮出出主意！！
在清理oracle回收站自作聪明了，没有使用purge dba_recyclebin;直接使用了truncate table sys.recyclebin$; 这个命令确实很快，操作完了还得意呢？
查询select * from dba_recyclebin; 也没有记录了。
问题来了，查询：select * from user_segments;
SQL> select * from user_segments;
SEGMENT_NAME
-----------------------------------------------------------------------------
INDEX_USAGE_TABLE
BIN$8S8+dB9BMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9BMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9BMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9BMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9AMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9AMTbgQ4I7CjgxNg==$0
BIN$8S8+dB9AMTbgQ4I7CjgxNg==$0
坑爹呀有几百G空间没有释放这可怎么办呀？
各位有过类似经验吗 ？如何补救 呢 ？
FYI
conn maclean/oracle
create tablespace dropit datafile '+DATA' size 100M;
create table drop1 tablespace dropit as select * from dba_objects;
create table drop2 tablespace dropit as select * from dba_objects;
create table drop3 tablespace dropit as select * from dba_objects;
create table drop4 tablespace dropit as select * from dba_objects;
create table drop5 tablespace dropit as select * from dba_objects;
create table drop6 tablespace dropit as select * from dba_objects;
create table drop7 tablespace dropit as select * from dba_objects;
create table drop8 tablespace dropit as select * from dba_objects;
drop table drop1;
drop table drop2;
drop table drop3;
drop table drop4;
drop table drop5;
drop table drop6;
drop table drop7;
drop table drop8;
SQL> select owner,object_name,original_name,ts_name from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME TS_NAME
------------------------------ ------------------------------ -------------------------------- ------------------------------
MACLEAN BIN$8UAzzkFKE+/gQ7IBqMCDyw==$0 DROP8 DROPIT
MACLEAN BIN$8UAzzkFJE+/gQ7IBqMCDyw==$0 DROP7 DROPIT
MACLEAN BIN$8UAzzkFIE+/gQ7IBqMCDyw==$0 DROP6 DROPIT
MACLEAN BIN$8UAzzkFHE+/gQ7IBqMCDyw==$0 DROP5 DROPIT
MACLEAN BIN$8UAzzkFGE+/gQ7IBqMCDyw==$0 DROP4 DROPIT
MACLEAN BIN$8UAzzkFFE+/gQ7IBqMCDyw==$0 DROP3 DROPIT
MACLEAN BIN$8UAzzkFEE+/gQ7IBqMCDyw==$0 DROP2 DROPIT
MACLEAN BIN$8UAzzkFDE+/gQ7IBqMCDyw==$0 DROP1 DROPIT
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
99
18 rows selected.
SQL> truncate table sys.recyclebin$;
Table truncated.
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
83
SQL> select tablespace_name,segment_name,bytes/1024/1024 from dba_segments where segment_name like 'BIN$%' and tablespace_name='DROPIT';
TABLESPACE_NAME SEGMENT_NAME BYTES/1024/1024
------------------------------ --------------------------------------------------------------------------------- ---------------
DROPIT BIN$8UAzzkFEE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFFE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFGE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFHE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFIE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFJE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFKE+/gQ7IBqMCDyw==$0 2
DROPIT BIN$8UAzzkFDE+/gQ7IBqMCDyw==$0 2
8 rows selected.
create table allocate1 tablespace DROPIT as select * from dba_tables where 0=1;
SQL> create table allocate1 tablespace DROPIT as select * from dba_tables where 0=1;
Table created.
SQL> alter table allocate1 allocate extent ( size 84M);
alter table allocate1 allocate extent ( size 84M)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.ALLOCATE1 by 1024 in tablespace DROPIT
SQL> alter table allocate1 allocate extent ( size 83M);
alter table allocate1 allocate extent ( size 83M)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.ALLOCATE1 by 1024 in tablespace DROPIT
SQL> alter table allocate1 allocate extent ( size 82M);
alter table allocate1 allocate extent ( size 82M)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.ALLOCATE1 by 1024 in tablespace DROPIT
SQL> alter table allocate1 allocate extent ( size 81M);
Table altered.
QL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
1.9375
SQL> drop table allocate1;
Table dropped.
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
83
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
83
conn / as sysdba
purge recyclebin;
alter system set recyclebin=off scope=spfile;
重启实例 ==>这步必须做！！！
shutdown immediate;
startup ;
SQL> select 'drop table ' || owner || '.'||'"'||SEGMENT_NAME||'"'||' cascade constraints purge;' from dba_segments where segment_name like 'BIN$%' and tablespace_name='DROPIT';
'DROPTABLE'||OWNER||'.'||'"'||SEGMENT_NAME||'"'||'CASCADECONSTRAINTSPURGE;'
--------------------------------------------------------------------------------------------------------------------------------------------
drop table MACLEAN."BIN$8UAzzkFDE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFEE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFFE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFGE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFHE+/gQ7IBqMCDyw==$0" cascade constraints purge;
http://t.askmaclean.com/thread-3863-1-1.html
drop table MACLEAN."BIN$8UAzzkFIE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFJE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFKE+/gQ7IBqMCDyw==$0" cascade constraints purge;
指定上面生成的语句！：
SQL> drop table MACLEAN."BIN$8UAzzkFDE+/gQ7IBqMCDyw==$0" cascade constraints purge;
Table dropped.
SQL> drop table MACLEAN."BIN$8UAzzkFEE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFFE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFGE+/gQ7IBqMCDyw==$0" cascade constraints purge;
Table dropped.
SQL> drop table MACLEAN."BIN$8UAzzkFHE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFIE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFJE+/gQ7IBqMCDyw==$0" cascade constraints purge;
drop table MACLEAN."BIN$8UAzzkFKE+/gQ7IBqMCDyw==$0" cascade constraints purge;
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL> select sum(blocks)*8/1024 from dba_free_space where tablespace_name='DROPIT';
SUM(BLOCKS)*8/1024
------------------
99
搞定！！！
多谢刘大了大年初一还帮我做了个实验，从实验过程看应该能解决我这个问题，不过春节期间客户封网不能对库进行重启操作，暂时只能加空间，等年后按你这方法试试到时候再把结果放上来。
好人呀，刘大！祝您马年马上升职，马上发财！！！！ Maclean Liu(刘相兵 发表于 2014-1-31 15:57 static/image/common/back.gif
FYI
我的环境是10.2.0.5 on windows 2008 64 bit
把recyclebin disable 以后直接drop的以BIN$开头的表，并没有重启数据库，空间也是能回收回来的。
问一下重启的目的是什么 lory 发表于 2014-2-4 11:30 static/image/common/back.gif
我的环境是10.2.0.5 on windows 2008 64 bit
把recyclebin disable 以后直接drop的以BIN$开头的表，并没 ...
在11.2中recyclebin =off 需要重启实例才能生效， 在10.2 中反而不需要重启实例。
想必是ORACEL DEV发现如果不重启实例而可以修改该参数会存在某些问题 Maclean Liu(刘相兵 发表于 2014-2-4 19:43 static/image/common/back.gif
在11.2中recyclebin =off 需要重启实例才能生效， 在10.2 中反而不需要重启实例。
想必是ORACEL DEV发现 ...
已经按照你的方法，成功收回空间了
页:
[1]