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.