Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: what happens if I use SET TRANSACTION READ ONLY and transaction writes to TEMP tables

    Hi,

    We have a report engine that does not write to the database.

    I would like to use SET TRANSACTION READ ONLY in order for that my transactions would not be assigned an undo segment, in order to save database resources.

    But as far as I understand, a readonly transactions can not write to the database, before I use it I would like to verify that such a transaction can write to temporary tablespace (e.g. when need to sort large amount of data).

    Can anybody give me a certified answer?


    Thanks,

    Tal Olier (otal@mercury.co.il).

  2. #2
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    You will have this error

    ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

  3. #3
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    I think my answer is too fast,

    If you use implicit writes on temporary tablespace because sort, compute ...

    eg
    select..... from.... where (select .... ) order by... an so on

    you will not have any errors,

    but if you do an explicit write (insert...) the above error will happen.

  4. #4
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by pmscontact
    You will have this error

    ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
    Are you sure this will happen on a temp tablespace?

  5. #5
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by pmscontact
    I think my answer is too fast,

    If you use implicit writes on temporary tablespace because sort, compute ...

    eg
    select..... from.... where (select .... ) order by... an so on

    you will not have any errors,

    but if you do an explicit write (insert...) the above error will happen.
    This was a question for general sql stmts, not a specific one.
    Thanks,
    Tal.

Posting Permissions

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