Oracle – Removing Editioning from a User in Oracle 11g R2

oracleoracle-11g-r2

I maintain an Oracle Enterprise 11.2.0.3 database where one user has editioning enabled. I understand that this was an irreversible choice according to the documentation.

If I wish to revert that user to a non-editioned status can I export their objects, drop and recreate the user and then import the old objects?

  • Are there any consequences?
  • Which edition will Oracle export?
  • should I drop all child editions first?

This question is caused by the assessment of the team that editioning is not used and does not offer any benefits when we have generous time periods for scheduled downtimed. It prevents any materialized views being created by the editioned user if they select non editioned object from another user. This is required for one project due soon and a data warehouse in the near future.

Best Answer

Based on Phil's test case I backed up the the production database using exp.

I dropped the editioned user, recreated them and imported the data with no issues. The user had views and packages which were versioned but as long as you set the edition to the most current one you are good to go.

This query showed that the recreated user was not using editioning

SELECT username,editions_enabled
FROM   dba_users
WHERE editions_enabled = 'Y';
Related Question