Month: April 2014

Posted on by

Editioning Implementation – Moving Triggers

You’ve got the tables renamed the EV’s in place, now it’s time to move the triggers from the base tables to the EV’s.
  1. Drop the triggers from the base tables
  2. Run the DDL file you saved the trigger source into earlier

That’s it!

You probably want to write some validation process here to check that all the triggers you had before are still there and in the same state. I would save the owner, trigger_name, and status into a text file, before and after and diff those, but you can do something else if you prefer. The goal is make sure you didn’t miss anything.

This shows the triggers and their status, spool the output and run it before and after as a validity check:

SELECT owner, trigger_name, status FROM dba_triggers WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’);

This SQL will generate the trigger drop commands:

SELECT ‘drop trigger ‘ || owner || ‘.’ || trigger_name || ‘;’ FROM dba_triggers WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’);

 

Posted on by

Editioning Implementation – Converting tables to Editioning Views

OK, ready to rename all your tables and break everything at once?
Good, let’s do this. Remember the SQL you used back in the planning stage to show the base table and editioning view names? We are going to build on that with the SQL below to generate the DDL commands to convert all the tables to EV’s. Remember this will invalidate a lot of objects, so give yourself time to revalidate.
SELECT ‘alter table ‘ || owner || ‘.’ || ev_name || ‘ rename to ‘ || new_table_name || ‘;’ ||
chr(10) || ‘create editioning view ‘ || owner || ‘.’ || ev_name || ‘ as select * from ‘ ||
owner || ‘.’ || new_table_name || ‘;’
FROM (SELECT owner, table_name ev_name, table_name || ‘_’ new_table_name FROM dba_tables
WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt) AND length(table_name) < 30
UNION
SELECT owner, table_name ev_name, substr(table_name, 1, 29) || ‘_’ new_table_name FROM dba_tables
WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt) AND length(table_name) = 30);
Pretty slick. This will save so much work, you can even expand on this to filter out any tables that are already converted. How? The DBA_EDITIONING_VIEWS view shows you the existing EV’s along with their base tables. I simple minus can find any tables that do not have an EV. Remember, a table can only have 1 EV and the EV will always be in the same schema as the base table. So, this SQL will show all of the tables in schemas you are intending to edition that do not yet have an EV.
SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt)
MINUS
SELECT owner, table_name FROM dba_editioning_views;
Note: once you have turned on editioning for the schemas involved, it would be more accurate to select from dba_users where the editioning flag is turned on, but I am developing this process without making changes to the db, so you can generate and save the DDL scripts from your prod system and then take them to your dev system for execution and testing.
Posted on by

Editioning Implementation – Save all triggers on the tables to be editioned

If you perform this before you rename any tables to replace them with editioning views, you will save yourself a lot of time editing source code. When  table is renamed, any triggers remain on the table. If you extract the source code after the rename it will have the new table name. Since we plan to drop the triggers and recreate them in the editioning views, we want to save the source code before the rename and apply it after creating the EV’s. Since the EV has the same name as the original table (so we don’t have to edit the name in all the dependent objects), running the saved trigger source will create the triggers correctly on the EV.
First to answer a question that is sure to occur to you. Why do we want to move the trigger to the EV, will it not work on the renamed base table?
It will continue to work if left on the base table, and it can still be editioned, and there are some use cases where it makes sense to put it here, but my reasoning for moving it is to enforce consistency of access and coding. If a developer is being retrained to use the EV’s instead of the underlying table, won’t they naturally use the EV to build their triggers on? Any triggers on the base table will not appear when viewing triggers on the EV and if you end up with some of both, confusion is the sure outcome.
That said, the EV’s hide certain things by design. For example, if you “alter” a column by adding a new column and making the EV hide that swap by displaying the new column with the old column name, how can a trigger see the old and new columns? It can’t. There are special triggers, called cross-edition triggers to handle this case, or you could create the trigger on the underlying table, that does have both columns visible. Just be careful when doing this, since your trigger is essentially hidden from developers used to using only the EV’s. My recommendation is to actively discourage triggers on base tables and monitor the appearance of any such triggers.
These commands, if put into a SQL script and run from sqlplus, will save a copy of the current source for all triggers on tables in the schemas you are going to edition. I would do this just before the table renames, but it’s safe to run now for testing. If you have all your trigger code easily deployable from the original codebase repository, you don’t really need this, but won’t you feel just a little better knowing it is saved off into a file?

SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET LINES 2047
SET PAGES 0
SET LONGCHUNKSIZE 2047
SET LONG 200000
SET TRIMSPOOL ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE);
COLUMN source_text WORD_WRAPPED
SPOOL trigger_ddl.sql
SELECT dbms_metadata.get_ddl(‘TRIGGER’, trigger_name, owner) source_text FROM dba_triggers
WHERE owner IN (SELECT owner FROM dheltzel.editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’)
AND base_object_type = ‘TABLE’;
SPOOL OFF
EXIT

Posted on by

Editioning Implementation – Enable schemas for editioning

