?
Solved

How do I know if there are uncommited transactions?

Posted on 2003-02-19
3
Medium Priority
?
2,401 Views
Last Modified: 2010-06-30
Hi experts,

I'm quiet new to administering Oracle databases. I have some guys on my db running big updates on some tables. From time to time I need to shutdown the system for maintenance, but before I can do that I need to know if there are some uncommitted transactions on the db. Otherwise I would rollback all the possibly uncommited updates, which is not, what my colleagues want. And no, they don't want to use autocommit...

So how can I find out, if there are uncommited transactions?

Oracle version is 8.1.7.0.0 Enterprise Ed. on Solaris 8.

Thanks for your help.
Andreas


0
Comment
Question by:joppi1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Expert Comment

by:hayrabedian
ID: 7980776
Hi Andreas,

I think you can use one of the SHUTDOWN command's options. Shortly, they are: NORMAL, ABORT, IMMEDIATE, TRANSACTIONAL

Oracle documentation says:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Shutting Down with the NORMAL Option
Normal database shutdown proceeds with the following conditions:

No new connections are allowed after the statement is issued.

Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL option:

 SHUTDOWN NORMAL;

Shutting Down with the IMMEDIATE Option
Use immediate database shutdown only in the following situations:

A power shutdown is going to occur soon.

The database or one of its applications is functioning irregularly.

Immediate database shutdown proceeds with the following conditions:

Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)

Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE option

SHUTDOWN IMMEDIATE;



--------------------------------------------------------------------------------
Note:
The SHUTDOWN IMMEDIATE statement disconnects all existing idle connections and shuts down the database. If, however, you have submitted processes (for example, inserts, selects or updates) that are awaiting results, the SHUTDOWN TRANSACTIONAL statement allows the process to complete before disconnecting.  

--------------------------------------------------------------------------------
 
 


Shutting Down with the TRANSACTIONAL Option
When you wish to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL option:

SHUTDOWN TRANSACTIONAL;


After submitting this statement, no client can start a new transaction on this instance. If clients attempt to start a new transaction, they are disconnected. After all transactions have completed, any client still connected to the instance is disconnected. At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted. The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

Shutting Down with the ABORT Option
You can shut down a database instantaneously by aborting the database's instance. If possible, perform this type of shutdown only in the following situations:

The database or one of its applications is functioning irregularly and neither of the other types of shutdown works.

You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).

You experience problems when starting a database instance.

Aborting an instance shuts down a database and yields the following results:

Current client SQL statements being processed by Oracle are immediately terminated.

Uncommitted transactions are not rolled back.

Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

If both the normal and immediate shutdown options do not work, abort the current database instance immediately by issuing the SHUTDOWN command with the ABORT option:

SHUTDOWN ABORT;



0
 
LVL 4

Accepted Solution

by:
asimkovsky earned 200 total points
ID: 7981103
There is an easier way to do it without shutting down the database.  Try this script:


set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
column osuser format a10
column process format a10
column machine format a15
column terminal format a10
column program format a25
column module format a10
column logon format a20

prompt ####################################################
prompt # current transactions:
prompt ####################################################
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;






Andrew
0
 

Author Comment

by:joppi1
ID: 7981321
That's exactly the answer I need.

Since my fellows sometimes forget to commit and leave sessions alone for a few days (sic!), this gives me the chance to phone up the guy with pending transactions and let him commit his work before I shut down.

Thank you, Andrew!

Bye,
Andreas
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question