Sunday, 20 January 2013

list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'


Search package name wise :-
select object_name, object_type , status
     from dba_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' )
     and object_name in ('DBMS_JAVA' ,'UTL_DBWS')  ;

OR  generic type search :-


select object_name, object_type , status
     from user_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' );
   


SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE Owner IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','APQOSSYS')
     AND Object_Type IN ('PACKAGE', 'TYPE')
     ORDER BY Owner, Object_Type, Object_Name;

SELECT DISTINCT Owner, Object_Type, Object_Name FROM dba_Objects
     WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','APQOSSYS')
     AND Object_Type IN ('PACKAGE', 'TYPE')
     ORDER BY Owner, Object_Type, Object_Name;

Latch Free wait event



"Latch Free" wait event in this database, to solve this wait event  re-size these parameters using below query.

alter system set open_cursors=600 scope=spfile;
alter system set session_cached_cursors=250 scope=spfile;
alter system set cursor_sharing='FORCE' scope=spfile;
                               
and increase the SGA and PGA accordingly .

We need to bounce DB for these parameters to implement.

last_analyzed Oracle tables


select table_name, last_analyzed, num_rows
from user_tables
where last_analyzed < sysdate - 1
order by 3;


select trunc(last_analyzed) "LAST_ANALYZED",
count(*), decode(db_unique_name,null,name,db_unique_name) DBNAME, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "RUNTIME"
from dba_tables, v$database
where owner='<username>' and last_analyzed >= sysdate-20
group by trunc(last_analyzed), decode(db_unique_name,null,name,db_unique_name)
order by trunc(last_analyzed);

finding last dml on tables in Oracle


There is a way to find the last dml time :-

Alter Table Tabname Monitoring;

Once this is there, Select * from User_Tab_Modifications;

If you want all tables to be Checked, Run the script like this
Spool Monitor.sql
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
Spool Off;
@Monitor.sql


select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>;

 select * from all_tab_modifications where table_name = '<table_name>';

If Oracle Queue is invalid in Oracle Streams


Use this:

conn / as sysdba
exec dbms_aqadm_syscalls.kwqa_3gl_validatequeue('<user_name>','RM_QUEUE_ID11');
commit;
connect <user_name>/&PASSWORD
exec DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => '<queue_name>', force => true);

Monday, 14 January 2013

Invitation for OTN Yathra 2013


Hi

I am proud to inform you that some of the best mind from the field of Oracle like Murali Vallath,Hans Forbrich,Vinay Agrawal,Lucas Jellema,Aman Sharma,Raj Mattamal  are coming to event OTN Yathra 2013 in Delhi on 16th Feb 2013 at Saket  Palace, New Delhi .

This event will be held on 16th February 2013 , from 7:30 AM to 16:00 PM (IST) at  Saket Palace New Delhi and I would like to invite you to participate.

 Please visit www.otnyathra.info and  register yourself .

I have invited a few closes DBAs/Programmer. Welcome to invite an additional team member from your organization who plays a role in DBA/Programming so that they can catch up  to listen from those great minds and latest technology from Oracle.

I will anticipate your visit and greatly appreciate you considering this request.

Regards

Shivendra Narain Nirala

Sunday, 13 January 2013

Oracle Job Backup script


