durga4578

 Profile Answers by durga4578 Questions by durga4578

• Mar 11th, 2006

 

DELETE: when u r using delete command in oracle the data will be deleted in the table. after that if u r using rollback the data in the table will be retrived.TRUNCATE: in the case of truncate, when u r using truncate what ever data in the table it will be deleted. after that if u r giving rollback also the data will not be retrived. but the structure of the table is available.DROP: in the case of DROP, if u r using drop the rollback command is not working and structure and data in the table also deleted. nothing willbe available about that table.EXAMPLE:DELETE: delete * from emp; "then the data inthe table will be deleted"after this u give ROLLBACKthen the data willbe retrieved. TRUNCATE: truncate table emp; " then the data inthe table willbe deleted" but the sturucture is available> if u type--- desc empthen it give the structure of that table.DROP: drop table emp:after that what ever command u r using on emp table it will give error. bcuz the table object emp is not available in database

Unknown

• Mar 16th, 2006

 

The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database. Delete is not effective when you use it to remove all data from a table, because it takes up storeage spaces due to unclear memories. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and only use it when you need to remove all data from a table. The drop statement will result in completely removing the table from the database. Note: Statements above are made with the assumption that a commit is has been executed. Once a commit statement is executed (commit is final). Therefore, rollback a transaction will not work if commit statement is executed.

asimjamal

 Profile Answers by asimjamal 

• Mar 23rd, 2006

 

Delete command: It actually removes the data permanently from the table with user specified conditions until you commit your work.It you want to save ,you could rollback the command. It will also create the space in the tablespace which in turn in a datafile.Drop command : It will remove both the structure and data in the specified table and could not be rolled back as it is a DDL command.Truncate : It will remove full data from the table without any user specified condition. It cannot be rolled back.

Rammohan

• Mar 27th, 2006

 

Hi

drop deleting the table and its structure from the data base

delete command used for deleting the records from the table,and it removing the table space which is allocated by the data base

truncate is also delete the records but it is not delete the table space which is created by the data base.

note:Here For every table in the DB the DB allocated some default space for that table.

Thanks

Ram

SUNIL

• Apr 17th, 2006

 

THE Ariticle is wrong even in Truncate the transaction can be rolled back

Ritesh Raj Singh

• Jun 27th, 2006

 

Hi

I dont think there is any possibility of rolling back a truncated transaction

through rollback command ...if requires one can recover it through other ways

either via a point in time recovery using incomlete recovery...but as per oracle syntax rollback is concerned no body can recover a truncated tables data through rollback command.

See Delete Deletes the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback the tables data after delete as well. one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim by delete.when you deletes table data indexes are not dropped.Its a DML statement

Drop drops all tables with its data structure idexes and integrity constraints.

Truncate command removes all the data from table and drops all integrity constraints its an DDL Statement and resets the HWM but you can't rollback a tables data after truncate and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.

mohits

 Profile Answers by mohits 

• Jul 26th, 2006

 

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. The operation cannot be rolled back.

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.

sneka

• Aug 9th, 2006

 

This article will be very useful for me. It 's a good explanation for sql. thank u

prachi

• Aug 23rd, 2006

 

Delete : A DML Statement creates a undo log . deletes record from table . Can be rolled back.

Truncate : A DDL it actually sets the high water mark level of the table back to zero (depends on MINEXTENTS too) , hence no one can read the table records.The  commend get itself issues a commit as it is  DDL command hence can not be rolled back . Unlike delete truncate can not be selective . Table defination remains as such. It is fast as no logs are maintained.

DROP:drops a table completelty (with table structure)can not  be rolled back . can be cascaded.

PRASHANTKULAT

 Profile Answers by PRASHANTKULAT 

• Mar 7th, 2007

 

I dont know about Oracle. But acccording to sql server

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. Of course, TRUNCATE TABLE can be rolled back.

dhanasweet

 Profile Answers by dhanasweet Questions by dhanasweet

• Apr 30th, 2007

 

                delete:
                           removes a sigle row from the table;
                truncate:
                           removes all rows from the table,the table view exist
               drop:
                        completely remove table from the database,the table viw not exist

riteshratna

 Profile Answers by riteshratna 

• May 11th, 2007

 

Delete
Delete remove the Data only, the Table structure remains intact.

This is a DML Statement
Rollback possible
No Commit is performed neither before nor after. (Because it is a DML Statement).
They take locks on rows,
They generate redo (lots of it)
They require segments in the UNDO tablespace.
A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
A truncate does not move the High Water Mark of the table back to zero, it retains it?s original position.
Delete deletes the specific rows filtered by where statement. and log is maintained for it.
It can activate the triggers.

Truncate
Truncate remove the Data only, the Table structure remains intact.
This is a DDL Statement
Rollback not possible (Except in SQL 2005)
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)
No row-level locks are taken.
No redo or rollback is generated.
They do not require segments in the UNDO tablespace.
All extents bar the initial are de-allocated from the table
A truncate moves the High Water Mark of the table back to zero
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.

