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.
