TRUNCATE Table Statement
TRUNCATE table statement is used to remove all the records permanently.
Its a DDL statement, so we do not need to explicitly COMMIT the transaction.
Also a ROLLBACK command does not rolled back the transaction.
SELECT * FROM EMPLOYEES_TEST;
TRUNCATE TABLE EMPLOYEES_TEST;
ROLLBACK;
SELECT * FROM EMPLOYEES_TEST;
TRUNCATE Impact on DML Statements:
TRUNCATE is a DDL statement. So any other DML transaction executed before this statement are automatically committed post this statement execution.
DROP TABLE EMPLOYEES_TEST;
CREATE TABLE EMP_TEST AS SELECT * FROM EMPLOYEES;
CREATE TABLE EMPLOYEES_TEST AS SELECT * FROM EMPLOYEES;
SELECT * FROM EMP_TEST;
SELECT * FROM EMPLOYEES_TEST;
DELETE EMP_TEST WHERE DEPARTMENT_ID = 30;
SELECT * FROM EMP_TEST;
TRUNCATE TABLE EMPLOYEES_TEST;
ROLLBACK;
SELECT * FROM EMP_TEST;
SELECT * FROM EMPLOYEES_TEST;
So ROLLBACK does not have any impact on TRUNCATE command. 7 Records from EMP_TEST table are permanently deleted.
RELATED TOPICS:
