Hi,
in my 12.1 Database, I have a table with approximatively 100 millions of records. I need to delete the duplicates raws from that table. I have tried to use the DML shown below, but after 24 hours, the operation was still in progress.
DELETE FROM
MSR_3219 A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
MSR_3219 B
WHERE
A.RAW_DATE = A.RAW_DATE
AND
A.RAW_VALUE = B.RAW_VALUE
AND RAW_DATE between TO_DATE('01/01/2007', 'DD/MM/YYYY') and TO_DATE('31/12/2007', 'DD/MM/YYYY'));
I cancelled it, and bounced the database. I wanted to put the DB in noarchivelog mode, but went to issues trying that (the DB did not stopped when I was issuing "shutdown imlmediate").
I have been goggling for the nologging option in a sql statement ot in an alter table statement but I understood that a DELETE statement do not involove NOLOGGING, NOLOGGING works only INSERT statements and other options.
How to quickly delete a large number of rows on this big table without switching to noarchivelog mode ?
Regards.
.