Oracle different between drop,delete,truncate.

kindly what is the different between drop table,delete table,truncate table.

Oracle difference between DELETE & TRUNCATE

i have read in a book of SQL the differences between DELETE & TRUNCATE, it says that :TRUNCATE drops the table & recreates the table but, DELETE deletes the rows row by row. now my doubt is that how can a table be again recreated once it has been dropped. If it is dropped, the meta data abou ...

Oracle commit, drop table, truncate takes longer

I have Oracle 10.2 installed in Linux. Select runs good. Commit, drop table, truncate takes longer. Would you have any suggestions? ...

Oracle Diff between function,procedure/packages and Diff bet delete,truncate,drop

I am your pal ranga from IT capital bangalore india. I would like have detail discussion with respect to subject. Pls discuss ...

Oracle diffence between delete,drop,truncate in case of memory.

Please explain diffence between delete,drop,truncate in caSE of memory. ...

Oracle DROP and TRUNCATE and DELETE

Which is the difference between DROP, TRUNCATE and DELETE a table?Which eliberates a space and which not? ...

Oracle Trigger ON DELETE or DROP or TRUNCATE

create trigger DDL_GUARD_cactbefore DROP or TRUNCATE or DELETEon cidcb_cacbeginraise_application_error( -20001, 'You are not allowed to DROP or TRUNCATE table in this schema. ' );end;I am try to build a triger so that no one can delete truncate or delete my table in schema but it is showing me error ...

Oracle Time Complexity of Delete, truncate and drop table operations

A function in my application requires to import a dump into the database; so before it i need to clear the tables. I have three options1. Delete all the rows of all tables 2. Truncate the tables 3. Drop all the tables and then create new tables before importing the dump.what method should i use???Is ...

Oracle drop, delete and truncate

Can anybody plz tell me what is the basic difference betwwen 'drop' , 'delete' and 'truncate ' ? ...

Oracle Prevent a User from delete, drop and truncate table created by its own..

How to prevent a user from dropping, deleting and truncating the table created by its own. Is it possible? ...

Oracle After drop tablespace,how to delete the datafile contained the table

i used rm to delete the datafile ,but after a while the database is down and can not be startup? please tell me how to do? ...

Oracle diff between delete and truncate

I have a tables likeX==ID NUMBER1 1002 101 Y=ID NUMBER Which query is optimixed either a or b and when use not exists and when use > condition a)SELECT * FROM X A, Y B WHERE ID NOT EXISTS(SELECT * FROM Y WHERE A.ID=B.ID AND A.NUMBER=B.NUMBER) b)SELECT * FROM X A,Y B WHERE A.ID>(SELECT COUNT(*) ...

Oracle Difference between Delete and Truncate

I have read in a book that Truncate is better performance wise than Delete becauseTruncate is a DDL. My understanding is that because Truncate is a DDL and it does not generate any roll back information it is faster whereas the delete command is a dml and ithas to keep the information til the commit ...

Oracle drop and truncate

i will revoke drop and truncate any table from USER witch have all privileges.When i try revoke drop any table from this USER, Oracle says that, the USER hasn't this system privilege.But the USER can drop and truncate any table.Thanks for all suggestions.Message was edited by: learn ...

Oracle delete/truncate

I'm looking for a way of deleting rows quicker than the delete dml sentence.I know truncate sentences, but i don't want to truncate a table or a partition, I want to delete a great number of rows (identified by a where clause), but not a whole partition or table.For example, inserting rows i can use ...

Oracle Delete / Truncate

I am using Oracle 9208.I have a huge table from which I want to purge 90% of data and to release the space. 'Truncate' will delete all data (100%) and 'delete' will be costly as it's a huge table.I have an idea to create a temp table - move 10% data there - truncate original table - move 10% data fr ...

Oracle How to drop/delete instance

I need to delete or drop an instance from the database. How do i do it? What command to use to drop the entire instance with the data in it. I dont need the instance as we'r creating a new one.pls help on how to delete instance. ...

Oracle Drop/Delete Instance

We are recycling an old system with oracle9i for as a test server for another project. How do i delete the instance from the system as we do not need it any more. ...

Oracle Unable to drop or truncate a plan table

I was trying to execute 'Explain Plan' on one of my query. However, when I tried to drop the plan table, i was prompted that the table or view does not exists:QL> EXPLAIN PLAN FOR2 SELECT * FROM ORDERS WHERE TO_CHAR(O_SHIP_DATE, 'YYYY')='2005' AND O_STATUS='PROCESSING';Explained.SQL> SELECT * ...

Oracle Delete/Truncate

Once you delete a row that DML statement is in the logbuffer and the old copy of the row is in the undo (rollback/undo segment)tablespace.Once you commit the DML the logbuffer is flushed out and DML is written is written to the redo log segments and simultaneosly the old copy from the undo tablespac ...

Oracle is "drop database including backups" delete archive logs as well?

Is "drop database including backups" delete archive logs as well?it seems not! ...

Oracle Shrink table and Truncate/delete command

Friends ,Regarding truncate cmmand we know ,"Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is."That means the table's data are deleted but the block is still not free .Blocks are still use the information of table's st ...

Oracle SQL loader and truncate drop storage clause

I check that during load data via sqlloader like that:LOAD DATATRUNCATEINTO TABLE TEMP_TABLEFIELDS TERMINATED BY ","(id,text)it use reuse storage in truncate command. Is there any way to set truncate with drop storage option? ...

Oracle Revoke drop / truncate privileges

I have granted on CONNECT role to the user. I'm running on 10.2.0.3. The user is unable to create a table. However, he is able to truncate and drop his own tables. How can I restrict these privileges to that user. ...

Oracle Difference between just Truncate and Truncate with drop storage option.

What is the difference between the below two commands.1) Truncate table test123;And2) Truncate table test123 drop storage; ...

Oracle How to delete/drop table in all_objects

How to delete/drop tablewhich exists in all_objectsbut sql-query saystable does not exists . ...

Oracle Release of space after delete/truncate table

How does release of space after delete/truncate table works? Is the space used before deletion released once delete is complete or not? Will I see the space occupied by deleted table as free in dba_segments or will I need to reorganize the table (drop and recreate again?). Reason why I am asking is ...

Oracle how to delete an entry in a table when drop a user????

I want to write a trigger that when someone drop a user, delete the related data in one table ..how to write this one ??? ...

Oracle How to delete archivelogs ???????

I am looking for a way to delete archive logs through RMAN's backup command, something like this:BACKUP ARCHIVELOG all delete input until time 'sysdate-5/24';We are on Oracle 10g Release 2 (10.2.0.3), IBM - AIX 5.3.I would like to retain archivelog's generated in the last 5 hours on disk and let RMA ...

Oracle Can we delete from a MV?

I have created a MV in our local Database as follows.CREATE MATERIALIZED VIEW MV_Name_1BUILD IMMEDIATEUSING INDEX REFRESH COMPLETE ASSELECT column1,column2,column3FROM TABLE1.......Purpose : -- We will lload data from Remote database into this MV and then import the needed data from this MV into our ...