sqlplus connections-old school

This blog was posted to be reminder that sqlplus connection may be initiated in different ways.

Core files

Content of tnsnames.ora file:

HACN.WORLD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=volt.teb-ing.hr)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SID=HACT)
    )
  )

tnsping checking:

c:\Documents and Settings\damir>tnsping hacn

TNS Ping Utility for 32-bit Windows: Version 10.2.0.5.0 - Production on 30-SVI-2012 15:20:28

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

Used parameter files:
d:\Users\DamirV\Documents\teb\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=volt.teb-ing.hr) (PORT=1521)) (CONNECT_DATA= (SID=HACT))) OK (40 msec)

Ordinary tnsnames connection string

Most common way to connect to some database by using entries from tnsnames.ora file:

c:\Documents and Settings\damir>sqlplus tab/passd@hacn

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:20:01 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


TAB@hacn>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Simulated tnsnames connection string

If you do not want to use tnsnames.ora definitions, you might simulate them, by entering values directly in command:

c:\Documents and Settings\damir>sqlplus tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:29:13 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


TAB@HACT>

If you use some special characters, for Windows env put whole string in double quotes:

sqlplus "tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))"

and for Linux env put in single quotes (and place escape character if needed):

sqlplus 'tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))'

Easy_connect_method

This is not too known but also regular method to connect without tnsnames.ora entry:

c:\Documents and Settings\damir>sqlplus tab/passd@//volt.teb-ing.hr:1521/HACT

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:21:05 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

TAB@//volt.teb-ing.hr:1521/HACT>

In all examples I use $ORACLE_HOME\sqlplus\admin\glogin.sql file custom definition:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"

If there is any other method, please let me know. Cheers!

APEX installation verification script

The primary purpose of this script is to verify the validity of an APEX installation after an installation has occurred or before an upgrade. However, since it checks a number of APEX installation prerequisites, it may be useful to run prior to an APEX installation.

1. Download apex_verify.sql
2. This script should be run from sqlplus.
3. This script must be run as SYSTEM in the database where APEX is installed.
4. The output of the script (apex_verify_out.html) will be located in the ORACLE_HOME\bin directory where sqlplus was executed.
5. The html output can be easily viewed by clicking on it which will bring it up in your default browser.

This script may be used for Oracle Application Express (formerly HTML DB) - Version 2.0.0.0.0 and later and applies to any platform.

The output of this script is largely self-explanatory. The output will help verify the status of the APEX installation as well as the DB objects needed to support the installation. 

Hope this helps someone.

Cheers!

Oracle custom PL/SQL errors

We are all aware that Oracle has exposed custom ORA errors through unique interval of ORA PL/SQL errors. Here is part from Oracle documentation:

Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR
The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.
To call RAISE_APPLICATION_ERROR, use the syntax
raise_application_error(error_number, message[, {TRUE | FALSE}]);
where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.

Sounds cool and very easy to implement.
But recently I found out that even Oracle itself, is using the same custom segment for it's own PL/SQL error codes. This came to mine mind once suddenly, while I was grepping through SYSMAN PL/SQL source schema, searching for something else. The matter of fact, I found out that this is not an exception but regular case. So to be able to grep the whole source, next script is greping through sys.dba_source on SYS, SYSTEM and SYSMAN schemas only:

set linesize 170
set pagesize 200

col excode for a6
col owner for a10
col TYPE for A15
col name for A24
col exname for A32
col LINE for 9990
col TEXT for a60

WITH data AS (
         SELECT (   '^.*PRAGMA\s+EXCEPTION_INIT\s*\(\s*' -- PRAGMA EXCPETION_INIT(
                 || '([A-Z0-9_$#]{1,30}|"[^"]{1,30}")'         -- Exception-Name
                 -- comment the following line out, if the exception_name
                 -- and the exception code are on different lines
                 || '\s*,\s*''?(-?\d{1,5})''?'                 -- Exception-Code
                 -------
                 || '.*$'                                         -- End-of-Line
                ) AS rx
              , (' ' || CHR (10) || CHR (13) || CHR (9)) AS trimset
           FROM DUAL
     )
   , data_source AS (
         SELECT s.owner
              , s.TYPE
              , s.name
              , s.line
              , UPPER (RTRIM (s.text, (' ' || CHR (10) || CHR (13)))) AS text
           FROM data d, dba_source s
          WHERE REGEXP_LIKE (s.text, d.rx, 'i')
     )
   , data_prepared AS (
         SELECT ds.owner
              , ds.TYPE
              , ds.name
              , ds.line
              , ds.text
              , REGEXP_REPLACE (ds.text, d.rx, '\1') AS exname
              , REGEXP_REPLACE (ds.text, d.rx, '\2') AS excode
           FROM data d, data_source ds
     )
 SELECT dp.excode
       , dp.owner
       , dp.TYPE
       , dp.name
       , dp.exname
       , dp.line
       , dp.text
    FROM data_prepared dp
   WHERE (TO_NUMBER (dp.excode) BETWEEN -20999 AND -20000)
     AND dp.owner IN ('SYS', 'SYSMAN', 'SYSTEM')
ORDER BY TO_NUMBER (dp.excode) DESC
       , dp.owner
       , dp.TYPE
       , dp.name;

The result is really amazing:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> /

EXCODE OWNER   TYPE       NAME                     EXNAME                            LINE TEXT                                                                    
------ ------- ---------- ------------------------ -------------------------------- ----- ------------------------------------------------------
-20000 SYSMAN  PACKAGE 
               BODY       MGMT_ECM_POLICY          EVALUATE_EXCEPTION                   6   PRAGMA EXCEPTION_INIT( EVALUATE_EXCEPTION, -20000 );               
-20025 SYS     PROCEDURE  AW_DROP_PROC             DBERR20                              5     PRAGMA EXCEPTION_INIT(DBERR20, -20025);                          
-20197 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_USER_PWD                   184 PRAGMA EXCEPTION_INIT(INVALID_USER_PWD, -20197);                     
-20199 SYSMAN  PACKAGE    MGMT_GLOBAL              EM_LOCK_CONTEXT                    189 PRAGMA EXCEPTION_INIT(EM_LOCK_CONTEXT, -20199);                      
-20200 SYSMAN  PACKAGE    EM_SEVERITY_REPOS        SEV_EVAL_PROC_NOT_FOUND             28 PRAGMA EXCEPTION_INIT(SEV_EVAL_PROC_NOT_FOUND, -20200);              
-20200 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_USER_NAME                  194 PRAGMA EXCEPTION_INIT(INVALID_USER_NAME, -20200);                    
-20201 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PASSWORD                   199 PRAGMA EXCEPTION_INIT(INVALID_PASSWORD, -20201);                     
-20202 SYSMAN  PACKAGE    EM_SEVERITY_REPOS        UNSUPPORTED_TARGET                  33 PRAGMA EXCEPTION_INIT(UNSUPPORTED_TARGET, -20202);                   
-20202 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_USER                     204 PRAGMA EXCEPTION_INIT(DUPLICATE_USER, -20202);                       
-20203 SYSMAN  PACKAGE    EM_SEVERITY_REPOS        SEV_EVAL_PROC_NOT_FOUND             38 PRAGMA EXCEPTION_INIT(SEV_EVAL_PROC_NOT_FOUND, -20203);              
-20203 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_ROLE_NAME                  209 PRAGMA EXCEPTION_INIT(INVALID_ROLE_NAME, -20203);                    
-20204 SYSMAN  PACKAGE    MGMT_GLOBAL              USER_DOES_NOT_EXIST                214 PRAGMA EXCEPTION_INIT(USER_DOES_NOT_EXIST, -20204);                  
-20205 SYSMAN  PACKAGE    MGMT_GLOBAL              ROLE_DOES_NOT_EXIST                219 PRAGMA EXCEPTION_INIT(ROLE_DOES_NOT_EXIST, -20205);                  
-20206 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_DOES_NOT_EXIST              224 PRAGMA EXCEPTION_INIT(TARGET_DOES_NOT_EXIST, -20206);                
-20207 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_DESCRIPTION                229 PRAGMA EXCEPTION_INIT(INVALID_DESCRIPTION, -20207);                  
-20208 SYSMAN  PACKAGE    MGMT_GLOBAL              DELETE_ALL_TARGETS                 234 PRAGMA EXCEPTION_INIT(DELETE_ALL_TARGETS, -20208);                   
-20209 SYSMAN  PACKAGE    MGMT_GLOBAL              EDIT_ALL_TARGETS                   239 PRAGMA EXCEPTION_INIT(EDIT_ALL_TARGETS, -20209);                     
-20210 SYSMAN  PACKAGE    MGMT_GLOBAL              DELETE_SUPERUSER                   244 PRAGMA EXCEPTION_INIT(DELETE_SUPERUSER, -20210);                     
-20211 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_ROLE                     249 PRAGMA EXCEPTION_INIT(DUPLICATE_ROLE, -20211);                       
-20212 SYSMAN  PACKAGE    MGMT_GLOBAL              EMAIL_TEST_FAILED                  254 PRAGMA EXCEPTION_INIT(EMAIL_TEST_FAILED, -20212);                    
-20213 SYSMAN  PACKAGE    MGMT_GLOBAL              PROGRAM_TEST_FAILED                259 PRAGMA EXCEPTION_INIT(PROGRAM_TEST_FAILED, -20213);                  
-20215 SYSMAN  PACKAGE    MGMT_GLOBAL              SNMP_TEST_FAILED                   267 PRAGMA EXCEPTION_INIT(SNMP_TEST_FAILED, -20215);                     
-20216 SYSMAN  PACKAGE    MGMT_GLOBAL              NO_SUCH_METRIC_FOUND               520 PRAGMA EXCEPTION_INIT(NO_SUCH_METRIC_FOUND, -20216);                 
-20217 SYSMAN  PACKAGE    MGMT_GLOBAL              MODIFY_FAILED                      271 PRAGMA EXCEPTION_INIT(MODIFY_FAILED, -20217);                        
-20218 SYSMAN  PACKAGE    MGMT_GLOBAL              DROP_FAILED                        276 PRAGMA EXCEPTION_INIT(DROP_FAILED, -20218);                          
-20219 SYSMAN  PACKAGE    MGMT_GLOBAL              GRANT_FAILED                       281 PRAGMA EXCEPTION_INIT(GRANT_FAILED, -20219);                         
-20220 SYSMAN  PACKAGE    MGMT_GLOBAL              REVOKE_FAILED                      286 PRAGMA EXCEPTION_INIT(REVOKE_FAILED, -20220);                        
-20221 SYSMAN  PACKAGE    MGMT_GLOBAL              INSUFFICIENT_PRIVILEGES            291 PRAGMA EXCEPTION_INIT(INSUFFICIENT_PRIVILEGES, -20221);              
-20222 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PRIVILEGE_TYPE             296 PRAGMA EXCEPTION_INIT(INVALID_PRIVILEGE_TYPE, -20222);               
-20223 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PRIVILEGE                  301 PRAGMA EXCEPTION_INIT(INVALID_PRIVILEGE, -20223);                    
-20224 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_TARGET                     306 PRAGMA EXCEPTION_INIT(INVALID_TARGET, -20224);                       
-20225 SYSMAN  PACKAGE    MGMT_GLOBAL              PRIV_DOES_NOT_EXIST                311 PRAGMA EXCEPTION_INIT(PRIV_DOES_NOT_EXIST, -20225);                  
-20226 SYSMAN  PACKAGE    MGMT_GLOBAL              CANT_DROP_YOURSELF                 316 PRAGMA EXCEPTION_INIT(CANT_DROP_YOURSELF, -20226);                   
-20227 SYSMAN  PACKAGE    MGMT_GLOBAL              ALREADY_EXISTS                     321 PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -20227);                       
-20228 SYSMAN  PACKAGE    MGMT_GLOBAL              CIRCULAR_ROLE                      326 PRAGMA EXCEPTION_INIT(CIRCULAR_ROLE, -20228);                        
-20229 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_GROUP                      331 PRAGMA EXCEPTION_INIT(INVALID_GROUP, -20229);                        
-20230 SYSMAN  PACKAGE    MGMT_GLOBAL              CREATE_FAILED                      336 PRAGMA EXCEPTION_INIT(CREATE_FAILED, -20230);                        
-20231 SYSMAN  PACKAGE    MGMT_GLOBAL              USER_EXISTS                        341 PRAGMA EXCEPTION_INIT(USER_EXISTS, -20231);                          
-20232 SYSMAN  PACKAGE    MGMT_GLOBAL              ROLE_EXISTS                        346 PRAGMA EXCEPTION_INIT(ROLE_EXISTS, -20232);                          
-20233 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PARAMS                     352 PRAGMA EXCEPTION_INIT(INVALID_PARAMS, -20233);                       
-20234 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_BASELINE_NAME            357 PRAGMA EXCEPTION_INIT(DUPLICATE_BASELINE_NAME, -20234);              
-20235 SYSMAN  PACKAGE    MGMT_GLOBAL              BASELINE_DOES_NOT_EXIST            363 PRAGMA EXCEPTION_INIT(BASELINE_DOES_NOT_EXIST, -20235);              
-20236 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_DEVICE_NAME              369 PRAGMA EXCEPTION_INIT(DUPLICATE_DEVICE_NAME, -20236);                
-20237 SYSMAN  PACKAGE    MGMT_GLOBAL              DEVICE_DOES_NOT_EXIST              375 PRAGMA EXCEPTION_INIT(DEVICE_DOES_NOT_EXIST, -20237);                
-20238 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_BEING_DELETED               381 PRAGMA EXCEPTION_INIT(TARGET_BEING_DELETED, -20238);                 
-20239 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_BEING_MOVED                 387 PRAGMA EXCEPTION_INIT(TARGET_BEING_MOVED, -20239);                   
-20242 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_CANNOT_BE_DELETED           401 PRAGMA EXCEPTION_INIT(TARGET_CANNOT_BE_DELETED, -20242);             
-20247 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_EMD_URL                    407 PRAGMA EXCEPTION_INIT(INVALID_EMD_URL, -20247);                      
-20248 SYSMAN  PACKAGE    MGMT_GLOBAL              MISSING_EM_KEY                     413 PRAGMA EXCEPTION_INIT(MISSING_EM_KEY, -20248);                       
-20249 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_EM_KEY                     419 PRAGMA EXCEPTION_INIT(INVALID_EM_KEY, -20249);                       
-20300 SYSMAN  PACKAGE    MGMT_GLOBAL              RULE_DOES_NOT_EXIST                425 PRAGMA EXCEPTION_INIT(RULE_DOES_NOT_EXIST, -20300);                  
-20301 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_RULE_NAME                431 PRAGMA EXCEPTION_INIT(DUPLICATE_RULE_NAME, -20301);                  
-20302 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_RULE_DATA                  437 PRAGMA EXCEPTION_INIT(INVALID_RULE_DATA, -20302);                    
-20303 SYSMAN  PACKAGE    MGMT_GLOBAL              INSUFFICIENT_PRIVILEGES            443 -- PRAGMA EXCEPTION_INIT(INSUFFICIENT_PRIVILEGES, -20303);  
-20400 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_JOB_TYPE                   533 PRAGMA EXCEPTION_INIT(INVALID_JOB_TYPE, -20400);                     
-20401 SYSMAN  PACKAGE    MGMT_GLOBAL              JOB_EXISTS                         538 PRAGMA EXCEPTION_INIT(JOB_EXISTS, -20401);                           
-20402 SYSMAN  PACKAGE    MGMT_GLOBAL              JOB_PARAM_MISSING                  543 PRAGMA EXCEPTION_INIT(JOB_PARAM_MISSING, -20402);                    
-20403 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_JOB                        548 PRAGMA EXCEPTION_INIT(INVALID_JOB, -20403);                          
-20404 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PARAMS_IN_PARAM_SRC        554 PRAGMA EXCEPTION_INIT(INVALID_PARAMS_IN_PARAM_SRC, -20404);          
-20405 SYSMAN  PACKAGE    MGMT_GLOBAL              SQL_EXECUTION_ERROR                560 PRAGMA EXCEPTION_INIT(SQL_EXECUTION_ERROR, -20405);                  
-20406 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_TARGETS_IN_JOB             565 PRAGMA EXCEPTION_INIT(INVALID_TARGETS_IN_JOB, -20406);               
-20407 SYSMAN  PACKAGE    MGMT_GLOBAL              INCORRECT_VALUES                   571 PRAGMA EXCEPTION_INIT(INCORRECT_VALUES, -20407);                     
-20408 SYSMAN  PACKAGE    MGMT_GLOBAL              SUSPEND_FINISHED_JOB               576 PRAGMA EXCEPTION_INIT(SUSPEND_FINISHED_JOB, -20408);                 
-20409 SYSMAN  PACKAGE    MGMT_GLOBAL              RESTART_INVALID_JOB                582 PRAGMA EXCEPTION_INIT(RESTART_INVALID_JOB, -20409);                  
-20410 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_SCHEDULE                   587 PRAGMA EXCEPTION_INIT(INVALID_SCHEDULE, -20410);                     
-20411 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PARAMETER                  592 PRAGMA EXCEPTION_INIT(INVALID_PARAMETER, -20411);                    
-20412 SYSMAN  PACKAGE    MGMT_GLOBAL              INSUFFICIENT_JOB_PRIVS             597 PRAGMA EXCEPTION_INIT(INSUFFICIENT_JOB_PRIVS, -20412);               
-20413 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PARAMS_IN_SEC              602 PRAGMA EXCEPTION_INIT(INVALID_PARAMS_IN_SEC, -20413);                
-20414 SYSMAN  PACKAGE    MGMT_GLOBAL              ACTIVE_EXECUTIONS_EXIST            607 PRAGMA EXCEPTION_INIT(ACTIVE_EXECUTIONS_EXIST, -20414);              
-20415 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_EXECUTION                  612 PRAGMA EXCEPTION_INIT(INVALID_EXECUTION, -20415);                    
-20416 SYSMAN  PACKAGE    MGMT_GLOBAL              PURGE_POLICY_EXISTS                617 PRAGMA EXCEPTION_INIT(PURGE_POLICY_EXISTS, -20416);                  
-20417 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PURGE_CRITERION            622 PRAGMA EXCEPTION_INIT(INVALID_PURGE_CRITERION, -20417);              
-20418 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_EVENT                      628 PRAGMA EXCEPTION_INIT(INVALID_EVENT, -20418);                        
-20419 SYSMAN  PACKAGE    MGMT_GLOBAL              EXEC_LOCK                          633 PRAGMA EXCEPTION_INIT(EXEC_LOCK, -20419);                            
-20420 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_PARAMS_IN_LOCK             638 PRAGMA EXCEPTION_INIT(INVALID_PARAMS_IN_LOCK, -20420);               
-20421 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_JOB_TYPE                   643 PRAGMA EXCEPTION_INIT(INVALID_JOB_TYPE, -20421);                     
-20422 SYSMAN  PACKAGE    MGMT_GLOBAL              JOB_SUSPENDED                      648 PRAGMA EXCEPTION_INIT(JOB_SUSPENDED, -20422);                        
-20423 SYSMAN  PACKAGE    MGMT_GLOBAL              RESTART_RESTART_FAILED_JOB         654 PRAGMA EXCEPTION_INIT(RESTART_RESTART_FAILED_JOB, -20423);           
-20424 SYSMAN  PACKAGE    MGMT_GLOBAL              EDIT_EXPIRED_JOB                   659 PRAGMA EXCEPTION_INIT(EDIT_EXPIRED_JOB, -20424);                     
-20425 SYSMAN  PACKAGE    MGMT_GLOBAL              JOB_CREATE_FAILED                  664 PRAGMA EXCEPTION_INIT(JOB_CREATE_FAILED, -20425);                    
-20451 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_FLATTENING_INPUT           671 PRAGMA EXCEPTION_INIT(INVALID_FLATTENING_INPUT, -20451);             
-20452 SYSMAN  PACKAGE    MGMT_GLOBAL              ONLY_ONE_TARGET_INPUT              675 PRAGMA EXCEPTION_INIT(ONLY_ONE_TARGET_INPUT, -20452);                
-20500 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_ALREADY_EXISTS              683 PRAGMA EXCEPTION_INIT(TARGET_ALREADY_EXISTS, -20500);                
-20501 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_TARGET                   688 PRAGMA EXCEPTION_INIT(DUPLICATE_TARGET, -20501);                     
-20502 SYSMAN  PACKAGE    MGMT_GLOBAL              MULTIPLE_AGENTS_PER_URL            693 PRAGMA EXCEPTION_INIT(MULTIPLE_AGENTS_PER_URL, -20502);              
-20503 SYSMAN  PACKAGE    MGMT_GLOBAL              ACTION_NOT_SUPP                    698 PRAGMA EXCEPTION_INIT(ACTION_NOT_SUPP, -20503);                      
-20600 SYSMAN  PACKAGE    MGMT_GLOBAL              TARGET_DELETION_IN_PROG            715 PRAGMA EXCEPTION_INIT(TARGET_DELETION_IN_PROG, -20600);              
-20601 SYSMAN  PACKAGE    MGMT_GLOBAL              DUPLICATE_RECORD                   727 PRAGMA EXCEPTION_INIT(DUPLICATE_RECORD, -20601);                     
-20602 SYSMAN  PACKAGE    MGMT_GLOBAL              UPDATE_RECORD                      737 PRAGMA EXCEPTION_INIT(UPDATE_RECORD, -20602);                        
-20603 SYSMAN  PACKAGE    MGMT_GLOBAL              UPDATE_TZRGN                       746 PRAGMA EXCEPTION_INIT(UPDATE_TZRGN, -20603);                         
-20604 SYSMAN  PACKAGE    MGMT_GLOBAL              OVERLAPPING_METRIC_DEF             755 PRAGMA EXCEPTION_INIT(OVERLAPPING_METRIC_DEF, -20604);               
-20610 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_SEVERITY_CODE              767 PRAGMA EXCEPTION_INIT(INVALID_SEVERITY_CODE, -20610);                
-20611 SYSMAN  PACKAGE    MGMT_GLOBAL              INCONSISTANT_SEVERITY              778 PRAGMA EXCEPTION_INIT(INCONSISTANT_SEVERITY, -20611);                
-20612 SYSMAN  PACKAGE    MGMT_GLOBAL              INTERNAL_SEVERITY                  788 PRAGMA EXCEPTION_INIT(INTERNAL_SEVERITY, -20612);                    
-20613 SYSMAN  PACKAGE    MGMT_GLOBAL              UNKNOWN_TARGET                     798 PRAGMA EXCEPTION_INIT(UNKNOWN_TARGET, -20613);                       
-20614 SYSMAN  PACKAGE    EM_MASTER_AGENT          NON_MASTER_AGENT                     6 PRAGMA EXCEPTION_INIT(NON_MASTER_AGENT, -20614);                     
-20614 SYSMAN  PACKAGE    MGMT_GLOBAL              NON_MASTER_AGENT                   809 PRAGMA EXCEPTION_INIT(NON_MASTER_AGENT, -20614);                     
-20615 SYSMAN  PACKAGE    MGMT_GLOBAL              SEVERITY_IN_BLACKOUT               820 PRAGMA EXCEPTION_INIT(SEVERITY_IN_BLACKOUT, -20615);                 
-20617 SYSMAN  PACKAGE    MGMT_GLOBAL              SERVERITY_OUT_ORDER                830 PRAGMA EXCEPTION_INIT(SERVERITY_OUT_ORDER, -20617);                  
-20618 SYSMAN  PACKAGE    MGMT_GLOBAL              AGENT_DELETION_IN_PROG             838 PRAGMA EXCEPTION_INIT(AGENT_DELETION_IN_PROG, -20618);               
-20619 SYSMAN  PACKAGE    MGMT_GLOBAL              STALE_TARGET_ADDTION_EXCEPTION     845 PRAGMA EXCEPTION_INIT(STALE_TARGET_ADDTION_EXCEPTION, -20619);       
-20631 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_UNRCH_CLR_VIOL             857 PRAGMA EXCEPTION_INIT(INVALID_UNRCH_CLR_VIOL, -20631);               
-20700 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_BLACKOUT_REASON            868 PRAGMA EXCEPTION_INIT(INVALID_BLACKOUT_REASON, -20700);              
-20701 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_BLACKOUT                   873 PRAGMA EXCEPTION_INIT(INVALID_BLACKOUT, -20701);                     
-20702 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_STOPPED                   879 PRAGMA EXCEPTION_INIT(BLACKOUT_STOPPED, -20702);                     
-20703 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_NOT_EXIST                 884 PRAGMA EXCEPTION_INIT(BLACKOUT_NOT_EXIST, -20703);                   
-20704 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_NOT_STOPPED               890 PRAGMA EXCEPTION_INIT(BLACKOUT_NOT_STOPPED, -20704);                 
-20705 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_PENDING_EDIT              896 PRAGMA EXCEPTION_INIT(BLACKOUT_PENDING_EDIT, -20705);                
-20706 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_STOP_PENDING              902 PRAGMA EXCEPTION_INIT(BLACKOUT_STOP_PENDING, -20706);                
-20707 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_STOP_FAILED               906 PRAGMA EXCEPTION_INIT(BLACKOUT_STOP_FAILED, -20707);                 
-20708 SYSMAN  PACKAGE    MGMT_GLOBAL              BLACKOUT_START_PROCESS             910 PRAGMA EXCEPTION_INIT(BLACKOUT_START_PROCESS, -20708);               
-20709 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_NOT_CREATED_USER            914 PRAGMA EXCEPTION_INIT(BLKOUT_NOT_CREATED_USER, -20709);              
-20710 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_STARTED_AT_AGENT            918 PRAGMA EXCEPTION_INIT(BLKOUT_STARTED_AT_AGENT, -20710);              
-20711 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_INVALID_STATE_MOD           922 PRAGMA EXCEPTION_INIT(BLKOUT_INVALID_STATE_MOD, -20711);             
-20712 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_INVALID_STATE_DEL           926 PRAGMA EXCEPTION_INIT(BLKOUT_INVALID_STATE_DEL, -20712);             
-20713 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_INVALID_STATE_STOP          930 PRAGMA EXCEPTION_INIT(BLKOUT_INVALID_STATE_STOP, -20713);            
-20714 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_NO_PRIVS_SEL_TGTS           934 PRAGMA EXCEPTION_INIT(BLKOUT_NO_PRIVS_SEL_TGTS, -20714);             
-20715 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_NO_PRIVS_HOST_TGTS          938 PRAGMA EXCEPTION_INIT(BLKOUT_NO_PRIVS_HOST_TGTS, -20715);            
-20716 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_ALREADY_EXISTS              942 PRAGMA EXCEPTION_INIT(BLKOUT_ALREADY_EXISTS, -20716);                
-20717 SYSMAN  PACKAGE    MGMT_GLOBAL              BLK_INVALID_SCHEDULE               947 PRAGMA EXCEPTION_INIT(BLK_INVALID_SCHEDULE, -20717);                 
-20718 SYSMAN  PACKAGE    MGMT_GLOBAL              BLKOUT_CREATE_FAILED               952 PRAGMA EXCEPTION_INIT(BLKOUT_CREATE_FAILED, -20718);                 
-20800 SYSMAN  PACKAGE    MGMT_GLOBAL              SNAPSHOT_LOCK                      960 PRAGMA EXCEPTION_INIT(SNAPSHOT_LOCK, -20800);                        
-20801 SYSMAN  PACKAGE    MGMT_GLOBAL              SNAPSHOT_MISSING                   964 PRAGMA EXCEPTION_INIT(SNAPSHOT_MISSING, -20801);                     
-20900 SYSMAN  PACKAGE    MGMT_GLOBAL              INVALID_CRED_SET                   973 PRAGMA EXCEPTION_INIT(INVALID_CRED_SET, -20900);                     
-20999 SYSMAN  PACKAGE    MGMT_JOB_UI_RUNS         ASSERTFAIL                          32 PRAGMA EXCEPTION_INIT(ASSERTFAIL, -20999);                           

125 rows selected.

125 ORA custom numbers are reserved for original Oracle product.

The End

As a conclusion two things has to be said:

1. Almost all custom exceptions are in SYSMAN schema, what is good.
2. Only one custom exception is in SYS schema and this has to kept in mind if programming any exception in SYS schema (not to advisable but very possible)
3. Huge number of custom ORA exceptions in SYSMAN schema tells me that OEM is not original Oracle kernel product but more like "Add on"-whatever marketing people from Oracle try to say about that.

Hope this helps someone.

Cheers!

Indexes ... indexes, life story

Recently I had a tipical life situation in mine ordinary DBA tuning task. I was asked to see if there is anything tunable in ad hoc way. Do not want to elaborate about ASH and it's known queries, but to explain one other, mine, approach, following thought hat biggest tables probably makes biggest problems.

This approach is sometimes fastest way to tune unknown database in relatively quick way.

To find biggest (in all mean ... by records and size) I use custom made tool, db_size_stat, explained in previous topic Monitor database size (part II).

Here are starting two queries for that:

SQL> select * from table(db_size_pkg.TOP_N_TABLES_BY_SIZE_NOW_PR (5));

OWNER      SEGMENT_NAME                   SEGMENT_TYPE            SIZE_MB    RECORDS
---------- ------------------------------ -------------------- ---------- ----------
TAB        XX_CASH_UP_DETAIL_T            TABLE                34786.4375  344092877
TAB        XX_TRANSACTION_OK_T            TABLE                14176.4375   49417035
YYY        XX_TRANSACTION_T               TABLE                13763.6875   35368073
YYY        XX_TRANSACTION_ULAZ_T          TABLE                  8469.875   28162972
TOOLS      AUD$_ARCH                      TABLE                      5293    5569440

SQL> select * from table(db_size_pkg.TOP_N_TABLES_BY_RECORDS_NOW_PR (5));

OWNER      SEGMENT_NAME                   SEGMENT_TYPE            SIZE_MB    RECORDS
---------- ------------------------------ -------------------- ---------- ----------
YYY        XX_CASH_UP_DETAIL_T            TABLE                34786.4375  344092877
YYY        XX_TRANSACTION_OK_T            TABLE                14176.4375   49417035
YYY        XX_TRANSACTION_T               TABLE                13763.6875   35368073
YYY        XX_SUBSCRIPTION_SALDO          TABLE                      4529   32349603
YYY        XX_TRANSACTION_PRIHOD_T        TABLE                 3735.0625   30577170

Normally I choose first one and start to see it's indexes and other important details:

SQL> @tbl_info YYY XX_CASH_UP_DETAIL_T
TABLE:XX_CASH_UP_DETAIL_T
TABLE/YYY/XX_CASH_UP_DETAIL_T
RECS:   324,254,534 BLKS:     4,186,049   MB: 32,703.51   AVGLEN:86
SMPL:   324,254,534 ANAL: 02.03.2012 20:29:05
----------------------------------------------
COL  1:ID_RESEAU                      TYP:VARCHAR2   VALS:           3 DENS:       0 NULLS:           0 HIST:   3
COL  2:ID_SITE                        TYP:VARCHAR2   VALS:          35 DENS:       0 NULLS:           0 HIST:  35
COL  3:MATRICULE                      TYP:VARCHAR2   VALS:         716 DENS:   .0015 NULLS:           0 HIST: 254
COL  4:DATE_REDDITION                 TYP:DATE       VALS:     370,912 DENS:       0 NULLS:           0 HIST:   1
COL  5:SAC                            TYP:NUMBER     VALS:       9,999 DENS:   .0001 NULLS:           0 HIST:   1
COL  6:CORRECTION_STATUS              TYP:VARCHAR2   VALS:           8 DENS:    .125 NULLS:           0 HIST:   1
COL  7:ID_PAIEMENT                    TYP:NUMBER     VALS:           7 DENS:   .1429 NULLS:           0 HIST:   1
COL  8:LIBELLE_PAIEMENT               TYP:VARCHAR2   VALS:       1,480 DENS:   .0007 NULLS:           0 HIST:   1
COL  9:ID_CLASSE                      TYP:NUMBER     VALS:           6 DENS:   .1667 NULLS:           0 HIST:   1
COL 10:MONTANT_PAIEMENT               TYP:NUMBER     VALS:         502 DENS:    .002 NULLS:           0 HIST:   1
COL 11:DEVISE_PAIEMENT                TYP:VARCHAR2   VALS:          16 DENS:   .0625 NULLS:           0 HIST:   1
COL 12:NB_PAIEMENT                    TYP:NUMBER     VALS:         694 DENS:   .0014 NULLS:           0 HIST:   1
COL 13:MONTANT_TOTAL                  TYP:NUMBER     VALS:       6,856 DENS:   .0001 NULLS:           0 HIST:   1
COL 14:INSERT_DATE                    TYP:DATE       VALS:   1,784,326 DENS:       0 NULLS:           0 HIST:   1
COL 15:DATKRE                         TYP:DATE       VALS:      98,474 DENS:       0 NULLS:           0 HIST:   1
COL 16:KORKRE                         TYP:VARCHAR2   VALS:           1 DENS:       1 NULLS:           0 HIST:   1
COL 17:DATPRO                         TYP:DATE       VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 18:KORPRO                         TYP:VARCHAR2   VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 19:IR_BROJ                        TYP:NUMBER     VALS:           0 DENS:       0 NULLS: 324,254,534 HIST:   0
COL 20:DATE_MESSAGE                   TYP:DATE       VALS:   1,142,770 DENS:       0 NULLS:           0 HIST:   1
----------------------------------------------
Indexes:
XXD_CASH_UP_I                  NORMAL                    LBLKS: 2,407,524    KEYS:   1,172,204    CLUSTR:   8,790,457
             20,578.125 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 00:43:53
                1 ID_RESEAU
                2 ID_SITE
                3 MATRICULE
                4 DATE_REDDITION
                5 SAC
                6 DATE_MESSAGE
XXD_CASH_UP_IX_REDD            NORMAL                    LBLKS: 1,026,283    KEYS:     370,912    CLUSTR:   8,599,816
              8,795.625 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 02:55:40
                1 DATE_REDDITION
XXD_PAIEMENT_I                 NORMAL                    LBLKS:   788,011    KEYS:           7    CLUSTR:  13,541,831
              6,760.375 MB  Index quality:5 (Excellent)  Analyzed:03.03.2012 01:39:54
                1 ID_PAIEMENT
------------------
Total: 32,703.51/ 36,134.13 MB
Index goodnes:-0.105
                Score is -1.000 (worst) to 1.000 (best)
============================================================================================

where description of colums are:

         VALS - number of different non null values!
         DENS - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2969235095639
                Density is a statistic used by the Cost Based Optimizer to give selectivity 
                estimates for columns where better information is unavailable (i.e. from histograms etc.). 
                Density is a column statistic and provides selectivity estimates for equi-join predicates 
                (e.g. and A.COL1 = B.COL1) and equality predicates (e.g. COL1 = 5).
                The density is expressed as a decimal number between 0 and 1. 
                    Values close to 1 indicate that this column is unselective
                    Values close to 0 indicate that this column is highly selective 
                When the WHERE clause includes a column(s) with a bind variable; 
                    for example: column x = :variable_y                      
         NULLS- Number of nulls values in column (according table statistic)
         HIST - represent num_buckets
                num_buckets = 1 ( = max(endpoint_number) ==> no histogram)
                num_buckets = num_distinct-1 ==> frequency histogram
                num_buckets = max(endpoint_number) ==> HB histogram without popular values 
                             ( this includes case one, treating the entire value range as one big bucket)
                num_buckets < max(endpoint_number) ==> HB histogram with popular values           
                Verifying Histogram Statistics

Here is mine tbl_info script:

/* ---------------------------------------------------------------------------
           
          Copyright(C) 1997-2011 TEB Informatika d.o.o.


 CR/TR#  : tbl_info.sql
 Purpose : Shows important details for chosen table
 Usage   : tbl_info OWNER table <table array> 
           i.e.
           1 table    : tbl_info TAB NC_SUBSCRIPTION_SALDO
           Many tables: tbl_info TAB NC_SUBSCRIPTION_SALDO','NC_BESPLATNI
 Example: 
 Changes (DD.MM.YYYY, Name, CR/TR#):
 --------------------------------------------------------------------------- */
set serveroutput on size 123456;
set linesize 140
set pagesize 120

DECLARE
  t_tabs   SYS.DBMS_DEBUG_VC2COLL;
  v_tab    VARCHAR2 (100);
  v_own    VARCHAR2 (100);
  v_typ    VARCHAR2 (100);
  v_user   VARCHAR2 (34) ;
  v_long   VARCHAR2 (32000);
  v_ind    NUMBER;
  i PLS_INTEGER;
  v_sum_indx_size NUMBER;
  v_sum_tbl_size NUMBER;
  --
  CURSOR c_obj IS
    SELECT object_type, owner
      FROM dba_objects
     WHERE object_name = v_tab AND 
           owner IN ('PUBLIC', v_own)
    ORDER BY DECODE (owner, v_own, 1, 2);

  --
  CURSOR c_syn IS
    SELECT table_owner, table_name
      FROM dba_synonyms
     WHERE synonym_name = v_tab AND 
           owner IN ('PUBLIC', v_own)
    ORDER BY DECODE (owner, v_own, 1, 2);

  --
  CURSOR c_vw IS
    SELECT text
      FROM dba_views
     WHERE owner = v_own AND 
           view_name = v_tab;

  --
  CURSOR c_tab IS
    SELECT table_name,
           num_rows,
           blocks,
           blocks*tblspc.block_size/1048576 size_mb,
           avg_row_len,
           sample_size,
           last_analyzed
      FROM dba_tables, dba_tablespaces tblspc
     WHERE table_name = v_tab AND 
           owner = v_own AND
           tblspc.TABLESPACE_NAME=dba_tables.TABLESPACE_NAME;

  --
  CURSOR c_tcols IS
    SELECT column_name,
           data_type,
           num_distinct,
           density,
           num_nulls,
           num_buckets,
           last_analyzed,
           avg_col_len,
           RAWTOHEX (low_value) low,
           RAWTOHEX (high_value)
      FROM dba_tab_columns
     WHERE table_name = v_tab AND 
           owner = v_own;

  --
  CURSOR c_ind IS
    SELECT 
          i.index_name,
          i.index_type, 
          i.distinct_keys,
          i.leaf_blocks,
          o.bytes/1048576 size_MB,
          i.avg_data_blocks_per_key, 
          i.avg_leaf_blocks_per_key,
          i.clustering_factor,
          i.last_analyzed,
          CASE when nvl(i.clustering_factor,0) = 0                                     then '0 (????)'  -- function index!?
               when nvl(t.num_rows,0) = 0                                              then '0 (No Stats)'
               when (round(i.clustering_factor / t.num_rows * 100)) < 6                then '5 (Excellent)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11   then '4 (Very Good)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 15  then '2 (Good)'
               when (round(i.clustering_factor / t.num_rows * 100)) between 16 and 25  then '2 (Fair)'
               else                                                                         '1 (Poor)' 
          END Index_Quality
     FROM dba_indexes i, 
          dba_segments o, 
          dba_tables t
    WHERE i.owner = v_own              AND
          i.table_name = v_tab         AND 
          i.owner=t.owner              AND
          i.table_name = t.table_name  AND 
          i.owner=o.owner              AND
          i.index_name = o.segment_name
    ORDER BY index_name
  ;

  --
  CURSOR c_icols (p_iname VARCHAR2) IS
    SELECT c.index_name,
           c.column_position,
           c.column_name,
           e.column_expression
      FROM dba_ind_columns c, dba_ind_expressions e
     WHERE e.index_owner(+) = c.index_owner           AND
           e.index_name(+) = c.index_name             AND
           e.column_position(+) = c.column_position   AND
           c.table_name = v_tab                       AND
           c.table_owner = v_own                      AND
           c.index_name = p_iname
    ORDER BY 1, 2;

  --
  PROCEDURE sp_out (p_text IN VARCHAR2, p_wrap IN NUMBER DEFAULT 1)
  IS
    v_text       VARCHAR2 (32500);
    v_point      NUMBER;
    e_overflow   EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_overflow, -20000);
    v_temp_arr   DBMS_OUTPUT.chararr;
    v_lines      NUMBER := 150;
  BEGIN
    DBMS_OUTPUT.PUT_LINE  (p_text);
    return;
    v_text := p_text;

    --Break it down into lengths up 248 characters in length
    -- If p_wrap is 1, it will try to 'wrap' the line at word breaks
    WHILE v_text IS NOT NULL LOOP
       v_point := LENGTH (v_text) + 1;

       IF v_point > 248 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), CHR (10), -1);
       END IF;

       IF v_point = 0 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), ' ', -1);
       END IF;

       IF v_point = 0 AND p_wrap = 1 THEN
         v_point := INSTR (SUBSTR (v_text, 1, 248), '/', -1);
       END IF;

       IF v_point = 0 THEN
         v_point := 248;
       END IF;

       BEGIN
         DBMS_OUTPUT.PUT_LINE (SUBSTR (v_text, 1, v_point - 1));
       EXCEPTION
         WHEN e_overflow THEN
           IF SUBSTR (SQLERRM, 12, 9) = 'ORU-10027' THEN
             --Clear out the oldest 150 lines to make room
             --in the DBMS_OUTPUT buffer
             DBMS_OUTPUT.GET_LINES (V_TEMP_ARR, V_LINES);
             DBMS_OUTPUT.PUT_LINE (SUBSTR (v_text, 1, v_point - 1));
           ELSE
             RAISE;
           END IF;
       END;
       v_text := SUBSTR (v_text, v_point + 1);
    END LOOP;
  END;
