How to generate a script to rebuild unusable index partitions

set trimspool on
set lines     200
set pages     0
set heading   off
set verify    off
set feedback  off
set echo      off
spool rebuild_stmts.sql
--Rebuild the index partition to the same tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ';'
FROM   dba_indexes idx, dba_ind_partitions idxpart
WHERE  idx.table_owner = 'SCOTT'
AND    idx.index_name = idxpart.index_name
AND    idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--For a specific INDEX, reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = 'IDX_01'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--Use sqlplus variables, also reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
-- Rebuild index subpartitions. Also add a parallel statement and the ONLINE keyword:
SELECT 'alter index ' || IDX.TABLE_OWNER || '.' ||  IDX.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' parallel 4 tablespace ' || IDXSUBPART.TABLESPACE_NAME ||' online;'
FROM    DBA_INDEXES IDX, DBA_IND_SUBPARTITIONS IDXSUBPART
WHERE   IDX.TABLE_OWNER IN ('SCOTT','JAMES')
AND     IDX.INDEX_NAME = IDXSUBPART.INDEX_NAME
AND     IDX.INDEX_NAME IN (SELECT UNIQUE INDEX_NAME
                                        FROM DBA_IND_SUBPARTITIONS
                                        WHERE STATUS = 'UNUSABLE')
AND INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND IDXSUBPART.STATUS <> 'USABLE'
ORDER BY IDX.OWNER, IDX.INDEX_NAME
/

Upon completion, the file "rebuild_stmts.sql" should contain your "ALTER INDEX REBUILD PARTITION..." directives.

How to display session information, followed by a kill-statement

Often, my customers ask me to kill a runaway session for them, typically this will be in the form "Can you please kill session with session id 170 for us?. It's urgent!"

Before I do so, I'd like to be one houndred percent certain that I kill the right session.
To help me in such situations, I execute the script below. It will take one parameter, the sessions SID, and echo back some basic information about what the session is doing. Finally it prints a "kill session" command, which I can choose to execute if it was indeed the right session.

set verify           off
set linesize 200
col "os process"     format a30
col "os process id"  format a10
col "osuser"         format a15
col "schemaname"     format a20
col "client program" format a20
col "client name"    format a20
col "session type"   format a20
col status           format a10
col "session type"   format a15
cle scr
prompt ===================================================
prompt This script will print basic information about a
prompt session.
prompt
accept SID prompt 'Pls enter SID of session: '
prompt ===================================================
prompt  Information about database session &&SID
prompt ===================================================
column host_name new_value v_hostname;

set termout off
select host_name
from   v$instance;
set termout on

select
        p.program "os process",
        p.spid "os process id",
        p.username "osuser",
        s.sid,s.serial# "serial num",
        lower(s.schemaname) "schemaname",
        lower(s.osuser) "client name",
        s.program "client program",
        lower(s.status) "status",
        lower(s.type) "session type"
from v$process p, v$session s
where p.program not like 'oracle@v_hostname (%'
and p.addr = s.paddr
and p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/
set heading off
prompt ===================================================
prompt  Kill statement for session &&SID:
prompt ===================================================
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
from   v$process p, v$session s
where  p.program not like 'oracle@v_hostname (%'
and    p.addr = s.paddr
and    p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/

exit

How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here

How to retreive information about CPUs on a Solaris server

For analytic purposes when dealing with installations, system validations, database design and performance tuning, you will often need to know hard facts about the CPUs of a server hosting an oracle database. Here are some of my notes on the matter.

Use the psrinfo utility. The flag -p displays the number of physical processors, and the -v flag is for verbose output:

oracle@host1[PRODDB11] psrinfo -pv
The physical processor has 8 virtual processors (0-7)
SPARC64-VII (portid 1024 impl 0x7 ver 0x91 clock 2400 MHz)
The physical processor has 8 virtual processors (8-15)
SPARC64-VII (portid 1032 impl 0x7 ver 0x91 clock 2400 MHz)

