Home » SQL & PL/SQL » SQL & PL/SQL » snapshot too old (PLSQL 11g)
snapshot too old [message #662769] Thu, 11 May 2017 19:32 Go to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
I have the following code where the cursor returns 6985604 rows. I am using the following PL/SQL procedure to insert and delete rows.

However, although I am using BULK COLLECT, it still takes over 12 hours to come back and eventually returns "SNAPSHOT TOO OLD" ! Is there something wrong with the PL/SQL below :

DECLARE
 type xml_tbl is table of xmltype; 
  a_AUDITID DBMS_SQL.NUMBER_TABLE ;
  a_STRUCTID DBMS_SQL.NUMBER_TABLE ;
  a_OPERATION DBMS_SQL.VARCHAR2_TABLE ;
  a_PRIMARYKEY DBMS_SQL.VARCHAR2_TABLE ;
  a_USERID DBMS_SQL.NUMBER_TABLE ;
  a_TIMESTAMP DBMS_SQL.DATE_TABLE ;
  a_APPSIG DBMS_SQL.VARCHAR2_TABLE ;
  a_DATA DBMS_SQL.CLOB_TABLE ;
  a_ROWVERSION DBMS_SQL.VARCHAR2_TABLE ;
    a_ROWID DBMS_SQL.VARCHAR2_TABLE ;
  a_PARTDATE DBMS_SQL.DATE_TABLE ;
-- 
  g_ToDate   DATE := TO_DATE('18082016','DDMMYYYY') ; 
-- 
  l_rows number(9) := 0;
-- 
  CURSOR c_DMLAUDIT IS
  SELECT a.*,a.rowid, a.TIMESTAMP
  FROM DMLAUDIT a
  WHERE   a.TIMESTAMP < to_date('18082016','DDMMYYYY');

BEGIN
-- 
  dbms_output.enable  ;
  dbms_output.put_line ( 'Archiving Table : DMLAUDIT') ;
  dbms_output.put_line ( 'Started         : ' || to_char(sysdate,'HH24:MI:SS DD-MON-YYYY') ) ;
-- 
  OPEN c_DMLAUDIT;
  LOOP
    FETCH c_DMLAUDIT BULK COLLECT INTO 
        a_AUDITID,
        a_STRUCTID,
        a_OPERATION,
        a_PRIMARYKEY,
        a_USERID,
        a_TIMESTAMP,
        a_APPSIG,
        a_DATA,
        a_ROWVERSION,
         a_ROWID,
        a_PARTDATE
          LIMIT 500;
-- 
    FOR i IN 1..a_AUDITID.COUNT LOOP
      INSERT INTO ARCH_DMLAUDIT
             (
          AUDITID
         ,STRUCTID
         ,OPERATION
         ,PRIMARYKEY
         ,USERID
         ,TIMESTAMP
         ,APPSIG
         ,DATA
         ,ROWVERSION
         ,ARCH_TIMESTAMP
             )
      VALUES (
        a_AUDITID(i), 
        a_STRUCTID(i), 
        a_OPERATION(i), 
        a_PRIMARYKEY(i), 
        a_USERID(i), 
        a_TIMESTAMP(i), 
        a_APPSIG(i), 
        a_DATA(i), 
        a_ROWVERSION(i), 
        a_PARTDATE(i) ) ;
        
    
     
    END LOOP ;
-- 
BEGIN
    FORALL i IN 1..a_AUDITID.COUNT
      DELETE FROM DMLAUDIT
      WHERE audited = a_auditid(i) ;
      exception
          when others then
              if sqlcode= -24381 then
               for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop
               dbms_output.put_line ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );
              End loop;
           end if;
   END;
-- 
  COMMIT ;
-- 
     EXIT WHEN c_DMLAUDIT%NOTFOUND ;
  END LOOP ;
  CLOSE c_DMLAUDIT ;

END ;
/

