Oracle hanging

[alanv25]

Hi,

One of our clients is having a problem with Oracle hanging. He is on
XP Professional with Oracle 9i. There are actually 2 places where
it's hanging. One is when he is running SQL scripts. The file with
the scripts gets partially complete when it stops executing just
before a DROP INDEX command.

The other case is when he tried to create a new database using the
Configuration Assistant. After he clicks Create Database, it begins
creating the database but stops when it's 88% completed and just
hangs.

In both cases, we've checked the alert.log and various trace files
and can find no error messages.

Has anyone here seen this before? Can anyone here give some insight to this situation?

Thanks,
Alan

[alanv25]

I found an answer on oracle.com where someone stated that, on any sql command, the database will hang if the redo log files are full. There will be no error message. So my question to you now is:
what command can I use to query how full the redo logs are?

[davey23uk]

no it doesnt mean that, if it means if you are in archive log mode and the database cannot archive the current redo log file it will wait until the file has been archived.

So are you in archive log mode, if so what is the value of log_archive_start, what is in the alert log and do you have a dba around who can help you?

[alanv25]

here is the quote from the Oracle forum:

"If the database is hanging on any sql command, check the volume where the logs and archives are kept. If they run out of room, the database will hang, waiting for space to become available."


We are not running in archivelogmode.

[marist89]

keyword being archive in that statement.

If you're not in archivelog mode, this isn't relavent to your situation.

Trace the process, see what you are waiting on.

[alanv25]

I passed on the info to the collegue who is working on this case. I was looking at the directions for the trace on the web page that you sent me and I see where the user is to query the xyz_master and xyz_detail tables, but I did not see where these were created. Are these created by a user or are they created by Oracle?