Oracle Pagination

In case you want to retrieve records from a query paginated, for a given range of start to end row, the following will do the trick.


SELECT *
FROM (SELECT acct_id, session_id, ROW_NUMBER () OVER (ORDER BY acct_id) AS rn
            FROM tb_cwlogin
            ORDER BY acct_id)
WHERE rn BETWEEN 1000 AND 1050

There is no issue with ORDER BY clause as well as with the usual "ROWNUM" method.
 
Hope this helped :)

Connecting to sqlplus without TNSNAMES

In case you want to connect to oracle through sqlplus without using TNSNAMES.ORA definition the following connection string can be used

sqlplus userid/password@dbserverIP:1521/dbSID

or

sqlplus /nolog

SQL> connect userid/password@dbserverIP:1521/dbSID

This has been tested on 10g and 11g, not too sure if previous versions can connect like this or not :)