Most of the times requirement comes to clone the production application and make it read only.
Like during major 11i to R12 upgrade cutover plan, we need to clone old 11i to create the clone and put that in Read only mode.
In Oracle Database 11g , we can put tables in read only mode.
I have tested this in 11.5.10.2 and R12.1.3
Steps :
1) Stop the application.
2) Run the spool of the below script to put the tables in read only mode.
-----------------------table_read_only.sql--
set echo off
set term off
set head off
spool read_only_table.txt
select 'alter table '|| OWNER ||'.'||TABLE_NAME|| ' read only;' from dba_tables where owner not in('APPLSYS','APPLSYSPUB','CTXSYS','OLAPSYS','SYS','OUTLN','SYSTEM','MDSYS','ORDSYS','PORTAL30','PORTAL30_SSO','APPS','DBSNMP','OWAPUB','ICX');
/
----------------------
It well spool read_only_table.txt, run the spool as sys user.
3) Start the application and test.
4) To revert back run the spool of below script after stopping the application and start the application.
---------- table_read_write.sql----------
set echo off
set term off
set head off
spool write_enable_table.txt
select 'alter table '|| OWNER ||'.'||TABLE_NAME|| ' read write;' from dba_tables where owner not in('APPLSYS','APPLSYSPUB','CTXSYS','OLAPSYS','SYS','OUTLN','SYSTEM','MDSYS','ORDSYS','PORTAL30','PORTAL30_SSO','APPS','DBSNMP','OWAPUB','ICX');
/
---------------------------------------------
Its not supported by Oracle, thus need to be done at your own risk.
If we have other ideas , please share with me.
Like during major 11i to R12 upgrade cutover plan, we need to clone old 11i to create the clone and put that in Read only mode.
In Oracle Database 11g , we can put tables in read only mode.
I have tested this in 11.5.10.2 and R12.1.3
Steps :
1) Stop the application.
2) Run the spool of the below script to put the tables in read only mode.
-----------------------table_read_only.sql--
set echo off
set term off
set head off
spool read_only_table.txt
select 'alter table '|| OWNER ||'.'||TABLE_NAME|| ' read only;' from dba_tables where owner not in('APPLSYS','APPLSYSPUB','CTXSYS','OLAPSYS','SYS','OUTLN','SYSTEM','MDSYS','ORDSYS','PORTAL30','PORTAL30_SSO','APPS','DBSNMP','OWAPUB','ICX');
/
----------------------
It well spool read_only_table.txt, run the spool as sys user.
3) Start the application and test.
4) To revert back run the spool of below script after stopping the application and start the application.
---------- table_read_write.sql----------
set echo off
set term off
set head off
spool write_enable_table.txt
select 'alter table '|| OWNER ||'.'||TABLE_NAME|| ' read write;' from dba_tables where owner not in('APPLSYS','APPLSYSPUB','CTXSYS','OLAPSYS','SYS','OUTLN','SYSTEM','MDSYS','ORDSYS','PORTAL30','PORTAL30_SSO','APPS','DBSNMP','OWAPUB','ICX');
/
---------------------------------------------
Its not supported by Oracle, thus need to be done at your own risk.
If we have other ideas , please share with me.
No comments:
Post a Comment