Skip to Content
avatar image
Former Member

brspace tbreorg fails with ORA-12015: cannot create a fast refresh materialized view from a complex query

Hi all

I tried to run a table reorg using the following command last night:

brspace -u / -c force -f tbreorg -a long2lob -t TST03 -NBR -e 88 > TST03_reorg.log

This has worked successfully for other tables in the system.

However, I got the following error:

BR0301E SQL error -42008 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPS1P"', ORIG_TABLE => '"TST03"', INT_TABLE => '"TST03#$"', COL_MAPPING => '"DCLIENT" "D

CLIENT", "DNAME" "DNAME", "DPART" "DPART", "DROWNO" "DROWNO", "DDATALEN" "DDATALEN", TO_LOB("DCONTENT") "DCONTENT"', OPTIONS_FLAG => DBMS_REDEFI

NITION.CONS_USE_ROWID); END;'

ORA-42008: error occurred while instantiating the redefinition

ORA-12015: cannot create a fast refresh materialized view from a complex query

ORA-06512: at "SYS.DBMS_REDEFINITION", line 50

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2013-01-23 19.12.34

BR0640I SQL statement waiting for busy resource:

'ALTER TABLE "SAPS1P"."TST03" PARALLEL 1'

ORA-00054: resource busy and acquire with NOWAIT specified

BR0280I BRSPACE time stamp: 2013-01-23 19.16.36

BR1106E Reorganization of table SAPS1P.TST03 failed

Now, I did half expect the "busy resource" error as SAP was still running and this is an exceptionally busy test system.

But the other errors come BEFORE this. Are they just red herrings - i.e. if I ran this with SAP down might this work? (this isn't something I can just simply test due to rare outage schedules). I need to know that it will work with SAP down (especially as it'll be my colleague running it as I will be away on holiday!).

All I can find for ORA-12015 talks about changing the DDL commands but obviously I can't do this, it's generated by brconnect...

Thanks

Ross

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 24, 2013 at 11:20 AM

    Hi Ross,

    if you administer a busy database you will see this error from time to time. It is nothing to worry about, but it can be quite annoying when it stops you from doing something.


    It simply means that session has a lock on an object that your session is trying to update (or lock). More specifically, your session has asked for a lock on the same object, but has specified the 'nowait' clause. The nowait clause, upon finding something locked, returns an error, rather than waiting for the lock to be released.


    Your options are as follows:

    • Make a cup of tea and try again later. Hopefully whatever was locking the object will have finished, and you can carry on.
    • Attempt to find out who/what is locking the object and kill it off. Use the below link to find the culprit.

    http://www.shutdownabort.com/dbaqueries/Performance_Locks_DML.php#Show-locked-objects

    Please check whether any system logs or any dump is occuring. Secondly, please try to increase the MAXOPENCURSORS value using the following command.

    open init.ora file from your oracle_home -> increase the value of open_cursors to much higher value.

    You can find few information about the error with the below link.

    http://it.toolbox.com/wiki/index.php/Ora_00054

    I hope it helps.

    Thanks and Regards,

    Vimal

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Vimal

      Thanks for your reply but I'm wondering if you have misunderstood my question?

      I KNOW what the ORA-00054: resource busy error is about already, and when we come to do this 'for real' we can have the database running but SAP down to avoid this.

      What I was asking was whether the ORA-12015: cannot create a fast refresh materialized view from a complex query message is one to worry about? Could this still occur with SAP down?

      Thanks


      Ross