All posts by Cyrille

Better stats job

Oracle script for the Oracle DBA. A better stats job in 10g than the native job.

-- ################################################
-- # Creator: Cyrille MODIANO (DBA Oracle in Montreal)
-- # Created: 2013/02/06
-- # Name: create better gather stats job
-- ################################################
-- #
-- # Compatible: 10g
-- #
-- ################################################

--disable the automatic statistics collection default job :

--10g

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

--11g 

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection', 
    operation => NULL, 
    window_name => NULL);
END;
/
 
-- Create a new job to calculate statistics with histogram :
 
BEGIN
  dbms_scheduler.create_job(
                job_name          =>'GATHER_DATABASE_STATS',
                job_type             =>'PLSQL_BLOCK',
                job_action          =>'BEGIN DBMS_STATS.GATHER_DATABASE_STATS(cascade=>TRUE,degree=>4,estimate_percent=>8,method_opt=>''FOR ALL INDEXED COLUMNS SIZE 251'');end;',
                repeat_interval               =>'FREQ=WEEKLY;BYHOUR=22;BYMINUTE=30',
                enabled                              =>TRUE,
                comments          =>'job to collect statistics with histogram');
END;
/
 
-- Verify what statistics exists for your database using this query :
 
select COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='your_table';

To manually calculate stats for a specific table or index:

For a TABLE:

begin
dbms_stats.gather_table_stats( 
ownname=> 'SCOTT', 
tabname=> 'ORDER' , 
estimate_percent=> null, 
cascade=> FALSE,              -- TRUE to calculate stats for indexes
degree=> DBMS_STATS.AUTO_DEGREE, 
no_invalidate=> FALSE, 
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
END; 

For an INDEX:

BEGIN
dbms_stats.gather_index_stats( 
ownname=> 'SCOTT', 
indname=> 'ORDER_INDX1' , 
estimate_percent=> null, 
degree=> null, 
no_invalidate=> FALSE, 
granularity=> 'AUTO');
END;

Tracking failed logon attempts

-- ################################################
-- # Creator: Cyrille MODIANO
-- # Created: 2011/11/16
-- # Name: Tracking failed logon attempts
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # This script enable auditing for failed login
-- # attempts
-- #
-- ################################################

-- # 1 - Modifiy initialization parameters

alter system set audit_trail=db scope=spfile;

-- # 2 - Restart the database 

shutdown immediate
startup

-- # 3 - Enable auditing for failed login attempts

audit create session whenever not successful;

-- # 4 - Query the dba_audit_trail view to see failed login attempts with error like ORA-"returncode"

set lines 130
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

ZFSFreeMem

-- ################################################
-- # Creator: Cyrille Modiano
-- # Created: 2011/24/02
-- # Name: ZFSFreeMem
-- ################################################
-- #
-- # Compatible: Solaris 10 with ZFS cache
-- #
-- ################################################
-- #
-- # This script returns the free memory size and his
-- # percentage on a solaris 10 host with ZFS cache
-- # More info et best practices:
-- # _http://www.solarisinternals.com/wiki/index.php/ZFS_Best_Practices_Guide
-- ################################################
 
# Memory available on server
typeset -i MEM=`prtconf | grep Memory | awk '{print $3}'`

# ZFS cache size in Mo
typeset -i CACHE=`kstat zfs:0:arcstats:c_max zfs:0:arcstats::c_max zfs:0:arcstats:size | grep size | awk '{print $2}'`/1024/1024

# Free memory
typeset -i FREE=$MEM-\($MEM-$CACHE\)

# Free memory pct
typeset -i PCT=$FREE*100/$MEM

echo Libre : $FREE Mo \($PCT%\)

oracle.xml

###########################################################
# Creator: Cyrille Modiano
# Created: 2011/24/02
# Compatible: Solaris update 6 and higher
###########################################################
#
# An example of SMF file: Service Management Facility
# Replacement of /etc/init.d/oracle file for Solaris 10 Update 6 and higher
# Folder: /var/svc/manifest/application/database
# Variables to change: user, exec
#
###########################################################
 





  
    
    

    
        
    
	
    
      
        
      
    

    
      
        
      
    

   

Start stop service triggers

-- ################################################
-- # Creator: Cyrille MODIANO
-- # Created: 2010/08/20
-- # Name: Start_stop_service_triggers.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # This script creates a trigger used to 
-- # run a scheduler job which call an external shell 
-- # script or executable.
-- # Useful to run a pre command before starting 
-- # and shutdown the DB
-- # Depending of your program it can be  mandatory
-- # to START THE LISTENER BEFORE THE DATABASE
-- ################################################

begin
DBMS_SCHEDULER.create_program (
   program_name => 'START_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');
   
dbms_scheduler.create_job  
  (job_name => 'START_SERVICE',  
   program_name=> 'START_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program START_SCRIPT');
end;
/

begin
DBMS_SCHEDULER.create_program (
   program_name => 'STOP_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');

dbms_scheduler.create_job  
  (job_name => 'STOP_SERVICE',  
   program_name=> 'STOP_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program STOP_SCRIPT');
end;
/

CREATE OR REPLACE TRIGGER START_SCRIPT AFTER STARTUP ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('START_SERVICE');
END;
/

CREATE OR REPLACE TRIGGER STOP_SCRIPT BEFORE SHUTDOWN ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('STOP_SERVICE');
END;
/