Skip to Main Content
  • Questions
  • ORA-32792: prebuilt table managed column cannot be an identity column - Any potential fixes

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nicholas.

Asked: March 30, 2023 - 5:00 am UTC

Last updated: April 04, 2023 - 5:41 am UTC

Version: 12c

Viewed 100+ times

You Asked

Dear Tom,
I am tasked with looking at how to keep 2 schemas of differing versions in sync, without the use of GoldenGate. Schema A uses a Sequence and a trigger to add the sequence next value to the ID column if it empty. Schema B uses an Identity configuration so that it is no longer necessary to use the sequence and trigger functionality.

When creating a MATERIALIZED VIEW over the Schema B table it errors with the following

ORA-32792: prebuilt table managed column cannot be an identity column


Is there a way to enable this functionality so that I can use this synchronization functionality?

with LiveSQL Test Case:

and Chris said...

I don't know of a way around this... but I'm also confused as to what you're trying to do!

The statement:

CREATE MATERIALIZED VIEW 
    objecttypes_mv 
ON PREBUILT TABLE 
REFRESH FAST ON COMMIT 
as  
    SELECT *  
    FROM objecttypes


Changes objecttypes_mv from a regular table to a materialized view over objecttypes. After doing this you can no longer insert into objecttypes_mv - the data comes from objecttypes. So there's no need to have an identity column on objecttypes_mv.

So what are you trying to achieve by turning the table into an MV?

Rating

  (2 ratings)

Comments

Nicholas Irving, March 30, 2023 - 9:04 pm UTC

The livesql does not allow me to create 2 different schemas so I am simulating that by naming the 2nd schema _mv and I have simplified the schema so I don't show all the transform I am having to do to translate a JSON stored in a clob. I can provide a more complex example of the transform.if required,

I am showing the error I am getting when creating the MV in the 2nd schema which would be the same name but without _mv.

The reason for the 2nd schema is that we are migrating from v5.5 to v7.1.4 of an application and they don't want the added cost of creating a new database and the associated hardware costs. Happy to pay for more storage, just not the compute.

In the complex example the data in the first schema JSON clob column is extracted and converted into columns of the 2nd schema table. GoldenGate is not possible, as I would simply the do the migration using that and reading other comments MV is the way to go as we can drop the MV and still keep the underlying data, indexes and sequences meaning the migration is transparent to the updates version of the software.

Does that make sense?

Chris Saxon
March 31, 2023 - 5:04 pm UTC

I see what you're trying to do; I still don't understand why you're trying to use a materialized view to do it!

I would simply the do the migration using that and reading other comments MV is the way to go as we can drop the MV and still keep the underlying data, indexes and sequences meaning the migration is transparent to the updates version of the software.

Perhaps I'm missing something, but I doubt it's that straightforward in practice. In particular, it assumes that you can have a fast refresh on commit MV. Depending on what exactly the transformation is, it may not be possible.

Have you considered using edition-based redefinition?

This is designed for you to do schema migrations with zero downtime & avoid the hassle/overhead of creating a second schema. Unlike the MV approach this also allows you to run both versions of the app at the same time.

If you're dead set on using MVs, then rather than identity columns use sequence defaults. In effect these are the same. A big difference is you can assign sequence defaults whenever you want. Identities can only be applied to new columns.

So you can create the table & make an MV on top of it. When it comes to the switchover, drop the MV and modify the column to use the sequence.

Nicholas Irving, March 31, 2023 - 8:59 pm UTC

Thanks asktom. Currently we are not concerned about the refresh delay, just more about getting the data into the new schema tables and transformed.

EBR sounds perfect as it removes the problem of rollback, just need to work out how to do the transformation of the JSON data in the CLOB on xx million records, hence why the Materialized View was so attractive to us, it was taken care of OOTB and gave us that refresh capability without having to figure out the deltas between creating the new schema and going live into production.

EBR is the way to go, if we can't get it working then we have the fallback of MV and Sequence update, or just a plain ETL into a staging table.

You have given us a new direction and focus and for that you have my grateful thanks
Connor McDonald
April 04, 2023 - 5:41 am UTC

glad to be of service - keep us posted on how it goes

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.