Skip to content

Upgrade your Database - NOW!

Mike Dietrich's Blog About Oracle Database Upgrades… Mostly

  • Blog
  • Slides
  • Hands-On Lab
    • 1. Setup
    • 2. Load
    • 3. Capture and Preserve
    • 4. AutoUpgrade
    • 5. AWR Diff
    • 6. SQL Perf Analyzer
    • 7. SQL Plan Mgmt
    • 8. SQL Tuning Advisor
    • 10. Plugin UPGR => CDB2
    • 11. Migrate FTEX
    • 12. Un-/ Plug / Upgrade
    • 13. Fallback Strategies
      • 14a. Issues During Upgrade
      • 14b. Issues After Upgrade
    • 15. AutoUpgrade – Special
    • AutoUpgrade – HOL 19c – Japanese
  • Events
  • Papers / Docs
  • Videos
  • Scripts
  • Links
  • Oracle Documentation
  • Privacy
    • Cookie Policy (EU)
    • GDPR – Request personal data
  • About

ORACLE_MAINTAINED attribute can be pretty helpful

Posted on March 21, 2018 by Mike.Dietrich General 6

You may have seen my two blog posts about Oracle supplied objects and Oracle supplied user schema and roles a few days ago, One person commented asking about what to do with these. And I agree: It looks a bit harsh to deal with two external files to query Oracle supplied stuff. But actually (as Patrick Hurley commented as well) there is a much easier way since Oracle Database 12c to deal with objects created and supplied by Oracle: the ORACLE_MAINTAINED attribute can be pretty helpful.

ORACLE_MAINTAINED attribute can be pretty helpful

ORACLE_MAINTAINED attribute can be pretty helpful

In Oracle Database 12.1.0.1 we added a column to several dictionary views: ORACLE_MAINTAINED. And of course others have blogged about it already a while ago:

  • https://martincarstenbach.wordpress.com/2013/07/17/dba_users-oracle_maintained-in-12c/
  • https://www.markusdba.net/?p=185

These tables have this helpful ORACLE_MAINTAINED column attribute in common. We mark objects and schemas and roles and users maintained by Oracle with it. You find a full list of dictionary views containing the column below:

  • CDB_OBJECTS
  • DBA_OBJECTS
  • ALL_OBJECTS
  • USER_OBJECTS
  • CDB_INVALID_OBJECTS
  • DBA_INVALID_OBJECTS
  • ALL_PROBE_OBJECTS
  • CDB_USERS
  • DBA_USERS
  • ALL_USERS
  • USER_USERS
  • CDB_ROLES
  • DBA_ROLES
  • CDB_OBJECTS_AE (for EBR)
  • DBA_OBJECTS_AE (for EBR)
  • ALL_OBJECTS_AE (for EBR)
  • USER_OBJECTS_AE (for EBR)

At the moment we use this ORACLE_MAINTAINED column in a transportable tablespace project. With ORACLE_MAINTAINED we determine whether we want to gather the object definition with DBMS_METADATA.GET_DDL or not. If yes, we generate the creation scripts.

Things to be aware of

Please be aware that for instance a Data Pump export may not export certain objects – those who are marked as ORACLE_MAINTAINED:

  • MOS Note:2114233.1 – Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655

In addition Data Pump ignores objects in KU_NOEXP_VIEW which is a UNION of KU_NOEXP_TAB and NOEXP$.

Be aware (I received this question just a week ago) that APEX is classified as ORACLE_MAINTAINED as well. In case you’ll do an expdp/impdp migration you’ll have to migrate your APEX applications with the APEX interface.

  • MOS Note:1912162.1 – 12.1.0.1 EXPDP Does Not Export APEX Schema

–Mike

Share this:
  •  
  •  

Related