-- mai part
BEGIN
  v_user := '&1';
  t_tabs := sys.DBMS_DEBUG_VC2COLL ('&2');

  v_ind := t_tabs.FIRST;

  WHILE v_ind IS NOT NULL LOOP
    v_tab := t_tabs (v_ind);
    sp_out ('TABLE:' || v_tab);
    v_own := v_user;

    OPEN c_obj;

    FETCH c_obj INTO v_typ, v_own;

    IF c_obj%NOTFOUND THEN
      CLOSE c_obj;
      RAISE NO_DATA_FOUND;
    END IF;

    CLOSE c_obj;

    --
    WHILE v_typ = 'SYNONYM' LOOP
      OPEN c_syn;
      FETCH c_syn INTO v_own, v_tab;
      CLOSE c_syn;
      sp_out ('SYN:' || v_typ || '/' || v_own || '/' || v_tab);
      OPEN c_obj;
      FETCH c_obj INTO v_typ, v_own;
      CLOSE c_obj;
    END LOOP;

    --
    sp_out (v_typ || '/' || v_own || '/' || v_tab);

    IF v_typ = 'VIEW' THEN
      OPEN c_vw;
      FETCH c_vw INTO v_long;
      CLOSE c_vw;
      sp_out (v_long);
    ELSE
      FOR rec IN c_tab LOOP
        v_sum_indx_size := 0;
        sp_out (
                'RECS:'
             || to_char(rec.num_rows, '9G999G999G990')
             || ' BLKS:'
             || to_char(rec.blocks, '9G999G999G990')
             || '   MB:'
             || to_char(rec.size_mb, '99G990D90')
             || '   AVGLEN:'
             || rec.avg_row_len);
        sp_out (
                'SMPL:'
             || to_char(rec.sample_size, '9G999G999G990')
             || ' ANAL: '
             || TO_CHAR (rec.last_analyzed, 'DD.MM.YYYY HH24:MI:SS'));
        sp_out ('----------------------------------------------');
        v_sum_tbl_size := rec.size_mb;
      END LOOP;
      i:=0;
      FOR rec IN c_tcols LOOP
        i := i+1;
        sp_out (
                'COL' || to_char(i,'90') || ':'
             || RPAD (rec.column_name, 30)
             || ' TYP:'
             || RPAD (rec.data_type, 10)
             || ' VALS:'
             || TO_CHAR (rec.num_distinct, '999G999G990')
             || ' DENS:'
             || LPAD (ROUND (rec.density, 4), 8)
             || ' NULLS:'
             || TO_CHAR (rec.num_nulls, '999G999G990')
             || ' HIST:'
             || LPAD (rec.num_buckets, 4));
       END LOOP;
       
       sp_out ('----------------------------------------------');
       
       sp_out ('Indexes:');
       FOR rec IN c_ind LOOP
           v_sum_indx_size := v_sum_indx_size + rec.size_MB;
           sp_out (
                RPAD (rec.index_name, 30)
             || ' '
             || RPAD (rec.index_type, 20)
             || '      LBLKS:'
             || TO_CHAR (rec.leaf_blocks, '9G999G990')
             || '    KEYS:'
             || TO_CHAR (rec.distinct_keys, '999G999G990')
             || '    CLUSTR:'
             || TO_CHAR (rec.clustering_factor, '999G999G990'));             
           sp_out (
                CHR (9)
             || LPAD(TO_CHAR (rec.size_MB, '99G990D990'),15)
             || ' MB  Index quality:'
             || RPAD (rec.index_quality,14)
             || ' Analyzed:'
             || TO_CHAR (rec.last_analyzed, 'DD.MM.YYYY HH24:MI:SS'));
         FOR rec2 IN c_icols (rec.index_name) LOOP
           v_long := rec2.column_expression;
           IF v_long IS NOT NULL THEN
             v_long := '         ' || SUBSTR (v_long, 1, 150);
           END IF;
           sp_out (
                CHR (9)
             || CHR (9)
             -- || ' POS:'
             || rec2.column_position
             || ' '
             || RPAD (rec2.column_name, 30)
             || v_long);
         END LOOP;
       END LOOP;
       sp_out ('------------------');
       sp_out ('Total:'|| to_char(v_sum_tbl_size,'99G990D90')||'/'||to_char(v_sum_indx_size,'99G990D90') ||' MB');
       sp_out ('Index goodnes:'||to_char(((1-(v_sum_indx_size/v_sum_tbl_size))),'0D990'));
       sp_out (chr(9)||chr(9)||'Score is -1.000 (worst) to 1.000 (best)');
    END IF;
    sp_out ('============================================================================================');
    sp_out(chr(9));
    v_ind := t_tabs.NEXT (v_ind);
  END LOOP;
  DBMS_OUTPUT.put_line ('Done.');
END;
/

PROMPT usage table_info OWNER table_name 
PROMPT usage table_info OWNER table_name','table_name2 ...

To see all picture I called db_size_pkg.GROW_HIST_ONE_SEGMENT for mentioned 4 segments (table+3 indxes). Result is here:

