ORA-01683 1

[jestrada101]

I'm getting ORA-01683, would resizing the tablespace rectify this? or do I actually have to add a datafile?

Thanks!
JE

 

[carp]

Either add a datafile, or drop unnecessary indexes in the same tablespace, or set AUTOEXTEND on.

Your problem is that there is not enough contiguous free space in the tablespace to create the extent. Any of the above actions will increase the free space and help you get your next extent.

 

[jestrada101]

Wouldn't adding additional space to the datafile like "alter database datafile '/data/mydata.dbf' resize 150M;" provide more free space?

Thanks!
Jose E

 

[SantaMufasa]

J,

Yes, but Carp's suggestion is my choice since AUTOEXTEND does "just-in-time" space allocation. If you "...resize 150M...", it may be months/years/never before you ever you that space. I believe Carp and I would both recommend "AUTOEXTEND" with a reasonable "NEXT nnM" setting.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:39 (16Jun04) UTC (aka "GMT" and "Zulu"), 16:39 (16Jun04) Mountain Time)

 

[jestrada101]

Thanks!

 

[carp]

Jose -
Yes, increasing the size of the file will add more free space, as will rebuilding all of your indexes (well, that PROBABLY will).

But as SantaMufasa says - AUTOEXTEND takes care of this problem on an as-needed basis. Which lowers your exposure to phone calls at two in the morning! The other side of this coin is that you still need to keep an eye on your tablespaces to make sure the sizes aren't getting away from you!

 

[appi]

But you have always take care that the filesystem is not getting full on AUTOEXTEND

 

[carp]

Appi -
I'm not sure I understand your point. Yes - the tablespace will continue to expand to accommodate your needs, but only up to a point. Unless you have an infinitely large file system, your files can only grow so big. I have been hit by data storms that left me with huge files which, after the storm passed, were virtually empty. If you have enough of these that span multiple tablespaces, you can deplete your entire system! Which is why I say - you STILL have to exercise due diligence and monitor space usage. But with AUTOEXTEND, you get to do it at 0900 instead of 0200!