query only!!!

[jm]

what is the simplest way to turn the whole database to be read-only mode temporarily.

my boss want switch the db to be read-only mode for few hours then switch back to normal operation (update, insert....).

any suggestion will be appreciately. thanks

[sreddy]

Specify READ ONLY to place the tablespace in transition read-only mode. In this state, existing transactions can complete (commit or roll back), but no further write
operations (DML) are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace.

Have a script to bring tablespaces READONLY mode when there is no activity on the database. Iam assuming here your database is not 24X7.



[Edited by sreddy on 02-16-2001 at 10:16 AM]

[deggzy]

This should work quite nicely:


spool read_only.sql;
select 'alter tablespace ' || tablespace_name || ' read only;'
from dba_tablespaces;
spool off;
@read_only.sql


Regards

[jm]

thanks so fast responses.

I have 7 tablespaces: data, index, rbs, system, temp, tools, and users.

all I need is (trun off 3 tbs):
alter tablespace data read only;
alter tablespace index read only;
alter tablespace users read only; (most user default tbs)
I should keep other tbs in read write mode so user still can query. is that correct?
(I assume at least use need temp to sort, if temp be read only, the big query does not work)

or only (turn off 1 tbs)
alter tablespace rbs read only;
since all update activities can not write to rollback seg, user only can do query

am I correct?

[deggzy]

Yeah,

Don't put system, rollback or temp tablespaces into read only mode!

Otherwise not much will happen.

Regards

[dragon99]

after that, how do you reverse back to read and write state?

[sreddy]

Use the same kinda script to put tablespaces in WRITE mode.
as follows

select 'alter tablespace ' || tablespace_name || ' read write;'
from dba_tablespaces;