YYY.XX_CASH_UP_DETAIL_T(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20100401   14.080,00   14.080,00          0,00          0,00      138.283.379   138.283.379            0,00            0,00
        20100501   15.104,00    1.024,00          7,27          7,27      148.038.809     9.755.430            7,05            7,05
        20100601   16.000,00      896,00          5,93         13,64      157.881.982     9.843.173            6,65           14,17
        20100701   16.959,00      959,00          5,99         20,45      167.597.811     9.715.829            6,15           21,20
        20100801   18.047,00    1.088,00          6,42         28,17      131.642.170   -35.955.641          -21,45           -4,80
        20100901   18.047,00        0,00          0,00         28,17      142.593.105    10.950.935            8,32            3,12
        20101001   18.047,00        0,00          0,00         28,17      152.581.707     9.988.602            7,00           10,34
        20101101   18.047,00        0,00          0,00         28,17      162.457.621     9.875.914            6,47           17,48
        20101201   18.047,00        0,00          0,00         28,17      171.849.364     9.391.743            5,78           24,27
        20110101   18.431,00      384,00          2,13         30,90      181.811.901     9.962.537            5,80           31,48
        20110201   19.341,44      910,44          4,94         37,37      191.463.063     9.651.162            5,31           38,46
        20110301   20.236,44      895,00          4,63         43,72      200.237.474     8.774.411            4,58           44,80
        20110401   21.260,44    1.024,00          5,06         51,00      210.132.211     9.894.737            4,94           51,96
        20110501   22.205,44      945,00          4,44         57,71      219.391.022     9.258.811            4,41           58,65
        20110601   23.136,44      931,00          4,19         64,32      229.119.594     9.728.572            4,43           65,69
        20110701   24.234,44    1.098,00          4,75         72,12      238.971.012     9.851.418            4,30           72,81
        20110801   25.386,44    1.152,00          4,75         80,30      250.716.669    11.745.657            4,92           81,31
        20110901   26.474,44    1.088,00          4,29         88,03      262.031.476    11.314.807            4,51           89,49
        20111001   27.562,44    1.088,00          4,11         95,76      272.403.807    10.372.331            3,96           96,99
        20111101   28.586,44    1.024,00          3,72        103,03      282.374.433     9.970.626            3,66          104,20
        20111201   29.602,44    1.016,00          3,55        110,24      292.654.644    10.280.211            3,64          111,63
        20120101   30.626,44    1.024,00          3,46        117,52      303.123.028    10.468.384            3,58          119,20
        20120201   31.714,44    1.088,00          3,55        125,24      313.630.238    10.507.210            3,47          126,80
        20120301   32.674,44      960,00          3,03        132,06      323.487.403     9.857.165            3,14          133,93
        20120401   33.762,44    1.088,00          3,33        139,79      334.051.188    10.563.785            3,27          141,57
        20120501   34.786,44    1.024,00          3,03        147,06      344.092.877    10.041.689            3,01          148,83
        ===========================================================================================================================


YYY.XXD_CASH_UP_I(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    8.704,00    8.704,00          0,00          0,00
        20100501    8.960,00      256,00          2,94          2,94
        20100601    7.808,00   -1.152,00        -12,86        -10,29
        20100701    8.512,00      704,00          9,02         -2,21
        20100801    9.344,00      832,00          9,77          7,35
        20100901    9.344,00        0,00          0,00          7,35
        20101001    9.344,00        0,00          0,00          7,35
        20101101    9.344,00        0,00          0,00          7,35
        20101201    9.984,00      640,00          6,85         14,71
        20110101   10.736,00      752,00          7,53         23,35
        20110201    9.433,13   -1.302,88        -12,14          8,38
        20110301   10.073,13      640,00          6,78         15,73
        20110401   10.827,13      754,00          7,49         24,39
        20110501   11.467,13      640,00          5,91         31,75
        20110601   12.171,13      704,00          6,14         39,83
        20110701   12.875,13      704,00          5,78         47,92
        20110801   13.729,13      854,00          6,63         57,73
        20110901   14.561,13      832,00          6,06         67,29
        20111001   15.335,13      774,00          5,32         76,18
        20111101   16.039,13      704,00          4,59         84,27
        20111201   16.807,13      768,00          4,79         93,10
        20120101   17.511,13      704,00          4,19        101,18
        20120201   18.279,13      768,00          4,39        110,01
        20120301   18.983,13      704,00          3,85        118,10
        20120401   19.746,13      763,00          4,02        126,86
        20120501   20.514,13      768,00          3,89        135,69
        ============================================================

YYY.XXD_CASH_UP_IX_REDD(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    3.584,00    3.584,00          0,00          0,00
        20100501    3.840,00      256,00          7,14          7,14
        20100601    3.392,00     -448,00        -11,67         -5,36
        20100701    3.712,00      320,00          9,43          3,57
        20100801    4.032,00      320,00          8,62         12,50
        20100901    4.032,00        0,00          0,00         12,50
        20101001    4.032,00        0,00          0,00         12,50
        20101101    4.032,00        0,00          0,00         12,50
        20101201    4.288,00      256,00          6,35         19,64
        20110101    4.608,00      320,00          7,46         28,57
        20110201    4.047,63     -560,38        -12,16         12,94
        20110301    4.367,63      320,00          7,91         21,86
        20110401    4.635,63      268,00          6,14         29,34
        20110501    4.955,63      320,00          6,90         38,27
        20110601    5.211,63      256,00          5,17         45,41
        20110701    5.531,63      320,00          6,14         54,34
        20110801    5.979,63      448,00          8,10         66,84
        20110901    6.171,63      192,00          3,21         72,20
        20111001    6.555,63      384,00          6,22         82,91
        20111101    6.875,63      320,00          4,88         91,84
        20111201    7.195,63      320,00          4,65        100,77
        20120101    7.451,63      256,00          3,56        107,91
        20120201    7.835,63      384,00          5,15        118,63
        20120301    8.091,63      256,00          3,27        125,77
        20120401    8.411,63      320,00          3,95        134,70
        20120501    8.667,63      256,00          3,04        141,84
        ============================================================

YYY.XXD_PAIEMENT_I(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20100401    2.688,00    2.688,00          0,00          0,00
        20100501    2.944,00      256,00          9,52          9,52
        20100601    2.496,00     -448,00        -15,22         -7,14
        20100701    2.752,00      256,00         10,26          2,38
        20100801    3.008,00      256,00          9,30         11,90
        20100901    3.008,00        0,00          0,00         11,90
        20101001    3.008,00        0,00          0,00         11,90
        20101101    3.008,00        0,00          0,00         11,90
        20101201    3.200,00      192,00          6,38         19,05
        20110101    3.520,00      320,00         10,00         30,95
        20110201    3.005,38     -514,63        -14,62         11,81
        20110301    3.265,38      260,00          8,65         21,48
        20110401    3.495,38      230,00          7,04         30,04
        20110501    3.687,38      192,00          5,49         37,18
        20110601    3.943,38      256,00          6,94         46,70
        20110701    4.135,38      192,00          4,87         53,85
        20110801    4.455,38      320,00          7,74         65,75
        20110901    4.711,38      256,00          5,75         75,27
        20111001    4.967,38      256,00          5,43         84,80
        20111101    5.287,38      320,00          6,44         96,70
        20111201    5.479,38      192,00          3,63        103,85
        20120101    5.735,38      256,00          4,67        113,37
        20120201    5.991,38      256,00          4,46        122,89
        20120301    6.183,38      192,00          3,20        130,04
        20120401    6.504,38      321,00          5,19        141,98
        20120501    6.696,38      192,00          2,95        149,12
        ============================================================

Obviously chosen table has along with indexes around 70.67GB in disk size with more then 344 M records. If you compare data from whole database perspective, then you realize what this numbers represent:

SQL> exec db_size_pkg.get_db_size;

PERIOD         SIZE MB        TREND MB           RECORDS        TREND REC         REC/1 MB     TR REC/1 MB
==========================================================================================================
20100401     212.396,06           0,00         547.508.368               0        2.577,77            0,00
20100501     214.708,25       2.312,19         560.250.058      12.741.690        2.609,36        5.510,66
20100601     216.058,38       1.350,13         570.327.760      10.077.702        2.639,69        7.464,27
20100701     222.598,19       6.539,81         583.152.070      12.824.310        2.619,75        1.960,96
20100801     231.681,31       9.083,13         563.291.109     -19.860.961        2.431,32       -2.186,58
20100901     238.611,50       6.930,19         586.688.618      23.397.509        2.458,76        3.376,17
20101001     242.191,75       3.580,25         596.948.818      10.260.200        2.464,78        2.865,78
20101101     251.025,25       8.833,50         613.029.785      16.080.967        2.442,10        1.820,45
20101201     268.175,00      17.149,75         644.685.678      31.655.893        2.403,97        1.845,85
20110101     294.427,38      26.252,38         656.266.145      11.580.467        2.228,96          441,12
20110201     258.931,69     -35.495,69         666.924.336      10.658.191        2.575,68         -300,27
20110301     259.844,75         913,06         675.320.474       8.396.138        2.598,94        9.195,58
20110401     263.566,19       3.721,44         690.339.150      15.018.676        2.619,23        4.035,72
20110501     272.683,69       9.117,50         709.499.198      19.160.048        2.601,91        2.101,46
20110601     275.960,56       3.276,88         724.469.828      14.970.630        2.625,27        4.568,57
20110701     290.073,06      14.112,50         748.612.143      24.142.315        2.580,77        1.710,70
20110801     298.008,38       7.935,31         777.900.233      29.288.090        2.610,33        3.690,86
20110901     309.598,00      11.589,63         803.806.703      25.906.470        2.596,29        2.235,32
20111001     320.487,94      10.889,94         822.130.066      18.323.363        2.565,24        1.682,60
20111101     314.488,81      -5.999,13         838.907.362      16.777.296        2.667,53       -2.796,62
20111201     323.648,50       9.159,69         865.764.316      26.856.954        2.675,01        2.932,08
20120101     337.712,44      14.063,94         883.382.388      17.618.072        2.615,78        1.252,71
20120201     349.180,06      11.467,63         904.223.659      20.841.271        2.589,56        1.817,40
20120301     360.276,38      11.096,31         941.912.168      37.688.509        2.614,42        3.396,49
20120401     362.167,56       1.891,19         978.799.520      36.887.352        2.702,62       19.504,86
20120501     353.547,19      -8.620,38         967.132.407     -11.667.113        2.735,51        1.353,43
==========================================================================================================

The whole database has 353.6 GB with 967M records. According previous number, mine nr. one candidate has 20% of disk size and 33% of all records. So this is really very important table for starting point. And this is not all, if you look closely, you'll see that tables is getting cca 10M records a month, what gives us 333k records per day. And this all is performed by updating 3 indexes with more then 35GB in it's size! we'll this is not small at all. In this moment, I didn't want to see when this inserts are happening but to understand those big index numbers with adequate usage. For that, next I look in gv$sql to see which queries are using this table. The query is rather simple:

select distinct substr(sql_text,1,100) sql 
  from gv$sql
 where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T%'

Result was like:

SQL
---------------------------------------------------------------------------------------------------
SELECT /*+ NOPARALLEL (GV_$SQL) */ INST_ID, SQL_TEXT, SQL_FULLTEXT,     SQL_ID, SHARABLE_MEM, PERSIS 
select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy 
select substr(sql_text,1,100) sql  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T% 
select sql_text  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_DETAIL_T%'                  
select distinct substr(sql_text,1,100) sql  from gv$sql where upper(SQL_FULLTEXT) LIKE '%XX_CASH_UP_ 
select min(date_reddition) from xx_cash_up_detail_t   

So if we extract system calls (gather_stats or db_size snapshot), only two queries remains:

select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy 
select min(date_reddition) from xx_cash_up_detail_t   

Making count on number of executions, we get the clearer picture when this table is used:

• 1 time min (date_reddition)
• 2 times count('1')

Cool-not too much. But this raise other questions and douobts. Let see complete sql's, executions plans, and execution timings for each sql with normal and changed plan.

Case 1

This example is far from ideal measuring (clearing buffer cache was not performed) but the result will be representative enough to clear (confirm) mine doubts. Here is original first sql:

select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')

Execution plan was using index:

Plan
SELECT STATEMENT ALL_ROWS Cost: 118 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 INDEX FAST FULL SCAN INDEX YYY.XXD_CASH_UP_IX_REDD Cost: 118 K Bytes: 310 M Cardinality: 41 M 

And when you run the query, here is the result:

SQL> select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:04:00.62
SQL>

So, 4 minutes for such a big table is not too much and especially when have in mind that DR was only 404.746. In the meantime (while sql was running), I checked in another session and see that index was really used:

 IID SID_SERIAL  USERNAME        TARGET                                SEC MESSAGE                        % Complete
---- ----------- --------------- ----------------------------------- ----- ------------------------------ ----------
   4 672,23628   SYS             YYY.XX_CASH_UP_DETAIL_T               165 Index Fast Full Scan:  YYY.XX_      66.16
                                                                           CASH_UP_DETAIL_T: 735860 out o
                                                                           f 1112291 Blocks done

I wanted to force to run the same query but not allowing to use index...forcing FTS on such a big table:

select /*+ FULL(xx_cash_up_detail_t) */ 
       count('1') 
  from xx_cash_up_detail_t 
 where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

Plan for that sql was showing really, as expected full table scan, on table:

Plan
SELECT STATEMENT ALL_ROWS Cost: 439 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 TABLE ACCESS FULL TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 439 K Bytes: 310 M Cardinality: 41 M 

Real run gave me this result:

SQL> select /*+ FULL(xx_cash_up_detail_t) */ 
  2         count('1') 
  3    from xx_cash_up_detail_t 
  4   where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:09:22.47
SQL>

So 9,5 minutes. As previously, I checked that FTS is really performing in this sql:

 IID SID_SERIAL  USERNAME        TARGET                                SEC MESSAGE                        % Complete
---- ----------- --------------- ----------------------------------- ----- ------------------------------ ----------
   4 672,23628   SYS             YYY.XX_CASH_UP_DETAIL_T               379 Table Scan:  YYY.XX_CASH_UP_DE      55.74
                                                                           TAIL_T: 2488405 out of 4464305
                                                                            Blocks done

6 minutes saving opposite sql which use index is not small but it depends how often query is run. To be fair, I repeat the same first query (which was using some kind of buffer cache-but this was best case scenario). Result was:

SQL> select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy');

COUNT('1')
----------
  62014296

Elapsed: 00:03:03.45
SQL>

So 3 minutes is the best time scenario. BC has gained for more then 15%. So far result didn't impressed me.

Case 2

Now come to test second sql. Here is execution plan as well as timing:

SQL> select min(date_reddition) from xx_cash_up_detail_t;

MIN(DATE
--------
01.10.09

Elapsed: 00:00:29.78
SQL>

Plan
SELECT STATEMENT ALL_ROWS Cost: 434 K Bytes: 8 Cardinality: 1 
  2 SORT AGGREGATE Bytes: 8 Cardinality: 1 
    1 INDEX FULL SCAN (MIN/MAX) INDEX YYY.XXD_CASH_UP_IX_REDD Bytes: 2 G Cardinality: 324 M 

According size of underlying table, this sql runs like hell. I tried to avoid usage of index again, to see run time results:

SQL> select /*+ FULL(xx_cash_up_detail_t) */ min(date_reddition) from xx_cash_up_detail_t;

MIN(DATE
--------
01.10.09

Elapsed: 00:13:09.98
SQL>

This was really slow, but again, number of executions is important (keep in mind all the time size of maintained indexes). So far in this case testing is enough. Let see AWR data which will cover time that ASH cannot.

AWR data

As explaind in Automated AWR reports in Oracle 10g/11g post, mine settings in database are defined as 46 days window retention with 15 minutes snapshot interval. This can be reviewed as:

SQL> col snap_interval format a30
SQL> col retention format a30
SQL> select snap_interval
  2  , retention
  3  from dba_hist_wr_control
  4  /

SNAP_INTERVAL                  RETENTION
------------------------------ ------------------------------
+00000 00:15:00.0              +00046 00:00:00.0

SQL> 

So 46 days, gave me nice time frame to picture is there anything happening with focused table. Just to mention that this queries were performed before running all previous tests, just to avoid wrong entries, which doesn't happened in real DB life. Query is again very simple:

select sql_text 
from dba_hist_sqltext
where upper(SQL_TEXT) LIKE '%XX_CASH_UP_DETAIL_T%'

Query gave me next results:

Nr Execs	SQL statement
1	INSERT INTO YYY.XX_CASH_UP_DETAIL_T INS_TBL (ID_RESEAU, ID_SITE, MATRICULE, DATE_REDDITION, SAC, CORRECTION_STATUS, ID_PAIEMENT, LIBELLE_PAIEMENT, ID_CLASSE, MONTANT_PAIEMENT, DEVISE_PAIEMENT, NB_PAIEMENT, MONTANT_TOTAL, INSERT_DATE, DATE_MESSAGE) SELECT ID_RESEAU, ID_SITE, MATRICULE, DATE_REDDITION, SAC, CORRECTION_STATUS, ID_PAIEMENT, LIBELLE_PAIEMENT, ID_CLASSE, MONTANT_PAIEMENT, DEVISE_PAIEMENT, NB_PAIEMENT, MONTANT_TOTAL, INSERT_DATE, DATE_MESSAGE FROM TR_CASH_UP_DETAIL@db_link.hr WHERE CORRECTION_STATUS IS NOT NULL
2	select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')
1	SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T"
1	select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU
1	select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59' AND SAC=731 AND DATE_MESSAGE IS NOT NULL

Let us look for plans that might use indexes. I'm using count on substr (sql_text1,100) to get more precise results:

Nr Execs	SQL statement	Index use?
1	INSERT INTO YYY.XX_CASH_UP_DETAIL_T	N
2	select count('1') from xx_cash_up_detail_t where date_reddition >= to_date('01.11.2011', 'dd.mm.yyyy')	Y
1	SELECT count(*) FROM "YYY"."XX_CASH_UP_DETAIL_T"	N (FTS)
1	select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU	?
1	select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59' AND SAC=731 AND DATE_MESSAGE IS NOT NULL	?

Pogledajmo planove za te upite (insert nema smisla :

select count(*), ID_RESEAU from yyy.XX_CASH_UP_DETAIL_T group by ID_RESEAU

Plan
SELECT STATEMENT  ALL_ROWSCost: 552 K  Bytes: 9  Cardinality: 3    
 2 HASH GROUP BY  Cost: 552 K  Bytes: 9  Cardinality: 3   
  1 TABLE ACCESS FULL TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 428 K  Bytes: 928 M  Cardinality: 324 M  

Not using index at all. And last one:

select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL

Plan
SELECT STATEMENT  ALL_ROWSCost: 8  Bytes: 86  Cardinality: 1     
 3 FILTER    
  2 TABLE ACCESS BY INDEX ROWID TABLE YYY.XX_CASH_UP_DETAIL_T Cost: 8  Bytes: 86  Cardinality: 1   
   1 INDEX RANGE SCAN INDEX YYY.XXD_CASH_UP_I Cost: 6  Cardinality: 1  

Let see in praxis:

SQL> select * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL;

no rows selected

Elapsed: 00:00:13.17
SQL>

Index is used and pretty quick. So let's test our FTS on that sql:

SQL> select /*+ FULL(xx_cash_up_detail_t) */ 
  2  * from yyy.XX_CASH_UP_DETAIL_T where ID_RESEAU='01' and ID_SITE='22' AND MATRICULE='115515' AND DATE_REDDITION BETWEEN '01.01.2011 00:00:00' AND '31.12.2011
 23:59:59'  AND  SAC=731 AND DATE_MESSAGE IS NOT NULL;

no rows selected

Elapsed: 00:16:26.84

Uff ... 16 minutes for query and this is really long. Haven't measured DR but should be the same as previous time for FTS ... irrelevant what I want to proove.

Conclusion

All mine doubts about this table were confirmed. In best case scenario in 46 days, there was 3 queries that have used one of indexes that are 36GB in total size. For those purposes, indexes are totally unnecessary in any mean in this context of use.

So I advised to drop all of indexes-do you agree? Performance gain against maintaining indexes against 3 runs in 46 days are worth full.

The end

Keep in mind that another big questions is here to be asked:

Why 33% of all db records and 20% of db disk size, are used only 5 times in a 46 days, while in the same time table grows by 1GB/10mil records a month?

What is the purpose of such a table?

Last but not least ... 330 M records and no primary key. Maybe if there is Guinness for that ... this will be probably in topmost results.

And mine approach that biggest tables makes the biggest problems in this case are totally missed. This case shows that they are not used!! Obviously, somewhere db design went terribly wrong... Still think mine approach will work in other ... better designed database.

Cheers!