Script to rebuild all UNUSABLE indexes in oracle

Index rebuild has to be done in oracle databases for different reasons. A planned index rebuild activity can be done monthly or ewe weekly on all indexes that has a benefit after rebuild.

Read Oracle support note on how to find the candidate index to rebuild for performance benefit.

Another immediate index rebuild is to be done when there are unusable indexes in the database.
Oracle index can become unusable for many reasons. Presence of unusable index will effect DML operations in a large extend. Unusable indexes should be rebuilt immediately they are discovered.
(Read: How does an oracle index become unusable)

If there are only a few indexes unusable, We can rebuild the unusable indexes one by one manually. But is there are a lot of indexes to be rebuild, manually rebuilding each index one by one will be a time consuming activity. Also it will take longer if there are partitioned and sub-partitioned indexes.

The script given below will rebuild all indexes selected by the query
   SELECT OWNER, INDEX_NAME,  INDEX_TYPE, PARTITIONED
   FROM ALL_INDEXES 
   WHERE  STATUS = 'UNUSABLE' 
   ORDER BY 1,4;

The WHERE condition can be changed to change the candidate index set. For example if you want to rebuild all indexes from a particular schema you can give condition on the column OWNER.

Find the pl/sql script below just copy paste it to rebuild all unusable indexes including index partitions and index subpartitions in your oracle database.



SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting: UNUSABLE index rebuild');
DBMS_OUTPUT.PUT_LINE('UNPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
            SELECT OWNER,INDEX_NAME FROM ALL_INDEXES 
            WHERE STATUS='UNUSABLE' AND PARTITIONED='NO' ORDER BY 1,2
    )
    LOOP
        BEGIN
              EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER ||'.'|| I.INDEX_NAME || ' REBUILD' ;
              DBMS_OUTPUT.PUT_LINE('REBUILT INDEX '|| I.OWNER ||'.'|| I.INDEX_NAME );
        EXCEPTION
              WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING INDEX: '|| I.OWNER ||'.'|| I.INDEX_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('PARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM ALL_IND_PARTITIONS
             WHERE STATUS='UNUSABLE' AND SUBPARTITION_COUNT=0 ORDER BY 1,2,3
                        )
    LOOP
        BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD PARTITION ' || I.PARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' PARTITION ' || I.PARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING PARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.PARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('SUBPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM ALL_IND_SUBPARTITIONS
             WHERE  STATUS='UNUSABLE' ORDER BY 1,2,3
     )
     LOOP
         BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD SUBPARTITION ' || I.SUBPARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '||  I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' SUBPARTITION ' || I.SUBPARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING SUBPARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.SUBPARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('END: UNUSABLE index rebuild');               
END;
/

Here in the script only basic offline rebuild is included. Other clauses like ONLINE, PARALLEL, NOLOGGING etc can be given as per your requirement.


How to insert & in Oracle table example

I used to get so many calls from developers asking how to insert & into a varchar2 column in oracle?
It is not a difficult question to answer. There are many ways to insert & into a  table column in oracle database.

First of all, if you want to insert simply an &, then have you tried this?

SQL>CREATE TABLE my_table (col1 VARCHAR2(15));
SQL>INSERT INTO my_table VALUES ('&');
1 row created.

It will work perfectly!!. The problem arises when there is something written after &. For example,  When you try to insert the string  'SQL & JAVA' in oracle table, into a varchar2 column,You will get a message 'Enter value for JAVA:'
Do you know why?
Because the character & is set as the define character in oracle by default.
So it defines JAVA as a substitution variable and asks value to be substituted.
You can check it as given below.

SQL> show define
define "&" (hex 26)

Let us move into the topic of how to insert & in oracle table. You have different ways to insert & into oracle table. Here I am explaining them.

1. Put nothing after &

When there is something after &, oracle defines it as a substitution variable(because & means define), and will ask for entering a value for the variable when you execute the command.
Here, I need to insert the string 'SQL & JAVA', then how can I put nothing after &? how will I add 'JAVA' to the string?
 I will split the string into two, 'SQL &' and ' JAVA', and then I will concatenate them. as given in below example.

SQL>INSERT INTO my_table VALUES('SQL &' || ' JAVA');
1 row created.

2.Unset the define character

As I have shown above, & is the default define character. We can unset it and insert & in any oracle table.
Here is the example:

SQL> set define off
SQL>INSERT INTO my_table VALUES('SQL & JAVA');
1 row created.
SQL> set define &

The last line is added to set the define character back to the default settings after inserting the string including & into the desired column.

3. Use escape character

