Home » RDBMS Server » Server Administration » Blank username in v$session , How to find who is connecting (Oracle 11.2.0.4)  () 1 Vote
Blank username in v$session , How to find who is connecting [message #678006] Wed, 30 October 2019 09:12 Go to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
Dear team,

In QA DB server , Today I got ORA-00020

Check and found too many active sessions from v$session.

The question is that the username column is blank.

Program - JDBC Thin Client

OSUSER - pqadmn

Since our sys and system users are in locked state.

Hence I am unable to find the real culprit.

Audit trail is not showing any login information and only filled with ora-01017

Attaching some logs
Re: Blank username in v$session , How to find who is connecting [message #678007 is a reply to message #678006] Wed, 30 October 2019 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) You should post the contents of your text file in line in [code] tags. Lots of people won't/can't download attachments.
2) What exact queries did you run to get that output?
3) Given that you've got a 120 sessions with program JDBC Thin Client, osuser pqaadm and no username the obiovus thing to do is look on the server to see what pqaadm is actually running. There's a good chance it's just the one program.
In fact, given all the other sessions are either toad, sqlplus, sql Developer or oracle internal, isn't the JDBC thin client your application front end?
Re: Blank username in v$session , How to find who is connecting [message #678008 is a reply to message #678007] Wed, 30 October 2019 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suspect the root cause is a rogue application code in the middle tier.
Post excerpt from listener.log showing the login requests which consumed all the sessions allowed.
ORA-01017 indicates failed login attempts due to incorrect USERNAME/PASSWORD combination
Re: Blank username in v$session , How to find who is connecting [message #678009 is a reply to message #678006] Wed, 30 October 2019 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Since our sys and system users are in locked state.

SYS is never locked even of its state is LOCKED.

Quote:
isn't the JDBC thin client your application front end?

I concur with this hypothesis.

Re: Blank username in v$session , How to find who is connecting [message #678010 is a reply to message #678006] Wed, 30 October 2019 12:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
All those active sessions with no username hanging on library cache locks looks to me as though your middle tier is trying to establish a connection pool a bit too quickly. If you really are killing the processes, it will probably keep retrying and make the problem worse until you hit your PROCESSES limit and get the ora-20 errors.
Re: Blank username in v$session , How to find who is connecting [message #678011 is a reply to message #678010] Wed, 30 October 2019 14:24 Go to previous messageGo to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
May I upload last 2 days audit trail and listener logs for reference?
Re: Blank username in v$session , How to find who is connecting [message #678012 is a reply to message #678011] Wed, 30 October 2019 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We don't need to thousands of records and only need to a couple of dozen of representative records/rows which can be COPY & PASTED into new entry here
Re: Blank username in v$session , How to find who is connecting [message #678081 is a reply to message #678007] Sat, 09 November 2019 02:34 Go to previous messageGo to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
Sorry for delayed response.

13:50:21 SQL> Select name Instance, (Select host_name from v$instance) Host ,open_mode from v$database;

INSTANCE  HOST            OPEN_MODE                                                                                                                                                                     
--------- --------------- --------------------                                                                                                                                                          
EPAINT    mesdbqa         READ WRITE                                                                                                                                                                    

13:50:29 SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU                                                                                                                
------------------------------ ------------------- --------------- ---------- ----------                                                                                                                
processes                                      140             150        150        150                                                                                                                
sessions                                       166             194        248        248                                                                                                                
enqueue_locks                                   20              38       3000       3000                                                                                                                
enqueue_resources                               18              25       1308  UNLIMITED                                                                                                                
ges_procs                                        0               0          0          0                                                                                                                
ges_ress                                         0               0          0  UNLIMITED                                                                                                                
ges_locks                                        0               0          0  UNLIMITED                                                                                                                
ges_cache_ress                                   0               0          0  UNLIMITED                                                                                                                
ges_reg_msgs                                     0               0          0  UNLIMITED                                                                                                                
ges_big_msgs                                     0               0          0  UNLIMITED                                                                                                                
ges_rsv_msgs                                     0               0          0          0                                                                                                                
gcs_resources                                    0               0  UNLIMITED  UNLIMITED                                                                                                                
gcs_shadows                                      0               0  UNLIMITED  UNLIMITED                                                                                                                
smartio_overhead_memory                          0           71704          0  UNLIMITED                                                                                                                
smartio_buffer_memory                            0               0          0  UNLIMITED                                                                                                                
smartio_metadata_memory                          0               0          0  UNLIMITED                                                                                                                
smartio_sessions                                 0               1          0  UNLIMITED                                                                                                                
dml_locks                                        0               3       1088  UNLIMITED                                                                                                                
temporary_table_locks                            0               5  UNLIMITED  UNLIMITED                                                                                                                
transactions                                    42              90        272  UNLIMITED                                                                                                                
branches                                         0               1        272  UNLIMITED                                                                                                                
cmtcallbk                                        0               5        272  UNLIMITED                                                                                                                
max_rollback_segments                           11              11        272      65535                                                                                                                
sort_segment_locks                               0               6  UNLIMITED  UNLIMITED                                                                                                                
k2q_locks                                        0               0        496  UNLIMITED                                                                                                                
max_shared_servers                               1               1  UNLIMITED  UNLIMITED                                                                                                                
parallel_max_servers                             0               0          0       3600                                                                                                                

27 rows selected.

13:50:30 SQL> 

======================================
No Sessions if Exclude SYS,SYSTEM
======================================

13:50:30 SQL> select count(*) as No_of_Sessions,osuser,USERNAME,PROGRAM,status from v$session
13:50:30   2  where username not in('SYS','SYSTEM')
13:50:30   3  group by osuser,USERNAME,PROGRAM,status
13:50:30   4  order by 1 desc;

NO_OF_SESSIONS OSUSER                         USERNAME   PROGRAM              STATUS                                                                                                                    
-------------- ------------------------------ ---------- -------------------- ----------                                                                                                                
             1 DBA_ANUP                        BKPUSR     sqlplus.exe          ACTIVE                                                                                                                    


========================================
Getting Sessions if include SYS,SYSTEM
========================================

13:50:30 SQL> select * from (select count(*) as No_of_Sessions,osuser,Machine,USERNAME,PROGRAM,status from v$session
13:50:30   2  group by osuser,USERNAME,PROGRAM,status,Machine
13:50:30   3  order by 1 desc)
13:50:30   4  where rownum<5;

NO_OF_SESSIONS OSUSER                         MACHINE              USERNAME   PROGRAM              STATUS                                                                                               
-------------- ------------------------------ -------------------- ---------- -------------------- ----------                                                                                           
           114 pqaadm                         sappoqa                         JDBC Thin Client     ACTIVE                                                                                               
             1 oracle                         mesdbqa                         oracle@mesdbqa (VKTM ACTIVE                                                                                               
             1 oracle                         mesdbqa                         oracle@mesdbqa (SMCO ACTIVE                                                                                               
             1 oracle                         mesdbqa                         oracle@mesdbqa (Q002 ACTIVE                                                                                               



========================================
Session Details of Culprit Program
========================================

13:50:31 SQL> select s.sid,substr(p.spid,1,10) "PID",s.status,
13:51:59   2  substr(s.OSUSER,1,10) "OS_User",
13:51:59   3  substr(s.username,1,15) "Session_User", substr(s.PROGRAM,1,15) "Program",
13:51:59   4  substr(w.event,1,20) "Event",round(w.SECONDS_IN_WAIT/(60),0) "WaitTime_Min",
13:51:59   5  to_char(LOGON_TIME,'DD-MON-YY HH24:MI') LogOn,
13:51:59   6  round(sysdate-s.LOGON_TIME,2) Login_Days from v$session s,v$process p,v$session_wait w
13:51:59   7  where p.addr=s.paddr and w.sid=s.sid and s.PROGRAM ='&Program' and p.background is NULL
13:51:59   8  order by w.SECONDS_IN_WAIT desc;
Enter value for program: JDBC Thin Client
old   7: where p.addr=s.paddr and w.sid=s.sid and s.PROGRAM ='&Program' and p.background is NULL
new   7: where p.addr=s.paddr and w.sid=s.sid and s.PROGRAM ='JDBC Thin Client' and p.background is NULL

       Process               OS     Session                                              WaitTime                    LogIn                                                                              
   SID ID         STATUS     User   User       Program              Event                  IN Min LOGON               Days                                                                              
------ ---------- ---------- ------ ---------- -------------------- -------------------- -------- --------------- --------                                                                              
   215 1113       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:28      .02                                                                              
    43 784        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:26      .02                                                                              
   155 1053       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:28      .02                                                                              
   171 610        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:23      .02                                                                              
    23 1584       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:34      .01                                                                              
   207 1557       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:33      .01                                                                              
   222 2260       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         12 09-NOV-19 13:40      .01                                                                              
   142 2300       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         11 09-NOV-19 13:40      .01                                                                              
    33 705        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         11 09-NOV-19 13:25      .02                                                                              
   180 1756       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         11 09-NOV-19 13:35      .01                                                                              
   178 2256       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:40      .01                                                                              
    55 2439       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:41      .01                                                                              
   191 2444       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:42      .01                                                                              
    63 641        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:24      .02                                                                              
    25 947        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:27      .02                                                                              
   144 614        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:23      .02                                                                              
   185 1588       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:34      .01                                                                              
   209 673        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:24      .02                                                                              
   223 953        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:27      .02                                                                              
    32 1788       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:36      .01                                                                              
   125 1774       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:35      .01                                                                              
   159 1772       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock         10 09-NOV-19 13:35      .01                                                                              
    90 2491       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          9 09-NOV-19 13:43      .01                                                                              
    40 2499       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          9 09-NOV-19 13:43      .01                                                                              
   203 2418       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          9 09-NOV-19 13:41      .01                                                                              
   143 2509       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          9 09-NOV-19 13:43      .01                                                                              
    84 2516       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          9 09-NOV-19 13:43      .01                                                                              
   153 1832       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:36      .01                                                                              
    17 2534       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:43      .01                                                                              
    64 1779       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:36      .01                                                                              
    52 1986       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:37      .01                                                                              
   152 2545       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:44      .01                                                                              
   187 1988       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:37      .01                                                                              
   196 1802       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          8 09-NOV-19 13:36      .01                                                                              
   194 2569       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:44      .01                                                                              
    31 2254       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:40      .01                                                                              
    46 2532       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:43      .01                                                                              
   166 2575       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:44      .01                                                                              
    98 2579       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:45      .00                                                                              
    94 2587       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          7 09-NOV-19 13:45      .00                                                                              
    37 2624       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:46      .00                                                                              
    22 1723       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:35      .01                                                                              
   148 1626       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:34      .01                                                                              
   198 2329       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:41      .01                                                                              
    49 2092       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:38      .01                                                                              
    80 2637       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:46      .00                                                                              
    71 1620       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:34      .01                                                                              
    75 2530       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:43      .01                                                                              
    34 2649       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          6 09-NOV-19 13:46      .00                                                                              
   150 2142       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          5 09-NOV-19 13:39      .01                                                                              
   193 2671       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          5 09-NOV-19 13:47      .00                                                                              
   170 612        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          5 09-NOV-19 13:23      .02                                                                              
    68 2692       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          5 09-NOV-19 13:47      .00                                                                              
   145 2665       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          4 09-NOV-19 13:46      .00                                                                              
   168 2716       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          4 09-NOV-19 13:47      .00                                                                              
   221 2723       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          4 09-NOV-19 13:48      .00                                                                              
    74 2540       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          4 09-NOV-19 13:44      .01                                                                              
   211 2132       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:39      .01                                                                              
    79 1939       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:37      .01                                                                              
    65 2437       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:41      .01                                                                              
    48 1904       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:37      .01                                                                              
   183 2511       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:43      .01                                                                              
   149 2814       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:49      .00                                                                              
   175 2246       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:40      .01                                                                              
   158 2833       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:49      .00                                                                              
    91 695        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:25      .02                                                                              
    83 2472       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:42      .01                                                                              
    62 2837       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:49      .00                                                                              
   154 1828       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:36      .01                                                                              
    53 2873       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:49      .00                                                                              
    57 1794       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          3 09-NOV-19 13:36      .01                                                                              
    70 2934       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:49      .00                                                                              
   173 2714       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:47      .00                                                                              
    76 2939       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:49      .00                                                                              
   186 2941       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:50      .00                                                                              
   151 2602       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:45      .00                                                                              
   197 1075       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:28      .02                                                                              
    93 665        ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:24      .02                                                                              
    89 1398       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:30      .01                                                                              
    41 2146       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:39      .01                                                                              
   174 2651       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:46      .00                                                                              
   192 2487       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:42      .01                                                                              
    99 1741       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          2 09-NOV-19 13:35      .01                                                                              
    16 2704       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:47      .00                                                                              
    59 2294       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:40      .01                                                                              
    50 2503       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:43      .01                                                                              
   220 2794       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:48      .00                                                                              
    30 3171       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:50      .00                                                                              
    56 2409       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:41      .01                                                                              
   165 3084       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:50      .00                                                                              
   164 1838       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:36      .01                                                                              
   224 1995       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:38      .01                                                                              
    54 2932       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:49      .00                                                                              
    26 2659       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:46      .00                                                                              
   162 3182       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:50      .00                                                                              
   172 2802       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:48      .00                                                                              
    85 2667       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:47      .00                                                                              
   184 2100       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:38      .01                                                                              
    97 2098       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:38      .01                                                                              
    36 3012       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:50      .00                                                                              
    13 1912       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:37      .01                                                                              
   177 2800       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:48      .00                                                                              
   137 1733       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:35      .01                                                                              
   179 3208       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:51      .00                                                                              
   156 2215       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:39      .01                                                                              
    28 2217       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:39      .01                                                                              
   201 2608       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          1 09-NOV-19 13:45      .00                                                                              
    72 3242       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:51      .00                                                                              
    51 3244       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:51      .00                                                                              
    81 2226       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:40      .01                                                                              
    24 3275       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:52      .00                                                                              
    38 3186       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:51      .00                                                                              
   217 3283       ACTIVE     pqaadm            JDBC Thin Clien      library cache lock          0 09-NOV-19 13:52      .00                                                                              

113 rows selected.


========================================
Getting No Running SQL Text from SPID 
========================================

Enter value for spid: 1113
old  11: and spid =&SPID
new  11: and spid =1113

no rows selected

13:52:44 SQL> 
13:52:45 SQL> 
13:52:45 SQL> 
13:52:45 SQL> /
Enter value for spid: 3283
old  11: and spid =&SPID
new  11: and spid =3283

no rows selected

13:52:53 SQL> 
13:52:54 SQL> 
13:52:54 SQL> 

============================================================================================================
Last 50 Login Records From AUD$ - Since Oracle has returned 1017 , Then how sessions are being created.?????
============================================================================================================

13:53:19 SQL> select *
13:53:34   2  from (select OS_USERNAME,USERNAME,USERHOST,TERMINAL,ACTION_NAME,RETURNCODE,COMMENT_TEXT,to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') Time
13:53:34   3  from dba_audit_trail
13:53:34   4  where trunc(TIMESTAMP)=trunc(sysdate)
13:53:34   5  order by timestamp desc)
13:53:34   6  where rownum<=50
13:53:34   7  order by rownum;

OS_USERNAME  USERNAME USERHOST                  TERMINAL               ACTION_NAM RETURNCODE COMMENT_TEXT                   TIME                                                                        
------------ -------- ------------------------- ---------------------- ---------- ---------- ------------------------------ -------------------                                                         
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:53:33                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=40009))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:53:23                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =55982))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:53:13                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =55483))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:53:03                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=48336))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:53                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=48318))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:43                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=15562))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:33                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =55694))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:23                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=61282))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:13                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56219))                                                                                                   
                                                                                                                                                                                                        
