Scripts

Oracle Apps R12 : How to find the list of responsibilities assigned to user(s)

Posted on December 17, 2008. Filed under: DBA/Admin Tips, R12, Scripts | Tags: , , |

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = ‘US’
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;

— ADD CONDITION ON START_DATE FOR RESPONSIBILITIES ADDED AFTER A SPECIFIC DATE LIKE “WHERE FURGA.START_DATE > TO_DATE(’22-JUL-2008′)”

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 1 so far )

SQL Query to view sql for a OS Pid / process

Posted on August 29, 2008. Filed under: DBA/Admin Tips, Scripts | Tags: , , , , |

Select a.sid,a.serial#,b.pid,c.sql_text,a.module,
to_number(b.spid) PROCESSID,
substr(a.username, 1, 12) username
From v$session a,
v$process b,v$sql c
Where a.paddr = b.addr
and a.sql_address = c.address
and b.spid = ‘&spid’
/

Read Full Post | Make a Comment ( 1 so far )

SQL Query to view sql of a session

Posted on August 25, 2008. Filed under: DBA/Admin Tips, Diagnostics, R12, RAC, Scripts, TroubleShooting | Tags: , , , , |

SELECT
T.sql_text
FROM
v$session S,
v$sqltext_with_newlines T
WHERE
S.sid = &sid
AND S.serial# = &serial
AND S.sql_address = T.address
AND S.sql_hash_value = T.hash_value
ORDER BY T.piece
/

Replace v$session with gv$session if the database is running on RAC configuration.

cheers,
Ram.

Read Full Post | Make a Comment ( 1 so far )

SQL Query to list running concurrent requests

Posted on August 25, 2008. Filed under: DBA/Admin Tips, R12, RAC, Scripts, TroubleShooting | Tags: , , , |

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,’DD-MON-YYYY HH24:MI:SS’) StartDate,
request_id RequestId,
(sysdate – actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = ‘US’
and fcr.phase_code = ‘R’
and fcr.status_code = ‘R’
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC
/

Replace v$session with gv$session if the database is RAC.

Click here to extract the sql being executed by a concurrent program

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 2 so far )

Database Replace Function to change profile option values

Posted on July 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, Discoverer, R12, Scripts, TroubleShooting | Tags: , , |

Heres an example of using the replace() to update profile_option_value column in fnd_profile_option_values table.

Scenario: Imagine you did a clone from DEV to TEST and in DEV there were lot of profile option values set at the responsibility and user level for the discoverer viewer url which came as is to TEST. Now you want to change the values in fnd_profile_option_values to refer to TEST.

DEV Discoverer Viewer URL: https://discodev.mydomain.com/discoverer/viewer?Connect=%5BAPPS_
SECURE]

TEST Discoverer VIewer URL: https://discotest.mydomain.com/discoverer/viewer?Connect=%5BAPPS_SECURE%5D

SQL to change the values

1. create table fnd_povbk as select * from fnd_profile_option_values; [  backup just in case you mess up ]