Without any argument, psrinfo prints a tabular output of the CPUs(or cores), as follows:
oracle@host1[PRODDB11] psrinfo
0 on-line since 07/18/2011 18:18:57
1 on-line since 07/18/2011 18:19:58
2 on-line since 07/18/2011 18:19:58
3 on-line since 07/18/2011 18:19:58
4 on-line since 07/18/2011 18:19:58
5 on-line since 07/18/2011 18:19:58
6 on-line since 07/18/2011 18:19:58
7 on-line since 07/18/2011 18:19:58
8 on-line since 07/18/2011 18:19:58
9 on-line since 07/18/2011 18:19:58
10 on-line since 07/18/2011 18:19:58
11 on-line since 07/18/2011 18:19:58
12 on-line since 07/18/2011 18:19:58
13 on-line since 07/18/2011 18:19:58
14 on-line since 07/18/2011 18:19:58
15 on-line since 07/18/2011 18:19:58

The utility uname can also be helpful, when executed with the -X flag, which prints expanded system information:

oraoracle@host1[PRODDB11] uname -X
System = SunOS
Node = zus60h-0034
Release = 5.10
KernelID = Generic_137111-04
Machine = sun4u
BusType =
Serial =
Users =
OEM# = 0
Origin# = 1
NumCPU = 16

The prtdiag utility, likewise:

oraoracle@host1[PRODDB11] prtdiag |more
System Configuration: Sun Microsystems sun4u Sun SPARC Enterprise M4000 Server
System clock frequency: 1012 MHz
Memory size: 32768 Megabytes

==================================== CPUs ====================================

CPU CPU Run L2$ CPU CPU
LSB Chip ID MHz MB Impl. Mask
--- ---- ---------------------------------------- ---- --- ----- ----
00 0 0, 1, 2, 3, 4, 5, 6, 7 2530 5.5 7 160
00 1 8, 9, 10, 11, 12, 13, 14, 15 2530 5.5 7 160

How to install java in an Oracle 11gR2 database

Source: Oracle Support article "How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)"

Here are the steps I followed:

First, confirmed that java is indeed not installed (see my previous note "Is java installed in my database")

Step 1: Executed full export or another type of full backup (Oracle recommends a cold backup):
Create a parameter file as follows:
USERID='/ as sysdba'
DIRECTORY=DPUMP
DUMPFILE=full_exp_before_java.dmp
LOGFILE=full_exp_before_java.log
JOB_NAME=PREJAVA_EXP
FULL=Y

Execute the export from the command line:

$ [PRODDB11] expdp parfile=full_exp.par

Check that export finished successfully:
Dump file set for SYS.PREJAVA_EXP is:
/db_impexp/oracle/PRODDB1/full_exp_before_java.dmp
Job "SYS"."PREJAVA_EXP" successfully completed at 09:10:41

Step 2: shutdown of the database:
$ [PRODDB11] srvctl status database -d PRODDB10
Instance PRODDB11 is running on node node1
$ [PRODDB11] srvctl stop database -d PRODDB1
$ [PRODDB11] srvctl status database -d PRODDB1
Instance PRODDB1 is not running on node node1

Step 3: Create script full_rmjvm.sql:
-- Start of File full_rmjvm.sql
spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnoexf.sql
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql

Step 4: Remove existing java installations, if any. From a new sqlplus session, run the script full_rmjvm.sql.
(Note that the script will start up the database for you)
$ [PRODDB11] sqlplus / as sysdba @rmjvm.sql

Step 5: Review the log file FULL_RMJVM.LOG
In my case, java was not installed, so the removal scritp had nothing to do and exited with the following message:

ERROR at line 1:
ORA-20000: CATJAVA has not been loaded into the database.
ORA-06512: at line 3

Step 6: Create script full_jvminst.sql
-- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst.sql

Step 7: Install the JVM
a) Be sure the REMOVAL script, full_rmjvm.sql, completed successfully
b) Create the following INSTALL script, full_jvminst.sql
c) Run it from a new SQL*Plus session
$ [PRODDB11] sqlplus / as sysdba @full_jvminst.sql

Step 8: Review the log file FULL_JVMINST.LOG
In my case, there were no errors and the logfile ends with:

SQL> select count(*), object_type from all_objects
  2  where object_type like '%JAVA%' group by object_type;

  COUNT(*) OBJECT_TYPE                                                          
---------- -------------------                                                  
       317 JAVA DATA                                                            
       762 JAVA RESOURCE                                                        
     20332 JAVA CLASS                                                           
         2 JAVA SOURCE                                                          

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 9: Start the database:
$ [PRODDB11] srvctl start database -d PRODDB10
Step 10: Resolve Invalid Objects:
$ [PRODDB11] sqlplus / as sysdba @?/rdbms/admin/utlrp.sql
Step 11: Create the file validate_java_installation.sql

