Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01653: Unable to extend table (ORACLE 9i)
ORA-01653: Unable to extend table [message #307100] Mon, 17 March 2008 17:34 Go to next message
manningda
Messages: 31
Registered: January 2008
Member
All:

I did make a search on this error before posting, however I fear it may have been brief and/or a bit rushed.

If I have overlooked an existing thread on my question, I will gladly accept the redirection there if necessary.

That said.

Stored procedure attempts to execute an insert and produces the following:

ORA-01653: unable to extend table [%%%%] by 8192 in tablespace [%%%%] 



Have reviewed information on the error and understand that the db is unable to extend CONTIGUOUS space sufficient to accommodate the volume of data for the insert [8192].

Began reviewing the tablespace settings so to evaluate the following options:
1. An update the [NEXT_EXTENT] and/or [PCT/INCREASE] fields
2. An additional data file to accommodate the new data
3. A "de-fragment" on the tablespace

However in reviewing I discovered that both the fields [NEXT_EXTENT] and [PCT/INCREASE] are null.
Shocked
This seems very odd. Would that not prevent ANY inserts from succeeding EVER??

This is new territory for my experience, so trying to be diligent and through in understanding my next action and the reason for it before proceeding.

Does anyone have experience with these fields set to NULL and should I fear simply updating the existing NULL values with something to fix the error?

As always, thank-you in advance for all input!

Happy St.Pat's Day!!
Re: ORA-01653: Unable to extend table [message #307101 is a reply to message #307100] Mon, 17 March 2008 17:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

ORA-01653: unable to extend table string.string by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.



http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/e1500.htm#sthref777

Trust me oracle documentation is good as well as google is.

Quote:

Began reviewing the tablespace settings so to evaluate the following options:
1. An update the [NEXT_EXTENT] and/or [PCT/INCREASE] fields
2. An additional data file to accommodate the new data
3. A "de-fragment" on the tablespace



http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#i8531

Above link should help you to clarify your doubts about extents.

Quote:
Does anyone have experience with these fields set to NULL and should I fear simply updating the existing NULL values with something to fix the error?


I won't think about what you said above atleast at my work place.

HTH

Regards

Raj

[Updated on: Mon, 17 March 2008 17:53]

Report message to a moderator

Re: ORA-01653: Unable to extend table [message #307172 is a reply to message #307100] Tue, 18 March 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However in reviewing I discovered that both the fields [NEXT_EXTENT] and [PCT/INCREASE] are null.

These parameters are meaningless in LMT (Locally Managed Tablespace).
In addition, when they are meaningful, these parameters give DEFAULT values when objects are created and not ACTUAL values on these objects.

Regards
Michel
Re: ORA-01653: Unable to extend table [message #307338 is a reply to message #307100] Tue, 18 March 2008 09:07 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Mike / SRajaram:

Much thanks for the input!!


SRajaram:

Couldn't completely comprehend your statement:

Quote:

I won't think about what you said above atleast at my work place.



I made some effort to leave a post that outlined the prob as well as my effort to date to research and understand the issue.

Was there something in the post that is glaringly wrong?

Thanks!
Re: ORA-01653: Unable to extend table [message #307340 is a reply to message #307338] Tue, 18 March 2008 09:12 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Does anyone have experience with these fields set to NULL and should I fear simply updating the existing NULL values with something to fix the error?

The way I understood your statement is whether anyone having any experience in updating the data dictionary views. These views are maintained internally by oracle and not to be updated by using DML statements , instead you should be using appropriate procedures defined to change the value. Hence i made a comment saying
Quote:
I won't think about what you said above atleast at my work place.

If my understanding is not correct just ignore the comment I made.

Regards

Raj

Previous Topic: function or proedure / datamodel needs to create
Next Topic: Flush\ purge values from Array
Goto Forum:
  


Current Time: Sat Dec 03 22:29:19 CST 2016

Total time taken to generate the page: 0.05112 seconds