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)
In Oracle:
SQL> ALTER SESSION SET nls_date_format = ‘YYYY-MON-DD HH24:MI:SS’;
Session altered.
SQL> SELECT TRUNC(sysdate) FROM DUAL;
TRUNC(SYSDATE)
——————–
2009-MAY-10 00:00:00
In SQL Server:
SELECT CAST(CONVERT(varchar, GetDate(), 101) AS DateTime)
2009-05-10 00:00:00.000
Here are some of the scenarios when one gets ORA-01031:
1) Insufficient privileges error is when “oracle” user is not part of the “dba” group when one tries to connect as sysdba, so it’s required user oracle is part of the “dba” group As you see in the following example below:
$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Sun Apr 5 16:26:06 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
$ id
uid=501(oracle) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t
2) A user/schema tries to truncate table owned by another user/schema and if the user doesn’t have access one will receive ORA-01031.
– connect as user1 which is trying to truncate table owned by scott
SQL> connect user1
Password:
SQL> truncate table scott.table1
truncate table scott.table
*
ERROR at line 1:
ORA-01031: insufficient privileges
There are couple of ways to grant this access:
(1) grant user1 “DROP ANY TABLE” granting this access may be an issue as the user can drop a table in any schema so the work around.
(2) Another way to give this grant is to create a store procedure in scott’s schema that truncates the table and grant user1 execute access to the store procedure.