This content has been marked as final. Show 6 replies
861100 wrote:in Oracle other sessions NEVER see uncommitted DML changes!
Hi guys ,
oracle 11g.2 ASM with RAC under RHEL 5
i have 2 tables old and new i copt the data from DEC 2012 from new to old
i use nologging and append hint i copy 13,000,000 rows and it's take 30 min to copy the data ,
now i want to delete from the original table
but when i run the script to show the running statement there is no rows :
delete from x where callstarttime >= to_date ('12/01/2012','MM/DD/YYYY') and callstarttime < to_date ('01/01/2013','MM/DD/YYYY');
so after 10 min i canceled this delete , can you please tell me how to improve the performace of delete statement
SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork ,round(sofar/totalwork*100,2) "% Complete" from v$session_longops where totalwork !=0) WHERE "% Complete" != 100;
can i use the nologging and append also as the insert (my tables there is no partitions)
or if you have idea of this issue , due to when i exec this delete i also check from OEM in TOP ACTIVITY
this delete not exists so after 10 min i canceled cuz i thought this delete will not completed.
thanks for your quick answer , but i want after run this query(delete statement)
to know current running transaction comleted in persent% but there is not (no rows) ???
Oracle PL/SQL has a bulk delete operator that often is faster than a standard SQL delete MOREOVER v$transaction has a new column in 10g USED_UBLK which tells Number of undo blocks used...
If possible drop all the indexes before the delete operation and then re-created the indexes.
Create a new table with CTAS only with required set of rows, drop the old table and rename the new table to old.
thanks all for your support and time .