Correct NLS_LANG for exports

It's important to set the right NLS_LANG environment variable when doing exports/imports.
The following query makes it easy getting the syntax and values right.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
from v$nls_parameters lan,
v$nls_parameters ter,
v$nls_parameters chr
where lan.parameter='NLS_LANGUAGE'
and ter.parameter='NLS_TERRITORY'
and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives the line (e.g.)
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

which can easily be copied before the imp/exp is run.
For windows environments, you would need to replace 'export' by 'set' in the query.

Enjoy!

determine the Oracle version in a nutshell

On a host with multiple Oracle versions in multiple Oracle homes, it might be prove hard to quickly determine the Oracle version in a shell script. I came across the following method and like to share it.

First, grep the line from /etc/oratab which contains the ORACLE_SID at the beginning of that line:

grep -i ^$ORACLE_SID: /etc/oratab

Then cut everything behind the semicolon away:

cut -f2 -d:

this gives you the Oracle home path.

Now cut out everything behind the first dot:

cut -f1 -d.

Taking the basename, will remove the path and leave the Oracle version.
In one statement it will become:

VERSION=$( basename $(grep -i ^$ORACLE_SID: /etc/oratab | cut -f2 -d: | cut -f1 -d. ) )

I know that there are different ways. Suggestions welcome!

Index montoring

You easily forget about all the options you have. I came across a situation, where 39 indexes where created on a single table. This extreme number was grown over the years. There was no administration on which indexes were needed for what purpose. Good luck!

Luckily, you have index monitoring that can be turned on.

alter index MY_IDX_1 monitoring usage;

If you wait for a considerable amount of time, you will see that v$object_usage will contain information on whether the index was used:

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

Note that it is important to log on as the index owner and not as sysdba to see any information in this view. The USED column will probably (maybe) switch from NO to YES if the index is being used.

If you turn off the monitoring:

alter index MY_IDX_1 nomonitoring usage;

the END_MONITORING date field will be filled.

You should not turn off monitoring too soon, there might be monthly reports or hardly used screens that incidentally need the index.

There is a drawback on this. During statististics the index will be used. This will blurr your results. A solution might be to lock statistics during monitoring, using

exec dbms_stats.lock_table_stats(ownname=>'SCOTT',tabname=>'DEPT');

but on heavily changing tables locking statistics might be no such a good idea.

hidden parameter checking

You might want to check the value of hidden parameters in Oracle.... Here's how to do this. You can enter a (part of) the parametername (in lowercase) to find what you're looking for.

set pages 8888
set lines 120
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%&Part_Of_Parametername%' escape '\'
order by
name
;