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:
cursor c_Tables is
select owner, table_name from all_tables where owner = '';
for v_Table in c_Tables loop
dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name);
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';
when others then
dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name ||
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.