The Script below gives the details of all Concurrent Programs submitted on a given date
==
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
to_char(f.actual_start_date,'DD-MON-YY HH24:MI:SS') start_on,
to_char(f.actual_completion_date,'DD-MON-YY HH24:MI:SS') end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date like '24-JUN-15%'
order by
f.actual_start_date desc;
Search This Blog
Sunday 31 January 2016
Friday 29 January 2016
Useful Linux Commands for Apps DBA's
Check Used Space on a Disk/ Mount Point
---------------------
The du command summarizes your disk usage. The sort command sorts the files by size. The command shown below sorts from smallest to largest all files in the current directory and all directories contained below the starting directory. Because the sort starts with the smallest file, the end of the list shows the largest files.
Deleting a large file is usually more efficient than deleting many small files. Often the largest files are core files or cache files, both of which you can delete.
du -h --max-depth=1
du -a | sort -k 1n,1|tail -30
du -sk * | sort -n | tail
Files more than 5mb
-------------------
find . -mount -size +50000000c -print | ls -lh
find . -size +100000000c -print
find . -size +50000000c -print | xargs ls -l
find . -size +10000000c -print
find . -size +10000 -print
Files more than 30 day old.
---------------------------
find . -name "*.aud" -mtime +7
find . -name *.req -mtime +30|xargs gzip
find . -name "TEST01_*.out" -mtime +2 |xargs ls -l
find . -name "*.log" -mtime -1|xargs ls -l
Files modified in the last 6hrs ago
------------------------------------
find . -name "*.log" -mmin 360|xargs ls -l
find . -mmin 360|xargs ls -l
find . -mtime +1|xargs ls -l
?+++++++++++++++++++++++++++++++++++++++++++++++++
find . -size +30000000c -xdev -exec ls -l {} \;
?+++++++++++++++++++++++++++++++++++++++++++++++++
---------------------
The du command summarizes your disk usage. The sort command sorts the files by size. The command shown below sorts from smallest to largest all files in the current directory and all directories contained below the starting directory. Because the sort starts with the smallest file, the end of the list shows the largest files.
Deleting a large file is usually more efficient than deleting many small files. Often the largest files are core files or cache files, both of which you can delete.
du -h --max-depth=1
du -a | sort -k 1n,1|tail -30
du -sk * | sort -n | tail
Files more than 5mb
-------------------
find . -mount -size +50000000c -print | ls -lh
find . -size +100000000c -print
find . -size +50000000c -print | xargs ls -l
find . -size +10000000c -print
find . -size +10000 -print
Files more than 30 day old.
---------------------------
find . -name "*.aud" -mtime +7
find . -name *.req -mtime +30|xargs gzip
find . -name "TEST01_*.out" -mtime +2 |xargs ls -l
find . -name "*.log" -mtime -1|xargs ls -l
Files modified in the last 6hrs ago
------------------------------------
find . -name "*.log" -mmin 360|xargs ls -l
find . -mmin 360|xargs ls -l
find . -mtime +1|xargs ls -l
?+++++++++++++++++++++++++++++++++++++++++++++++++
find . -size +30000000c -xdev -exec ls -l {} \;
?+++++++++++++++++++++++++++++++++++++++++++++++++
Database Links in Oracle
A database link is a schema object in one database that enables you to access objects on another database.
SQL> desc dba_db_links
Nombre ¨Nulo? Tipo
------------------------------------------------------------------------------------------ -------- -------------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(30)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE
Use the below query to find the db links available for a database:
set lines 152
col owner for a15
col db_link for a30
col username for a15
col host for a30
select owner,db_link,username,host,CREATED
from dba_db_links;
spool db_link_test.sql
select 'select count(*) from tab@'||db_link||';'
from dba_db_links;
spool off
spool db_link_output.log
@db_link_test.sql
spool off
=================
How to Test if a DB link is working fine?
select ' connect '||owner||'/'||owner||'1'||chr(10)||
'select count(*) from tab@'||db_link||';'
from dba_db_links;
select distinct owner
from dba_db_links;
SQL> desc dba_db_links
Nombre ¨Nulo? Tipo
------------------------------------------------------------------------------------------ -------- -------------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(30)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE
Use the below query to find the db links available for a database:
col owner for a15
col db_link for a30
col username for a15
col host for a30
select owner,db_link,username,host,CREATED
from dba_db_links;
spool db_link_test.sql
select 'select count(*) from tab@'||db_link||';'
from dba_db_links;
spool off
spool db_link_output.log
@db_link_test.sql
spool off
=================
How to Test if a DB link is working fine?
select ' connect '||owner||'/'||owner||'1'||chr(10)||
'select count(*) from tab@'||db_link||';'
from dba_db_links;
select distinct owner
from dba_db_links;
Monday 18 January 2016
R12.2 Filesystem Layout
- File System 1 (FS1)--Stores a complete copy of all Applications and Middle Tier code
- File System 2 (FS2)--Stores a complete copy of all Applications and Middle Tier code
- File System Non-Editioned (FS_ NE)--Stores data that is written or read from the file system,Report Outputs,Log Files etc.
How to Put Concurrent Requests in Hold Status?
During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.
1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
4) Commit;
To Release hold on Concurrent Requests patching, run the below sql :
5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);
6)Commit the changes
commit;
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.
1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
4) Commit;
To Release hold on Concurrent Requests patching, run the below sql :
5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);
6)Commit the changes
commit;
Sunday 17 January 2016
ONE NODE RAC Features in Oracle Database 11g Edition
•Available
from 11gr2, Express Edition.
•High
availability feature.
•Provides
Cold failover Solution.
•Automates
instance relocation incase of instance failure or fault in the first node.
•Facilitates
rolling upgrade for single instance database.
•Live
migration of instances across servers.
•Uses omotion
utility to migrate the instance.
Query to Find Free Space in a Tablespace
undefine tbsp
set lines 152
set echo off
col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)
/
set lines 152
set echo off
col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)
/
Subscribe to:
Posts (Atom)