-- Start of File validate_java_installation.sql
set lines 200
col banner format a80
col comp_name format a30
col version format a20
col status format a20
-- Validation Query 1
select count(*), object_type
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
group by object_type;

-- Validation Query 2
select owner, count(*)
from all_objects 
where object_type like '%JAVA%'
and owner = 'SYS'group by owner;

-- Validation Query 3
select owner, object_type, count(*)
from all_objects 
where object_type like '%JAVA%'
and status <> 'VALID'
and owner = 'SYS'
group by owner, object_type;

SELECT * FROM ALL_REGISTRY_BANNERS
WHERE   BANNER LIKE INITCAP('%Java%') 
OR      BANNER LIKE UPPER('%JAVA%');

SELECT  COMP_NAME,VERSION,STATUS 
FROM    DBA_REGISTRY 
WHERE   COMP_NAME LIKE INITCAP('%Java%') 
OR      COMP_NAME LIKE UPPER('%JAVA%');

SELECT * FROM V$OPTION 
WHERE PARAMETER = 'Java';
exit
-- End of File validate_java_installation.sql

Step 12: Validate the Install. Expected output should be similar to the following:
$ [PRODDB11] sqlplus / as sysdba @validate_java_installation.sql

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 8 11:19:04 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

  COUNT(*) OBJECT_TYPE
---------- -------------------
       317 JAVA DATA
       761 JAVA RESOURCE
     20285 JAVA CLASS
         2 JAVA SOURCE

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 21365

no rows selected

BANNER
--------------------------------------------------------------------------------
JServer JAVA Virtual Machine Release 11.2.0.2.0 - Development
Oracle Database Java Packages Release 11.2.0.2.0 - Development

COMP_NAME                      VERSION              STATUS
------------------------------ -------------------- --------------------
JServer JAVA Virtual Machine   11.2.0.2.0           VALID
Oracle Database Java Packages  11.2.0.2.0           VALID

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Java                                                             TRUE

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options´

-- All done! --

Is java installed in my database?

Check the following views to confirm whether or not java is a part of your Oracle installation:

ALL_REGISTRY_BANNERS displays the valid components loaded into the database.

SELECT * FROM ALL_REGISTRY_BANNERS
WHERE   BANNER LIKE INITCAP('%Java%') 
OR      BANNER LIKE UPPER('%JAVA%');

If java is installed, the query would typically return:

BANNER
--------------------------------------------------------------------------------
JServer JAVA Virtual Machine Release 11.1.0.7.0 - Production
Oracle Database Java Packages Release 11.1.0.7.0 - Production

DBA_REGISTRY displays information about the components loaded into the database.

SELECT  COMP_NAME,VERSION,STATUS 
FROM    DBA_REGISTRY 
WHERE   COMP_NAME LIKE INITCAP('%Java%') 
OR      COMP_NAME LIKE UPPER('%JAVA%');

If java is installed, the query would typically return:

COMP_NAME                                VERSION              STATUS
---------------------------------------- -------------------- ---------------------------------
JServer JAVA Virtual Machine             11.1.0.7.0           VALID
Oracle Database Java Packages            11.1.0.7.0           VALID

V$OPTION lists database options and features.
Some options must be separately purchased and installed, while other features come with the product and are enabled based on the product that is running (Standard Edition, Enterprise Edition, or Personal Oracle).

SELECT * FROM V$OPTION 
WHERE PARAMETER = 'Java';

Source: Oracle Documentation

SQL statement to list all database objects and their sizes

Use the WITH clause to create two different subqueries that you can reference later in the query:

WITH
  OBJECT_COUNT AS
   (
    SELECT OWNER, OBJECT_TYPE, COUNT(*) "NUM_OBJECTS"
    FROM DBA_OBJECTS
    GROUP BY OWNER,OBJECT_TYPE
    ),
    SPACE_USAGE AS
    (
    SELECT SEGMENT_TYPE, SUM(BYTES)  "BYTES"
    FROM DBA_SEGMENTS
    GROUP BY SEGMENT_TYPE
    )
    SELECT  O.OWNER,O.OBJECT_TYPE,O.NUM_OBJECTS, ROUND(U.BYTES/1024/1024) "MB"
    FROM    OBJECT_COUNT O LEFT OUTER JOIN SPACE_USAGE U
    ON      O.OBJECT_TYPE = U.SEGMENT_TYPE
    ORDER BY 1 ASC;