Error in materialized view or zonemap refresh path

ORA-12008 and ORA-00942 Error Reported During Materialized View Refresh Across Schema (Doc ID 2559390.1)

Last updated on FEBRUARY 28, 2021

Applies to:

Symptoms

When usera has a base table, userb has a materialized view based on usera’s table.
userc executed userb’s materialized view, and failed with ORA-12008 and ORA-00942.

The test case is as following:

Event when using sysdba to execute userb’s materialized view, still failed.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Materialized View Refresh Failing with errors ORA-12008 ORA-31600 ORA-30372 (Doc ID 2738707.1)

Last updated on JULY 21, 2022

Applies to:

Symptoms

On : 19.3.0.0.0 version, Materialised Views

Materialized ViewВ Refresh Failing below errors.

The creation of the materialized view works fine, but the refresh fails.

SQL> exec BEGIN DBMS_MVIEW.REFRESH(‘ .xxx’, method => ‘CF’,atomic_refresh => FALSE,out_of_place => TRUE); END;
BEGIN BEGIN DBMS_MVIEW.REFRESH(‘VALUATION.WS_PROPERTY_SEARCH_VW’, method => ‘CF’,atomic_refresh => FALSE,out_of_place => TRUE); END; END;

*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3012
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2424
ORA-31600: invalid input value COMPATIBLE for parameter VERSION in function
OPEN
ORA-06512: at «SYS.DBMS_SNAP_INTERNAL», line 1487
ORA-06512: at «SYS.DBMS_METADATA», line 6516
ORA-06512: at «SYS.DBMS_SYS_ERROR», line 105
ORA-06512: at «SYS.DBMS_METADATA», line 6422
ORA-06512: at «SYS.DBMS_METADATA», line 6762
ORA-06512: at «SYS.DBMS_SNAP_INTERNAL», line 1427
ORA-06512: at line 1
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 88
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 253
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2405
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 2968
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3255
ORA-06512: at «SYS.DBMS_SNAPSHOT_KKXRCA», line 3287
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 16
ORA-06512: at line 1
В

Changes

Refresh works fine with «USING TRUSTED CONSTRAINTS» , but fails when MView created with «USING ENFORCED CONSTRAINTS» .

ORA-30372 error found on internal objects only.

No policy found, No Fine grained access policy found on objects or no VPD access found.В

And With «out_of_place» => FALSE works fine but TRUE fails.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Ora-12008 And Ora-01732 When Doing Complete Refresh On Materialized View (Doc ID 732614.1)

Last updated on FEBRUARY 01, 2022

Applies to:

Oracle Database — Enterprise Edition — Version 10.2.0.2 and later
Oracle Database Cloud Schema Service — Version N/A and later
Oracle Database Exadata Cloud Machine — Version N/A and later
Oracle Cloud Infrastructure — Database Service — Version N/A and later
Oracle Database Backup Service — Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.В

When try to refresh the materialized view with complete method, the following error is displayed:В

SQL> EXEC DBMS_MVIEW.REFRESH(‘ ‘);
BEGIN DBMS_MVIEW.REFRESH(‘ ‘); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01732: data manipulation operation not legal on this view
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2255
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2461
ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2430
ORA-06512: at line 1

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-12008: error in materialized view refresh path

I am a newbie DBA and while doing daily health check found the below errors.

ORA-12012: error on auto execute of job 576872

ORA-12008: error in materialized view refresh path

ORA-00980: synonym translation is no longer valid

ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2256

ORA-06512: at «SYS.DBMS_SNAPSHOT», line 2462

ORA-06512: at «SYS.DBMS_IREFRESH», line 685

ORA-06512: at «SYS.DBMS_REFRESH», line 195

ORA-06512: at line 1

now I can only understand that error occurred in some materialized view refresh job.

Any help to dig down the issue and with some solution for the same?

Answers

I think you have a problem with a synonym:

ORA-00980: synonym translation is no longer valid Cause: A synonym did not translate to a legal target object. This could happen for one of the following reasons:

1. The target schema does not exist.

2. The target object does not exist.

3. The synonym specifies an incorrect database link.

4. The synonym is not versioned but specifies a versioned target object.

Action: Change the synonym definition so that the synonym points at a legal target object.

thanks a bunch . Any help in finding in which job and in which mview it is located and has failed?

DB version: Oracle 10.2.0.5

OS:SUn solaris 5.10

You could check the alert log for starters (but it looks like you did already).

Also, since you have the job identifier, you could query DBA_JOBS or ALL_JOBS

here’s the query from DBA_JOBS

