{"id":19,"date":"2012-03-22T13:08:09","date_gmt":"2012-03-22T12:08:09","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=19"},"modified":"2012-09-12T11:45:51","modified_gmt":"2012-09-12T09:45:51","slug":"a-la-caza-del-usuario-i-sesiones-en-la-bbdd","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/a-la-caza-del-usuario-i-sesiones-en-la-bbdd\/","title":{"rendered":"Sqlplus para dummies II (sesiones en la BBDD)"},"content":{"rendered":"

Encontrar quien hay en la base de datos y que est\u00e1 haciendo cada uno es uno de los primeros pasos a la hora de determinar un problema en la base de datos, las consultas b\u00e1sicas para encontrar el quien es quien son:<\/h4>\n

 <\/p>\n

Numero de sesiones de usuario por maquina<\/strong>
\nselect machine,count(*) numero from
\nv$session where
\ntype != ‘BACKGROUND’
\ngroup by machine
\norder by numero desc;<\/em>
\n Numero de sesiones de usuario Activas por maquina<\/strong>
\nselect machine,count(*) numero
\nfrom v$session where type != ‘BACKGROUND’
\nand status=’ACTIVE’
\ngroup by machine order by numero desc;<\/em>
\nNumero de sesiones por maquina<\/strong>
\nselect machine,count(*) numero from
\nv$session group by machine
\norder by numero desc;<\/em><\/p>\n

Numero de sesiones Activas por maquina<\/strong>
\nselect machine,count(*) numero from v$session
\nwhere status=’ACTIVE’ group by machine order by numero desc;<\/em><\/p>\n

Texto sql de las v$session<\/strong>
\nselect s.sid,s.serial#,s.username,s.machine,s.program,s.osuser,s.status,x.sql_fulltext
\nfrom v$session s ,v$sqlarea x
\nwhere s.sql_address=x.address;<\/p>\n

\u00a0Consulta para enlazar un proceso del sistema con una sesi\u00f3n<\/strong>
\nselect s.sid, s.serial# , q.sql_text, s.username, s.machine, s.logon_time, s.program, s.status
\nfrom v$session s, v$process p, v$sql q
\nwhere p.spid=4038 and p.addr=s.paddr
\nand q.address=s.sql_address
\nand s.sql_hash_value=q.hash_value;<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

Encontrar quien hay en la base de datos y que est\u00e1 haciendo cada uno es uno de los primeros pasos a la hora de determinar un problema en la base de datos, las consultas b\u00e1sicas para encontrar el quien es … Sigue leyendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,7,3],"tags":[248,247,22],"class_list":["post-19","post","type-post","status-publish","format-standard","hentry","category-dummie","category-sqlplus","category-tunning","tag-dummie","tag-sqlplus","tag-tablespace"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/19","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=19"}],"version-history":[{"count":10,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/19\/revisions"}],"predecessor-version":[{"id":170,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/19\/revisions\/170"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=19"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=19"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=19"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}