Drop
Drop permanently removes both the Data as well as the Table Structure.
This is a DDL Statement
Rollback not possible
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)
No row-level locks are taken.
No redo or rollback is generated.
They do not require segments in the UNDO tablespace.
All extents bar the initial are de-allocated from the table
A truncate moves the High Water Mark of the table back to zero
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.

sari_pot

 Profile Answers by sari_pot Questions by sari_pot

• Oct 18th, 2007

 

delete:1)It uses where clause.
            2)It makes an entry in the transaction log each time it deletes a row,hence
                is time taking.

drop: It deletes the structure as well as the data.

truncate: 1)It uses no where clause.
               2)It makes use of less system and tansaction log,hence is faster compared  to delete.
                3)you can't delete the structure.

dinesh kumar tiwary

• Nov 21st, 2007

 

Yes you can rollback the truncate command in SQL 2000

Pareshv

 Profile Answers by Pareshv 

• Feb 11th, 2008

 

It is possible to rollback the truncate operation

If you defined your truncate command within a begin and end transaction then it is possible to rollback the truncate operation

Check the below code :

create table sampletest(id int identity(1,1),Name varchar(50))

insert into sampletest(Name)values('Paresh')
insert into sampletest(Name)values('Naresh')
insert into sampletest(Name)values('Suresh')
insert into sampletest(Name)values('Ramesh')
insert into sampletest(Name)values('Kalesh')

select * from sampletest

begin tran

truncate table sampletest

rollback

ketanbenegal

 Profile Answers by ketanbenegal 

• Jun 2nd, 2008

 

Delete: DML statement, can rollback and specify where clause.
Truncate: DDL statement, can't rollback, cannot specify where clause, retains table structure, deletes all rows.
Drop: DDL statement, can't rollback, cannot specify where clause, deletes table structure, deletes all rows.

hossain_camp4

 Profile Answers by hossain_camp4 

• Jul 9th, 2009

 

DELETE

Delete keyword is used to delete some row from table with
conditionally or all rows from table.
Like example:
SELECT COUNT(*) FROM
employees;
21

DELETE FROM employees WHERE job_id='it_back';
some rows are deleted from employees where job_id is it_back.

SELECT COUNT(*) FROM employees;
14

ROLLBACK;

SELECT COUNT(*) FROM employees;
21

rollback is applicable on delete keyword.
but if we use commit after 2no statement, then we will get after execute select count(*) from employees - 14

DROP

Drop is a keyword which is used to drop a table from database. it
makes free the storage of table.


DROP TABLE employees;

SELECT * FROM employees;
it will be show a error.

ROLLBACK;

SELECT * FROM employees;
it will
show error because drop statement is completed auto commit;


TRUNCATE

Truncate is used to delete all rows from table. It does not use condition to delete rows from table.
It is also completed auto commit statement;

TRUNCATE TABLE employees;
SELECT *
FROM employees;
No row is selected

ROLLBACK;
SELECT * FROM employees;
No row is selected

gaurav_131

 Profile Answers by gaurav_131 

• Jul 23rd, 2009

 

DELETE

We can use the DELETE command to remove rows from a table. We must specify a WHERE clause in our DELETE statements so only some rows will be removed.If we do not do that all rows will be removed and that something we most certainly never want. DELETE operations will cause all DELETE triggers on the table to fire.

TRUNCATE

We can use the TRUNCATE statement to remove all rows from a table. A very important thing to note is that the TRUNCATE operation cannot be rolled back and no triggers are fired. 

DROP

The DROP statement removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. The operation cannot be rolled back and no triggers will be fired.

yasho_d

 Profile Answers by yasho_d 

• Jan 16th, 2010

 

Truncate can be rolled back. DDL command.
Delete cannot roll back. DML command.
Drop cannot roll back. DDL command.

gauravt601

 Profile Answers by gauravt601 

• Mar 25th, 2010

 

DELETE is used to delete all records in the table. It is a DML command while DROP deletes the structure of the table. TRUNCATE is much faster than DROP and it cannot ROLLBACK.

Prashant Sharma

• Jul 17th, 2011

 

(1)Drop and Truncate both are DDL (Data Definition Language) commands while Delete is a DML(Data Manipulation language) command.

(2)Drop and Truncate commands can't be rolled back once implemented while Delete command can be rolled back.

(3)Drop command deletes all the things belonging to the table (schema, metadata,complete table structure and data) while Truncate removes all the rows from the table .

(4)Delete command removes the rows specified in the where condition. If NO WHERE condition is specified then all the rows are removed from the table . But, the operation can be rolled back if we want to undo the changes. If we really want to keep the changes we have done then commit is required.

raviraj044

 Profile Answers by raviraj044 

• Jan 25th, 2012

 

