Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Right, I've just done a quick test.
- I created a couple of editions and an editioned view in each:
- SQL> SELECT object_name, object_type, edition_name
- 2 FROM user_objects_ae
- 3 ORDER BY object_name;
- OBJECT_NAME OBJECT_TYP EDITION_NAME
- ------------------------- ---------- ---------------
- EDITIONTEST TABLE
- EDITIONTEST2 TABLE
- EDITIONTEST_EDITION1_VIEW VIEW ORA$BASE
- EDITIONTEST_EDITION2_VIEW VIEW EDITION2
- SQL>
- 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):
- $ impdp phil/phil directory=EXPDPDIR
- Import: Release 11.2.0.4.0 - Production on Wed Mar 4 18:47:40 2015
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Oracle Label Security, OLAP, Data Mining
- and Real Application Testing options
- Master table "PHIL"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "PHIL"."SYS_IMPORT_FULL_01": phil/******** directory=EXPDPDIR
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- . . imported "PHIL"."EDITIONTEST" 0 KB 0 rows
- . . imported "PHIL"."EDITIONTEST2" 0 KB 0 rows
- Processing object type SCHEMA_EXPORT/VIEW/VIEW
- ORA-39083: Object type VIEW failed to create with error:
- ORA-42314: editioning view cannot be owned by a non-editioned user
- Failing sql is:
- CREATE FORCE EDITIONING VIEW "PHIL"."EDITIONTEST_EDITION1_VIEW" ("A", "B") AS select "A","B" from editiontest
- Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
- Job "PHIL"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Mar 4 18:47:45 2015 elapsed 0 00:00:03
- [UPGR] oracle@localhost:~
- $
- This tells me:
- - It only exported the default `ORA$BASE` edition
- - Any editioned objects won't be recreated unless you explicitely GRANT
- use of editioning to the newly created user
- So, time for a second test...
- Re-created the test user `phil` and some editioned objects.
- SQL> CREATE OR REPLACE EDITIONING VIEW editiontest_orabase_view
- 2 as select * from editiontest;
- View created.
- SQL>
- SQL> alter session set edition = edition2;
- Session altered.
- SQL> CREATE OR REPLACE EDITIONING VIEW editiontest_edition2_view
- 2 as select * from editiontest2;
- View created.
- Switched the DB so that it presented `edition2` to the user upon logon:
- BEGIN
- DBMS_SERVICE.modify_service(
- service_name => 'UPGR',
- edition => 'edition2',
- modify_edition => TRUE);
- END;
- Exported the schema, then recreated the user:
- SQL> drop user phil cascade;
- User dropped.
- SQL> create user phil identified by phil;
- User created.
- SQL> grant connect, resource, create view to phil;
- Grant succeeded.
- SQL> grant read,write on directory expdpdir to phil;
- Grant succeeded.
- I didn't grant anything to do with editions to the user. Switch the DB back to `ORA$BASE` upon login:
- BEGIN
- DBMS_SERVICE.modify_service(
- service_name => 'UPGR',
- edition => NULL,
- modify_edition => TRUE);
- END
- Now try the import:
- Processing object type SCHEMA_EXPORT/VIEW/VIEW
- ORA-39083: Object type VIEW failed to create with error:
- ORA-42314: editioning view cannot be owned by a non-editioned user
- Failing sql is:
- CREATE FORCE EDITIONING VIEW "PHIL"."EDITIONTEST_ORABASE_VIEW" ("A", "B") AS select "A","B" from editiontest
- Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
- Job "PHIL"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Mar 4 19:13:44 2015 elapsed 0 00:00:03
- Again, this shows me that its only exporting `ORA$BASE` even though I forced the DB to present a different edition.
- Another test. This time I did the same as before, but used:
- SQL> alter database default edition = edition2;
- This exported `edition2`:
- So, I think this answers a few parts of your question.
- `expdp` will export everything in the `ORA$BASE` edition.
- You don't need to drop child editions - editions will effectively get ditched
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement