How to Skip N Rows in an Oracle SELECT Statement

I saw this question on a SQL Server blog. Apparently it was asked at an interview. Here is the suggested answer:

SELECT *

FROM [AdventureWorks2014].[Person].[Address]

ORDER BY AddressID

OFFSET 100 ROWS;

 

I don’t have access to SQL Server right now so I wondered how I might do this in Oracle. This is what I came up with. I ran it in an Oracle 11.2.0.4 database:

SQL> l

  1   select username from dba_users

  2   minus

  3   (select username from

  4    (select username from dba_users order by username)

  5    where rownum <=10)

  6* order by username

SQL> /

 

USERNAME

------------------------------

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

22 rows selected.

 

SQL>

 

It skips the first 10 rows from DBA_USERS and returns the rest. Here are all the rows, in case you are interested:

 

SQL> select username from dba_users order by 1

  2  /

 

USERNAME

------------------------------

ABCD

ANONYMOUS

APEX_030200

APEX_PUBLIC_USER

APPQOSSYS

CTXSYS

DBSNMP

DIP

EXFSYS

FLOWS_FILES

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

32 rows selected.

 

SQL>

Oracle DBA Interview Question

This Oracle DBA interview question is based on some work I did recently. I will leave you to decide whether it is too easy, too difficult or just right for your candidate.

Question:

I created two Oracle 11.2.0.4 test databases on a UNIX server some time ago. I called them GBASRDB1 and GBASRDB2. Each database has a user called ANDREW. I can connect to GBASRDB1 from my Micrososft Windows PC like this:

C:\Users\J0294094>sqlplus andrew/reid@gbasrdb1

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 8 11:29:59 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

I can also logon to the UNIX server and connect to GBASRDB1 as follows:

GBASRDB1: sqlplus andrew/reid

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 8 11:18:50 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

I can connect to GBASRDB2 from my Microsoft Windows PC like this:
 
C:\Users\J0294094>sqlplus andrew/reid@gbasrdb2

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 8 11:31:42 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
 
...but when I logon to the UNIX server and try to connect to GBASRDB2 as follows, I see this message:
 
GBASRDB2: sqlplus andrew/reid

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 8 11:23:23 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

Enter user-name: 

What could cause this to happen?

Answer:

Looking at the two sessions in GBASRDB1, you can see that the V$SESSION PROGRAM column in the first SQL*Plus session is 11 characters long. However, the PROGRAM column from the second session includes the name of the UNIX server and is 35 characters long:

SQL> select name from v$database
  2  /

NAME
---------
GBASRDB1

SQL> select program, length(program)
  2  from v$session
  3  where username = 'ANDREW'
  4  order by logon_time
  5  /

PROGRAM                             LENGTH(PROGRAM)
----------------------------------- ---------------
sqlplus.exe                                      11
sqlplus@sge-mktred-lab2 (TNS V1-V3)              35

SQL>
 
GBASRDB2 has a database level logon trigger which was created as shown below. The original trigger was much longer so I have only left enough of the code to illustrate the problem:
 
SQL> conn / as sysdba
Connected.
SQL> select name from v$database
  2  /

NAME
---------
GBASRDB2

SQL> grant select on v_$session to public
  2  /

Grant succeeded.

SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> create or replace trigger trigger1
  2  after logon on database
  3  declare
  4   current_program varchar2(30);
  5  begin
  6   select program into current_program
  7    from v$session
  8    where audsid = userenv('sessionid');
  9  end;
 10  /

Trigger created.

SQL> show errors
No errors.
SQL>

This assumes that the PROGRAM column from V$SESSION is never longer than 30 characters. It can actually be up to 48 characters long:
  
SQL> desc v$session
 Name                       Null?    Type
 -------------------------- -------- ------------------
 etc
 etc
 PROGRAM                             VARCHAR2(48)
 etc
 etc
SQL>
 
So when a user tries to connect with a PROGRAM which is greater than 30 characters long, the logon trigger fails and displays the error shown.

Correct Password Gives ORA-01017

This post replicates a real-life situation where Oracle returned an ORA-01017 when the correct password was used. First I created a user in an Oracle 11 database and checked that I could connect to it:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 10:55:39 2019

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user blah identified by secret_password
  2  /

User created.

SQL> grant create session to blah
  2  /

Grant succeeded.

SQL> conn blah/secret_password
Connected.
SQL>

Then I connected to that user from a server which only had Oracle 9 software installed:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:02:32 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

The user had a hashed password created using the pre Oracle 11 routine:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:05:08 2019

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select password from sys.user$ where name = 'BLAH'
  2  /

PASSWORD
------------------------------
92D633C444E0CD1A

SQL>

It also had a hashed password created using the Oracle 11 routine:

SQL> select spare4 from sys.user$ where name = 'BLAH'
  2  /

SPARE4
--------------------------------------------------------------------------------
S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384

SQL>

In the real-life situation, the user's password expired because the PASSWORD_LIFE_TIME was set to 180 in the DEFAULT profile. For the purposes of this test I expired it manually:

SQL> alter user blah password expire
  2  /

User altered.

SQL> select account_status from dba_users
  2  where username = 'BLAH'
  3  /

ACCOUNT_STATUS
--------------------------------
EXPIRED

SQL>

In the real-life situation I did not know the user's password so I reinstated the Oracle 11 hash to preserve the password's case sensitivity:

SQL> alter user blah identified by values
  2  'S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384'
  3  /

User altered.

SQL>

This set the ACCOUNT_STATUS to OPEN:

SQL> select account_status from dba_users
  2  where username = 'BLAH'
  3  /

ACCOUNT_STATUS
--------------------------------
OPEN

SQL>

It did not occur to me at the time but this also removed the Oracle 10 hash presumably because Oracle had no way of knowing if it was still correct:

SQL> select nvl(password,'NULL') from sys.user$
  2  where name = 'BLAH'
  3  /

NVL(PASSWORD,'NULL')
------------------------------
NULL

SQL>

I spoke to the developer concerned. He confirmed that he knew the user's password so I told him he could login again. A couple of hours later he told me that he could login to the user using SQL*Plus:

C:\>sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 23 11:42:58 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

...but he could not connect using JDBC. I am not in a position to replicate that now but I can show the same problem by trying to login from the server with the Oracle 9 software:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:45:18 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

I guess this was sending a hash of the password created using the pre Oracle 11 routine.
The Oracle 11 database had nothing to compare this with so it returned an ORA-01017. The developer told me the password and I reset it. This reinstated the pre Oracle 11 hash of the password:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:49:12 2019

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user blah identified by secret_password
  2  /

User altered.

SQL> select password from sys.user$ where name = 'BLAH'
  2  /

PASSWORD
------------------------------
92D633C444E0CD1A

SQL> select spare4 from sys.user$ where name = 'BLAH'
  2  /

SPARE4
--------------------------------------------------------------------------------
S:784AAC15DBA436AA97653EE6C3868F7B87B434793F2BD69DCEDF55C2EDDB

SQL>

This allowed me to login again from the server with the Oracle 9 software:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:54:01 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

...and the developer was able to set up his JDBC connection.