script - invobjsq.

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

Oracle EBS Concurrent Mangers logs files

Concurrent Managers log files are located in the $APPLCSF/$APPLLOG location



cd $APPLCSF/$APPLLOG
For ICM Log                     –> ls -lrt *$TWO_TASK*
For Standard manager Log        –> ls -lrt w*.mgr
For Conflict Resolution manager Log –> ls -lrt c*.mgr

SQL Query to get ICM manager log file location

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name

FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

AND fcq.manager_type = '0'

AND fcp.process_status_code = 'A';

Oracle EBS Debug is on

If you have Debug is on at Site level then it will Impact the performance of the Instance, here is the script which will help you to quickly check your EBS Instance if ‘Debug’ is on (all level) of the EBS.


set linesize 160
col USER_PROFILE_OPTION_NAME for a30
col CONTEXT for a30
col VALUE for a30
col NAME for a30

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Oracle Database archive Alert Log

Hello DBAs,

Here is the simple script which can be used for clear archive log on every Sunday. I know we should make it smart but this is for easy setup :)

Steps 1: create a shell script to do this operation


#!/usr/bin/sh
# File name : alert_log_archive.sh
LOGFILE=/u01/oracle/12.1.1/diag/rdbms/dev/DEV/trace/alert_DEV.log
DDATE=`date +"%Y%m%d"`
cp $LOGFILE $LOGFILE.$DDATE
cat /dev/null > $LOGFILE
touch $LOGFILE


Steps 2: schedule it in cronjob

30 23 * * 0 sh /home/oracle/alert_log_archive.sh

Please put your suggestion in a comment.

Oracle Database Archive Log Tables

This script will help you to query the oracle database and list how archive logs are generating.

Script name: archtab.sql

set line 120

set linesize 180

set pagesize 180

SELECT to_date(first_time) DAY,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"

from

v$log_history

where to_date(first_time) > sysdate - 8

GROUP by

to_char(first_time,'YYYY-MON-DD'), to_date(first_time)

order by to_date(first_time)

/

Oracle Database tablespace Query

Check Tablespace size

scriptname : tbsn.sql

set heading on

set linesize 180

set pagesize 100

col TABLESPACE_NAME for a25

col bytes_gb for 99,999,999.999

col FREE_bytes_gb for 99,999,999.999

col ACTUAL_USED_MB for 99,999,999.999

col FILE_USED_PTC for a8

col MAXbytes_gb for 99,999,999.999

col BAL_MAX_EXT_MB for 99,999,999.999

col max_ext_used_pt for a8

col AUTO_EXTEND for a3

set feedback off

select name from v$database;

SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_data_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_free_space

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

set heading off

 SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_temp_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_temp_files

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

==> OLD SQL

set linesize 120
set pagesize 100
col TABLESPACE_NAME for a25
col BYTES_MB for 99,999.999
col FREE_BYTES_MB for 99,999.999
col ACTUAL_USED_MB for 99,999.999
col FILE_USED_PTC for a8
col MAXBYTES_MB for 999,999.999
col BAL_MAX_EXT_MB for 999,999.999
col max_ext_used_pt for a8
col AUTO_EXTEND for a3
SELECT a.tablespace_name
     , round(a.bytes_mb,3) bytes_mb
     , round(b.free_bytes_mb,3) free_bytes_mb
     , round((a.bytes_mb - b.free_bytes_mb),3) actual_used_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) || '%' file_used_ptc
     , round(a.maxbytes_mb,3) maxbytes_mb
     , round((a.maxbytes_mb - (a.bytes_mb - b.free_bytes_mb)),3) bal_max_ext_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.maxbytes_mb),3) || '%' max_ext_used_pt
     , decode(a.autoext,0,'No','Yes') Auto_extend
  FROM (SELECT   dbf.tablespace_name
               , SUM (dbf.bytes)/1024/1024 bytes_mb
               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024 maxbytes_mb
               , sum (dbf.maxbytes) autoext
            FROM dba_data_files dbf
        GROUP BY tablespace_name) a
     , (SELECT   tablespace_name
               , SUM (BYTES) / 1024 / 1024 free_bytes_mb
            FROM dba_free_space
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
-- and   round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) > 85
 order by round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) desc ;


Get Datafile in Table Space


set linesize 120
col file_name for a60
select file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_data_files where tablespace_name = '&TB';

Temporary Tablespace

set linesize 120

col tablespace_name for a10

col file_name for a60

select tablespace_name,file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_temp_files order by 1;

Add datafile

alter tablespace APPS_UNDOTS1 add datafile '/db02/PROD/proddata/APPS_UNDOTS2.dbf' SIZE 1024M autoextend on NEXT 16M MAXSIZE 4096M;

Resize tablespace

ALTER DATABASE DATAFILE '/u02/oracle/PROD/db/apps_st/data/sysaux05.dbf' AUTOEXTEND ON NEXT 16M MAXSIZE 4096M;