The error message is :
ORA-01658: unable to create INITIAL extent for segment in tablespace.
Problem solved immediately after I purged recyclebin from my 2nd feeling.
Finally , I found :
in 10g , if you have database running with recyclebin=on for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF. Hence, the space shown in dba_free_space is not swapped out, cuased the error.
Below is my test.
SYS@ODSPRX2> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
2 extent management local uniform size 512k
3 segment space management auto;
Tablespace created.
@> conn / as sysdba
Connected.
SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;
User created.
SYS@ODSPRX2> grant dba to liqy;
Grant succeeded.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number) ;
Table created.
LIQY@ODSPRX2> create table t2 (f1 number) storage(initial 512k);
Table created.
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
524288
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
BYTES
----------
3670016
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
7
LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);
Table altered.
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
no rows selected
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
4194304
LIQY@ODSPRX2> drop table t1;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> drop table t3;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;
System altered.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> purge recyclebin;
Recyclebin purged.
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
SYS@ODSPRX2> spool off
But in 11.2.0.2, the dropped objects will be swapped out even with recyclebin=off.
BTW, in 11gr2 we can't use 'alter system' to change value of recyclebin, while in 10g we can change it on the fly. The oracle document is not correct.
