Oracle DBA Daily Experiences

In SQLServer TRUNCATE TABLE can be rolled back when within a transaction. TRUNCATE still doesn’t log each row when it’s deleting each row and it doesn’t call DELETE trigger during truncate.

Example:
BEGIN TRANSACTION
SELECT count(*) FROM test
TRUNCATE TABLE test
SELECT count(*) FROM test
ROLLBACK TRAN
SELECT count(*) FROM test

Output:
———–
2

(1 row(s) affected)

———–
0

(1 row(s) affected)

———–
2

(1 row(s) affected)

Adding the following line before running a SQL Script will terminate the SQL script from running and rollback the change when an error is encountered.

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

Example:
SQL> select * FROM test;

no rows selected

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
SQL> insert into test values ( 1 );

1 row created.

– error in the script it terminates the session
SQL> insert into test ( ‘aaaa’);
insert into test ( ‘aaaa’)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

– upon error the session terminates upon encountering an error
Disconnected from Oracle Database ……

– check if the insert was committed
$ sqlplus user/password

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Sep 2 11:44:08 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
– shows the insert was rolled-back
SQL> select * FROM test;

no rows selected

The other options available for WHENEVER SQLERROR are:
WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Created UNDO tablespace

CREATE UNDO TABLESPACE undotbs
DATAFILE ‘/ora_data_1/GIST/oradata/undotbs_01.dbf’
SIZE 100M REUSE AUTOEXTEND ON;

ALTER DATABASE DATAFILE ‘/ora_data_1/GIST/oradata/undotbs_01.dbf’ RESIZE 3000M;

shutdown immediate;

Changed UNDO parameters in the init file

Make sure COMPATIBLE=9.0.2 in the init.ora file is set.
### ROLLBACK SEGMENTS ###
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention = 1800

Comment out RBS parameters in init file

# rollback_segments = (r01,r02,r03,r04,r05)

startup;

Check undo and rollback parameters:

SQL> show undo
SQL> show rollback

Drop RBS tablespace

DROP TABLESPACE rbs INCLUDING CONTENTS CASCADE CONSTRAINTS;