Oracle DBA Daily Experiences

In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.

select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null — to ignore background process
order by last_call_et;

USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

At times you may have a scenario where you may want to monitor temp space usage in a running session. So using the following PL/SQL code loops which inserts every interval on temp usage for a specific sid (session id).

create table monitor_usage (
RUN_DATE DATE,
BLOCKS NUMBER(38),
BYTES NUMBER(38),
APP VARCHAR2(10)
);

declare
cursor l_cur IS SELECT sysdate, b.blocks, b.blocks*d.value bytes
FROM v$session a, (select session_addr, sum(blocks) blocks from v$tempseg_usage group by session_addr) b, v$sqlarea c,
(select value from v$parameter where name=’db_block_size’) d
WHERE a.saddr = b.session_addr
and a.sid = &sid
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*d.value > 1024;
l_rec l_cur%rowtype;
app varchar2(2) := ‘&1′;
interval integer := 10;
begin
while true loop
open l_cur;
fetch l_cur into l_rec;
If l_cur%found THEN
insert into monitor_usage values (l_rec.sysdate, l_rec.blocks, l_rec.bytes, app);
commit;
end if;
DBMS_LOCK.sleep(seconds => interval);
close l_cur;
end loop;
end;
/

Example:

SQL> alter session set nls_Date_format=’DD-MON-YYY

Session altered.

SQL> select * from monitor_usage where app = ‘R1′
RUN_DATE BLOCKS BYTES APP
——————– ———- ———- ——-
15-JUN-2010 10:21:21 1792 14680064 R1
15-JUN-2010 10:21:32 8576 70254592 R1
…
15-JUN-2010 10:27:52 119808 981467136 R1
15-JUN-2010 10:28:04 122240 1001390080 R1
15-JUN-2010 10:28:15 124672 1021313024 R1
…
15-JUN-2010 10:29:59 33920 277872640 R1
15-JUN-2010 10:30:10 34048 278921216 R1

In 10g, there is a column in V$session called blocking_session to find the session id that is blocking.

Session 1) sid # 145

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
145

SQL> create table t ( x number );

Table created.

SQL> create unique index t_idx on t(x);

Index created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

Session #2) sid – 159

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
159

– hung as 145 is blocking as i haven’t committed as the table t has primary key
SQL> insert into t values ( 1 );

Session 1) to find the blocking_session

– shows both session are active, the second session# 159 is blocked by 145

SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);

SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE

– another way of find sessions blocked the status of blocked session is VALID
SQL> select sid, blocking_session, seconds_in_wait from v$session where blocking_session_status = 'VALID';

SID BLOCKING_SESSION SECONDS_IN_WAIT
———- —————- —————
159 145 130

By querying V$parameter one can find if the parameter can be modified in session and/or system.

If isses_modifiable is true then value can be modified in session, if FALSE then it can’t be modified using ALTER SESSION SET..

If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and the change will take into effect into subsequent sessions.

If issys_modifiable =FALSE, the value can be changed using ALTER SYSTEM only if spfile is used and the change will take into effect after instance restart.

If issys_modifiable = IMMEDIATE, the value can be changed with scope=[memory|both|spfile]

To find out if the database is using spfile is being used refer to the following link http://oraclespin.wordpress.com/2008/06/22/how-to-tell-if-the-database-was-started-with-spfile-or-pfile/

Eg:

SELECT  isses_modifiable, issys_modifiable FROM V$parameter WHERE name = ‘shared_pool_size’;

ISSES   ISSYS_MOD

—– ———
FALSE  IMMEDIATE

– note this will work when not logged in as SYS, as the session id is the same for all background processes
SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.audsid = userenv(‘sessionid’);

Or for SYS you can use the following
SQL> SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = (select sid from v$mystat where rownum = 1);

ORACLE_DEDIC CLIENTPID
———— ————
2301958 839726

1 row selected.

SQL> !ps -ef | grep 2301958
oracle 2301958 839726 0 15:18:04 – 0:00 oracleTEST (DESCRIPTION=(LOCAL=YES(ADDRESS=(PROTOCOL=beq)))
oracle 2441306 839726 0 15:18:13 pts/4 0:00 grep 2301958

SQL> !ps -ef | grep 839726
oracle 839726 2928804 0 15:18:02 pts/4 0:00 sqlplus
oracle 2441308 839726 0 15:18:19 pts/4 0:00 grep 839726

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.
SQL> STARTUP RESTRICT;

– shows database is in restricted mode
12:49:30 @> select logins from v$instance;

LOGINS
———-
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
– find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
———— ————————————-
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.

CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
ora_username VARCHAR2(30) DEFAULT NULL;
os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE audsid = userenv(‘sessionid’)
– and upper(machine) like ‘ABC123′
and username = ‘EPCBATCH’;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename,v_program;
IF c1%FOUND THEN
DBMS_SESSION.set_sql_trace (TRUE);
END IF;
CLOSE c1;
END;
/

To see current stats of your session, one can run the following query.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#;

To view session stats of another session one can run the query below passing the session id.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# and sid = &v_sid;

Killing the rman main process from the command line doesn’t immediately kill the rman sessions using the script below which will create SQL statements to kill your RMAN sessions in Oracle and in most cases it will also terminate the oracle shadow process. The script below generates ALTER statements to kill oracle session(s) and a ps command to verify the UNIX processes have been terminated. The script works on 8i, 9i and 10g

Script:
set linesize 120
set serveroutput on

declare
cursor l_cur is select ‘alter system kill session ”’ || s.sid || ‘,’ || s.serial# || ”’;’ msg, spid
from v$session s, V$process p
where s.program like ‘%rman%’
and s.paddr = p.addr;
l_found boolean := false;
l_process_list VARCHAR2(2000) := null;
begin
for l_rec in l_cur
loop
if l_process_list is not null Then
– create process list to use with ps
l_process_list := l_process_list || ‘|’;
end if;

l_found := true;
dbms_output.put_line(l_rec.msg);
l_process_list := l_process_list || l_rec.spid;
end loop;

if l_found = FALSE THEN
dbms_output.put_line(‘No RMAN sessions found’);
else
dbms_output.put_line(‘ps -ef | egrep “‘ || l_process_list || ‘”‘);
end if;
end;
/

Output:
alter system kill session ’19,9935′;
alter system kill session ’21,9470′;
…
ps -ef | egrep “9248918|9109690″