0

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.

  • I wasn't aware that it's not reversible. Good to know. – Philᵀᴹ Mar 4 '15 at 9:38
  • I suppose one question to ask is do you really need to do this? Is there a limitation caused by the editioning that you can't get round? – Phil Sumner Mar 4 '15 at 11:18
  • 1
    @kevinsky i've not had a chance to finish my testing, but this is my answer so far - bits may be wrong as i've not had time to sit down for an hour to finish testing: pastebin.com/etVuL9wH – Philᵀᴹ Mar 7 '15 at 16:40
  • Impressive, as they say, I should of thought of that...:) – kevinsky Mar 7 '15 at 19:18
0

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.