select 'declare lv_job number(10); begin ' ||
       'sys.dbms_job.submit(job => lv_job, what => ''' || what ||
       ''', next_date => (sysdate + 1000), interval => ''' || interval ||
       '''); commit; end;'
  from user_jobs;

job stuck in RAC , Process and locks in Oracle RAC



1. Check for blocking sessions in DB.
2. Check for wait events in the system at time of job execution.
3. Execution plan of sql queries.
4. Stats/Fragmentation in the tables.

 select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=75 order by time_waited desc;

 select count(*),event from v$active_session_history where session_id=158 group by event order by count(*) desc;


select PROGRAM,sid,status,last_call_et/(60*60) ,sql_id from gv$session where last_call_et/(60*60) > 3 and status='ACTIVE' AND PROGRAM LIKE '%J00%';


select * from dba_hist_sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$active_session_history where session_id = 72 order by sample_id desc;
select * from dba_tab_statistics  where owner like '<user_name>'AND table_name like '<table_name>';
select * from gv$system_event where wait_class !='Idle' order by time_waited desc ;
select * from gv$session where blocking_session is not null;


select sid, process, event, blocking_session, seq# from gv$session where sid=2194;

 SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
    || '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
    FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
    WHERE V.TYPE = 'JQ'
    AND J.JOB = V.ID2
    AND S.SID = V.SID;


SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
|| '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
WHERE V.TYPE like 'CJ%'
AND J.JOB = V.ID2
AND S.SID = V.SID;

select SCHEMA_USER ||','||job||','||WHAT||',' || INTERVAL ||','||BROKEN||','||LAST_DATE||','||round((sysdate-next_date)*1440)||','||NEXT_DATE  from dba_jobs where job in (310,265,266,267,268,645,281,285,286) order by job;

select inst_id,event,time_waited,average_wait,wait_class from (select inst_id,event,time_waited,average_wait,wait_class, 
row_number() over (partition by inst_id order by time_waited desc, average_wait desc) row_num
from gv$system_event 
where wait_class!='Idle' )
where row_num<=10
order by inst_id,row_num;




select chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "lock",
to_char( bitand(p1, 65535) )    "mode", sid
from gv$session_wait
where lower(event) = 'dfs lock handle';


SELECT b.name||'|'|| b.value_string
FROM
gv$sql_bind_capture b
WHERE   b.sql_id = '4tsv2myfxsr12';



SELECT event AS "Event|Name",
       total_waits "Total|Waits",
       round(time_waited / 100, 0) "Seconds|Waiting",
       total_timeouts "Total|Timeouts",
       average_wait / 100 "Average|Wait|(in secs)"
  FROM sys.v_$system_event e
  ORDER BY time_waited DESC;

select nvl(s.username,s.program) username, s.sid sid, s.serial# serial, s.sql_hash_value sql_hash_value, 
substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event , w.p1 p1, w.p2 p2, w.p3 p3 from v$session s, v$session_wait w
where w.sid=s.sid and s.status='ACTIVE'and s.type='USER';


stick the job to the specific instance in Oracle RAC

First method ( and easy one too ) :-
note:-  must perform this activity on each instance one by one.
......stop jobs which is assigning to the specific instance.....

$ export ORACLE_SID=SID

connect database using job owner ....

$ sqlplus username/password

list of jobs and its instance.... 

sql> select JOB,INSTANCE from user_jobs;
       JOB   INSTANCE
---------- ----------
      2510          0
      2951          0
      2992          0
      3091          0
      3111          0
      1570          0
      1567          0
      1568          0
      1569          0
      1571          0
      1572          0

stick the job to the specific instance.....

Syntax
DBMS_JOB.INSTANCE (
   job        IN BINARY_INTEGER,
   instance   IN BINARY_INTEGER,
   force      IN BOOLEAN DEFAULT FALSE);

sql>execute dbms_job.instance(job=>1571, instance=>2,force=>true);

verify from the instance number........

sql>select JOB,INSTANCE from user_jobs;
       JOB   INSTANCE
---------- ----------
      2510          0
      2951          0
      2992          0
      3091          0
      3111          0
      1570          0
      1567          0
      1568          0
      1569          0
      1571          2

sql> exit


....start jobs........

======================================================================

Second Method ( the long one ) :- 

BEGIN
  -- Create a new service associated with the specified TSN service name.
  DBMS_SERVICE.create_service (
    service_name => '<service name>',
    network_name => '<db name>.<domain name>');
 
  -- Start the specified service.
  DBMS_SERVICE.start_service (
    service_name => ' <service name>');
 
  -- Disconnects all sessions associated with the specified service.
  DBMS_SERVICE.disconnect_session (
    service_name => '<service name>');
 
  -- Stop the specified service.
  DBMS_SERVICE.stop_service (
    service_name => '<service name>');
 
  -- Delete the specified service.
  DBMS_SERVICE.delete_service (
    service_name => '<service name>');
END;
/
 
Some examples of using the srvctl utility to do similar actions are listed below.
 
# Create the service on two nodes.
srvctl add service -d <db name> -s <service name> -r <instance name>,<instance name>
 
# Stop and start the service on a single or multiple nodes.
srvctl stop service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl start service -d <db name> -s <service name> -i <instance name>
 
# Disable and enable the service on a single or multiple nodes.
srvctl disable service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl enable service -d <db name> -s <service name> -i <instance name>
 
# Display the current status of the service.
srvctl status service -d <db name> -s <service name> -v
 
# Remove the service from both nodes.
srvctl remove service -d <db name> -s <service name> -i <instance name>,<instance name>
 
Once a service is present, it can be assigned to a job class during creation or subsequently using the set_attribute procedure, as shown below.
 
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name => '<job class name>',
    service        => ‘<service name>’);
 
  DBMS_SCHEDULER.set_attribute (
    name      => '<job class name>',
    attribute => 'instance_stickiness',
    value     => ‘true’);
END;
/
 
The following scenario will explain more specifically how services can be used to partition applications in a three-node RAC environment.
 
For services to function correctly, the Global Services Daemon (GSD) must be running on each node in the cluster.  The GSDs are started using the gsdctl utility, which is part of the Cluster Ready Services (CRS) installation, so they must be started from that environment.
 
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/crs
export PATH=$ORACLE_HOME/bin:$PATH
 
# Start GSD daemon.
gsdctl start
 
Once the GSDs are running, the user must check that the cluster configuration is correct.  The following command and output show the expected configuration for a three-node database called <db name>.
 
srvctl config database -d <db name>
 
This configuration is typically performed during the cluster database creation, but it can be performed subsequently using the following commands.
 
srvctl add database -d <db name> -o /u01/app/oracle/product/11.2.0/db_1

 
Assume that two applications should run in the following way:
OLTP - Should run on nodes one and two of the RAC, but is able to run on node three if nodes one and two are not available.
BATCH - Should run on node three, but is able to run on nodes one and two if node three is not available.
To meet this requirement, the following services can be created:
 
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
 
# Create services.
srvctl add service -d <db name> -s OLTP_SERVICE -r <instance 1>,<instance 2> -a <instance 1>,<instance 2>,<instance 3>
srvctl add service -d <db name> -s BATCH_SERVICE -r <instance 3> -a <instance 1>,<instance 2>,<instance 3>
 
The OLTP_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on nodes one and two, indicated by the -r option.  The BATCH_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on node three, indicated by the -r option.
 
The services can be started and stopped using the following commands:
 
srvctl start service -d <db name> -s OLTP_SERVICE
srvctl start service -d <db name> -s BATCH_SERVICE
 
srvctl stop service -d <db name> -s OLTP_SERVICE
srvctl stop service -d <db name> -s BATCH_SERVICE

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database



Installing DBMS_JAVA package in Oracle following  files need to be run:- 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';

@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
@?/rdbms/admin/catexf.sql;

and verify your java installation in Oracle Database 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';
sql> select dbms_java.longname(object_name), status, object_type from all_objects where
      object_type = 'JAVA CLASS';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

calling UTL_DBWS web services through Oracle Database :- 

set the follwing parameters in .bash_profile of oracle user 
export JAVA_HOME=$ORACLE_HOME/jdk
ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/bin:$PATH; export PATH
CLASSPATH=.:/opt/oracle/product/11.2.0/dbhome_1/jdbc/lib:$ORACLE_HOME/sqlj/lib/
translator.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/sqlj/lib/dbwsa.j
ar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORA
CLE_HOME/jdk/lib/dt.jar:$ORACLE_HOME/jdk/lib/tools.jar:$ORACLE_HOME/jlib/jssl
1_1.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORAC
LE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aq
api.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.
jar; export CLASSPATH
LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/lib; export LD_LIBRARY_PATH
PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1/bin; export PATH

then at sql prompt :- 

show parameter SHARED_POOL_SIZE;
show parameter JAVA_POOL_SIZE ;
show parameter LARGE_POOL_SIZE;


sql>alter system set JAVA_POOL_SIZE=150M scope=both; 
sql>alter system set SHARED_POOL_SIZE=150M scope=both; 
sql>alter system set LARGE_POOL_SIZE=150M scope=both;  


sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';
In Oracle Database 11g, the UTL_DBWS PL/SQL package is installed in the database SYS schema. To verify the installation, 
try to describe the package as follows:
SQL> describe sys.utl_dbws;

ERROR:
ORA-04043: object sys.utl_dbws does not exist

Cuase : - UTL_DBWS package was not installed in SYS schema by default in Oracle Database 11g R2.

Solution :- Manually install the UTL_DBWS package  in SYS schema by following the steps as below:


install utl_dbws :- If the output indicates that the package is not yet installed,download the latest UTL_DBWS, install them in the 
$ORACLE_HOME/sqlj/lib location  and then run the following scripts under SYS:

download site : - http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME
unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj
unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

@$ORACLE_HOME/sqlj/lib/utl_dbws_decl.sql
@$ORACLE_HOME/sqlj/lib/utl_dbws_body.sql

at the sql prompt :- 

sql>SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status; 

If there are classes that are listed as being in an 'INVALID' state, you can run the following script to attempt to make these objects VALID: 

$ORACLE_HOME/rdbms/admin/utlrp.sql 

Once this has been run, recheck the status of your Java objects. 

When loading the jar files in the desired schema, make sure the PATH environment variable includes the $ORACLE_HOME/bin directory.  
From a terminal window, run the following command:-

loadjava -u sys/sys -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar 

select owner, object_type, created, status from dba_objects where object_name='UTL_DBWS';

then run the follwoing calls :- sys as sysdba for required user - 


call dbms_java.grant_permission( '<user_name>','SYS:java.util.logging.LoggingPermission','control', '' ); 
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' ); 
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','shutdow
nHooks', '' );
call dbms_java.grant_permission('<user_name>','SYS:java.util.logging.LoggingPermission', 'control', '' );
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxySet','write');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');
call dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');  
call dbms_java.grant_permission( '<user_name>','SYS:oracle.aurora.security.JServerPermission', 'Verifier', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','' ) ;
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '<<machineName>>', 'resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.4.146', 'connect,resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', 'localhost', 'resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.3.229:80', 'connect,resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');




Oracle DB Health Scripts 2 - I/O,cpu_time,user_time,wait_time,PGA_memory


SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (12/24);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select name,round(value/1024/1024) MB from v$pgastat where name in ('total PGA allocated','maximum 
PGA allocated')
union all
select 'alloc v$process',round(sum(pga_alloc_mem)/1024/1024) MB from v$process
union all
select 'max PGA v$process',round(sum(pga_max_mem)/1024/1024) MB from v$process
union all
select name,round(value/1024/1024) MB from v$sysstat where name like '%pga%';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise PGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise UGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session uga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session uga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





memory leak


Recently i found a interesting situation where a LQA server has only 3 GB of RAM and  5 GB of swap . I assigned the 20GB of memory to the Oracle and re-start it  and without any problem it started smoothly . I tried to found out the memory leakage on this server but in vain , and the most interesting part is when all the users log on this server , this worked fine without any problem .Again i re-assigned the memory to it  according to the RAM and requirement . :)

Sunday, 6 January 2013

Find out invalid objects in Oracle Database and Generate scripts to compile


Find out invalid objects in Oracle and compile them :-

select owner,count(*),object_type from dba_objects where status='INVALID' group by owner,object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by owner;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :-


select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
===========================================================


query about catalog and catproc :-

select substr(comp_id,1,12) comp_id, status, substr(version,1,10) version, substr(comp_name,1,40) comp_name from dba_registry order by 1;





get ddl of dblinks ,tablespace,user creation, role granted


Get ddl of database links in Oracle Database :-

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

==================================================================
Get ddl of tablespace creation in Oracle Database :- 

 select 'create tablespace ' || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes 
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize ' 
 || maxbytes) 
 || chr(10) 
 || 'default storage ( initial ' || initial_extent 
 || decode (next_extent, null, null, ' next ' || next_extent )
 || ' minextents ' || min_extents
 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents) 
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name 
/
=====================================================================
Get ddl of table and index :-

select DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') from dual; 

select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

==================================================================
Get ddl of User creation in Oracle :-

select 'create user ' || U.username || ' identified ' || 
DECODE(password, 
      NULL, 'EXTERNALLY', 
      ' by values ' || '''' || password || ''''
      ) 
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace || 
decode (account_status,'LOCKED', ' account lock',
      'EXPIRED', ' password expire',
      'EXPIRED \& LOCKED', ' account lock password expire',
      null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;


or use :-

SELECT dbms_metadata.get_ddl('USER','<username>') from dual;
==============================================================
Get ddl of dba_directories: -

select dbms_metadata.get_ddl('DIRECTORY',directory_name) from  dba_directories;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<username>') from dual;

SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') from dual;



ORA-01476 divisor is equal to zero , DBMS_STATS.GATHER_TABLE_STATS


exec dbms_stats.gather_schema_stats('<username>',CASCADE => TRUE);

to gather index stats :-

SQL>  BEGIN
         dbms_stats.gather_schema_stats(
         ownname=>'<username>',
          METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
          CASCADE=>TRUE,
           ESTIMATE_PERCENT=>100);
           END;
     /

 exec dbms_stats.gather_schema_stats('<username>',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

===================================================================
Login as schema user and follow below steps one by one :-



step 1:-
  exec DBMS_STATS.SET_TABLE_PREFS('<username>','<tablename>','INCREMENTAL','TRUE');

Step 2:-
Check if the above command has set the preferences correcctly.

  select dbms_stats.get_prefs('INCREMENTAL', tabname=>'<tablename>') from dual;

step3:-

exec dbms_stats.gather_table_stats(ownname=>'<username>',tabname=>'<tablename>',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);

=======================================================================
Or Use this :-
BEGIN
  DBMS_STATS.gather_table_stats(
    '<username>',
    '<tablename>',
    method_opt => 'for all columns size auto');
END;
/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

step to  gather stats for partition table:-

Step 1 :-
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED < SYSDATE - 2 AND
TABLE_NAME = '<partition_table_name>';

BEGIN
  FOR I IN C1 LOOP
LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''<username>'',''<partition_table_name>'',partname=>'||':1'||',granularity=>''partition'') ;' ;
   LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING  I.P ;
END LOOP;
END;
/

Or use step 2:-
  exec DBMS_STATS.SET_TABLE_PREFS('<username>','<partition_table_name>','INCREMENTAL','true');

next step of 2:-
 exec dbms_stats.gather_table_stats('<username>','<parttion_table_name>',CASCADE => TRUE ,ESTIMATE_PERCENT => 1);


OR You can use :-

BEGIN
dbms_stats.gather_table_stats ('<username>','<partition_table_name>',granularity=>'SUBPARTITION') ;
END ;

=============================================================================================
To remove the following error :-

ORA-01476: divisor is equal to zero , DBMS_STATS.GATHER_TABLE_STATS

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('<username>','<patition_table_name>', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8, cascade=>true , granularity=>'ALL')
END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_STATS", line 10502
ORA-06512: at "SYS.DBMS_STATS", line 10516
ORA-06512: at line 1

It is a bug. following steps solved the problem as suggested by metalink.

SQL> ALTER SESSION SET EVENTS '38041 TRACE NAME CONTEXT FOREVER, LEVEL 24';
Session altered.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('OPS$MTNMUPB','CHARGE', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8,casca
de=>true , granularity=>'ALL')
PL/SQL procedure successfully completed.
Elapsed: 05:46:29.55
SQL> SPOOL OFF



finding fragmentation at table level and tablespace level in Oracle and steps to remove them

Use this query :-


select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
    round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
    round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
    from all_tables  WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Or This one :- It will collect the data which are having more than 100MB fragmentation.


select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from
dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;


========================================================================

Description

This script lists details of the extents within a tablespace. This will help you determine if a tablespace is fragmented or not. A tablespace with little or no fragmentation the biggest, smallest and average will be similar (e.g. as in a LOCALLY MANAGED tablespace of UNIFORM SIZE)

Parameters

None.

SQL Source


set pages 50

PROMPT
PROMPT Tablespace Freespace Fragmentation Report
PROMPT

column "Blocks" format 999999
column "Free" format 999999
column "Pieces" format 99999
column "Biggest" format 999999
column "Smallest" format 999999
column "Average" format 999999
column "Dead" format 9999
select substr(ts.tablespace_name,1,12) "Tspace",
       tf.blocks "Blocks",
       sum(f.blocks) "Free",
       count(*) "Pieces",
       max(f.blocks) "Biggest",
       min(f.blocks) "Smallest",
       round(avg(f.blocks)) "Average",
       sum(decode(sign(f.blocks-5),-1,f.blocks,0)) "Dead"
from   dba_free_space f,
       dba_data_files tf,
       dba_tablespaces ts
where  ts.tablespace_name=f.tablespace_name
and    ts.tablespace_name = tf.tablespace_name
group by ts.tablespace_name,tf.blocks
/

exit


========================================================================
Description

This script lists details how chained or migrated rows there are within a table. It may help you determine if a table needs to be rebuilt. In order for this script to be effective, you must analyze your tables regularly.

Parameters

None.

SQL Source

CLEAR
SET HEAD ON
SET VERIFY OFF
set pages 100
set lines 79

PROMPT
PROMPT Table Fragmentation Report
PROMPT

col owner form a12
col table_name form a20
col empty_blocks form 999,999 heading "Empty Blks"
col blocks form 999,999 heading "Blks"
col pct form 99

select owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where chain_cnt > 0
and owner not in ('SYS','SYSTEM')
/

exit

================================================================

select table_name,round((blocks*8),2) "size (kb)" ,
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from user_tab_partitions
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

===============================================================
steps to remove them :-


If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. To remove this use the following options:

1. Export and import the table (difficult to implement in production environment)
2. Move table in to different or same tablespace (Depends upon the free space available in the tablespace)

Here for the 2nd  option:

1. Collect status of all the indexes on the table.
select index_name,status from user_indexes where table_name like 'table_name';

status may be valid or unusable.

2. Command to move in to new tablespace:
alter table <tb_name> enable row movement;---- Run this command before moving table
alter table table_name move tablespace new_tablespace_name

3. Command to move in to old tablespace
alter table table_name move tablespace old_tablespace_name

If we have free space available  in the tablespace which contain the table. Then we can replace step 2 and 3 by

alter table table_name move ;
alter table <tb_name> disable row movement;---- Run this command after moving table

4. rebuild all indexes
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.
select index_name from user_indexes where table_name like 'table_name';
alter index index name rebuild online;

5. check status of all the indexes
select index_name,status from user_indexes where table_name like 'table_name';
here value in status field must be valid.

6. Crosscheck space is reclaimed in tablespace

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Above query will show increase in value of Free Size MB field of tablespce which owns fragmented table.

7. Gather table states:
exec dbms_stats.gather_table_stats('schema_name','table_name');

Oracle : force query to use hints


alter session set tracefile_identifier='RC_CBO';
alter session set events '10053 trace name context forever, level 1';
Following these two commands, run either the problem query or an explain plan for the problem query. Look for the tracefile in the user_dump_dest directory; it can be readily identified by its tracefile_identifier (in this case,RC_CBO). Browse it, attempt to understand as much of it as you can, hint your query, and start the tracing process over again. It can be very helpful to have both tracefiles (before and after hinting.


To get a trace of your own SQL session, the following statements should be included preceding the SQL under analysis:
alter session set tracefile_identifier='RC_PROF';
alter session set sql_trace=TRUE;
To also see wait events (recommended!) use this alternative :-
alter session set tracefile_identifier='RC_PROF';
alter session set events '10046 trace name context forever, level 8';
Look for files with the tracefile_identifier (in this case, RC_PROF) in the user_dump_dest directory, and apply those files to the formatter of your choice according to its documented procedures.

exclude parameter in Oracle EXP Datapump


expdp dumpfile=<file_name>.dmp exclude=TABLE:\"IN \(\'<table_name1>\',\'<table_name2>\',\'<table_name3>\'\)\"   schemas=<user_name>


Note :- If original Export used INDEXES=n, then Data Pump uses the EXCLUDE=INDEX parameter. If original Export used INDEXES=y, then Data Pump uses the INCLUDE=INDEX parameter".

Oracle Custom Procedure for defragmentation Partition tables and their associated index and move non partitioned table


create or replace procedure sp_process_move_data as
lv_tablespace varchar2(100) := '<tablespace_name>';
lv_objects_owner varchar2(100) := '<schema_name>';
lv_sql varchar2(100);

type lr_user_segments is record ( segment_name user_segments.segment_name%type,
segment_type user_segments.segment_type%type,
segsize number,
partition_number number);

type lt_user_segments is table of lr_user_segments index by binary_integer;
type lt_user_tab_partitions is table of user_tab_partitions%rowtype index by binary_integer;
type lt_user_indexes is table of user_indexes%rowtype index by binary_integer;
type lt_user_ind_partitions is table of user_ind_partitions%rowtype index by binary_integer;

la_user_tab_partitions lt_user_tab_partitions;
la_user_indexes lt_user_indexes;
la_user_ind_partitions lt_user_ind_partitions;
la_user_segments lt_user_segments;

procedure log_msg(pv_msg in varchar2) is
lv_ind number;
begin
execute immediate 'select nvl(max(sr_no),0)+1 from process_log'
into lv_ind;

execute immediate 'insert into process_log (sr_no,process_msg) values(:sr_no,:msg)' using lv_ind,pv_msg;
commit;
exception
when others then
log_msg(sqlerrm);
end log_msg;

begin

begin
execute immediate 'drop table process_log';
exception
when others then
null;
end;

begin
execute immediate 'create table process_log (sr_no number,process_msg varchar2(4000 char))';
exception
when others then
null;
end;

--- selecting segment name and size in table space.Also checks whether the segment is partitioned or not by using partition number
select segment_name,
segment_type,
sum(bytes) segsize,
count(*) partition_number
bulk collect into la_user_segments
from user_segments
where segment_type in ('TABLE PARTITION','TABLE')
and tablespace_name = lv_tablespace
and segment_name not like 'BIN$%'
--and segment_name in('STG_CONTACTINFO','CT_SUBSCRIBERS') ----use this condition for testing ---
group by segment_name, segment_type
order by segsize asc;


for iSegments in 1..la_user_segments.count loop
----if condition bifurcate the code for partitioned or non partitioned object
if la_user_segments(iSegments).partition_number >= 1  and la_user_segments(iSegments).segment_type ='TABLE PARTITION' then

-- selecting respective partitions for a table
select *
bulk collect into la_user_tab_partitions
from user_tab_partitions
where table_name = la_user_segments(iSegments).segment_name;

for iTabPart in 1..la_user_tab_partitions.count loop
---moving the partitions in loop of a table
begin
lv_sql:= 'alter table '||la_user_tab_partitions(iTabPart).table_name;
lv_sql := lv_sql||' move partition '||la_user_tab_partitions(iTabPart).partition_name;
lv_sql := lv_sql||' tablespace '||lv_tablespace;

execute immediate lv_sql;
log_msg('Moved Table:'||la_user_tab_partitions(iTabPart).table_name ||' Partition:'||la_user_tab_partitions(iTabPart).partition_name);
exception
when others then
log_msg('Moved Table:'||la_user_tab_partitions(iTabPart).table_name ||' Partition:'||la_user_tab_partitions(iTabPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
---- else of if condition bifurcate the code for partitioned or non partitioned object
else
---moving entire non partitioned table
-- for rec2 in c2(rec.segment_name) loop

--execute immediate 'analyze table '||la_user_segments(iSegments).segment_name||' compute statistics';
begin
execute immediate 'alter table ' || la_user_segments(iSegments).segment_name ||' move tablespace ' || lv_tablespace;
log_msg('Moved Table:'||la_user_segments(iSegments).segment_name);
exception
when others then
log_msg('Moved Table:'||la_user_segments(iSegments).segment_name||';Error=> '||sqlerrm);
goto end_of_code;
end;
-- end loop;
---if condition bifurcates the code for the partitioned index of a NON partitioned table
--- a non partion table may have partitioned index
end if;

--selecting respective indexes for a table
select *
bulk collect into la_user_indexes
from user_indexes
where table_name = la_user_segments(iSegments).segment_name;

for IUsrIdx in 1..la_user_indexes.count loop
---if condition bifurcates the code for the partitioned index of a partitioned table
if la_user_indexes(IUsrIdx).partitioned = 'YES' then
-- selecting index partitions for an index
select *
bulk collect into la_user_ind_partitions
from user_ind_partitions
where index_name = la_user_indexes(IUsrIdx).index_name;

for iUsrIdxPart in 1..la_user_ind_partitions.count loop
begin
execute immediate 'alter index '||la_user_indexes(iUsrIdx).index_name||' rebuild partition '||la_user_ind_partitions(iUsrIdxPart).partition_name||' online';
log_msg('Rebuild Index:'||la_user_indexes(iUsrIdx).index_name ||' Partition:'||la_user_ind_partitions(iUsrIdxPart).partition_name);
exception
when others then
log_msg('Rebuild Index:'||la_user_indexes(iUsrIdx).index_name ||' Partition:'||la_user_ind_partitions(iUsrIdxPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
else
begin
execute immediate 'alter index ' || la_user_indexes(iUsrIdx).index_name ||' rebuild online';
log_msg('rebuild Index:'||la_user_indexes(iUsrIdx).index_name);
exception
when others then
log_msg('rebuild Index:'||la_user_indexes(iUsrIdx).index_name||'.Error => '||sqlerrm);
goto end_of_code;
end;
end if;
end loop;
----gathering statatistics for the prtioned table at partition level and for NON partioned table
----bifurcation of code for partioned and non partitioned object for the statistics gathered.
if la_user_segments(iSegments).partition_number > 1 then

select *
bulk collect into la_user_tab_partitions
from user_tab_partitions
where table_name = la_user_segments(iSegments).partition_number;

for iUsrTabPart in 1..la_user_tab_partitions.count loop
begin
DBMS_STATS.GATHER_TABLE_STATS( lv_objects_owner,
la_user_segments(iSegments).segment_name,
partname => la_user_tab_partitions(iUsrTabPart).partition_name,
cascade => true,
granularity => 'PARTITION');
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name ||'Patition:'||la_user_tab_partitions(iUsrTabPart).partition_name);
exception
when others then
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name ||'Patition:'||la_user_tab_partitions(iUsrTabPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
else
begin
DBMS_STATS.GATHER_TABLE_STATS( lv_objects_owner,
la_user_segments(iSegments).segment_name,
cascade => true);
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name);
exception
when others then
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end if;

end loop;
<<end_of_code>>
null;
exception
when others then
log_msg('Main => '||sqlerrm);
end;
/

Thursday, 3 January 2013

modify statistics gather in Oracle



BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


create or replace procedure user_stats
AS
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
      obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
  filter_lst.extend(1);
filter_lst(1).ownname:='<schema_name>';
filter_lst(1).objname:='<object_name>';

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'<schema_name>',objlist=>obj_lst, obj_filter_list=>filter_lst);
END;
end user_stats;
/


declare
  my_job number;
begin
  dbms_job.submit(job => my_job,
    what => 'user_stats;',
    next_date => trunc(sysdate)+1,
    interval => 'trunc(sysdate)+1');
end;
/

how to use dbverify tool in Oracle


select tablespace_name, segment_name, TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK
from sys.sys_user_segs
where tablespace_name='USERS' and SEGMENT_NAME like 'JUNK%';

dbv file=/ora_data/oracle/data/common_data.dbf   LOGFILE=common_data.log   feedback=200

Wednesday, 2 January 2013

Oracle Audit and how to clear off Oracle Audit


select user_name, audit_option, success, failure
        from sys.dba_stmt_audit_opts;
     
select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,
userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff
from sys.aud$ where userid=<USER_NAME>';

select username, priv_used, ses_actions from dba_audit_object;

select action, action_name, username from dba_audit_trail ;

select * from stmt_audit_option_map;


clear Audit :-


begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval    => 7*24 );
end;
/

Next, to perform the actual purge, you have to execute a packaged procedure dbms_audit_mgmt.clean_audit_trail().

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type =>  dbms_audit_mgmt.audit_trail_all
);
end;
/

Beware: this performs a DELETE operation so there will be a lot of redo and undo. When the initial purge is complete, you may want to set up an automated process to execute this periodically. You will need to create a DBMS Scheduler Job as shown below. This job will be run every week.

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
   audit_trail_purge_interval  => 7*24,
   audit_trail_purge_name      => 'all_audit_trails_job'
   );
end;
/

Note the parameter audit_trail_type which is set to dbms_audit_mgmt.audit_trail_all, which means all the audit trails: AUD$, SYS Audit, FGA audit trails, OS files and XML files. You can also specify those trails specifically as well.

to set a property for  type of Database Audit trails trail  give:-

begin
 dbms_audit_mgmt.set_audit_trail_property(
  audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,
  audit_trail_property_value => 100000);
end;
/