Very interesting!
I was out sick yesterday so I'm just getting caught up on the email today.
There are currently 898334 rows and 2654300 blocks in the table (the number
of rows will grow over the next 2 months before the next big delete which is
done quarterly). But this does seem to imply that
How is the insert being used?
Is it one row per insert?
Is the column values hardcoded or passed in variables?
What is the maximum row length?
-Original Message-
Sent: Friday, December 06, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L
Very interesting!
I was out sick
This is what Oracle came up with just now:
Hi Jay,
Based on my research:
1/From note: 1029850.6 Freelist and Freelist Groups:
It is advised that the space calculated as (100% - (PCTFREE + PCTUSED)) be
greater than the maximum size of a table row.
Based on the above formula, the leftover space
]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree
One row/insert. One commit every 1-6 inserts (rows).
Column values are passed to pl/sql procedure which does the insert (i.e,.
passed in variables).
Maximum row length: I assume you mean the largest row in the table? Does
anyone have an easy way to get this? Other than applying formulas to
Okay, now that I've read this over it makes sense (and thanks again to Kirti
who supplied the same note albeit without the quote from the unpublished
section on bug 450349. Apparently Oracle will check a maximum of 5 blocks
on freelist for sufficient free space for an insert before grabbing a new
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
I remember reading that when a free block fails the space requirements for
an insert it gets flagged 'unlink' and either it gets unlinked or it may
require to fail five times before it gets unlinked.
Waleed
-Original Message-
Sent: Friday, December 06, 2002 2:34 PM
To: Multiple
How badly do you want the space back? I believe you will indeed need to touch each row.
You could update each row with something like (update set column-1=column-1)
Good luck!
Barb
"Miller, Jay" [EMAIL PROTECTED] wrote:
But will this solve my problem in the near term? My understanding is
of a new system uncovers a new class of bugs. -- Kernighan
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable
: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event
:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002 2:39
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
ORA-1653: unable to extend table - Why?Or maybe Miller. It
is, after all, the name of a well-known alcoholic beverage. Good thing my first
name is not spelled Moans. I would probably
Vitals:
Average Row Length = 1895
Block Size = 4096
pct_free = 10%
Threshold to put block off freelist = 3686
pct_used = 75%
Threshold to put block on freelist = 3072
Average free space = 3895
Working with averages, there could be at most 2 rows per block. The Average
free space is also very
Dan,
I think you're right: There's exactly one row in each block and the rest
of the space is wasted. No wonder the table is growing. So make it
possible for Oracle to put more than one row into a block (or rather: to
pack data more tightly into the blocks) either by changing the storage
Dan, I think you nailed it!
It will be interesting to see the # of blocks and # of rows in this table.
- Kirti
-Original Message-
Sent: Thursday, December 05, 2002 9:44 AM
To: Multiple recipients of list ORACLE-L
Vitals:
Average Row Length = 1895
Block Size = 4096
pct_free = 10%
I think you may have meant _walk_insert_threshold
which by default is set to 0 (not set).
Waleed
Dan, I think you nailed it!
It will be interesting to see the # of blocks and # of rows in this table.
- Kirti
-Original Message-
Sent: Thursday, December 05, 2002 9:44 AM
To:
I came across the param in my Internals handbook (I'd never heard of it
before). It could be different for the release/platform. The concept is
probably the same. (?)
-Original Message-
Sent: Thursday, December 05, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
I think you may
. -- Kernighan
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why
class of bugs. -- Kernighan
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why
] Subject: RE: ORA-1653:
unable to extend table - Why?
12/03/2002 08:18
AM
Please respond to
ORACLE-L
Ron,
Good idea, but DEGREE=1
There are two indexes, but they are in a different tablespace
] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event
PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond
]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event there was no free space until I
:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant
by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
To:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond
:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
system uncovers a new class of bugs. -- Kernighan
JayMiller@TDWater
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable
] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event
:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event there was no free space until I
Title: RE: ORA-1653: unable to extend table - Why?
It just depends on what your definition of it is...
-Original Message-
From: Miller, Jay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 03, 2002 12:50 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1653
house.comTo:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
I was told by the department manager that they were neither using a direct
load nor the Append hint.
But the developer is back from vacation today so I'll get a more definite
answer from him.
Thanks,
Jay Miller
-Original Message-
Sent: Friday, November 29, 2002 2:05 PM
To: Multiple
Okay, I just heard back from the developer. It was definitely not using
either a Direct load or and Append hint. Just a regular insert.
Any more ideas?
-Original Message-
Sent: Friday, November 29, 2002 1:39 PM
To: Multiple recipients of list ORACLE-L
Did you insert using direct path
Yep, I agree that coalescing is irrelevant in my current situation. In any
event there was no free space until I added the additional datafile but
there was the 8gig of space on the freelists.
Jay
-Original Message-
Sent: Friday, November 29, 2002 8:54 PM
To: Multiple recipients of list
]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653: unable to
extend table - Why
My experience yesterday was that dropping an index and trying to rebuild the same
index failed ( even after coalescing the tablespace) since we need to wait for SMON to
clean up the extents to make them available. I don't know how we make SMON process to
coalesce the free space faster enough(
Check oradebug to wake up smon
--- [EMAIL PROTECTED] a écrit : My
experience yesterday was that dropping an index
and trying to rebuild the same index failed ( even
after coalescing the tablespace) since we need to
wait for SMON to clean up the extents to make them
available. I don't know
Okay, I can't figure this one out. Earlier this week I got an ORA-1653:
unable to extend table on a really big table. However this was just after I
had deleted over 2 million rows in the table and we were only inserting
30,000.
After reanalyzing the table I saw the following stats in
Did you insert using direct path ?
If so the insert inserts after the highwater mark.
The highwater mark is not reinitialized after deletes.
So maybe that's why the insert failed.
--- Miller, Jay [EMAIL PROTECTED] a
écrit : Okay, I can't figure this one out. Earlier
this
week I got an
did u coalesced the tablespaces?
-Original Message-
Sent: sexta-feira, 29 de Novembro de 2002 17:59
To: Multiple recipients of list ORACLE-L
Okay, I can't figure this one out. Earlier this week I got an ORA-1653:
unable to extend table on a really big table. However this was just
How are the inserts being done? Are you doing an insert with append hint?
-Original Message-
Sent: Friday, November 29, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L
Okay, I can't figure this one out. Earlier this week I got an ORA-1653:
unable to extend table on a really big
Coalescing might help if there are many smaller free extents
that can be coalesced. But that still doesn't solve Jay's problem.
Because he doesn't want the table to extent at all since he just deleted
2 million rows so there are plenty of space within the segment itself.
Those free blocks should
how would coalescing help even if there were a lot of smaller free
extents? Oracle would do the coalesce automatically, there would be no
difference between manually coalescing or allowing Oracle to do it when
a new extent was needed.
--- Richard Ji [EMAIL PROTECTED] wrote:
Coalescing might
Rachel,
What I mean to say is when there are a lot of contiguous smaller free
extents.
Then coalesce will produce a larger free extent so Jay wouldn't have to
add a datafile for his table to grow.
On the automatically coalescing part, I believe SMON will only coalesce
when pctincrease != 0, or
Richard,
if pctincrease is zero, and there are a large number of contiguous
smaller extents, SMON will not automatically coalesce the tablespace.
However, whether or not SMON does an automatic coalesce, if you need an
extent that is larger than any of the small ones, Oracle will coalesce
those
51 matches
Mail list logo
