Script to generate SQLs to terminate rman sessions

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″

Internal parameters

– view oracle internal parameters, note this can be run by SYS user
– name, value, default value, session modifiable, system modifiable, description
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by name
/

– If SYSMOD = Immediate (The Parameter can be changed with ALTER SYSTEM without restart of th e instance)
– If SYSMOD = DEFERRED (The Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions)
– If SYSMOD = FALSE (The parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances)

ORA-19206 when running export (exp)

On Oracle9i if you get the following error when running export (exp) utility, to fix this issue one would need to run the $ORACLE_HOME/rdbms/admin/catmeta.sql as sys to fix this issue.

About to export SYSTEM’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at “SYS.DBMS_XMLGEN”, line 83
ORA-06512: at “SYS.DBMS_METADATA”, line 345
ORA-06512: at “SYS.DBMS_METADATA”, line 410
ORA-06512: at “SYS.DBMS_METADATA”, line 449
ORA-06512: at “SYS.DBMS_METADATA”, line 1156
ORA-06512: at “SYS.DBMS_METADATA”, line 1141
ORA-06512: at line 1

Setting NLS_LANG for Oracle

NLS_LANG=<language>_<territory>.<character set>
Example: export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Setting NLS_LANG tells Oracle what characterset the client is using so Oracle can do conversion if needed from client’s characterset to the database characterset and setting this parameter on the client does not change the client’s characterset. Setting Language and Territory in NLS_LANG has nothing to do with storing characters in database, it’s controlled by the characterset and of course if the database can store that characterset.

To check session NLS session parameters, note this doesn’t return the characterset set by NLS_LANG
SELECT * FROM NLS_SESSION_PARAMETERS;
SELECT USERENV (‘language’) FROM DUAL;

To find the NLS_LANG of your database one can run the following SQL:
SQL> SELECT * from NLS_DATABASE_PARAMETERS WHERE parameter IN ( ‘NLS_LANGUAGE’, ‘NLS_TERRITORY’, ‘NLS_CHARACTERSET’);

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252

To get the possible value for language, territory and characterset you can check the view V$NLS_VALID_VALUES.
– parameter – possible values LANGUAGE, TERRITORY, CHARACTERSET
SELECT parameter, value FROM V$NLS_VALID_VALUES;

To change the client language
export NLS_LANG=BELGIUM_.WE8ISO8859P1
To change the client territory
export NLS_LANG=_BELGIUM.WE8ISO8859P1
To change the client characterset
export NLS_LANG=.AL32UTF8

French accents character when using SQL*Plus

For SQL*Plus to process french accents characters when using SQL*Plus, you would need to set NLS_LANG

On Windows
set NLS_LANG=.AL32UTF8
set NLS_LANG=.UTF8
On Unix (ksh)
export NLS_LANG=.AL32UTF8
set NLS_LANG=.UTF8

Now when you start SQLPlus from Unix or Windows it will keep the accents on the french characters.