Delete archive logs of all instances running on the machine

one line command to delete archive log older than  10 days for all instance currently running on the machine.

This is useful on machines that run non-production instance and there is no specific naming convention for the SID's. 

ps -ef | grep ora_pmon | grep -v grep | awk '{print $8}' | awk '{gsub(/ora_pmon_/,"export ORACLE_SID=");}1' | grep -v awk | awk '{print $0,"; echo \"delete noprompt archivelog until time \x27 sysdate - 7 \x27 ;\" | rman target /;";} > del_arch.sh ; sh del_arch.sh




Regards
Manoj

Move Datafiles on standby server without moving Primary

Move Datafiles on standby server without moving Primary
Include below parameter in standby parameter file
DB_FILE_NAME_CONVERT = '/primary_location/xyz.dbf','/standby_location/xyz.dbf'
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
shut immediate
startup nomount pfile=initSCSL.ora
alter database mount standby database ;
alter system set standby_file_management='MANUAL' SCOPE=MEMORY ;
! cp /primary_location/xyz.dbf'  /standby_location/xyz.dbf
alter database rename file  '/primary_location/xyz.dbf' to '/standby_location/xyz.dbf';
alter system set standby_file_management='AUTO' SCOPE=MEMORY ;
alter database recover managed standby database parallel 4 disconnect from session;

OR
-concept not tested--do a test on non-produciton-
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
mv  /primary_location/xyz.dbf  /standby_location/xyz.dbf
create a soft link ln -s /standby_location/xyz.dbf /primary_location/xyz.dbf
startup nomount pfile=initSCSL.ora
alter database mount standby database ;
alter database recover managed standby database parallel 4 disconnect from session;

Regards
Manoj

XXX Is not a valid responsibility for the current user

There are multiple fixes for the error, some are listed below

This error is displayed because of the caching framework of apache, workflow components in oracle apps are suppose to sync the cache. When workflow events fail to trigger/function due to some XYZ reason, this issue occurs. Make sure workflow components are working optimally for permanent fix for temporary fix clear cache.

1. Clear cache

Go to > Functional Administrator responsibility

Click on Core services tab (the tab at the top right)

Click on Caching Framework Tab (blue tab under main tab)

Click on Global configuration

Click on Clear All Cache

A warning message related to performance will come , say yes

2. Bounce Apache by clearing cache

 

 

Regards

Manoj

Datafile high water mark
How lower can you resize datafiles ?
define blksize=8192 –set  your db block size

select /*+rule*/ 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&blksize)/1024/1024)||'m;',
ceil( (nvl(hwm,1)*&blksize)/1024/1024 ) smallest,
ceil( blocks*&blksize/1024/1024) currsize,
ceil( blocks*&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&blksize)/1024/1024 ) savings
from
   dba_data_files a,
   (select file_id, max(block_id+blocks-1) hwm
   from
      dba_extents
   group by file_id ) b
where a.file_id = b.file_id(+);


source: dba-oracle.com

Regards
Manoj

Check India Localization patchset level
How to check the current India Localization Patchset Level installed on an Instance

India Localization Patchset is IN60107(Patch Number = 5498551)
If the Patch Number 5498551 is applied, your current India Localization patchset is IN60107.
This can be verified by running the below select
select * from JAI_APPLIED_PATCHES where Patch_number = 5498551;

If the Patch Number 5498551 is not applied, the India Localization Patchset should be IN60106 or below.
This can be checked by running the below statement:-
select * from JA_IN_INSTALL_CHECK_INFO where name like '601%';


source: Metalink ID: 752704.1

Regards
Manoj

select Query to view long running sql's

select Query to view long running sql's

set linesize 160
set pagesize 100

col machine for a15

select
machine,
ELAPSED_SECONDS,
TIME_REMAINING,
100 - (TIME_REMAINING*100/ELAPSED_SECONDS),
OPNAME  ,
b.TARGET,
b.sid
from
v$session a,
v$session_longops b
where a.sid=b.sid and
time_remaining != 0 ;

Compiling forms in Oracle apps (e-business suite) 11i

Compiling forms in Oracle apps (e-business suite) 11i

All module fmb are located in $AU_TOP
-Change directory to $AU_TOP/forms/US
Take backup of existing fmx

f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=<required_module_top>/forms/US/<formname>.fmx

Regards

Manoj

Given a username, gives his roles and privileges

Query to return roles and privileges in tabular format. Usefull for auditing.

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role

Allocated Tablespace quota for each oracle database user

select
username,
tablespace_name,
trunc(bytes/1024/1024,0) MB,
decode(max_bytes, '-1', 'UNLIMITED',MAX_BYTES/1024/1024) "QUOTA (MB)"
from
dba_ts_quotas

List of Database users about to expire in 'n' days.

select * from dba_users where expiry_date  > sysdate - 10 and expiry_date < sysdate + 10

Currently Logged in Forms users

SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v
ORDER BY 1


Sessions opened by each user

select user_name, count(1) "Sessions Opened"
from apps.fnd_form_sessions_v
group by user_name order by count(1)desc