2.10.2010

Why alter system kill session IMMEDIATE is good

I am pretty sure that many of us come across of situations when a killed session by 'alter system kill session' command did put the session in 'KILLED' status and never released the session  for a long time on the database. It could be due to the fact that the session would be rolling back the ongoing transaction.
Whenever we are in such situation, we generally try to find out the OS pid (on UNIX OS) associated with the killed session (which is a bit difficult task, as the killed session paddr in v$session changes while the addr corresponding value in v$process does not), and kill the associated OS process with 'kill -9' command on the OS level.
I have found the IMMEDIATE option with the 'alter system kill session' is more useful as it writes the following information in the alert.log file after killing the session and also try to finish the things at the earliest possible to close the session from the database:

Wed Feb 10 11:02:39 2010 
Immediate Kill Session#: 515, Serial#: 36366
Immediate Kill Session: sess: c0000001be20d9f0  OS pid: 14686

As you see, it writes the time stamp when the session was killed, and also gives the associated OS pid of the killed session in the alert.log. As per Oracle documentation, 'Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.'

Syntax:

alter system kill session 'sid,serial#' IMMEDIATE;

Regards,

Jaffar

2.02.2010

What happens when the dump file and log file names are the same during expdp?

Though it was a silly mistake, I felt discussing the funny part here.

Few days ago, I was doing an export (datapump) and come across the following error:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/tmp_backup/dpump/text.dmp"
ORA-27038: created file already exists
Additional information: 1

For the first few minutes, I was wondering what went wrong and started checking all the arguments that were passed with expdp command as the error message suggested  'invalid argument value'. After confirming everything is fine with the command and then started looking at the subsequent error messages.  My second thought was, the file could be already exits. I deleted the file and run the expdp command which result in same error.
Any guesses what could be wrong here? Well, no prize for guessing. It was a silly mistake that I had committed. I had given the similar file name to the dumpfile and logfile arguments in the expdp command.

Sometime we may overlook the basic problems.

Happy reading,

Jaffar