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
  • 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 7

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...

  • Transportable Tablespaces: Keep tablespace files read-only

    Transportable Tablespaces: Keep tablespace files read-only

  • Data Pump Bundle Patches: You may need to download and apply again

    Data Pump Bundle Patches: You may need to download and apply again

  • Data Pump: The Time Zone Pitfalls

7 Responses

  • Comments7
  • 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
          • eliana says:
            February 24, 2021 at 12:03

            We did that, seeking to normalize and protect the environment. Thanks Mike for your response and your publications that contribute so much to us. Greetings!

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

Upgrade @YouTube

RSS RSS Feed for Upgrade your Database – NOW!

  • Suppress nasty error messages and traces during datapatch
  • MRP3 for Oracle 19.17.0 adds an interesting surprise
  • Patching my environments with the January 2023 Bundle Patches
  • Important alert for Oracle Database and GI RU 19.18.0 on Linux
  • Rolling back or removing all patch SQL changes

Subscribe via Email

Join 6,577 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

  • Suppress nasty error messages and traces during datapatch
  • MRP3 for Oracle 19.17.0 adds an interesting surprise
  • Patching my environments with the January 2023 Bundle Patches
  • Important alert for Oracle Database and GI RU 19.18.0 on Linux
  • Rolling back or removing all patch SQL changes

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

Manage Cookie Consent
We use cookies to optimize our website and our service.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}
 

Loading Comments...