query only!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: query only!!!

  1. #1
    Join Date
    Nov 2000
    Posts
    245
    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

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]
    Reddy,Sam

  3. #3
    Join Date
    Jul 2000
    Posts
    53
    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
    Steve

  4. #4
    Join Date
    Nov 2000
    Posts
    245
    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?


  5. #5
    Join Date
    Jul 2000
    Posts
    53
    Yeah,

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

    Otherwise not much will happen.

    Regards
    Steve

  6. #6
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    after that, how do you reverse back to read and write state?

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Use the same kinda script to put tablespaces in WRITE mode.
    as follows

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



    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width