Difference between DROP,Truncate and Delete in Oracle?

Difference between Truncate and Delete in Oracle?

Answer:

1.TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

2. DELETE is a DML command and can be rolled back.

3. TRUNCATE : You can't use WHERE clause and DELETE : You can use WHERE clause

4. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

5.Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.Delete: You can keep object's statistics and all allocated space.

6. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

7. Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.

8. Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

9. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

10. Truncate will not use the undo TBS, whereas a delete will.

What is High Water Mark in Oracle?

What is High Water Mark in Oracle?

Answer:

1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation won't reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

I hope now you understand well. see the below example.

for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.


Explanation in Detail:
http://asktom.oracle.com/pls/asktomf?p=100:11:0::::P11_QUESTION_ID:492636200346818072

Check Concurrent Manager Status from the Back end?

How to check Concurrent Managers Status from the Back End?

select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));

Find out Explain Plan of Session

How to get explain plan of Session?

set lines 120
select
lpad(' ', 2*(depth))||operation||' '||options
||' '||decode(object_owner,null,null,
object_owner||'.'||object_name)
||' '||decode(cost,null,null,'Cost = '||cost)
||' '||decode(partition_start,null,null,
'Partition='||partition_start||'..'||partition_stop)
||' '||decode(bytes,null,null,'Bytes= '||bytes)
-- ||' '||decode(access_predicates,null,null,'Access= '||access_predicates)
-- ||' '||decode(filter_predicates,null,null,'Filter= '||filter_predicates)
--, other,other_tag, temp_space
"Execution Plan"
from v$sql_plan
where (address, hash_value) =
(select sql_address, sql_hash_value
from v$session
where sid = &sid)
order by id
/
undefine 1

Note: Pass SID when it prompts

Find out Session Waits in a Database

How to find out Session wait events in a Database?

column event format a41
set pagesize 100

select a.event,count(*),avg(b.wait_time)
from v$session a,v$session_wait b
where a.sid = b.sid
and a.type != 'BACKGROUND'
group by a.event
order by count(*) desc
/

How to find out sid of a query running?

How to find out session id of a sql that is running?

SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%ALTER INDEX%';

Note: Where sql_text is some unique part of the sql running

Check Redolog count hourly basis

How to find , how many redo switches happening every hour?

select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/