The DELETE part is taking the longest and running it outside for just 1000 rows using a simple Cursor FOR LOOP also takes 1 hour

[b]Is the error due to the COMMIT being in the Wrong place ?
Why does DELETING only a 1000 rows take that long ?


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 11 May 2017 20:23] by Moderator

Report message to a moderator

Re: snapshot too old [message #662770 is a reply to message #662769] Thu, 11 May 2017 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
Do NOT do in PL/SQL that which can be done in plain SQL

The most common cause for ORA-01555 Snapshot Too Old error is COMMIT inside LOOP.
The SQL that reports ORA-01555 is the victim & not necessarily the culprit.
There is a LOOOOONG running SELECT.
ORA-01555 is caused by DML against same table as SELECT plus COMMIT inside LOOP.
COMMIT instructs Oracle that UNDO from DML is no longer needed & eventually it gets overwritten.
Then SELECT can no longer obtain Read Consistent view of data & ORA-01555 gets thrown
Re: snapshot too old [message #662771 is a reply to message #662770] Thu, 11 May 2017 20:38 Go to previous messageGo to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
Thank you very much for the reply.

So if I put a COMMIT outside the END LOOP , will it still be done after every 500 rows i.e the Limit for the BULK COLLECT ?

Why does DELETING only a 1000 rows take that long ?
Re: snapshot too old [message #662772 is a reply to message #662771] Thu, 11 May 2017 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
row by row is slow by slow.
DELETE statement processes only a single row.

PL/SQL is processed by its own "engine"
SQL is processed by its own "engine".
You are forcing Oracle to complete a FULL Context switch when going from SQL to PL/SQL
& then another Full Context switch going back from PL/SQL to SQL
I am not sure anyone could write a more inefficient procedure.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: snapshot too old [message #662773 is a reply to message #662772] Thu, 11 May 2017 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
IMO, the whole procedure can be replaced by a single INSERT statement followed by a single DELETE statement.
Re: snapshot too old [message #662774 is a reply to message #662773] Thu, 11 May 2017 21:02 Go to previous messageGo to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
This Procedure has been written many many years ago by a Developer no longer with the company. The PL/SQL procedure is a dynamic SQL and generated based on parameters entered

Not sure why XMLTYPE is used either for the variable declarations but I suspect its because its generic

I can provide EXPLAIN PLANS for the individual insert and Deletes and the table and index definitions but I do not have access to run SQL Trace and Prof
Re: snapshot too old [message #662775 is a reply to message #662774] Thu, 11 May 2017 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
>The PL/SQL procedure is a dynamic SQL and generated based on parameters entered
Above adds yet another layer of designed inefficiency.
Dynamic SQL must be Hard Parsed which means yet another reason it does not scale.
Re: snapshot too old [message #662777 is a reply to message #662769] Fri, 12 May 2017 01:03 Go to previous messageGo to next message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
If you adjust your 7 million row cursor to include an ORDER BY clause, that may help your ora-1555 problem as Oracle will have to materialize the query right away to sort it (a tip from MC). This might introduce other problems later.

Alternatively, it looks as though you could throw out the PL/SQL and do it with just an INSERT and a DELETE statement. The BS is usually correct about that sort of thing.
Re: snapshot too old [message #662800 is a reply to message #662777] Fri, 12 May 2017 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
An hour to delete 1000 rows indicates something is very wrong.
Doing 1000 individual deletes in a for loop shouldn't take more than a few seconds:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> set timing on
SQL> create table bob as select a.*, rownum as row_id from user_tables a, user_tables b where rownum <= 1000;

Table created.

Elapsed: 00:00:02.06
SQL> select count(*) from bob;

  COUNT(*)
----------
      1000

Elapsed: 00:00:00.04
SQL> begin

  for rec in (select * from bob) loop

    delete from bob where row_id = rec.row_id;

  end loop;

end;  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
SQL> select count(*) from bob;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>

You need to see where all that time is being spent - trace the session.
Do you have triggers on the table?
Re: snapshot too old [message #662823 is a reply to message #662800] Fri, 12 May 2017 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... or cascading constraints with ON DELETE CASCADE option.

Re: snapshot too old [message #662856 is a reply to message #662823] Sat, 13 May 2017 15:43 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if there are child tables you'll need to check if any of them have delete triggers
Re: snapshot too old [message #662875 is a reply to message #662769] Sun, 14 May 2017 16:19 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
One other possible explanation would be that the "audited" Column is not indexed
causing a full table scan of the 7 Million Row Table one row at a time from the Array.
From my experience application using some type of auditing usually do not index
the Audit Tables in order to increase the speed of the processing.

A simple SQL Report of Indexes for the Table. Report is intended to be run by the
application owner schema and can be modified as needed.


-- Script
COLUMN  table_name      FORMAT A20  HEADING 'Table|Name';
COLUMN  index_name      FORMAT A20  HEADING 'Index|Name';
COLUMN  column_name     FORMAT A20  HEADING 'Column|Name';
COLUMN  column_position FORMAT 99   HEADING 'Column|Position';
BREAK ON table_name NODUPLICATES ON index_name NODUPLICATES SKIP 1

SELECT   table_name
        ,index_name
        ,column_name
        ,column_position
FROM     user_ind_columns
WHERE    table_name = 'DMLAUDIT'
ORDER BY table_name
        ,index_name
        ,column_name
        ,column_position;
Re: snapshot too old [message #662887 is a reply to message #662875] Mon, 15 May 2017 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I just spotted that where clause. Is audited a Y/N flag? If it is then doing it in a FORALL is pretty pointless, the first execution will delete all the rows. Also if it is indexing it isn't going to do any good.
If not what does it contain?
Re: snapshot too old [message #662926 is a reply to message #662887] Mon, 15 May 2017 18:37 Go to previous messageGo to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
Thank you for all your responses
Sorry about the typo - audited is AUDITID

AUDITID is INDEXED

There are triggers on the table but just PK and Not null triggers


CREATE TABLE "SPAUS"."DMLAUDIT"
( "AUDITID" NUMBER(9,0) NOT NULL ENABLE,
"STRUCTID" NUMBER(9,0) NOT NULL ENABLE,
"OPERATION" VARCHAR2(1) NOT NULL ENABLE,
"PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
"USERID" NUMBER(9,0),
"TIMESTAMP" DATE NOT NULL ENABLE,
"APPSIG" VARCHAR2(30),
"DATA" CLOB,
"ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ENABLE,
CONSTRAINT "DMLOP48" CHECK (Operation in ('I','U','D')) ENABLE,
CONSTRAINT "FK_AUDITTABSTRUCT_DMLAUDIT" FOREIGN KEY ("STRUCTID")
REFERENCES "SPAUS"."AUDITTABSTRUCT" ("STRUCTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A"
LOB ("DATA") STORE AS (
TABLESPACE "HUB_DATA_A" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

CREATE UNIQUE INDEX "SPAUS"."SYS_IL0000091031C00008$$" ON "SPAUS"."DMLAUDIT" (
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A"
PARALLEL (DEGREE 0 INSTANCES 0) ;

CREATE INDEX "SPAUS"."XIE1DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("STRUCTID", "PRIMARYKEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A" ;

CREATE INDEX "SPAUS"."XIE2DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("STRUCTID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;

CREATE INDEX "SPAUS"."XIE3DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("TIMESTAMP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;

CREATE UNIQUE INDEX "SPAUS"."XPKDMLAUDIT" ON "SPAUS"."DMLAUDIT" ("AUDITID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;

ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "DMLOP48" CHECK (Operation in ('I','U','D')) ENABLE;

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("AUDITID" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("STRUCTID" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("OPERATION" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("PRIMARYKEY" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("TIMESTAMP" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("ROWVERSION" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ENABLE;
Re: snapshot too old [message #662927 is a reply to message #662926] Mon, 15 May 2017 19:04 Go to previous messageGo to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
Explain Plan :

SELECT STATEMENT, GOAL = ALL_ROWS 94929 3003654 324394632
SORT ORDER BY 94929 3003654 324394632
TABLE ACCESS BY INDEX ROWID SPAUS DMLAUDIT 21577 3003654 324394632
INDEX RANGE SCAN SPAUS XIE3DMLAUDIT 765 3003654
Re: snapshot too old [message #662928 is a reply to message #662927] Mon, 15 May 2017 19:59 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
When are you going to start following Posting Guidelines?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: snapshot too old [message #662929 is a reply to message #662928] Mon, 15 May 2017 20:10 Go to previous messageGo to next message
kmnainani
Messages: 6
Registered: May 2017
Junior Member
CREATE TABLE "SPAUS"."DMLAUDIT"
             (
                          "AUDITID"    NUMBER(9,0) NOT NULL ENABLE,
                          "STRUCTID"   NUMBER(9,0) NOT NULL ENABLE,
                          "OPERATION"  VARCHAR2(1) NOT NULL ENABLE,
                          "PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
                          "USERID"     NUMBER(9,0),
                          "TIMESTAMP"  DATE NOT NULL ENABLE,
                          "APPSIG"     VARCHAR2(30),
                          "DATA" CLOB,
                          "ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
                          CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE,
                          CONSTRAINT "DMLOP48" CHECK (operation IN ('I',
                                                                    'U',
                                                                    'D')) ENABLE,
                          CONSTRAINT "FK_AUDITTABSTRUCT_DMLAUDIT" FOREIGN KEY ("STRUCTID") REFERENCES "SPAUS"."AUDITTABSTRUCT" ("STRUCTID") ENABLE
             )
             PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS logging STORAGE
             (
                          INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
             )
             TABLESPACE "HUB_DATA_A" lob
             (
                          "DATA"
             )
             store AS
             (
                          TABLESPACE "HUB_DATA_A" ENABLE STORAGE IN ROW chunk 8192 pctversion 10 NOCACHE logging STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT)
             ) ;CREATE UNIQUE INDEX "SPAUS"."SYS_IL0000091031C00008$$"
ON "SPAUS"."DMLAUDIT"
                    (
                                        PCTFREE 10 INITRANS 2 MAXTRANS 255 compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_DATA_A" PARALLEL (degree 0 instances 0) ;CREATE INDEX "SPAUS"."XIE1DMLAUDIT"
                    ON "SPAUS"."DMLAUDIT"
                                 (
                                              "structid",
                                              "primarykey"
                                 )
                                 PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
                                 (
                                              INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
                                 )
                                 TABLESPACE "HUB_DATA_A" ;CREATE INDEX "SPAUS"."XIE2DMLAUDIT"
                    ON "SPAUS"."DMLAUDIT"
                                 (
                                              "structid"
                                 )
                                 PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
                                 (
                                              INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
                                 )
                                 TABLESPACE "HUB_IDX_A" ;CREATE INDEX "SPAUS"."XIE3DMLAUDIT"
                    ON "SPAUS"."DMLAUDIT"
                                 (
                                              "timestamp"
                                 )
                                 PCTFREE 10 INITRANS 2 MAXTRANS 255 compute STATISTICS STORAGE
                                 (
                                              INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
                                 )
                                 TABLESPACE "HUB_IDX_A" ;CREATE UNIQUE INDEX "SPAUS"."XPKDMLAUDIT"
                    ON "SPAUS"."DMLAUDIT"
                                        (
                                                            "auditid"
                                        )
                                        PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
                                        (
                                                            INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
                                        )
                                        TABLESPACE "HUB_IDX_A" ;ALTER TABLE "SPAUS"."dmlaudit" ADD CONSTRAINT "DMLOP48" CHECK (operation IN ('I',
                                                                                                 'U',
                                                                                                 'D')) ENABLE;ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("auditid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("structid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("operation" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("primarykey" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("timestamp" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("rowversion" NOT NULL ENABLE);ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
                    USING       INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE;


Re: snapshot too old [message #662932 is a reply to message #662929] Tue, 16 May 2017 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
You seem to have lost some carriage returns there. so fix that please.
Also post the exact code you used to test how long 1000 took.
Re: snapshot too old [message #662933 is a reply to message #662932] Tue, 16 May 2017 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
PK and NOT NULL aren't triggers, they're constraints.
Re: snapshot too old [message #662936 is a reply to message #662933] Tue, 16 May 2017 06:17 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
You wrote your code in the most ineffective way possible. The whole thing can be done by using the following code
INSERT INTO ARCH_DMLAUDIT
             (
          AUDITID
         ,STRUCTID
         ,OPERATION
         ,PRIMARYKEY
         ,USERID
         ,TIMESTAMP
         ,APPSIG
         ,DATA
         ,ROWVERSION
         ,ARCH_TIMESTAMP
             )
select AUDITID,STRUCTID,OPERATION,PRIMARYKEY,USERID,TIMESTAMP,APPSIG,
       DATA,ROWVERSION,ROWID,PARTDATE
FROM DMLAUDIT a
  WHERE   a.TIMESTAMP < to_date('18082016','DDMMYYYY');

delete from DMLAUDIT a
WHERE   a.TIMESTAMP < to_date('18082016','DDMMYYYY');

commit;
An easier way to do it is to have the DMLAUDIT partitioned by timestamp with an interval of a month. Also the ARCH_DMLAUDIT would be partitioned. Then simply move the partition from the active table to the archive table. Very quick. The following link is one way to do it. To give a version specific answer we would need your database version.

https://www.akadia.com/services/ora_exchange_partition.html

[Updated on: Tue, 16 May 2017 06:25]

Report message to a moderator

Re: snapshot too old [message #662938 is a reply to message #662929] Tue, 16 May 2017 07:52 Go to previous messageGo to next message
joy_division
Messages: 4802
Registered: February 2005
Location: East Coast USA
Senior Member
kmnainani wrote on Mon, 15 May 2017 21:10
CREATE TABLE "SPAUS"."DMLAUDIT"
             (
                          "AUDITID"    NUMBER(9,0) NOT NULL ENABLE,
                          "STRUCTID"   NUMBER(9,0) NOT NULL ENABLE,
                          "OPERATION"  VARCHAR2(1) NOT NULL ENABLE,
                          "PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
                          "USERID"     NUMBER(9,0),
                          "TIMESTAMP"  DATE NOT NULL ENABLE,
                          "APPSIG"     VARCHAR2(30),
                          "DATA" CLOB,
                          "ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
                          CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE,
...
ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("auditid" NOT NULL ENABLE);

ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("structid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("operation" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("primarykey" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("timestamp" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("rowversion" NOT NULL ENABLE);ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
These will fail. NEVER put columns in double quotes, especially lower case identifiers.

SQL> create table g123 (foo1 varchar2(10));

Table created.

SQL> alter table g123 modify "foo1" not null;
alter table g123 modify "foo1" not null
                        *
ERROR at line 1:
ORA-00904: "foo1": invalid identifier
Re: snapshot too old [message #662952 is a reply to message #662938] Wed, 17 May 2017 06:27 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
I just noticed that you are saving your rowid into the archive table. Why are you doing that. outside of the immediate transaction there are no guarantees that oracle wont move data and have the rowid change. useless information.
Re: snapshot too old [message #662958 is a reply to message #662952] Wed, 17 May 2017 07:02 Go to previous message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's being selected but not inserted.
Previous Topic: ListAgg results
Next Topic: Operating with columns within a table
Goto Forum:
  


Current Time: Thu Dec 14 14:38:05 CST 2017

Total time taken to generate the page: 0.02258 seconds