Purform Bulk Deletes on Tables with Commit for Every N number of Rows and update row count in Temp Table

1) Create Temporary Table for Logging the activity

create table snanabala.log_table (SEQ_NUM number(10), Time DATE , rows_deleted number(38));


2) Create Sequence for row sequence


create sequence snanabala.delete_sequence;

3) Now give grants on this table and sequence since we are using procedure to delete on table and logging in temp table

grant select,delete on snanabala.Sunil_archive to snanabala;  ( deletions on this table )
grant all on snanabala.log_table to snanabala;
grant all on snanabala.delete_sequence to snanabala;


4)   Now we need to perform deletions on sunil_archive Table .


CREATE OR replace PROCEDURE snanabala.Delete_rows_TEST1
AS

    ln_DelSize                      NUMBER := 1000;
    ln_DelCount                     NUMBER;

  BEGIN
    LOOP
           DELETE FROM SNANABALA.Sunil_ARCHIVE
             WHERE  
                  STARTDATETIME between to_date('10-01-2014 00:00:00','MM-DD-YYYY HH24:MI:SS') and to_date('10-01-2014 04:59:59','MM-DD-YYYY HH24:MI:SS')
              AND
                    rownum <= ln_DelSize;
ln_DelCount := SQL%ROWCOUNT;
                           dbms_output.put_line(ln_DelCount);
  INSERT INTO LOG_TABLE (SEQ_NUM, TIME, "ROWS_DELETED")  VALUES (delete_sequence.nextval,sysdate,ln_DelCount);
            EXIT WHEN ln_DelCount = 0;
            COMMIT;

   END LOOP;

END;
 /

5) Execute the above procedure

exec snanabala.Delete_rows_TEST1

6) Monitor how many deletions performing

select * from snanabala.log_table order by date desc

Purform Bulk Deletes on Tables with Commit for Every N number of Rows

-- Delete Row from Table Sunil_temp and Commit Every 10000 rows.


CREATE OR replace PROCEDURE Delete_rows_SUNIL
AS

    ln_DelSize                      NUMBER := 10000;
    ln_DelCount                     NUMBER;

  BEGIN
    LOOP
           DELETE FROM SUNIL_TEMP
             WHERE
                  STARTDATETIME between to_date('04-01-2015 00:00:00','MM-DD-YYYY HH24:MI:SS') and to_date('04-25-2015 23:59:59','MM-DD-YYYY HH24:MI:SS')
              AND
                    rownum <= ln_DelSize;

ln_DelCount := SQL%ROWCOUNT;

             dbms_output.put_line(ln_DelCount);

            EXIT WHEN ln_DelCount = 0;
            COMMIT;

   END LOOP;

END;
 /

Give Grants For Objects Which are Missing Grants on Objects & Which are Created Last 24 hrs Create

CREATE OR replace PROCEDURE snanabala.Pr_fix_missing_grants
AS
  CURSOR cu_missing_grants_identity IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to IDENTITY_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'IDENTITY' )
           AND Trunc(created) = Trunc(SYSDATE);
  CURSOR cu_missing_grants_siw IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to SIW_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'SIW' )
           AND Trunc(created) = Trunc(SYSDATE);
  CURSOR cu_missing_grants_edw IS
    SELECT 'grant select on '
           || owner
           ||'.'
           || object_name
           || ' to EDW_READ_ROLE' AS script
    FROM   dba_objects
    WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
           AND owner IN ( 'EDW_READ_ROLE' )
           AND Trunc(created) = Trunc(SYSDATE);
BEGIN
    FOR l_missing_gr_identity IN cu_missing_grants_identity LOOP
        dbms_output.Put_line (l_missing_gr_identity.script);
   EXECUTE IMMEDIATE l_missing_gr_identity.script;
    END LOOP;

    FOR l_missing_gr_siw IN cu_missing_grants_siw LOOP
        dbms_output.Put_line (l_missing_gr_siw.script);
    EXECUTE IMMEDIATE l_missing_gr_siw.script;
    END LOOP;

    FOR l_missing_gr_edw IN cu_missing_grants_edw LOOP
        dbms_output.Put_line (l_missing_gr_edw.script);
    EXECUTE IMMEDIATE l_missing_gr_edw.script;
    END LOOP;
END;
/