DBA_ANUP      BKPUSR   WORKGROUP\HP-PC           HP-PC                  LOGON               0 Authenticated by: DATABASE; Cl 09-11-2019 13:52:11                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=133.0.2.127)(PORT                                                                             
                                                                                             =50916))                                                                                                   
                                                                                                                                                                                                        
DBA_ANUP      BKPUSR   WORKGROUP\HP-PC           HP-PC                  LOGOFF              0                                09-11-2019 13:52:11                                                         
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:52:03                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=61269))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:53                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56252))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:43                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56332))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:33                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56601))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:23                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56662))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:13                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56931))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:51:03                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=14170))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:53                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56410))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:43                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56933))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:33                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56771))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:23                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=48258))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:13                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56564))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:50:03                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56814))                                                                                                   
                                                                                                                                                                                                        
DBA_ANUP      BKPUSR   WORKGROUP\HP-PC           HP-PC                  LOGON               0 Authenticated by: DATABASE; Cl 09-11-2019 13:49:56                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=133.0.2.127)(PORT                                                                             
                                                                                             =50710))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:49:53                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56435))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:49:43                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56409))                                                                                                   
                                                                                                                                                                                                        
DBA_ANUP      BKPUSR   WORKGROUP\HP-PC           HP-PC                  LOGON               0 Authenticated by: DATABASE; Cl 09-11-2019 13:49:36                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=133.0.2.127)(PORT                                                                             
                                                                                             =50663))                                                                                                   
                                                                                                                                                                                                        