The SQL below will write the DDL to enable the schemas for editioning. You are probably  not ready to execute these alter statements yet because here is what will happen:
  1. Most of the non-system objects in your database will become invalid – allow time to revalidate everything
  2. Some objects, with dependencies on the objects you just placed under EBR control, will fail to revalidate. If you did your homework with the prerequisite cleanups well, there won;t be many surprises here, but do allocate some time to deal with the unexpected.
  3. From now on, whenever you create a new user, you will need to be aware of their intended editioning status. The default is to not enable editions, likely because this can’t be undone, you can always enable editions on a user later, but you can disable editions. If you want to create a new user with editioning enabled, you must add the “ENABLE EDITIONS” clause to the create statement.
SELECT DISTINCT owner, editions_enabled, ‘alter user ‘ || username || ‘ enable editions force;’ cmd
  FROM editioned_users_obj_cnt  JOIN dba_users ON (username = owner) WHERE editions_enabled = ‘N’;
When you do feel ready to turn this on in any database, run the SQL and inspect it carefully (because it can’t be undone). You may want to do a backup and/or set a restore point, out of an abundance of caution.
After you execute the created DDL statements, re-run the SQL to see if they are all enabled. You should get no rows in the final run, except for any that you manually removed from the results the first time.
Posted on by

Editioning Implementation – Replace Public Synonyms

Identify the public synonyms that will become invalid when you enable editioning for the selected schemas. Remember the rule here is that no non-editioned object can depend on an editioned object. The special Oracle users, like SYS and PUBLIC, can never be editioned, so by extension none of their objects can depend on anything in the schemas that you are editioning.
SELECT synonym_name, table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt);

Now that you’ve found them what do you do? It would be nice if you could ascertain that some or all are not in use and just drop them. Doing this for objects that reside in the database is possible.
First, you should identify any dependencies on these public synonyms you need to drop and make them explicit references. This SQL will show the objects that need to be edited:
SELECT DISTINCT t.owner,t.name,t.type object_type,s.owner synonym_owner,s.synonym_name,s.table_owner || ‘.’ || s.table_name real_object
  FROM dba_dependencies t JOIN dba_synonyms s ON (s.owner = t.referenced_owner AND s.synonym_name = t.referenced_name AND t.referenced_type = ‘SYNONYM’)
 WHERE s.owner = ‘PUBLIC’ AND s.table_owner IN (SELECT owner FROM editioned_users_obj_cnt);
Fix those dependencies and check by running the SQL again and assure that it is clean.
For external connections, you cannot query a dependencies view. You can drop the synonyms on a test system and fix what breaks (either fixing the code to not need a synonym or create the private synonym),or you can simply create all the private synonyms that might possibly be used. The first way is obviously cleaner and better, but time consuming and more dangerous if you can’t test everything.
If you decide to take the approach of creating every possibly needed private synonym, this SQL will help you to do that. If you can all a filter to further restrict the grantee to a list of actual users that login on behalf of your app, even better. Be warned that running the resulting DDL will cause a lot of object invalidations, so schedule downtime appropriately.
SELECT ‘create or replace synonym ‘ || grantee || ‘.’ || table_name || ‘ for ‘ || owner || ‘.’ || table_name || ‘;’
  FROM ((SELECT r.grantee, p.owner, p.table_name FROM dba_role_privs r JOIN dba_tab_privs p ON (p.grantee = r.granted_role)
         UNION
         SELECT grantee, owner, table_name FROM dba_tab_privs)
        MINUS SELECT owner, table_owner, table_name FROM dba_synonyms) a
 WHERE (owner, table_name) IN
  (SELECT table_owner, table_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ AND table_owner IN (SELECT owner FROM editioned_users_obj_cnt));
Posted on by

Editioning Implementation – Create Editions

We are now going to create the editions that you decided on in the planning stage:
create edition RED as child of ora$base;
comment on edition RED is ‘This is the red edition’;
grant use on edition RED to PUBLIC;
create edition BLACK as child of RED;
comment on edition BLACK is ‘This is the black edition’;
grant use on edition BLACK to PUBLIC;
Now you can see your shiny new editions:
SELECT e.*, c.comments FROM dba_editions e JOIN dba_edition_comments c ON (c.edition_name = e.edition_name);
You should see 3 editions, all marked as usable.
Check on the grants with this SQL, each edition should have a grant to public.
SELECT * FROM dba_tab_privs WHERE privilege = ‘USE’;
Now you are ready to set the default edition to the first of your newly created editions.
First, check the current default edition:
SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
It should return ORA$BASE
Now change the default edition to RED:
ALTER SESSION SET DDL_LOCK_TIMEOUT=30;
ALTER DATABASE DEFAULT EDITION=RED;
 Check the default edition again:
SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
This time it should return ‘RED’
 As a final check, exit your sqlplus session and relogin. You should now be in the RED edition, check that with:
SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) FROM dual;
 Incidentally, after you are fully switched over to the new editions, you can revoke use on ORA$BASE from public to retire the default edition. Don’t do that yet, you might want to switch back.