Tags: APEXDBMS_METADATADBMS_METADATA.GET_DDLexpdpGET_DDLimpdpORACLE_MAINTAINEDTTS

  • Next Upgrade to Oracle 12.2.0.1: Check your DEFAULT temporary tablespaces
  • Previous When will Oracle Database 18c be available on-prem?

You may also like...

  • How to get the Master Table from a Data Pump expdp?

    How to get the Master Table from a Data Pump expdp?

  • Transportable Tablespaces – Does it work between SE2 and EE?

    Transportable Tablespaces – Does it work between SE2 and EE?

  • Does Data Pump import only serially into PDBs?

    Does Data Pump import only serially into PDBs?

6 Responses

  • Comments6
  • Pingbacks0
  1. Dietmar Aust says:
    March 27, 2019 at 16:22

    This behaviour is great and all for the regular use case where you want to protect people from harming themselves. But in the case that you know what you are doing this is severly limiting your options … there should be a least a switch to choose otherwise.

    Reply
    • Mike.Dietrich says:
      April 1, 2019 at 20:03

      Dietmar,

      not sure what you concern is here.
      Actually we are using the attribute as well, e.g. during the preupgrade.jar checks.

      Thanks,
      Mike

      Reply
  2. Eliana says:
    February 3, 2021 at 14:18

    hi Mike i got a db with apex users set ORACLE_MAINTAINED = ‘N’, and i want to change it to ‘Y’.
    Is it possible to change the value of ORACLE_MAINTAINED for apex users? I have not found documentation about it for days.

    Reply
    • Mike.Dietrich says:
      February 7, 2021 at 22:04

      Hi Eliana,

      why do you want to change the APEX user to ORACLE_MAINTAINED?
      You should never manipulate the dictionary by yourself.

      Can you tell me a bit more about your motivation please?

      Cheers,
      Mike

      Reply
      • Eliana says:
        February 7, 2021 at 23:00

        Hi Mike, I haven’t changed it. I am given a database in this state, with the apex users with the column value oracle_maintained = ‘N’.

        The db is migrated from 11g to 18.c.
        All the installations we have of Apex 19.2 on db 18.3, are installed with that column in ‘Y’ for these users. Default.

        Looking for a reason why objects in the apex schemas are invalidated, I find that these users have been manipulated.

        As a DBA, making apex have oracle_maintained = ‘Y’ as Oracle does by default, resolves no one to manipulate these users again.

        I did not change them, someone did.
        I try to reassure it by assigning ‘Y’.

        Perhaps the only alternative is to remove apex and reinstall to get it right.

        Is there another way to set the default value back to those users?

        thanks for your answer

        Reply
        • Mike.Dietrich says:
          February 24, 2021 at 10:35

          Hi Eliana,

          I guess the only supported way – as you proposed – would be to remove APEX, and reinstall it afterwards again.
          Make sure you export applications and images if there are any inside as they’d be lost when you remove APEX.

          Cheers,
          Mike

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archives

Categories

Upcoming Events

  1. Converged Databases and Upgrade to Oracle 19c – Kiev, Ukraine

    March 18 @ 10:00 - 13:00
  2. Virtual Classroom: Performance Testing Using the Oracle Cloud for Upgrades and Migrations

    May 19 @ 10:00 - 12:00

View All Events

Upgrade @YouTube

RSS RSS Feed for Upgrade your Database – NOW!

  • Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0
  • Important Recommended Patches for Oracle Database 19c
  • Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE
  • Virtual Classroom Series – Episode 2: Database Upgrade on Feb 18
  • Virtual Classroom Episode 8: Upgrade Internals and so much more

Subscribe via Email

Join 5,038 other subscribers

Follow me on Twitter

My Tweets
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

Recent Posts

  • Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0
  • Important Recommended Patches for Oracle Database 19c
  • Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE
  • Virtual Classroom Series – Episode 2: Database Upgrade on Feb 18
  • Virtual Classroom Episode 8: Upgrade Internals and so much more

Upgrade your Database – NOW! © 2021. All Rights Reserved.