sqlplus session read only

In sqlplus I can create a readonly transaction.

set transaction read only;

Is it possible to create a readonly session?

I want to connect to the Oracle DB, execute some tests with UPDATEs and INSERTs but don't commit them even if开发者_开发技巧 commit is executed during the whole session.

---

You could look at the the following

CREATE TABLE dummy 
  (val NUMBER(1) 
       CONSTRAINT dummy_ck CHECK(val =1) DEFERRABLE INITIALLY DEFERRED)
/

insert into dummy VALUES (2);
..<bits here>..
commit;

Unless the actually deletes/updates the pending entry from DUMMY, the transaction will fail upon COMMIT and be automatically rolled back. If is

INSERT....
COMMIT...
INSERT....

Then the first commit will fire the failure/rollback and so the second would succeed. So it is ugly and not especially safe. But it may be appropriate for your needs

---

The short answer to your question is no, you cannot create a "read only session".

You're going to get "insufficient privileges" errors if you have SELECT-only privileges on a table and then issue UPDATE/INSERT/DELETE statements.

You also cannot issue UPDATE/INSERT/DELETE statements inside a read only transaction, you'll get an error. I think this means that what you think you're doing with a "set transaction read only" isn't going to do what I understand you want it to do. Here's what really would happen:

SQL> set transaction read only;

Transaction set.

SQL> update tbl set code = 'ACTIVE' where id = 10;
update tbl set code = 'ACTIVE' where id = 10
                                           *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction


SQL>

For your testing, you should:

1. Create a test database that you won't cry over if you screw it up.
2. Use Flashback Database to restore back to your baseline after each test.

---

For this we create a new user with read only privileges.