SHARE
TWEET

Untitled

a guest Mar 7th, 2015 291 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top