SQL> select JOB,BROKEN,FAILURES,SCHEMA_USER,LAST_DATE,LAST_SEC,INTERVAL from DBA_JOBS where JOB=576872;

JOB BROKEN FAILURES SCHEMA_USER LAST_DATE LAST_SEC INTERVAL

576872 N 14 APOLLO 02-JUL-14 18:20:14 SYSDATE + 1

I think i am asking too much, but it would be great if you could also help me out finding the failed materialized view refresh name. please

I found the owner and the related mview also.

OWNER MVIEW_NAME LAST_REFRESH

APOLLO MV_APOLLO_RES 08-JUL-14

APOLLO MV_BSE3 08-JUL-14

APOLLO MV_BSE2 08-JUL-14

APOLLO MV_MIN 08-JUL-14

APOLLO MV_MINAPN 08-JUL-14

APOLLO MV_BSE 08-JUL-14

APOLLO MV_NODIR 08-JUL-14

want to know what caused the problem and want to dig down to the root cause now.

Could be something got dropped, could be something changed in a manner different than you expected. I’ve seen the latter in obscure circumstances like a procedure is in use, so it is not flushed from the library cache when someone tries to recompile it (or even flush the cache), and some other procedure is recompiled and expects it to have changed. Or something like that, I get these things backwards sometimes. The key to understanding a particular situation is to understand everything that can invalidate a synonym.

>want to know what caused the problem

somebody changed something.

software does not spontaneously break.

BTW, all changed data can be found using DBMS_LOGMNR

or you could ask who did DDL that broke this job.

Источник

DBMS_REDEFINITION package throwing error ORA-12008 & ORA-01410: invalid ROWID (Doc ID 1572193.1)

Last updated on APRIL 09, 2020

Applies to:

Symptoms

Trying to partition a table with a LOB column using the DBMS_REDEFINITION package and getting the below error message

exec dbms_redefinition.start_redef_table( uname => ‘TEST’, orig_table => ‘TAB1’, int_table => ‘INTERIM_PARTITION’, options_flag => dbms_redefinition.cons_use_pk );
BEGIN dbms_redefinition.start_redef_table( uname => ‘TEST’, orig_table => ‘TAB1’, int_table => ‘INTERIM_PARTITION’, options_flag => dbms_redefinition.cons_use_pk ); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01410: invalid ROWID
ORA-06512: at «SYS.DBMS_REDEFINITION», line 50
ORA-06512: at «SYS.DBMS_REDEFINITION», line 1343
ORA-06512: at line 1

Changes

Tablespace has been created with large extent size 1025 M( > 1 GB )

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Some time ago I wrote a blog note describing a hack for refreshing a large materialized view with minimum overhead by taking advantage of a single-partition partitioned table. This note describes how Oracle 12c now gives you an official way of doing something similar – the “out of place” refresh.

I’ll start by creating a matieralized view and creating a couple of indexes on the resulting underlying table; then show you three different calls to refresh the view. The materialized view is based on all_objects so it can’t be made available for query rewrite (ORA-30354: Query rewrite not allowed on SYS relations) , and I haven’t created any materialized view logs so there’s no question of fast refreshes – but all I intend to do here is show you the relative impact of a complete refresh.

rem
rem     Script:         12c_mv_outofplace_3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2013
rem     Purpose:        
rem
rem     Last tested
rem             12.1.0.2
rem

create materialized view mv_objects nologging
build immediate
refresh on demand
as
select
        *
from
        all_objects
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'mv_objects',
                method_opt       => 'for all columns size 1'
        );
end;
/

create index mv_obj_i1 on mv_objects(object_name) nologging compress;
create index mv_obj_i2 on mv_objects(object_type, owner, data_object_id) nologging compress 2;

This was a default install of 12c, so there were about 85,000 rows in the view. You’ll notice that I’ve created all the objects as “nologging” – this will have an effect on the work done during some of the refreshes.

Here are the three variants I used – all declared explicitly as complete refreshes:

begin
        dbms_mview.refresh(
                list                    => 'MV_OBJECTS',
                method                  => 'C',
                atomic_refresh          => true
        );
end;
/

begin
        dbms_mview.refresh(
                list                    => 'MV_OBJECTS',
                method                  => 'C',
                atomic_refresh          => false
        );
end;
/

begin
        dbms_mview.refresh(
                list                    => 'MV_OBJECTS',
                method                  => 'C',
                atomic_refresh          => false,
                out_of_place            => true
        );
end;
/

The first one (atomic_refresh=>true) is the one you have to use if you want to refresh several materialized views simultaneously and keep them self consistent, or if you want to ensure that the data doesn’t temporarily disappear if all you’re worried about is a single view. The refresh works by deleting all the rows from the materialized view then executing the definition to generate and insert the replacement rows before committing. This generates a lot of undo and redo – especially if you have indexes on the materialized view as these have to be maintained “row by row” and may leave users accessing and applying a lot of undo for read-consistency purposes. An example at a recent client site refreshed a table of 6.5M rows with two indexes, taking about 10 minutes to refresh, generating 7GB of redo as it ran, and performing 350,000 “physical reads for flashback new”. This strategy does not take advantage of the nologging nature of the objects and, as a side effect of the delete/insert cycle, you’re likely to see the indexes grow to roughly twice their optimal size and you may see the statistic “recursive aborts on index block reclamation” climbing as the indexes are maintained.

The second option (atomic_refresh => false) is quick and efficient – but may result in wrong results showing up in any code that references the materialized view (whether explicitly or by rewrite). The session truncates the underlying table, sets any indexes on it unusable, then reloads the table with an insert /*+ append */. The append means you get virtually no undo generated, and if the table is declared nologging you get virtually no redo. In my case, the session then dispatched two jobs to rebuild the two indexes – and since the indexes were declared nologging the rebuilds generated virtually no redo. (I could have declared them with pctfree 0, which would also have made them as small as possible).

The final option is the 12c variant – the setting atomic_refresh => false is mandatory if we want  out_of_place => true. With these settings the session will create a new table with a name of the form RV$xxxxxx where xxxxxx is the hexadecimal version of the new object id, insert the new data into that table (though, strangely, not using the /*+ append */ hint), create the indexes on that table (again with names like RV$xxxxxx – where xxxxxx is the index’s object_id). Once the new data has been indexed Oracle will do some name-switching in the data dictionary (shades of exchange partition) to make the new version of the materialized view visible. A quirky detail of the process is that the initial create of the new table and the final drop of the old table don’t show up in the trace file  [Ed: wrong, see comment #1] although the commands to drop and create indexes do appear. (The original table, though it’s dropped after the name switching, is not purged from the recyclebin.) The impact on undo and redo generation is noticeable but not hugely significant. Because the table is empty and has no indexes when the insert takes place the insert creates a lot less undo and redo than it would if the table had been emptied by a bulk delete – even though the insert is a normal insert and not an append; then the index creation honours my nologging definition so produces very little redo. At the client site above, the redo generated dropped from 7GB to 200MB, and the time dropped to 200 seconds which was 99% CPU time.

Limitations, traps, and opportunities

The manuals say that the out of place refresh can only be used for materialized views that are joins or aggregates and, surprisingly, it’s correct – you actually can’t use the method on a view that simply extracts a subset of rows and columns from a single table.  There’s a simple workaround, though – join the table to dual (or some other single row table if you want to enable query rewrite).

Because the out of place refresh does an ordinary insert into a new table the resulting table will have no statistics – you’ll have to add a call to gather them. (If you’ve previously been using a non-atomic refreshes this won’t be a new problem, of course). The indexes will have up to date statistics, of course, because they will have been created after the table insert.

The big opportunity, of course, is to change a very expensive atomic refresh into a much cheaper out of place refresh – in some special cases. My client had to use the atomic_refresh=>true option in 11g because they couldn’t afford to leave the table truncated (empty) for the few minutes it took to rebuild; but they might be okay using the out_of_place => true with atomic_refresh=>false in 12c because:

• the period when something might break is brief
• if something does go wrong the users won’t get wrong (silently missing) results, they’ll an Oracle error (probably ORA-08103: object no longer exists)
• the application uses this particular materialized view directly (i.e. not through query rewrite), and the query plans are all quick, light-weight indexed access paths
• most queries will probably run correctly even if they run through the moment of exchange

I don’t think we could guarantee that last statement – and Oracle Corp. may not officially confirm it – and it doesn’t matter how many times I show queries succeeding. Thanks to “cross-DDL read-consistency” as it was called in 8i when partition-exchange appeared and because the old objects still exist in the data files, provided your query doesn’t hit a block that has been overwritten by a new object, or request a space management block that was zero-ed out on the “drop” a running query can keep on using the old location for an object after it has been replaced by a newer version. If you want to make the mechanism as safe as possible you can help improve your chances by putting each relevant materialized view (along with its indexes) into its own tablespace so that the only thing that is going to overwrite an earlier version of the view is the stuff you create on the next refresh.