Showing posts with label DELETE_QUERY. Show all posts
Showing posts with label DELETE_QUERY. Show all posts

Tuesday, 9 May 2017

How to find the data Who deleted and commited in database

SQL> SELECT NAME, value FROM v$parameter WHERE NAME LIKE 'audit%';

NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
audit_sys_operations
FALSE
audit_file_dest
E:\ORACLE\PRODUCT\10.2.0\ADMIN\XP10R2JAN\ADUMP
audit_trail
DB
The audit is being done to the DB


SQL> desc dba_audit_trail;
SELECT userid, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss')
FROM sys.aud$ ORDER BY timestamp# asc;

How to use BULK COLLECT statement for delete SQL query

CREATE OR REPLACE PROCEDURE SADMIN.DEL_CONTRI_DET_FOR_RECON
IS
   L_LIMIT   PLS_INTEGER := 20000;

   CURSOR C1
   IS
      SELECT row_id
        FROM siebel.s_invoice_item
       WHERE person_id IN
                (SELECT b.row_id
                   FROM SADMIN.disalloc A, SIEBEL.S_CONTACT B
                  WHERE A.PD_NEW_EYEE_ID = B.PERSON_UID
                        AND B.CON_CD = 'Member'
                        AND A.PD_DT_MODIFY BETWEEN '01-JAN-2010'
                                               AND '31-MAY-2010');

   TYPE temp_mem IS TABLE OF C1%ROWTYPE
                       INDEX BY PLS_INTEGER;

   tem_mp    temp_mem;
BEGIN
   OPEN C1;

   LOOP
      FETCH C1
      BULK COLLECT INTO tem_mp
      LIMIT L_LIMIT;

      EXIT WHEN tem_mp.COUNT = 0;

      FORALL indx IN 1 .. tem_mp.COUNT
         DELETE FROM siebel.s_invoice_item
               WHERE row_id = tem_mp (indx).row_id AND row_id LIKE '%-%-%';

      DBMS_OUTPUT.put_line (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
      COMMIT;
   END LOOP;

   CLOSE C1;
END;

Tuesday, 1 November 2011

Receipt_Delete_Qry

/* Formatted on 20-08-2011 14:12:14 (QP5 v5.115.810.9015) */
DELETE FROM   rcv_shipment_headers
      WHERE   SHIPMENT_HEADER_ID IN
                    (  SELECT   rsh.SHIPMENT_HEADER_ID
                         FROM   rcv_shipment_headers rsh,
                                org_organization_definitions ood,
                                rcv_shipment_lines rsl
                        WHERE   SHIP_TO_ORG_ID = 129
                                AND ood.ORGANIZATION_ID = SHIP_TO_ORG_ID
                                AND rsl.SHIPMENT_HEADER_ID(+) =
                                      rsh.SHIPMENT_HEADER_ID
                                AND rsl.SHIPMENT_LINE_ID IS NULL
                     ORDER BY   receipt_num, rsh.SHIPMENT_HEADER_ID)