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.