Full Database Size:
SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ‘ GB’
“Database Size”,ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)ROUND (free.p / 1024 / 1024 / 1024)|| ‘ GB’ “Used space”,
ROUND (free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
FROM (SELECT bytes FROM v$datafile UNION ALL
SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;
Tablespace Status
SELECT df.tablespace_name TABLESPACE,
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb – fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;
long running concurrent :
SELECT a.request_id
,a.oracle_process_id “SPID”
,frt.responsibility_name
,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)1440 mins ,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || ” = ‘C’) avg_mins
,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = ‘R’
AND a.status_code = ‘R’
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;
concurrent history for daily base :
SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),’DD-MON-YY DY’) STARTDATE,
COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE – ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,
ROUND(AVG(ACTUAL_COMPLETION_DATE – ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,
ROUND(SUM(ACTUAL_START_DATE – REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,
ROUND(AVG(ACTUAL_START_DATE – REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS
FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.FND_CONCURRENT_REQUESTS R
WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
AND R.STATUS_CODE IN (‘C’,’G’)
AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)
AND TO_CHAR(TRUNC(ACTUAL_START_DATE),’DD-MON-YY DY’) IS NOT NULL
GROUP BY TRUNC(ACTUAL_START_DATE)
ORDER BY TRUNC(ACTUAL_START_DATE) ASC;
Workflow mailer working :
SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = ‘WF_MAILER’;
Invalid objects :
select owner,object_name,object_type,created from dba_objects where status<>’VALID’;
Active User :
SELECT fu.user_id, fu.user_name, fu.employee_id,fu.email_address,fu.description,
fu.CREATION_DATE,
fu.CREATED_BY,fu.LAST_UPDATE_LOGIN
FROM apps.fnd_user fu, apps.per_all_people_f papf
WHERE papf.person_id = fu.employee_id
AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
AND SYSDATE BETWEEN papf.effective_start_date
AND NVL (papf.effective_end_date, SYSDATE ) ORDER BY 2;
Verify the MRP process :
select process,status,sequence# from v$managed_standby;
select max(sequence#) from v$archived_log where applied=’YES’;
select sequence#, applied, to_char(first_time, ‘mm/dd/yy hh24:mi:ss’) first from v$archived_log order by first_time;
select sequence#, applied, to_char(first_time, ‘mm/dd/yy hh24:mi:ss’) first from v$archived_log where applied=’YES’ order by first_time;
To last archive log sequence Applied by standby:
select thread#, max(sequence#) “Last Standby Seq Applied”
from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in (‘YES’,’IN-MEMORY’)
group by thread# order by 1;
To last archive log sequence received by standby:
select thread#, max(sequence#) “Last Standby Seq Received”
from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
log sequence gap in standby:
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
(ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))
ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Instance Startup time:
select to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup time” from v$instance;