SQL - SUBSTR

Giriş
Belirtilen konumlar arasındaki string'i döner. İlk parametre string değeridir. İkinci ve üçüncü parametre konum değeridir.

Örnek
Şöyle yaparız. ":" karakterine kadar olan kısım isteniyor.

SQL> SELECT substr(VALUE, 1, instr(VALUE, ':')-1) FROM MY_PROPERTIES WHERE KEY = 'IP2';
--------------------------------------------------------------------------------
10.179.66.73

Örnek
Şöyle yaparız

with t as
(
select 'red/green/blue' as str from dual
)
select substr(str,1,instr(str,'/',1,1)-1) str1, -- birinci / karakterine kadar
       substr(str,1,instr(str,'/',1,2)-1) str2, -- ikinci / karakterine kadar
       str as str_whole
  from t;

STR1    STR2        STR_WHOLE
----    ----------  --------------
red     red/green   red/green/blue

v$session Sistem Tablosu

Giriş
TCP bağlantısı eşittir session gibi düşünülüyor. Ancak böyle olmak zorunda değil. Açıklaması şöyle

Connections vs. Sessions

It surprises many people to discover that a connection is not synonymous with a session. In most people’s eyes they are the same, but the reality is they do not have to be. A connection may have zero, one, or more sessions established on it. Each session is separate and independent, even though they all share the same physical connection to the database. A commit in one session does not affect any other session on that connection. In fact, each session using that connection could use different user identities! In Oracle, a connection is simply a physical circuit between your client process and the database instance — a network connection, most commonly. The connection may be to a dedicated server process or to a dispatcher. As previously stated, a connection may have zero or more sessions, meaning that a connection may exist with no corresponding sessions.

v$session Tablosu Nedir

Açıklaması şöyle

The standard views to check for database performance are v$session, v$sql, and others. The v$ views work well if the database is hosted on a single node. However, if the database is split into multiple nodes, we need to look at the gv$ views. The INST_ID column will tell us which exact node is fulfilling the request.

Örnek - All Established Connections

Şöyle yaparız

elect status, count(1) 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
group by status;

Örnek - Active Connections

Şöyle yaparız

select last_call_et, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
and status = 'ACTIVE' 
order by last_call_et desc, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait;

select * from gv$sql where sql_id = '<above-sql-id>';

Örnek - Inactive Connections

Şöyle yaparız

select last_call_et, module, prev_sql_id   from gv$session 
  where module = '<module-name>' 
  and type = 'USER' 
  and status = 'INACTIVE';

select * from gv$sql where sql_id = '<above-prev-sql-id>';  

Örnek
Şöyle yaparız.

SQL> select sid, process, port, paddr from v$session where username = 'BP';

       SID PROCESS                        PORT PADDR
---------- ------------------------ ---------- ----------------
       395 31251                         35298 0000000066E75338
       399 31251                         35298 0000000066E75338




JSON_VALUE

Giriş
Açıklaması şöyle.

Oracle Database 18c fully supports schemaless application development using the JSON data model. This allows for a hybrid development approach: all of the schema flexibility and speedy application development of NoSQL document stores, combined with all of the enterprise-ready features in Oracle Database 18c.

Örnek
Şöyle yaparız.

SELECT CASE WHEN SBM_MESSAGE_BODY IS JSON THEN 1 ELSE 0 END AS IS_JSON,  
       JSON_VALUE(SBM_MESSAGE_BODY, '$.CRMId') AS CRMId
  FROM SBM_SERVICEBUS_MESSAGE 
 WHERE SBM_ID=1;