Difference between Delete, Truncate and Drop

What is the difference between Delete, Truncate and Drop ?

• Delete is a DML statement
• Truncate and Drop are DDL statements
• Delete statement will fire a trigger.
• Truncate and Drop will not fire the trigger
• Delete requires rollback or commit to complete the transaction
• Truncate and Drop do not require either rollback or commit
• Delete will not reset the high water mark (reclaim extents)
• Truncate will reset the high water mark and reclaim space (except first extent)
• Drop will release space used by the object 
• Delete and Truncate can only be used against a table.
• Drop can be used against other object types too (for example views, procedure...)
• Delete and Truncate will not remove (drop) the indexes or triggers associated with the table.
• Drop will also remove (drop) all the indexes and triggers associated with the table.

Types of Oracle SQL Statements

There are six types of Oracle SQL Statements.

1. Data Definition Language (DDL) Statements
2. Data Manipulation Language (DML) Statements
3. Transaction Control Statements
4. Session Control Statements
5. System Control Statements
6. Embedded SQL Statements

What are Data Definition Language (DDL) Statements ?

• DDL statements can neither be rolled back nor need to be committed.
• DDL statements are auto-commit.
• DDL statements create, alter, drop users, objects, database structure and database also.
• for example
• create
• alter
• drop
• Assign or Remove privileges and roles
• for example
• grant
• revoke
• Turn auditing on or off
• for example
• audit
• noaudit
• Empty the table (delete all data from the data and reset high water mark)
• for example
• truncate
• Comments for an objects
• for example
• comment

What are Data Manipulation Language (DML) Statements ?

• DML statements can either be rolled back or commited.
• If a DDL is issued immediately after DML all previous DML transactions are committed.
• i.e. DDL issues a commit or is an auto-commit.
• DML statements are used to query or manipulate data
• Statement to query data
• select
• Statement to add data
• insert
• Statement to manipulate (change) data
• update
• Statement to insert data or update data based on condition
• merge
• Statement to delete data (not truncate)
• delete
• Statement to see execution plan for any of the above DML Statements
• explain plan
• Statement to lock of limit access to an object (table or view)
• lock table

What are Transaction Control Statements ?

• These statements help manage the changes made by DML statements
• Make transaction changes permanent
• commit
• Remove (undo) transaction changes 
• rollback
• Set a save point to undo changes
• savepoint
• Set properties for a transaction
• set transaction

What are Session Control Statements ?

• Session Control Statements allow you to manage and control the properties of a user session.
• Alter current session properties
• alter session
• for example
• data format using
• alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
• enable or disable SQL tracing
• alter session set SQL_TRACE = TRUE;
• alter session set SQL_TRACE = FALSE:
• enable or disable roles for a current session
• set role
• for example
• set role all except data_modify;

What are System Control Statements ?

• System Control Statement are used to change the database instance properties
• alter system
• for example
• Enables you to set system level parameters
• alter system set sec_case_sensitive_logon=true;
• Enables you to terminate a session
• alter system kill session ",;

What are Embedded SQL Statements ?

• Embedded SQL Statements use DDL, DML and Transaction Control Statements within a procedural language (viz : procedure, package, function....)
• They enable you to
• Define, allocate and release cursors
• declare
• open
• close
• Retrieve data from database
• fetch
• Specify a database and connect to it
• declare database
• connect
• Assign variable names
• declare statement
• Initialize descriptors
• describe
• Specify how error and warning conditions are handled
• whenever
• Parse and run SQL statements
• prepare
• execute
• execute immediate

Reference or Source : Various : Oracle documentation, Internet, Personal experience.

How to find Trace File associated with every session the database ?

Following query will list the USER session and trace file Associated with each background session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A40 WRAP HEADING 'SCHEMANAME TYPE|PROGRAM'
COLUMN NAME FORMAT A30 WRAP HEADING 'USERNAME|OSUSER'
COLUMN MACHINE FORMAT A20 WRAP HEADING 'MACHINE|TERMINAL'
COLUMN INSTANCE FORMAT A15 WRAP HEADING 'INST_ID|INST_NAME'
COLUMN SID FORMAT A10 WRAP HEADING 'SID|SERIAL#'
COLUMN PID FORMAT A10 WRAP HEADING 'PID|SPID'
COLUMN LOGON_TIME FORMAT A20 WRAP HEADING 'LOGON_TIME|STATUS'
COLUMN TRACEFILE FORMAT A109 WRAP

select
rpad(rtrim(to_char(vses.SID)),10,' ') || rpad(rtrim(to_char(vses.SERIAL#)),10,' ') as SID,
rpad(rtrim(to_char(vproc.PID)),10,' ') || rpad(rtrim(to_char(vproc.SPID)),10,' ') as PID,
rpad(rtrim(vses.USERNAME),30,' ') || rpad(rtrim(vses.OSUSER),30,' ') as NAME,
rpad(trim(vses.SCHEMANAME),25,' ') || ' ' || rpad(rtrim(vses.TYPE),14,' ') || rpad(rtrim(vses.PROGRAM),40,' ') as PROGRAM,
rpad(rtrim(vses.MACHINE),20,' ') || rtrim(vses.TERMINAL) as MACHINE,
rpad(rtrim(TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS')),20,' ') || rpad(rtrim(vses.STATUS),10,' ') as LOGON_TIME,
rpad(rtrim(to_char(vses.INST_ID)),15,' ') || rpad(rtrim(vinst.INSTANCE_NAME),15,' ') as INSTANCE,
vproc.TRACEFILE,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM
from gv$session vses, gv$process vproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background is null -- Uncomment this to get User Process only.
-- and vproc.background = 1 -- Uncomment this to get Background Process only.
order by vses.type,vses.status,vses.inst_id,vses.USERNAME
/

clear columns

Following query will list the BACKGROUND session and trace file Associated with each user session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A25 WRAP
COLUMN OSUSER FORMAT A20 WRAP
COLUMN TERMINAL FORMAT A20 WRAP
COLUMN MACHINE FORMAT A20 WRAP
COLUMN DESCRIPTION FORMAT A40 WRAP
COLUMN TRACEID FORMAT A8 WRAP
COLUMN TRACEFILE FORMAT A62 WRAP
COLUMN PID FORMAT 999999 WRAP
COLUMN SPID FORMAT A8 WRAP
COLUMN LOGON_TIME FORMAT A20 WRAP
COLUMN INST_ID FORMAT 999999
COLUMN INSTANCE_NAME FORMAT A10 HEADING INST_NAME

select
vbgproc.NAME,
vses.SID,
vses.SERIAL#,
vses.PROGRAM,
vproc.PID,
vproc.SPID,
vses.OSUSER,
vses.MACHINE,
vses.TERMINAL,
TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
vses.INST_ID,
vbgproc.DESCRIPTION,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM,
vproc.TRACEID,
vproc.TRACEFILE,
vinst.INSTANCE_NAME
from gv$session vses, gv$process vproc, gv$bgprocess vbgproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.PADDR=vbgproc.PADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vbgproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background = 1
order by vses.inst_id,vbgproc.name
/

clear columns