Advertisement
Guest User

Untitled

a guest
Mar 7th, 2015
504
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.62 KB | None | 0 0
  1. Right, I've just done a quick test.
  2.  
  3. I created a couple of editions and an editioned view in each:
  4.  
  5. SQL> SELECT object_name, object_type, edition_name
  6. 2 FROM user_objects_ae
  7. 3 ORDER BY object_name;
  8.  
  9. OBJECT_NAME OBJECT_TYP EDITION_NAME
  10. ------------------------- ---------- ---------------
  11. EDITIONTEST TABLE
  12. EDITIONTEST2 TABLE
  13. EDITIONTEST_EDITION1_VIEW VIEW ORA$BASE
  14. EDITIONTEST_EDITION2_VIEW VIEW EDITION2
  15.  
  16. SQL>
  17.  
  18.  
  19.  
  20. After dropping the user, when you try and re-import the data you get errors when trying to create editioned objects (if you re-create the user without editioning enabled):
  21.  
  22. $ impdp phil/phil directory=EXPDPDIR
  23.  
  24. Import: Release 11.2.0.4.0 - Production on Wed Mar 4 18:47:40 2015
  25.  
  26. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  27.  
  28. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  29. With the Partitioning, Oracle Label Security, OLAP, Data Mining
  30. and Real Application Testing options
  31. Master table "PHIL"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  32. Starting "PHIL"."SYS_IMPORT_FULL_01": phil/******** directory=EXPDPDIR
  33. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  34. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  35. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  36. . . imported "PHIL"."EDITIONTEST" 0 KB 0 rows
  37. . . imported "PHIL"."EDITIONTEST2" 0 KB 0 rows
  38. Processing object type SCHEMA_EXPORT/VIEW/VIEW
  39. ORA-39083: Object type VIEW failed to create with error:
  40. ORA-42314: editioning view cannot be owned by a non-editioned user
  41. Failing sql is:
  42. CREATE FORCE EDITIONING VIEW "PHIL"."EDITIONTEST_EDITION1_VIEW" ("A", "B") AS select "A","B" from editiontest
  43. Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
  44. Job "PHIL"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Mar 4 18:47:45 2015 elapsed 0 00:00:03
  45.  
  46. [UPGR] oracle@localhost:~
  47. $
  48.  
  49. This tells me:
  50.  
  51. - It only exported the default `ORA$BASE` edition
  52. - Any editioned objects won't be recreated unless you explicitely GRANT
  53. use of editioning to the newly created user
  54.  
  55. So, time for a second test...
  56.  
  57. Re-created the test user `phil` and some editioned objects.
  58.  
  59. SQL> CREATE OR REPLACE EDITIONING VIEW editiontest_orabase_view
  60. 2 as select * from editiontest;
  61.  
  62. View created.
  63.  
  64. SQL>
  65.  
  66. SQL> alter session set edition = edition2;
  67.  
  68. Session altered.
  69.  
  70. SQL> CREATE OR REPLACE EDITIONING VIEW editiontest_edition2_view
  71. 2 as select * from editiontest2;
  72.  
  73. View created.
  74.  
  75. Switched the DB so that it presented `edition2` to the user upon logon:
  76.  
  77. BEGIN
  78. DBMS_SERVICE.modify_service(
  79. service_name => 'UPGR',
  80. edition => 'edition2',
  81. modify_edition => TRUE);
  82. END;
  83.  
  84. Exported the schema, then recreated the user:
  85.  
  86. SQL> drop user phil cascade;
  87.  
  88. User dropped.
  89.  
  90. SQL> create user phil identified by phil;
  91.  
  92. User created.
  93.  
  94. SQL> grant connect, resource, create view to phil;
  95.  
  96. Grant succeeded.
  97.  
  98. SQL> grant read,write on directory expdpdir to phil;
  99.  
  100. Grant succeeded.
  101.  
  102.  
  103. I didn't grant anything to do with editions to the user. Switch the DB back to `ORA$BASE` upon login:
  104.  
  105. BEGIN
  106. DBMS_SERVICE.modify_service(
  107. service_name => 'UPGR',
  108. edition => NULL,
  109. modify_edition => TRUE);
  110. END
  111.  
  112. Now try the import:
  113.  
  114. Processing object type SCHEMA_EXPORT/VIEW/VIEW
  115. ORA-39083: Object type VIEW failed to create with error:
  116. ORA-42314: editioning view cannot be owned by a non-editioned user
  117. Failing sql is:
  118. CREATE FORCE EDITIONING VIEW "PHIL"."EDITIONTEST_ORABASE_VIEW" ("A", "B") AS select "A","B" from editiontest
  119. Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
  120. Job "PHIL"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Mar 4 19:13:44 2015 elapsed 0 00:00:03
  121.  
  122. Again, this shows me that its only exporting `ORA$BASE` even though I forced the DB to present a different edition.
  123.  
  124. Another test. This time I did the same as before, but used:
  125.  
  126. SQL> alter database default edition = edition2;
  127.  
  128. This exported `edition2`:
  129.  
  130.  
  131.  
  132. So, I think this answers a few parts of your question.
  133.  
  134. `expdp` will export everything in the `ORA$BASE` edition.
  135.  
  136. You don't need to drop child editions - editions will effectively get ditched
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement