ORA-12838: cannot read/modify an object after modifying it i

All posts relating to Oracle database administration.

Moderator: Tim...

ORA-12838: cannot read/modify an object after modifying it i

Postby kytemaniac » Wed Mar 27, 2013 12:57 pm

my rdbms version is as follow

Code: Select all
SYS@shielo>select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SYS@shielo>



currently I have the problem of ORA-12838,
Code: Select all
INSERT /*+ APPEND */ INTO tableA
select * from tableB;

INSERT /*+ APPEND noparallel (tableC) */ INTO tableC
select /*+ APPEND noparallel (tableA) */ * from tableA;

ORA-12838: cannot read/modify an object after modifying it in parallel



so is there a way to to insert table A with nologging and later insert into tableC with no logging and no parallel hints ?

any assistance is greatly appreciate!

thanks
kytemaniac
Senior Member
 
Posts: 234
Joined: Tue May 19, 2009 12:59 am

Re: ORA-12838: cannot read/modify an object after modifying

Postby Tim... » Wed Mar 27, 2013 3:25 pm

Hi.

I'm confused. You are talking about nologging, but you are using APPEND. The APPEND hint isn't about suppressing logging??? Also, you have APPEND in a SELECT. What is that for? It is not a valid hint for SELECT.

You can't query a table you have loaded with APPEND or PARALLEL unless the changes have been committed.

Code: Select all
test@db11g> CREATE TABLE t1 (id NUMBER);

Table created.

test@db11g> INSERT /*+ APPEND */ INTO t1
  2  SELECT level
  3  FROM   dual
  4  CONNECT BY level <=10;

10 rows created.

test@db11g> SELECT * FROM t1;
SELECT * FROM t1
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


test@db11g> COMMIT;

Commit complete.

test@db11g> SELECT * FROM t1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

test@db11g>


That's the rule.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-12838: cannot read/modify an object after modifying

Postby kytemaniac » Wed Mar 27, 2013 4:34 pm

well my supervisor who has more than 10 years of experience told me to do so.

from http://oracleexamples.wordpress.com/201 ... ng-option/

on one hand it said /*+ append */

but from

http://hemantoracledba.blogspot.sg/2010 ... -hint.html

it says no.

well how should i generate no logging? alter table <tableA> nologging?

then insert /*+ append */

looks like I'm in deep deep trouble right now :(

from http://docs.oracle.com/cd/E11882_01/ser ... m#i1106265 it only mentioned about create table no logging.

how should I resolve this bugging issues and the actual queries is running for at least 3 hrs! Ouch! :(

thanks for the advice!
kytemaniac
Senior Member
 
Posts: 234
Joined: Tue May 19, 2009 12:59 am

Re: ORA-12838: cannot read/modify an object after modifying

Postby Tim... » Wed Mar 27, 2013 5:54 pm

Hi.

Well, either your supervisor has not told you the whole story, or you have misunderstood him/her.

If the database is running on NOARCHIVELOG mode, then using just the APPEND hint will reduce redo generation. In reality, you will rarely run database in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation. It just doesn't. If you want to reduce redo generation you need to set the table to be NOLOGGING.

To prove this compare these examples. First, the database running in NOARCHIVELOG mode.

Code: Select all
SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1
  2  SELECT * FROM all_objects;

56295 rows created.


Statistics
----------------------------------------------------------
        416  recursive calls
       7938  db block gets
      63665  consistent gets
          0  physical reads
    6646764  redo size
        839  bytes sent via SQL*Net to client
        792  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1501  sorts (memory)
          0  sorts (disk)
      56295  rows processed

SQL> INSERT /*+ APPEND */ INTO t1
  2  SELECT * FROM all_objects;

56295 rows created.


Statistics
----------------------------------------------------------
        207  recursive calls
       1102  db block gets
      61588  consistent gets
          0  physical reads
      20932  redo size
        824  bytes sent via SQL*Net to client
        806  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1500  sorts (memory)
          0  sorts (disk)
      56295  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>


So the redo is reduced. Awesome. APPEND reduces redo then, right? Wrong. In a production system using ARCHIVELOG mdoe we get this.

Code: Select all
SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1
  2  SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        613  recursive calls
      11792  db block gets
     116808  consistent gets
          2  physical reads
   10222352  redo size
        370  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3142  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> INSERT /*+ APPEND */ INTO t1
  2  SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
   10222864  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>


Shoot! It didn't reduce redo any longer. How do I get the redo generation to be reduced again?

Code: Select all
SQL> CREATE TABLE t1 NOLOGGING AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1
  2  SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        506  recursive calls
      11790  db block gets
     116652  consistent gets
          0  physical reads
   10222328  redo size
        373  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3139  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> INSERT /*+ APPEND */ INTO t1
  2  SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
      25968  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>


So creating the table as NOLOGGING makes the behavior return. So, except in the special case of a NOARCHIVELOG mode database, APPEND doesn't really control redo generation. It is the presence of the NOLOGGING setting on the table that determines if using the APPEND hint has any impact on the redo generation.

The NOLOGGING hint you are trying to use is not valid. Oracle will ignore it. It's just junk. Using APPEND in a SELECT is junk also.

Note. In my last example I created the table using the NOLOGGING clause. If it was an existing table I would do the following to switch the logging state of the table:

Code: Select all
ALTER TABLE t1 NOLOGGING;
ALTER TABLE t1 LOGGING;


Regarding the "bugging issues", this is not a bug. This is the way this is documented. It is working as it should.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 8 guests