There is no default escape character in Oracle, So we have to define an escape character if you want to use one. You can define any character as an escape character. 

Here is the example of inserting & into an oracle table using escape character,

SQL> show escape
escape OFF
SQL> set escape \
SQL> show escape
escape "\" (hex 5c)
SQL>INSERT INTO my_table VALUES('SQL \& JAVA');
1 row created.
SQL> set escape off

4. Use ascii instead of &

This actually is an over head, but it will be helpful in some type of programs.
The ascii of & is (hex 26) or (dec 38). We can just check it out.

SQL> select chr(38) from dual;

C
-


So in the statement 
INSERT INTO my_table VALUES('SQL & JAVA'); 
we will replace the & with, '||chr(38)||'
so the statement will be 
SQL>INSERT INTO my_table VALUES('SQL '||chr(38)||' JAVA'); 
1 row created.

-----------------------

Let us check whether all values in the table MY_TABLE is inserted correctly by selecting from the table.

SQL> select * from my_table;

COL1
---------------
&
SQL & JAVA
SQL & JAVA
SQL & JAVA
SQL & JAVA

So, The data were inserted correctly. We have learned how to insert & in oracle table.

If you have any other idea, Please post as comment. 

Session generating huge Archive logs

Yesterday we had an issue in our database, I was not there in office and my junior was taking care of the databases.

He was informed that one of the mount point is growing very fast and is about 90% used. The infrastructure team was afraid  of running the server out of space. He some how found out it was huge generation of archive log caused the issue. The growth rate was 1GB per minute.

As an immediate response he took a compressed backup of the archive logs, to the backup mount point from where it will be moved to tape later. He made enough room in the mount point to accommodate archive logs. The archive log generation went normal after some time. But he did not try to catch the session generating huge archive logs.

Today,  I need to find the sessions generated this much archive logs. And also the queries which was responsible for the huge archive log generation yesterday.

Well, I could have used  the query below to list the sessions and the generated redo in MB if the session is still connected. This was the query I used to use whenever I notice a huge archive log generation.
select s.username, s.osuser, s.status,s.sql_id,  sr.*  from 
  (select sid, round(value/1024/1024) as "RedoSize(MB)" 
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;

This could not help me as the connected sessions listed were not responsible for huge redo generation. The session would have logged out yesterday itself.

I thought of changing the above query to list it from the dba_hist views. Well, I can get the history of v$session from dba_hist_active_sess_history. and the v$statsname is static, we just need to get the statistics name from there. But the historical v$sessstat is not there. DBA_HIST_SESSMETRIC_HISTORY is supposed to have the history but the view is not populated(!).

I googled a lot to get some hints. Searched in the Oracle support site. Got a lot of information about redo generation, redo and archive log maintenance. I have listed some of the links in the bottom.

I took an AWR report for the problematic window, and scrolled up and down many times. My eye stuck on the section  'Segments by DB Blocks Changes' where one of the segment was having very huge DB_BLOCK_CHANGES. Which means what? I got the segment which was having huge block changes. Now I just need to find out what was the SQL statement causing this segment change a lot. That will be the SQL statement caused the huge archive generation.  Then I searched in the section 'Complete List of SQL Text' of AWR report to find out the list of SQLs which refers the mentioned segment. With God's grace I could find out the query and session generated huge archive logs yesterday in our database. 

Later on I found this blog related to the same what I need. This is really helpful, He is giving queries to find out the same I did using AWR.
http://appcrawler.com/wordpress/2009/04/15/who-is-generating-all-the-redo/

Some other useful  links:
http://www.4tm.com.ar/blog/2008/oracle-howto-collect-session-history-metrics.html
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs[Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]
http://4dag.cronos.be/village/dvp_forum.OpenThread?ThreadIdA=42511
http://www.orafaq.com/forum/t/180221/0/

Manual Script to compile invalid objects in Oracle

We have many methods to compile invalid objects in oracle, most of the provided methods are for bulk recompiling of the objects. Some times we may have invalid objects distributed in several schema but not all of the objects. In such cases we may have to write manual script to validate/ compile them.

Below is the script to compile all invalid objects which includes PACKAGE, PACKAGE BODY, PROCEDURE, MATERIALIZED VIEW, SYNONYM, VIEW, FUNCTION and TRIGGER.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_invobj IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   dba_objects
                  WHERE  status = 'INVALID'
                  AND    object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','MATERIALIZED VIEW','SYNONYM','VIEW','FUNCTION','TRIGGER')
                  ORDER BY 3)
  LOOP
    BEGIN
      IF cur_invobj.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_invobj.owner ||'"."' || cur_invobj.object_name || '" COMPILE BODY';
      ElSE
        EXECUTE IMMEDIATE 'ALTER ' || cur_invobj.object_type || ' "' || cur_invobj.owner || '"."' || cur_invobj.object_name || '" COMPILE';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Not Compiled: '|| cur_invobj.object_type || ' : ' || cur_invobj.owner || '.' || cur_invobj.object_name);
    END;
  END LOOP;
