Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

1- Find locked objects and identify the Oracle session :

SELECT s.inst_id, o.owner||’.'||o.object_name objeto, start_time, t.xidusn, s.sid, s.serial#, s.machine, s.username usuario, s.osuser,
DECODE(l.type, ‘TM’, ‘DML’, ‘UL’, ‘PL/SQL User Lock’, l.type) lock_type,
DECODE(lmode,
0, ‘None’, /* Mon Lock equivalent */
1, ‘Null’, /* N */
2, ‘Row-S (SS)’, /* L */
3, ‘Row-X (SX)’, /* R */
4, ‘Share’, /* S */
5, ‘S/Row-X (SSX)’, /* C */
6, ‘Exclusive’, /* X */
TO_CHAR(lmode)) held,
DECODE(request,
0, ‘None’, /* Mon Lock equivalent */
1, ‘Null’, /* N */
2, ‘Row-S (SS)’, /* L */
3, ‘Row-X (SX)’, /* R */
4, ‘Share’, /* S */
5, ‘S/Row-X (SSX)’, /* C */
6, ‘Exclusive’, /* X */
TO_CHAR(request)) requested,
DECODE(block,
0, ‘Not Blocking’, /* NOT blocking any other processes */
1, ‘Blocking’, /* This lock blocks other processes */
2, ‘Global’, /* This lock is global, so we can’t tell */
TO_CHAR(block)) blocking_others
FROM gv$lock l, dba_objects o, gv$session s, gv$transaction t
WHERE l.type IN (‘TM’,'UL’) AND
o.object_id(+) = id1 AND
s.sid = l.sid AND
s.inst_id = l.inst_id AND
l.inst_id = t.inst_id AND
saddr = ses_addr(+)
ORDER BY 1,5;

2- Sql session Trace :

SELECT SUBSTR(USERNAME, 1, 10) “User”,
SUBSTR(MACHINE, 1, 40) “Machine”,
SHARABLE_MEM,
PERSISTENT_MEM,
RUNTIME_MEM,
EXECUTIONS,
V$SQL.MODULE,
SUBSTR(V$SQL.SQL_TEXT, 1, 60) “Statement”
FROM V$SESSION, V$SQL, V$OPEN_CURSOR
WHERE V$OPEN_CURSOR.SADDR = V$SESSION.SADDR
AND V$OPEN_CURSOR.ADDRESS = V$SQL.ADDRESS
AND ROWNUM <= 50
AND V$SQL.SQL_ID =”gc1bqqbahdz9v”;
ORDER BY SUBSTR(USERNAME, 1, 10), SUBSTR(MACHINE, 1, 10);

3- Determine Roles and Privileges Granted to an Oracle User :

SELECT GRANTEE, ‘ROL’ TYPE, GRANTED_ROLE PV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ‘&usercheck’
UNION
SELECT GRANTEE, ‘PRV’ TYPE, PRIVILEGE PV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = ‘&usercheck’
UNION
SELECT GRANTEE,
‘OBJ’ TYPE,
MAX(DECODE(PRIVILEGE, ‘WRITE’, ‘WRITE,’)) ||
MAX(DECODE(PRIVILEGE, ‘READ’, ‘READ’)) ||
MAX(DECODE(PRIVILEGE, ‘EXECUTE’, ‘EXECUTE’)) ||
MAX(DECODE(PRIVILEGE, ‘SELECT’, ‘SELECT’)) ||
MAX(DECODE(PRIVILEGE, ‘DELETE’, ‘,DELETE’)) ||
MAX(DECODE(PRIVILEGE, ‘UPDATE’, ‘,UPDATE’)) ||
MAX(DECODE(PRIVILEGE, ‘INSERT’, ‘,INSERT’)) || ‘ ON ‘ || OBJECT_TYPE || ‘ “‘ ||
A.OWNER || ‘.’ || TABLE_NAME || ‘”‘ PV
FROM DBA_TAB_PRIVS A, DBA_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.GRANTEE = ‘&usercheck’
GROUP BY A.OWNER, TABLE_NAME, OBJECT_TYPE, GRANTEE
UNION
SELECT USERNAME GRANTEE, ‘—’ TYPE, ‘empty user —’ PV
FROM DBA_USERS
WHERE NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_TAB_PRIVS)
AND USERNAME LIKE ‘%&usercheck%’
GROUP BY USERNAME
ORDER BY GRANTEE, TYPE, PV;

4- Commit vs Checkpoint :

A commit flushes whatever in the redolog buffer to the redo log files.
A redo log buffer contains changed data.
But it is not just commit that flushes redolog buffer to redo log files.
LGWR activates whenever:
1)a commit occurs
2)when redo log is 1/3rd full
3)every 3sec
It is not always necessary that redolog file will contain committed data.
If there is no commit after 3 secs,redologfile would bound to contain uncommitted data.
What about datafiles?
DBWR writes whenever :
1) a checkpoint occurs
2) and when there are few free buffers than required by the sever process.

But what happens when we commit?
1)writes redolog buffer to redolog files
2)releases locks on TABLES

And checkpoint does the follwoing
1)flushes changed block from data buffer cache to datafiles
2)mark all the datafiles and controlfiles with the same scn.

But who transfers changed data from data buffer cache to redo log buffer cache?
=> its the server process