Tag: Transaction

Global temporary tables

-- Global temporary table which keeps records till session create global temporary table temp1( year number ) on commit preserve rows; SQL> insert into temp1 values ( 11 ); 1 row created. SQL> select * from temp1; YEAR ---------- 11 -- reconnect to session and check records SQL> connect /as sysdba Connected. SQL> select * … Continue reading Global temporary tables →

Using AUTONOMOUS_TRANSACTION and setting TRANSACTION ISOLATION

Scenario 1) Default transaction using AUTONOMOUS TRANSACTION and checking records in table called APP_LOG at each step. The store procedure write_log inserts record in the app_log table. Session (a) In this session execute AUTONOMOUS transaction, get row count as table is empty will be 0 at first after exeuction of the procedure both session will … Continue reading Using AUTONOMOUS_TRANSACTION and setting TRANSACTION ISOLATION →

Example of AUTONOMOUS_TRANSACTION

Example of how AUTONOMOUS_TRANSACTION, it allows one to isolate a DML statements, the DML statements with the AUTONOMOUS transaction are independent of the main transaction. AUTONOMOUS_TRANSACTION can be used in PL/SQL code in procedures, functions, triggers. -- store procedure that writes message to a log table and defined AUTONOMOUS_TRANSACTION SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg … Continue reading Example of AUTONOMOUS_TRANSACTION →

Managing SQL Server 2000 Transaction Log Growth

Article to manage SQL Server 2000 transaction log growth http://www.mssqltips.com/tip.asp?tip=950

How to shrink transaction log file for a database?

The following article gives some background on how to stop the transaction log from growing Using the steps below one can shrink the transaction log. Use TestDB SELECT file_id, name FROM sys.database_files GO file_id name 1 TestDB 2 TestDB_log DBCC SHRINKFILE ('TestDB_log', 2) GO BACKUP LOG TestDB WITH TRUNCATE_ONLY GO DBCC SHRINKFILE ('TestDB_log', 2) GO

What are the different options to shutdown?

shutdown (or shutdown normal) - In this method the database shuts down cleanly and oracle will wait for all users process to terminate before shutting down the database, so if there is user with SQL*Plus session that is idle oracle will not terminate till the user session exits it. If user sessions haven't been terminated … Continue reading What are the different options to shutdown? →

Transaction isolation level (Serial and Read-committed)

The following 2 links explains transaction isolation. http://www.acs.ilstu.edu/docs/Oracle/server.101/b10743/consist.htm http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html