Answer / manoj.oracle

Delete command is a dml command
truncate command is a ddl command

Delete command has a where clause.
truncate hasn't where clause.

Delete doesn't release memory space(means we can rollback
the deleted rows)
truncate releases memory space(means we can't rollback)

By using synonym we can delete a rows(Delete command).
By using synonym we can't delete a rows(Truncate command).

Answer / pr@$@d

1 TRUNCATE is a DDL command whereas DELETE is a DML command.
2 TRUNCATE is much faster than DELETE.
Reason:
a) When you type DELETE. All the data get copied into
the Rollback Table space first. Then delete operation get
performed. That’s why when you type ROLLBACK after deleting
a table, you can get back the data (The system gets it for
you from the Rollback Table space).All this process takes
time. But when you type TRUNCATE, it removes data directly
without copying it into the Rollback Tablespace.Thatswhy
TRUNCATE is faster. Once you truncate you can’t get back
the data.
b) TRUNCATE command resets the High Water Mark for the
table but DELETE does not. So after TRUNCATE the
operations on table are much faster.
3 You can’t rollback in TRUNCATE but in DELETE you can
rollback. TRUNCATE removes the record permanently.
4 In case of TRUNCATE, Trigger doesn't get fired.But in DML
commands like DELETE .Trigger get fired.
5 You can’t use conditions (WHERE clause) in TRUNCATE.But
in DELETE you can write conditions using WHERE clause

Regards,
-Pr@$@d Reddi.
prasadreddi_mca@yahoo.com

Answer / thirupathi.l

delete is a dml command using this we can getback the records.
it will not call inmplicit commit.
perfomance slow in case of delete no.of records.
truncate is a ddl commnd.itwillcall implicit commit
we can't get back the record
performance good in case of deleting no.of records

Answer / shree

Delete command always used with where clause.
but Truncate is not.

Delete command deletes only selected rows/records from table
which are defined in where clause.
But Truncate command deletes all records from the Table
without using where clause.

Answer / ron

To add to ans#1. truncate needs drop table level priviledges.

Answer / shilpa.oracle

Answer given by Manoj is Absolutely correct.

Answer / dhananjay mukhedkar

The Answer given by Prasad Reddi is perfect.

Answer / kumar

DELETE TABLE is a logged operation, so the deletion of each
row gets logged in the transaction log, which makes it
slow.
TRUNCATE TABLE also deletes all the rows in a table, but it
won't log the deletion of each row, instead it logs the
deallocation of the data pages of the table, which makes it
faster.

The records deleted through DELETE can be roll backed.
The records deleted through TRUNCATE can't be roll back.

If we are using TRUNCATE ina Transaction then only we can
do the roll back else not.

Both will delete the data but not the stucture of the table.

Answer / p.baskar

DELETE is DML Command, TRUNCATE is DLL Command.

DElete used to particular record or, all records from table.
But TRUNCATE only relete the all records.

Ee can rollback the DELETE Command, But we cant rollback the
TRUNCATE

Answer / ron

again to add to my earlier positing, many forget that is the
table has materialized view log truncating table will
corrupt the materialized view.

in that case you have to user:
truncate table schema_name.table_name purge materialized views;

you can read more about it here:
http://www.psoug.org/reference/truncate.html

How to revoke create session privilege from a user in oracle?

0 Answers  

what is the syntax of INSERT command?

7 Answers  

Explain the dml?

0 Answers  

I have a table emp. There is only one column in the table. In that , there are only three rows in that column. The value in the first row is 'A' and the value in the second row is 'B' and the third row is 'C'. Now, my question is , How will you write a select query to display the output as B C A Note: order by cannot be used coz it gives us output as CBA. But the output should be BCA.

4 Answers  

1. what is a single procedure? 2. suppose a view constructed on a query... after that in the master table a column was dropped, then what will be the status of that view? will it be invalid or something else? 3. what is mutating table error? how can you solve that problem? 4. suppose "select * from emp where empno=7788;" is running slow because of what ever the cause it is.. how can you fix it and make the execution faster? 5. will the snapshot of data will be stored in the RAM , where will ir be stored exactly. 6. how to delete 100 tables or 100000 tables' data from the schema without writing delete statement for no.of times. write an sql statement to do that job. 7. suppose a package contains so many cursors, procedures and all.. and we are using such types of procedures so many in a program. so the execution is to be stored all those packages and all to the RAM. utimately it is using many resources for each time execution. how can you reduce the time and using of resources for each execution because the query need to be run many times. how can you tune that? 8. suppose a procudere is there with specs as (a,b,c,d), all of these are IN mode only then while calling them should we pass all the 4 arguments? how to pass (b,c) only.. how ever the specs are wrote in the same order displayed in the above? 9. a function is returning a table type, how to access that function in a select statement? 10. i have a table in database A and another table in database B, how can you access the data of these two tables? write a query for that with dblinks. 11. i have two tables might have 15/20 columns in that.. i wanna send these columns(table) to the front end application for example you are using asp.net by writing a procedure. write that to perform the above job. 12. shall we create a table in the package? 13. shall we create a table in the procedure? 14. suppose i have 10 records, in that i want to select 7th record. write a query for that. cant you use rownum for this? 15. suppose my procedure has got around 20 select statements, in that some three select statements are running very slow. what will you write to detect which queries are running slowly? 16. what fields you use frequently in plan_table? 17. what is pipeline function? explain me with an example. 18. does a unique column can contain more than one null? 19. how many hints are there provided by oracle?

5 Answers   Honeywell,

why dont we assign not null constraint as table level constraint.

0 Answers  

what is integrity constrains?

3 Answers  

What is Database Trigger ?

1 Answers  

15. Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.

0 Answers   Wipro,

Which Database is the best for the Security issue ? 1. DB2 2.SQL Server 3.MySQL 4.ORACLE

1 Answers  

i have executed the Delete command after the I have created table whether deletions will be commit or not? if table is successfully created?

5 Answers   Oracle,

How to return top 5 rows in oracle?

0 Answers