END;
/

At the end you will get the objects which cannot be compiled. It is not possible to compile an object if there is an error in the code. The actual errors will not be listed while compiling them.
Read: How to find compilation errors related to specific object in oracle

Note:

For invalid synonyms, a select from the synonym will compile them.
Example:
SELECT count (1) FROM my_synonym;

The syntax to compile invalid objects except package body is 
ALTER <OBJECT_TYPE> [<SCHEMA_NAME>].<OBJECT_NAME> COMPILE;

To compile package body, the syntax is,
ALTER PACKAGE [<PACKAGE_OWNER>].<PACKAGE_NAME> COMPILE BODY;

ORA-04031: unable to allocate bytes of shared memory ("","","","")

I see the following lines continuously in the alert log. 

Fri Nov 01 04:04:14 2013
Errors in file /sw/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/trace/EBSPRD_q003_9998.trc:
ORA-22303: type "SYS"."AQ$_AGENT" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","unknown object","KKSSP^774","kgllk")
Fri Nov 01 04:05:28 2013
Errors in file /sw/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/trace/EBSPRD_j000_11170.trc:
ORA-12012: error on auto execute of job 340
ORA-04031: unable to allocate  bytes of shared memory ("","","","")
ORA-04031: unable to allocate  bytes of shared memory ("","","","")
ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","SCHEDULER$_CLASS","KKSSP^2525","kgllk")

Fri Nov 01 04:06:32 2013

Fri Aug 20 15:53:23 2010
Errors in file /opt/oracle/admin/w552pr/dump/bg/orcl102a_j000_14645.trc:
ORA-12012: error on auto execute of job 634953
ORA-04031: unable to allocate 39920 bytes of shared memory ("shared pool","DBMS_AQADM_SYS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at line 1

Below note from the oracle support gives some idea about the same. 

High CPU load analysis in ORACLE with OS PID

What will you do if there is a high load in in your ORACLE database server?

I am sure that you can do something if you can identify what is the oracle database session causes the load and what is the SQL being run by the session.

We can identify the operating system process id (OSPID) which consumes high CPU from the output of "top" command in Linux. You can see from the output of top, whether it is oracle sessions causes the load or something other than the database.

Below is the query/script to display top 15 sessions in the order of CPU consumption with all the session details including SQL ID and OSPID. We can compare the top output and the output of the below query to analyze what are the top CPU consuming sessions.

set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username !='SYS' and
ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc)
where rownum <16;

The above query will display only ACTIVE sessions, you can remove the given condition to get INACTIVE sessions as well. Also it has to be noted that the output of the above query will list in the order of total CPU usage by the sessions. Not by the current CPU usage by the sessions or the database sessions caused the spike. 

We can modify the above query to get all the sessions listed in the top output so that we can get the sessions which are causing the high CPU usage at the given point of time. The modified query will be like

set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username is not null
and ss.paddr=p.addr and value > 0
and p.spid in (give the PIDs from the top output separated by comma)
order by se.VALUE desc;

Now, what we have is the sessions caused high CPU load in our server. While giving the PIDs in the above modified query to find oracle sessions consuming high CPU, make sure that the PIDs are of the oracle process itself.

It is also necessary to check the details of the queries. whether the SQL statements causes high CPU utilization in the database has been running for long time? How long this query is being run? When this is expected to finish? How much percentage of the activity has been done?

We can check all these details from the view V$SESSION_LONG_OPS. Below given query can be used to do the same.

set lines 1000
COLUMN percent FORMAT 999.99 
col start_time for a20
col message for a45
SELECT sid,SERIAL#,SQL_ID,to_char(start_time,'dd-mm-yy hh24:mi:ss') start_time,
ELAPSED_SECONDS/60 Elapsed_Mins,TIME_REMAINING/60 "TIME_REMAINING_Mins" ,message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1;

Here we have SQL ID and other session details. We can compare this with the previous output for analysis.

After the analysis you may reach in a conclusion that a particular statement is responsible for the high CPU load. To find out the SQL text of that particular SQL, We can use the below given query

select sql_text from v$sqltext where sql_id ='&SQL_ID' order by piece;

I hope you must have found out what is the exact reason of the high CPU.