| ORA-01653: Unable to extend table [message #307100] |
Mon, 17 March 2008 17:34  |
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.
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  |
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 #307338 is a reply to message #307100] |
Tue, 18 March 2008 09:07 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
