| insert hanging on 40GB table in Oracle / NT [message #56266] |
Sat, 15 March 2003 00:41  |
sunny jay
Messages: 2
Registered: March 2003
|
Junior Member |
|
|
I have a 80GB oracle 8.1.7 database running on NT.
It has a table which is 40GB in size. We have been making regular insert every month of 400 to 2000 rows in the table. Every thing was going fine except for last week the insert hang and then error out after 8 hours saying unable to get memory segment in Shared_pool. When I did a little test, I found that
I can make insert upto 22 rows, but its the 23rd row
which get this 'handing' situation until it errors out with unable to get memory in shared pool. No matter how I try this, I am not able to get anything in the table past 22 rows. I am able to create another table in the same tablespace and insert more then 22 rows.
When the insert was hanging, the v$session_wait showed
wait event to be 'sqlnet more data from client'.
The sql statement executing in the sqlarea shows a statement like...'select from sys.fet$......'. This select from sys.fet$ stays there till the shared pool runs out of memory. The dba_free_space for the tablespace where this table resides does have a large
chunk available in case the table needs another extent.
THE MOST CONFUSING PART IS THAT I AM ABLE TO INSERT UPTO 22 ROWS, AND ITS THE 23rd ROW THAT CAUSES THIS WHOLE HANGING SENARIO. it appears as if there is some form of limit on how large a table or how many rows a table can hold on NT platform. I have exhusted all my thinking power and would appreciate any help or suggestion. Thanks in advance.
|
|
|
|
|
|
| Re: insert hanging on 40GB table in Oracle / NT [message #56270 is a reply to message #56266] |
Sat, 15 March 2003 17:29  |
sunny jay
Messages: 2
Registered: March 2003
|
Junior Member |
|
|
Hi,
The tablespace is dictionary managed and contains about
80 other segments (both table and indexes). The tablespace does have this problem that it is very fragmanted, Select from dba_free_space shows 600,000
fragment pieces. However there are few large fragment
enough to allocate extent to the 40GB table if needed.
There are four datafiles in this tablespace. 3 of these are 20GB each and the last one added is 4GB. The table has extents from all the 4 datafiles.
Select from dba_extents shows that the last extent that the table grabed was the first one from this new
4GB datafile. One more thing...the next extent size is set to 1.6GB. And there is a chunk of 2GB available in the dba_free_space for this tablespace.
No matter what I do but am not able to find out why the table wont accept the 23rd row. I am successfuly able to create other table in the tablespace and insert
more then 23 rows. Let me know if you need any other info...Thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
