| DML fails due to ORA-01658 error [message #486618] |
Fri, 17 December 2010 07:15  |
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
|
Member |
|
|
One of my table refers to a tablespace, having 3 datafiles.
These 3 datafile refers 2 different unix mount point.
Space on one of the mount point(/u0201) is 100% full,
But in the another mount point (/u0101) having enough free space. corresponding datafile is set with Autoextension.
Unix data $ df -h
/u0101 - 6.1T 5.3T 826G 87% /u0101
/u0201 - 2.9T 2.9T 43M 100% /u0201
Oracle Data
SQL> select * from dba_data_files where tablespace_name='ID_DW_STR_DATA';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES STATUS AUTOEXTENSIBLE MAXBYTES
/u0101// DATA_001.dbf 41 ID_DW_STR_DATA 33554432000 AVAILABLE YES 33554432000
/u0201 //DATA_003.dbf 374 ID_DW_STR_DATA 11523850240 AVAILABLE NO 0
SQL> Alter tablespace ID_DW_STR_DATA add datafile '/u0101/DATA_004.dbf' size 1000M AUTOEXTEND ON ;
/u0101// DATA_004.dbf 505 ID_DW_STR_DATA 1073741824 AVAILABLE YES 16106127360
Error
ORA-01658: unable to create INITIAL extent for segment in tablespace ID_DW_STR_0010_DATAunable to create INITIAL extent for segment in tablespace ID_DW_STR_DATA
Question
If one mount point is full, then Why Oracle automatically re directing data into another(i.e. old /u0101) mount point?
After adding one more new datafile in /u0101 mount point only I could insert data in that table
Regards
sbmk
[Updated on: Fri, 17 December 2010 08:27] by Moderator Report message to a moderator |
|
|
|
| Re: DML fails due to ORA-01658 error [message #486650 is a reply to message #486618] |
Fri, 17 December 2010 13:38  |
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
|
Senior Member |
|
|
|
It didn't re-direct the data - it attempted to allocate an extent in datafile that was marked as 'autoextensible' and since it was at maxsize, it failed. No mystery here that I can see . . . . .
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