Delete and Truncate Both Can be Rolled back

Code
CREATE TABLE sampletest(id int identity(1,1),Name varchar(50))


 


INSERT INTO sampletest(Name)VALUES(Paresh)


INSERT INTO sampletest(Name)VALUES(Naresh)


INSERT INTO sampletest(Name)VALUES(Suresh)


INSERT INTO sampletest(Name)VALUES(Ramesh)


INSERT INTO sampletest(Name)VALUES(Kalesh)


 


SELECT * FROM sampletest1


 


SELECT * INTO sampletest1 FROM sampletest


 


begin tran


 


DELETE sampletest1


 


rollback

mohitgyl

 Profile Answers by mohitgyl 

• Jan 26th, 2012

 

--Delete is a DML Operation while Drop/Truncate are DDL operation

--Delete can be rolled back but Drop/Truncate can not be rolled back

--On Delete, transaction logs are written but Drop/Truncate does not write transaction logs

--Delete operation is applied on the data inside a table with where condition (may or may not), Truncate operation is used to remove ALL the data from a table (no where condition is allowed) and Drop is used to drop a Database Object like Table,view, Store Procedure etc.

Aditi

• Feb 8th, 2012

 

Truncate table command can be rolled back.
Try this:
Create a table named as test
insert some records into this table
After insertion run select * From test to check insertion of records in the table

then execute these lines
begin tran
truncate table test

execute - select * From test, to check deletion of records
the execute this:
rollback

raman sharma

• Mar 2nd, 2012

 

first of all delete is DML command and truncate is a DDL command requirement is DML command does rollback but DDL command does not rollback ....

Hakkim

• May 22nd, 2012

 

Drop : Deletes both Table Structure and the Data
Truncate : Deletes only Data and it performs automatic Commit
Delete : Deletes only Data and Doesn't performs automatic Commit.

karunakarsaroj

 Profile Answers by karunakarsaroj 

• May 24th, 2012

 

Truncate is much faster than delete. all same ans like above

lokesh

• Jun 5th, 2012

 

In truncate all the data will be deleted whereas in delete also same but the difference in delete we again reback the data by using flashback (without use commit) in truncate it is not possible while drop the total table is drop

arunkumar

• Jun 18th, 2012

 

Delete: delete a single row or multiple row
it is DML operation so rollback is enable.

Truncate: delete from all record in table. commit or rollback is not using. trigger will not fired.but column will there

Drop : delete a table structure. commit or rollback is not working. trigger also not working.

Sheraz Baig

• Nov 21st, 2013

 

Delete is DML statement which delete the record/s and can be Rolled Back.
Truncate does same work but because its a DDL statement so cannot be Rolled Back.
Drop is DDL statement which delete the Table structure witht he data, Cannot be Rolled Back.

chowdary

• Dec 7th, 2014

 

delete is useful to delete any record in a table or complete table but the structure is same,

drop it will erase the entire structure and data of the table ,truncate it will delete entire table but structure is same

jayesh patel

• Dec 10th, 2014

 

DELETE:-delete to table and records.
DROP:-delete to table.
TRUN:-table structure are same in records are delete.

Ashakant Kendre

• May 9th, 2016

 

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason: When you type DELETE. All the data get copied into the Rollback Tablespace first, then delete operation get performed. That is why when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Tablespace). All this process take time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That is why TRUNCATE is faster.Once you Truncate you cannot get back the data.

3>You cannot rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.

4>In case of TRUNCATE, Trigger does not get fired. But in DML commands like DELETE. Trigger get fired.

5>You cannot use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause

Amit

• Aug 28th, 2016

 

Adding 2 cents
1) DELETE is DML command because it does not change data dictionary (Every command which changes data dictionary tables
is called DDL command ) TRUNCATE is DDL command is changes storage statistics for table if used with DROP STORAGE option. Also after truncate data dictionary tables like USER_EXTENRS, USER_SEGEMNTS updated. DROP is DDL command, as it updates USER_TABLES and other statistics tables.

2) DELETE is slow than TRUNCATE because of INDEXES on tables. DELETE command deletes INDEX entries as well. In case of TRUNCATE, Oracle truncates INDEX and TABLE data faster than DELETE. DROP is faster than DELETE.

3) DELETE does not reduce HIGH HIGH WATER marks , so storage space is not usable even if data deleted.TRUNCATE brings HIGH HIGH Water marks to zero and makes storage space available.
DROP makes storage space available.

4) DELETE generates REDO LOGS. TRUNCATE does not generate REDO LOGS ( TRUNCATE generates REDO logs but very less compare to DELETE) DROP generated very less REDO logs.

5) DELETE required use of UNDO SEGMENTS to rollback. TRUNCATE does not use UNDO SEGMENTS. DROP does not require ROLLBACK.

6) DELETE fires DELETE event TRIGGERS but TRUNCATE does not fire TRIGGERS.