Detect Unindexed Foreign Keys

Unindexed foreign keys continue to be a prime performance bottleneck.

Here is a script that will run through the foreign keys in a schema and display them all and the index that is associated with them (or not) and indicate whether the foreign key requires an index.

set lines 140
set pages 1000

col status for a6
col child_table for a24
col fk_columns for a27
col indexed_columns for a27
col parent_table for a24
col pk_columns for a27

select decode( b.table_name, NULL, '****', 'ok' ) Status
     , a.table_name                               Child_Table
     , c.table_name                               Parent_Table
     , c.columns                                  PK_Columns
     , a.columns                                  FK_Columns
     , b.columns                                  Indexed_Columns
from
( select a.table_name
       , a.constraint_name
       , b.r_constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name
          , a.constraint_name
          , b.r_constraint_name ) a,
( select table_name
       , index_name
       , max(decode(column_position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_ind_columns
   group by table_name
          , index_name ) b,
( select a.table_name
       , a.constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P' , 'U' )
   group by a.table_name
          , a.constraint_name ) c
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  and a.r_constraint_name = c.constraint_name
order by a.table_name
/

You will need quite a wide screen to incorporate all the data. I use 140 character width.

How to disable the DBMS_SCHEDULER

I discovered this after searching all over the web and am putting it here so that I do not have to do that again.

To turn the scheduler off use

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE')

Note that the TRUE is not a BOOLEAN but text.

To check the status

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

To turn the scheduler back on again

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE')

Creating Object DDL

Here is some handy code to create object DDL using DBMS_METADATA.

There are 2 scripts, I shall call them the calling script and the running script.

The calling script (which I called get_obj.sql) is as follows

doc
 Possible objects to get DDL for are :-
  CONSTRAINT
  DB_LINK
  DIRECTORY
  FUNCTION
  INDEX
  MATERIALIZED_VIEW
  MATERIALIZED_VIEW_LOG
  PACKAGE_SPEC
  PACKAGE_BODY
  PROCEDURE
  SEQUENCE
  SYNONYM
  TABLE
  TRIGGER
  TYPE_SPEC
  TYPE_BODY
  VIEW
#

accept Object prompt  'Enter Object type -> '
accept OwnerIN prompt   "Enter Schema Owner (default %) -> "
accept ObjNameIN prompt "Enter Object Name (default %) -> "

set verify off 
set head off
set feed off
set pages 0
set trims on
set term off
set lines 200

col spool_on      for a200
col select_clause for a200
col from_clause   for a200
col where_clause  for a200
col and_clause    for a200
col object_clause for a200
col spool_off     for a200

col object_type_dir  new_value dir_and_type  noprint
col object_type_dirs new_value dir_and_types noprint

col OwnerOUT new_value Owner noprint
col ObjNameOUT new_value ObjName noprint

select nvl('&OwnerIN','%') OwnerOUT , nvl('&ObjNameIN','%') ObjNameOUT from dual;

SELECT 
   CASE upper('&Object')
  WHEN 'MATERIALIZED_VIEW'     THEN 'mview'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'mview_log'
  WHEN 'PACKAGE_SPEC'          THEN 'package'
  WHEN 'TYPE_SPEC'             THEN 'type'
  ELSE                              lower('&Object')
   END        object_type_dir 
FROM      dual;

SELECT 
  CASE '&dir_and_type'
  WHEN 'index'                 THEN 'indexes'
  WHEN 'package_body'          THEN 'package_bodies'
  WHEN 'type_body'             THEN 'type_bodies'
  ELSE                              '&dir_and_type'||'s'
  END         object_type_dirs
FROM     dual;

spool get_sp_obj.sql

SELECT     'spool do_get_sp_obj.sql'                                                spool_on
 ,  q'[SELECT '@@get_ddl '||upper('&Object')||' '||]'
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[log_owner||' '||log_table||' '||log_owner||'/]'||'&dir_and_types'||q'[ '||master]'
  WHEN 'DB_LINK'               THEN q'[owner||' '||db_link||' '||owner||'/]'||'&dir_and_types'||q'[/'||db_link]'
  WHEN 'FUNCTION'              THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PROCEDURE'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_SPEC'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_BODY'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'SEQUENCE'              THEN q'[sequence_owner||' '||sequence_name||' '||sequence_owner||'/]'||'&dir_and_types'||q'[ '||sequence_name]'
  WHEN 'TYPE_SPEC'             THEN q'[owner||' '||type_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||type_name]'
  WHEN 'TYPE_BODY'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  ELSE                              q'[owner||' '||]'||'&dir_and_type'||q'[_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||]'||'&dir_and_type'||'_name'
    END                                                                      select_clause
 ,  'from '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN 'dba_objects'
  WHEN 'PROCEDURE'             THEN 'dba_objects'
  WHEN 'PACKAGE_SPEC'          THEN 'dba_objects'
  WHEN 'PACKAGE_BODY'          THEN 'dba_objects'
  WHEN 'TYPE_SPEC'             THEN 'dba_types'
  WHEN 'TYPE_BODY'             THEN 'dba_objects'
  ELSE                              'dba_'||'&dir_and_types'
    END                                                                      from_clause
 , 'where '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'log_owner'
  WHEN 'SEQUENCE'              THEN 'sequence_owner'
  ELSE                              'owner'
    END
 || q'[ like upper('&Owner')]'                                                    where_clause
 , 'and '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN q'[object_type = 'FUNCTION']'
  WHEN 'PROCEDURE'             THEN q'[object_type = 'PROCEDURE']'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_type = 'PACKAGE']'
  WHEN 'PACKAGE_BODY'          THEN q'[object_type = 'PACKAGE BODY']'
  WHEN 'TYPE_BODY'             THEN q'[object_type = 'TYPE BODY']'
  ELSE                              '1 = 1'
    END                                                                     and_clause
 , 'and '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[master like upper('&ObjName')]'
  WHEN 'DB_LINK'               THEN q'[db_link like upper('&ObjName')]'
  WHEN 'FUNCTION'              THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PROCEDURE'             THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_BODY'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'TYPE_SPEC'             THEN q'[type_name like upper('&ObjName')]'
  WHEN 'TYPE_BODY'             THEN q'[object_name like upper('&ObjName')]'
  ELSE                              '&dir_and_type'||q'[_name like upper('&ObjName')]'
    END
 || ';'                                                                     object_clause
 ,  'spool off'                                                             spool_off
FROM    dual
/
spool off

@@get_sp_obj.sql

@@do_get_sp_obj.sql

set verify on
set head on
set pages 60
set lines 140
set term on
set feed 5

This script as you can see on line 72 calls the running script called get_ddl.sql

Here is get_ddl.sql

set lines 32767
set pages 0
set head off
set feed off
set trims on
set verify off
set term off
set long 5000000

col text for a32767

!if [ ! -d &4 ]; then mkdir -p &4;fi

spool &4/&5..sql

select dbms_metadata.get_ddl('&1','&3','&2') text from dual
/

prompt /

spool off

Load both of these scripts into the same directory and call the calling script from SQL*Plus.

For example

@get_obj

You will be prompted to input some object_types and possibly names and each object creation script will be spooled to its individual file in a directory structure that matches the object type.
For example all the table definitions will be in a directory named 'tables'.

These scripts can be handy if you need to do a lot of reconfiguring or simply wish to have a record of schema objects.

Finding the Events

To find out which events you have set in your 10g oracle database use the following from SQL*Plus when connected as sysdba.

oradebug setmypid
oradebug dump events 4

Find the trace file in the user dump destination and it will contain something like this

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/product/10.2.0
System name:    Linux
Node name:      bob
Release:        2.6.9-78.0.8.ELxenU
Version:        #1 SMP Wed Nov 5 07:40:25 EST 2008
Machine:        x86_64
Instance name: fred
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 26048, image: oracle@bob (TNS V1-V3)

*** 2010-02-10 17:26:22.100
*** ACTION NAME:() 2010-02-10 17:26:22.099
*** MODULE NAME:(sqlplus@bob (TNS V1-V3)) 2010-02-10 17:26:22.099
*** SERVICE NAME:(SYS$USERS) 2010-02-10 17:26:22.099
*** SESSION ID:(197.7107) 2010-02-10 17:26:22.099
Dump event group for level SYSTEM
TC Addr  Evt#(b10)   Action   TR Addr    Arm    Life
81246A20 10298       1       81246ab0 0 0
       TR Name             TR level   TR address       TR arm     TR life    TR type
       CONTEXT                      32 0         -1          2          0 

Here we have set event 10298 at level 32 like so

alter system set events '10298 trace name context forever, level 32';

This event incidentally turns off the checking for the correct parameters used when using nfs mountpoints for the datafiles and rman backups.

To do the same in 11g use the following

alter session set events 'immediate eventdump(system)';
alter session set events 'immediate eventdump(session)';
alter session set events 'immediate eventdump(process)';

Another quick SQL way to get events set is to run the following code

SET SERVEROUTPUT ON

DECLARE
   l_level NUMBER;
BEGIN
   FOR l_event IN 10000..10999
   LOOP
       dbms_system.read_ev (l_event,l_level);
       IF l_level > 0
       THEN
           dbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));
       END IF;
   END LOOP;
END;
/ 

This will catch most of the events commonly set.

Bad SnapShot Controlfile Configuration

Have you ever configured your snapshot controlfile incorrectly?

Can you no longer resync your catalog?

Are you getting the following messages

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 02/04/2010 13:52:00
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 02/04/2010 13:52:00
ORA-01580: error creating control backup file /home/luke/rman_backup_area/test/scf/test_scf.f
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory 

You cannot now just change the entry whilst connected to the catalog like so

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/rman_backup_area/test/scf/test_scf.f';

As to do so it requires that the catalog be resync'ed but it can't do that because it cannot write a snapshot controlfile! Ahh!

To resolve the issue there are 2 solutions that I know of

1. Using RMAN connect to the target with no catalog

rman target / nocatalog
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/rman_backup_area/test/scf/test_scf.f';

2. Use the DBMS_BACKUP_RESTORE package from the database

EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME ('/home/oracle/rman_backup_area/test/scf/test_scf.f');

Become another user in PL/SQL

There are some things even SYS cannot achieve without becoming the user, two things spring to mind.

1. Create private database links
2. Submit and modify DBMS_JOBS and scheduler jobs.

For the last 2 you can use DBMS_IJOB and DBMS_ISCHED.

But for database links there is another way - DBMS_SYS_SQL

This hidden package has a function called PARSE_AS_USER which can parse a cursor after doing a 'become user'.

Here's a sample bit of code that creates a database link as another user.

First you have to find out the user's USER_ID (in my case it was 428) and then pass that number in to the function to tell it which user to become.

DECLARE
  l_cursors DBMS_SQL.NUMBER_TABLE;
  l_result  NUMBER;
BEGIN
  l_cursors(428):=DBMS_SYS_SQL.OPEN_CURSOR;

  --parsing statement as a different owner

  DBMS_SYS_SQL.PARSE_AS_USER(
      c             => l_cursors(428),
      statement     => 'create database link luke connect to scott identified by tiger using ''dev''',
      language_flag => dbms_sql.native,
      userid        => 428
  );

 l_result:=DBMS_SYS_SQL.EXECUTE(l_cursors(428));
END;
/

And there you have it the database link luke is now created with an owner user_id 428.