DBA_ANUP      BKPUSR   WORKGROUP\HP-PC           HP-PC                  LOGOFF              0                                09-11-2019 13:49:36                                                         
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:49:33                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=10.170.63.50)(POR                                                                             
                                                                                             T=48228))                                                                                                  
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:49:23                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                                                                             OL=tcp)(HOST=176.0.11.32)(PORT                                                                             
                                                                                             =56660))                                                                                                   
                                                                                                                                                                                                        
pqaadm       150415   sappoqa                   unknown                LOGON            1017 Authenticated by: DATABASE; Cl 09-11-2019 13:49:13                                                         
                                                                                             ient address: (ADDRESS=(PROTOC                                                                             
                                       
		
		
		
Re: Blank username in v$session , How to find who is connecting [message #678082 is a reply to message #678081] Sat, 09 November 2019 02:38 Go to previous messageGo to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
How sessions are created , if DB is returning ora 1017.
Also Sessions are showing active , but got nothing running in v$sql
Pls help

[Updated on: Sat, 09 November 2019 02:38]

Report message to a moderator

Re: Blank username in v$session , How to find who is connecting [message #678083 is a reply to message #678082] Sat, 09 November 2019 02:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A session is created before being authenticated. How else could the session determine that the password is wrong?

It is pretty obvious that your middle tier is bombarding the database with connection requests that have the wrong password.
You need to fix that before anything else.
Re: Blank username in v$session , How to find who is connecting [message #678084 is a reply to message #678083] Sat, 09 November 2019 03:16 Go to previous messageGo to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
But as per wait event , It is showing library cache lock, that's show session is created and query is in parsing process in shared pool.

How it it possible
Re: Blank username in v$session , How to find who is connecting [message #678085 is a reply to message #678084] Sat, 09 November 2019 03:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Well, yes. It takes quite a few queries to authenticate a user.

Have you looked at the middle tier yet? That is likely where the problem is. As you have been told. Repeatedly.
Re: Blank username in v$session , How to find who is connecting [message #678086 is a reply to message #678085] Sat, 09 November 2019 04:08 Go to previous message
Akmmhto
Messages: 38
Registered: September 2018
Member
I need to ask these details from Application team.
Previous Topic: Add new data file or extend existing data files ?
Next Topic: Object change from valid to invalid
Goto Forum:
  


Current Time: Thu Mar 28 17:39:24 CDT 2024