Wednesday, September 17, 2014

ORA-02030: can only select from fixed tables/views Error While Granting Access To Oracle V$ Views

This is a pretty common mistake to make when we are learning Oracle database. Basically when we select the data from all these dynamic performance views like V$SESSION or V$TRANSACTION etc., we are using the public synonyms. So if you try to grant select access on these synonyms like below, you will get "ORA-02030: can only select from fixed tables/views" error.

SQL> grant select on v$transaction to test;
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

So you need to grant select permission on the underlying views, not on synonyms. That's pretty straightforward as Oracle has a common naming convention for these v$ views. Just add a underscore "_" after "V" like V_$SESSION or V_$TRANSACTION. So if you just change the above SQL statement like below, you will scceed.

SQL> grant select on v_$transaction to test;
Grant succeeded.

Saturday, September 13, 2014

Does SELECT Statement Start A Transaction In Oracle

Normal SELECT query doesn't start a transaction in Oracle. Oracle will start a transaction implicitly when it acquires a Transaction Lock for the first time. And TX lock is only obtained when you are trying to modify data. So a SELECT query like below which just retrieves data doesn't start a transaction:

select id from test_table where id=1;

But if you are using SELECT ... FOR UPDATE statement, that's a different scenario. SELECT ... FOR UPDATE will lock the selected rows so that you can modify them later and no other session can update the rows in the meantime. So in the below case, Oracle will start a new transaction:

select id from test_table where id=1 for update nowait;

Hope this clear up the things.

Sunday, September 7, 2014

Easiest Way to Run Command Prompt In Full Screen Mode In Windows

If you are using Windows 7 or any later version, then you know that Alt + Enter doesn't work when you try to run DOS in a full screen mode. There's an easy way to fix this.
command prompt layout

Right click on the title bar of the command prompt program. Select "Properties" option.
From the new window, select "Layout" tab and put any large number in width section of "Window Size". Then just save it. If the entered number is bigger than screen width, Windows will automatically resize the value to screen width. Now when you click on "Maximize" button, the DOS console will cover the whole screen.