Recently we wanted to migrate users from an old system to a new one giving them access to both systems at the same time but making the old system "read only". It's possible, in Oracle, to set either the whole Database or a complete Table Space but we discovered that when the users connected an audit record was created. We didn't want to change the code in the existing system but wanted the guarantee the users could not update any old data. The script we came up with is:
declare
cursor c_Tables is
select owner, table_name from all_tables where owner = '';
begin
for v_Table in c_Tables loop
dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name);
begin
execute immediate 'ALTER TABLE ' || v_Table.owner || '.' ||
v_Table.Table_Name || ' ADD CONSTRAINT ' ||
substr(v_Table.Table_Name, 1, 27) ||
'_RO check(1=1) disable validate';
exception
when others then
dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name ||
sqlerrm);
end;
end loop;
end;
This
script adds a constraint to each table (called _RO, for example the MASTER_BATCH_REC_HDR table would
have a constraint called MASTER_BATCH_REC_HDR_RO. Of course if you use
the full 30 characters for lots of your table names you might run into a
problem if the first 27 characters are the same!
This constraint can never be evaluated so every insert/update will fail but select statements will still run.
Of
course the problem with audit records is that you still have to insert
them so after a few tries running the application (and reading the error
messages) we worked out which tables to drop the existing constraint
from in order to allow the audit records to be inserted but no more.
No comments:
Post a Comment