Oracle & SQL Server: Disable/Enable all Triggers in a Database

Let's keep this post simple and straight forward  ;-)

/* Oracle */

-- Disable All Triggers

BEGIN FOR REC IN (select table_name from user_triggers) LOOP execute immediate 'alter table '|| REC.table_name ||' disable all triggers'; END LOOP; END;

-- Enable All Triggers

BEGIN FOR REC IN (select table_name from user_triggers) LOOP execute immediate 'alter table '|| REC.table_name ||' enable all triggers'; END LOOP; END;

/* SQL Server 2005 */

-- Disable All Triggers
DISABLE TRIGGER ALL ON DATABASE

-- Enable All Triggers

ENABLE TRIGGER ALL ON DATABASE

/* SQL Server 2000 */

-- Disable All Triggers
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- Enable All Triggers
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? ENABLE TRIGGER ALL'


-- Enjoy!

SQL Server: Execute same operation on all Tables in a Database, in a Go.

I recently found a Gem, i. e. an undocumented Stored Procedure sp_MSforEachTable which you can find in the Master database of your SQL Server 2000 and SQL Server 2005 (you may also find it in SQL Server 2008, but I personally haven't verified it yet). You can execute/use this Stored Procedure in any of your databases to perform the same operation on each and every Table in a database, in one Go.

For example, you may want to temporarily disable all the triggers in your Database. There's a simple way to do that in SQL Server 2005, i. e. by executing command DISABLE TRIGGER ALL ON DATABASE. But, this command doesn't work on SQL Server 2000. So, you may utilize this Stored Procedure to achieve just that in SQL Server 2000. 

-- Disable all triggers in a Database 
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- Enable all triggers in a Database
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? ENABLE TRIGGER ALL'

Note: During execution of this command, the Question Mark (?) will be replaced by Tables' names one after another to execute the same command on each and every Table in the Database.

There is also a way you can filter the set of Tables you want to target. Given below is the complete definition of sp_MSforEachTable Stored Procedure. You can explore and do more.

create proc sys.sp_MSforeachtable 
 @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, 
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null, 
 @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null 
as 
 /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */ 
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */ 
 
 /* Preprocessor won't replace within quotes so have to use str(). */ 
 declare @mscat nvarchar(12) 
 select @mscat = ltrim(str(convert(int, 0x0002))) 
 
 if (@precommand is not null) 
  exec(@precommand) 
 
 /* Create the select */ 
   exec(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
 syso.object_id ' 
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 ' 
         + @whereand) 
 declare @retval int 
 select @retval = @@error 
 if (@retval = 0) 
  exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 
 
 if (@retval = 0 and @postcommand is not null) 
  exec(@postcommand) 
 
 return @retval 

Oracle error: ORA-02392: exceeded session limit on CPU usage, you are being logged off

This error occurs when during your Session with Oracle Database Server, you are performing lots of operations which consumes more and more of CPU time on the Oracle Database server, which exceeded the CPU_PER_SESSION setting/value (total CPU time in hundreds of seconds) of Profile your UserID has been assigned.

You can check this value for your UserID/Profile, by running query:

Select * From User_Resource_Limits

You may contact your DB Administrator and ask them to set CPU_PER_SESSION to UNLIMITED to resolve this issue. The Default Profile on Oracle DB has this value set to UNLIMITED already. So, you also have an option to set your UserID to Default Profile.

If your Organization does not allow you to set CPU_PER_SESSION setting/value to UNLIMITED, then you have a workaround. This is for .NET, but it shouldn't be hard to implement in any other programming language.

CODE:
try
{
    // Your code
}
catch (Exception dbExc)
{
    // Oracle: exceeded session limit on CPU usage, you are being logged off
     if (dbExc.Message.Contains("ORA-02392"))
     {
        System.Threading.Thread.Sleep(30000);
        }
        else
        throw dbExc;
}

Basically what you will be doing here is to Catch the Exception, identify it to confirm that it is the error about exceeded CPU usage, then put the current Thread to Sleep for say 30 seconds and then continue with your process flow. It will reconnect to the Oracle database and will work fine.Make sure that in your code you are checking the Connection state before executing your SQL. If the Connection is closed after returning from the Catch block, then just Open it and continue.