Insert hangs???

What do you mean hung? Can you log in as sysdba?

Did you issue "archive log list" as adrianomp suggested?

Jodie,

Thanks for your reply. I have 7 table names. But the table where I am doing insert is not in the list.
What should I do

Quote:

---

What should I do If my database hangs? Do I have to re-start the database?

---

If your database hung due to an archival pending, you can archive manually and database gets back to normal. Again, that's not your case since other operations are running and your alert log shows sucessful archives.

I think we're losing focus here. Based on what you said:

Quote:

---

We are using a tool called Marketic one to process letters.
In the beginning it inserts in a table. The insertion seems to hangs. It is been 2 hours it is doing nothing.

---

Doing NOTHING in 2 hours smells "lock". If it is doing anything, but doing slowly, you may check various types of waits and performance issues. Also, it's possible that the application is waiting for some input or in a infinite loop state.

Here is the reply when i do the archive log list command


Database log mode Archive Mode
Automatic archival Enabled
Archive destination O:\MOPROD
Oldest online log sequence 4160
Next log sequence to archive 4163
Current log sequence 4163

So the automatic archival is enabled

Adriano - the waits indicate that it is waiting on DB File Scattered Read. If it was a lock, wouldn't it be waiting on an Enqueue? Please correct me if I'm wrong.

If the table you are inserting into isn't in the list, my hunch is something else is going on. Do you have a trigger on the table you are inserting into? If so, you could be modifying other objects.

If you want to know what the SQL is that the query is executing, you can use:

SELECT osuser, address, piece, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
AND sid=&sid
ORDER BY osuser, address, piece;

You may want to execute several times, to see if the SQL is changing or it's stuck on one statement.


Jodie

There is no trigger in this table.
I am watching from toad the query is the same since 18h30

In toad i see the RBS usage,
The consistent gets, physical IO, and undoblocks used are changing every second.
This means it is inserting in the table is it not!!!

Naeem,
Well, something is using the rollback, probably your insert.

If your session is still waiting on DBFile Scattered Read, I'd guess it's an IO problem. Is there contention? There could be many causes.

Maybe someone else has an idea???

In the mean time, you could set Event tracing on to get a trace file. It may give you more information.

Here is some code (copied from Oracle Performance Tuning 101):

Code:

---

select s.username, p.spid
  from v$session s v$process p
 where s.paddr = p.addr
  and s.sid=&sid

alter system set timed_statistics=true;

alter system set max_dump_file_size=unlimited;

oradebug setospid=SPID_from_above

oradebug unlimit

oradebug event 10046 trace name context forever, level 8

---

Trace for some time, then look for the trace file (using the spid) in the user_dump_dest). Scan the file for all lines that begin with WAIT

HTH...
Jodie

Thanks Jodie,

It is IO problem. The DBFile is 148 GB. The data are pletely dispersed. I am recreating the table in a different tablespace in a differnt partition(with RAID 1).
Hope this will workout.

Thanks all of you, It was of great help.

Quote:

---

Originally posted by Naeem
The DBFile is 148 GB.

---

Single file and 148GB..r u sure?

Does WIN2000 Server suport such big file?

Abhay.