update fnd_profile_option_values set profile_option_value = replace(profile_option_value,’https://discodev.mydomain.com’,’https://discotest.mydomain.com’) where profile_option_value like ‘https://discodev.mydomain.com%’;

TADA !!! values replaced.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

Ad-hoc Tips (HIT and MISS Ratios)

Posted on July 17, 2008. Filed under: Scripts |

Got few useful scripts to check HIT and MISS ratios… Wanted to share the same. Check out below scripts.

prompt
prompt =========================
prompt DATA DICTIONARY HIT RATIO
prompt =========================
prompt Should be higher than 90 else increase shared_pool_size in init.ora prompt

column “Data Dict. Gets” format 999,999,999
column “Data Dict. cache misses” format 999,999,999
select sum(gets) “Data Dict. Gets”,
sum(getmisses) “Data Dict. cache misses”,
trunc((1-(sum(getmisses)/sum(gets)))*100) “DATA DICT CACHE HIT RATIO”
from v$rowcache;

prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column “LIBRARY CACHE MISS RATIO” format 99.9999
column “executions” format 999,999,999
column “Cache misses while executing” format 999,999,999
select sum(pins) “executions”, sum(reloads) “Cache misses while executing”,
(((sum(reloads)/sum(pins)))) “LIBRARY CACHE MISS RATIO”
from v$librarycache;

prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 . . .
prompt

column “reloads” format 999,999,999
select namespace, trunc(gethitratio * 100) “Hit ratio”,
trunc(pinhitratio * 100) “pin hit ratio”, reloads “reloads”
from v$librarycache;

Thanks,

OraclePitStop Team

Read Full Post | Make a Comment ( None so far )

Shell Script to verify and start database, listener and wallet

Posted on July 11, 2008. Filed under: DBA/Admin Tips, RAC, Scripts, TDE | Tags: , |

If Wallet is being used on your RAC or Single Node environment, opening the wallet after starting the database is very critical, failing which users trying to access encrypted data will get ORA-28365: wallet is not open error.

Things to remember while maintaining a wallet on a RAC and Single Node environment

1. The wallet password is case sensitive

2. Wallet should be opened on both the instances. Click here for more information on this.

We had the situation of not opening the wallet after starting the database which resulted in lot of access issues. To overcome this i wrote this script to verify and start the listeners, databases (Single Node and RAC) and Wallet.

Heres what the script does:

1. If opted for RAC option, the script will gather the rac information (nodes), cluster status – if down exit, listener status – if down, start, database/instance status – if down start and finaly wallet – if not open, open it.

2. If option for Single Node option, the script will perform all the steps as mentioned in Option 1 except for nodes and cluster.

You can also use this script to open just the wallets.

Heres a sample of the help message:

Usage : startdbwall.sh [-h|-t|-o] [R|S|W|RW] [-p] <walletpwd>

Example :
1. To Display this help message
startdbwall.sh -h

2. To Start RAC DB and to Open Wallet
startdbwall.sh -t R

3. To Start Single Node DB and to Open Wallet
startdbwall.sh -t S

4. To Open just the Wallet in single DB mode
startdbwall.sh -o W

5. To Open just the Wallet in RAC DB mode
startdbwall.sh -o RW

6. To Open just the Wallet with password (CASE SENSITIVE)
startdbwall.sh -o W -p <walletpwd>

Download script here. startdbwallsh

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 1 so far )

Script to list profile option values for a specific user

Posted on June 26, 2008. Filed under: DBA/Admin Tips, R12, Scripts | Tags: , |

col profile_option_name for a35
col profile_option_value for a30
col profile_option_id for 9999

set lines 500

select
 p.profile_option_name,
 substr(decode(v.level_id,10001,’Site’,10002,’Application’,
 10003,’Repsonsibiltiy’,10004,’User’,10005,’Server’,10006,’Organisation’),1,15) profile_level,
 substr(fu.user_name,1,12) user_name,
 v.level_value,
 v.profile_option_value
from
 fnd_profile_options p,
 fnd_profile_option_values v,
 fnd_user fu
where
 p.profile_option_id = v.profile_option_id
and  v.level_value = fu.user_id
and fu.user_name like upper(‘&USER_NAME’)
/

Read Full Post | Make a Comment ( None so far )

SQL Script to generate privileges to be granted

Posted on May 29, 2008. Filed under: DBA/Admin Tips, Scripts, User | Tags: , , , |

Imagine you want to grant same exact privileges of existing user A to User B.

Login as a user who has a dba privilege and run below command to generate the list of privileges to be granted.

select
‘grant ‘ || privilege || ‘ on ‘ || owner || ‘.’ || table_name || ‘ to USER B;’
from
dba_tab_privs
where
grantee = upper(‘&GRANTEE_NAME’);

GRANTEE_NAME is USER A.

You can also check this link (https://oraclepitstop.wordpress.com/2008/05/29/sql-script-to-list-oject-privileges-granted-to-a-particular-user/) to list all the privileges granted to a particular user.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

SQL Script to list object privileges granted to a particular user

Posted on May 29, 2008. Filed under: DBA/Admin Tips, Scripts, User | Tags: , , , |

Login to database as sys / system / or any user who had dba privileges and run below command

select grantor || ‘ granted ‘ || privilege || ‘ on ‘ || table_name || ‘ owned by ‘|| owner || ‘ to ‘|| grantee
from dba_tab_privs where grantee like upper(‘&GRANTEE_NAME’) order by privilege;

where GRANTEE_NAME is the user you want to find out the privileges.

Even though the view name is dba_tab_privs, it lists privileges on all objects including tables, views, procedures, etc.,

Sample Output

APPS granted DELETE on FND_USER_RESP_GROUPS owned by APPS to XXXXXX
APPLSYS granted DELETE on FND_USER owned by APPLSYS to XXXXXX;

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

« Previous Entries

Liked it here?
Why not try sites on the blogroll...