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.