#getconf LONG_BIT
#uname -a (***i686 <processor_name> i386) is a 32 bit .
#uname -a (***i686 <processor_name> i386) is a 32 bit .
Saturday, July 30, 2011
scripts
ManjunathaReddy
+++++++++++++++++++++++++++++++++ Node Details +++++++++++++++++++++++++++++++
SELECT node_name,
support_cp,
support_forms,
support_web,
support_admin,
status
FROM apps.fnd_nodes
ORDER BY 2,3,4;
+++++++++++++++++++++++++++++++++++ Find URL +++++++++++++++++++++++++++++++
select name from v$database;
select home_url from apps.icx_parameters;
grep login $CONTEXT_FILE
+++++++++++++++++++++++++++++++++++ tkprof usage +++++++++++++++++++++++++++++++
tkprof coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc_tkprof sys=no explain=apps/b1ue2uth sort='(fchela,exeela,prsela)'
+++++++++++++++++++++++++++++++++++ FNDCPASS usage +++++++++++++++++++++++++++++++
FNDCPASS apps/g00g1e 0 Y system/dv1s4seme USER sysadmin dv1admeme
+++++++++++++++++++++++++++++++++++ zip usage +++++++++++++++++++++++++++++++
zip -rl foldername.zip folder
+++++++++++++++++++++++++++++++++++ Scp usage +++++++++++++++++++++++++++++++
scp trace_aug1.zip /tmp@dev-db3, scrp trace_aug1 bgembali@dev-db3:/tmp
+++++++++++++++++++++++++++++++++++ If you give concurrent request name it will show concurrent program details
col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;
select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';
+++++++++++++++++++++++++++++++++ Get oracle process id for a request for trace +++++++++++++++++++++++++++++++
select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;
+++++++++++++++++++++++++++++++++ Kill Sessions syntax +++++++++++++++++++++++++++++++
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='XXNTF' AND status='ACTIVE';
+++++++++++++++++++++++++++++++++ Find Which manager took care of request +++++++++++++++++
$FND_TOP/sql/@afcmcreq.sql
+++++++++++++++++++++++++++++++++ Know front end nodes from database node +++++++++++++++++
select distinct machine from gv$session where osuser='oa&sid';
+++++++++++++++++++++++++++++++++ Script Locations ++++++++++++++++++++++++++++++++++++++
/usr/tools/oracle/scripts - Script location
/usr/tools/oracle/Standard/onCall/core - Another script location for core db
/usr/tools/oracle/Standard/onCall/11i - Script location for Apps db
setdb <sid> -- For CTS and COE instances
eg: setdb CTSPRD
cd $APPL_TOP/admin/$TWO_TASK/log
+++++++++++++++++++++++++++++++++ Gather Table level stats ++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(OWNNAME=>'MRP',TABNAME=>'MRP_FORECAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
++++++++++++++++++++++++++++++++++ Screen Usage ++++++++++++++++++++++++++++++++++++++++++++
screen -S ganesh1
screen -x screen1
screen -list
++++++++++++++++++++++++++++++++++ Gather Schema level stats ++++++++++++++++++++++++++++++++
set time on;
set timing on;
select name from v$database;
exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'JTF','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'OKC','ESTIMATE_PERCENT' =>23, DEGREE=>20,CASCADE=>TRUE) ;
exit;
++++++++++++++++++++++++++++++++++ Recompile invalid objects +++++++++++++++++++++++++++++++++
exec sys.utl_recomp.recomp_parallel(32);
++++++++++++++++++++++++++++++++++ Temp File Info ++++++++++++++++++++++++++++++++++++++++++++
select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;
+++++++++++++++++++++++++++++++++++ Grants to apps schema ++++++++++++++++++++++++++++++++++++
col grantee format a20
col owner format a15
col TABLE_NAME format a15
col GRANTOR format a15
col PRIVILEGE format a10
set pagesize 100
select grantee,owner, table_name, grantor,PRIVILEGE from dba_tab_privs where TABLE_NAME='RVTAB';
/usr/tools/oracle/.refresh/password_TS1KPR.sql --- Get the pwd of the schema
grant select on AQADM.RVTAB to apps_read_only;
+++++++++++++++++++++++++++++++++++++++ start oracle script usage +++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
/usr/tools/oracle/Standard/script/start_oracle -s YCODV1
/usr/tools/oracle/Standard/script/stop_oracle -s TS3ECP -t 300 -m immediate
++++++++++++++++++++++++++++++++++++++++++++++ Concurrent mangers Log locatoin +++++++++++++++++++++++++++++++++++++++++++++
cd $FND_TOP/log -- concurrent program log
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1. @lock_holder
2. Note process id which causes lock say 1214
3. @sid
4. Give Enter value for sid: 1214
5. NOte os process id say 2803
6. select request_id,phase_code,status_code from apps.fnd_concurrent_requests where oracle_process_id=2803;
++++++++++++++++++++++++++++++++++++++++++++++++++++++
TS1OAS - tst-db5/tst-db6
R3 Internal BPEL - oa1-itst-16/oa1-itst-17 (oaoasts1)
sudo su - oaoasts1
status
stop
status
start
status
R3 Internal J2EE - oa1-itst-03/oa1-itst-04 (oaj2ets1)
R3 Internal OHS - oa1-itst-05/oa1-itst-06 (oaj2ets1/oaoasts1)
++++++++++++++++++++++To create new user for dart request+++++++++++
/usr/tools/oracle/Standard/script/Create_User.ksh
++++++++++++++++++++++ Password Reset ++++++++++++++++++++++++++++++
SQL> select name from v$database;
NAME
---------
TS1BVP
SQL> select USERNAME,PASSWORD,PROFILE from dba_users where username like 'UNI%';
USERNAME PASSWORD PROFILE
------------------------------ ------------------------------
UNIVDB F84281E3BA66D865 SOX_PROFILE
SQL> select * from dba_profiles where PROFILE='SOX_PROFILE' and RESOURCE_NAME like 'PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
PASSWORD_DONTCHANGE_FUNCTION
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> alter user UNIVDB identified by "univdb123";
User altered.
SQL> alter profile SOX_PROFILE limit password_verify_function PASSWORD_DONTCHANGE_FUNCTION;
Profile altered.
++++++++++++++++++++++++++ create soft link +++++++++++++++++++++++++++++++++
ln -s /usr/tools/oracle/Standard/script/free_vol.sh .
+++++++++++++++++++++++++++++ When Jiniatior crashes
C:\Program Files\Oracle\JInitiator 1.1.8.7\bin\symcjit_old.dll
++++++++++++++++++++++++++++++ To know whether trace enabled or not
select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Cisco%RMA%and%2Tier%Report%';
select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id in (45394,45395);
+++++++++++++++++++++++++++++++ Create Index Syntax
CREATE UNIQUE INDEX "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" ON "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG" ("PROC_ID", "QUOTE_LINE_ID") PCTFREE 10 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "XXCSSD1" PARALLEL 32;
drop index "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" force;
++++++++++++++++++++++++++++++++++++++++= oracle session trace (ora debug)++++++++++++++++++++
oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
++++++++++++++++++++++++++++++++++++++++++++++++++++
Make an entry in jserv.properties under Classpath section.
Add a new liine as "wrapper.classpath=<path>".
Bounce the Apache.
++++++++++++++++++++++++++++++++++++++++++++++++++++Concurrent requests show Inactive NoManager
Get the request id from the user,
Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.
If the Manager screen does not show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers
+++++++++++++++++++++++++++++++++++++++++++++++++++ Analyzing a table partition ++++++++++++++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)
++++++++++++++++++++++ Temp Usage ++++++++++++++++++++++++++++++.Give DB block size as input. show parameter db_block_size
SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;
++++++++++++++++++++++++++++++++++ Tablespace Info
select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
+++++++++++++++++++++++++++++++++++++== LDAP
select fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') from dual;
+++++++++++++++++++++++++++++++++++++ package locks
select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%CTS_CSS_QOT_LINES_ALL%');
select inst_id,sid,serial#,status from v$session where sid in ( select sid from v$access where object='CTS_CSS_QOT_LINES_ALL');
select inst_id,session_id from gv$locked_object where object_id=3253564
+++++++++++++++++++++++++++++++++++++ R12 URL
http://wwwin-dv2coe.cisco.com:8044/OA_HTML/dare2use.jsp
++++++++++++++++++++++++++++++++++++++++ Bulk concurrent program termination
update apps.fnd_concurrent_requests set phase_code='C',status_code='T' where concurrent_program_id= and phase_code='P' and status_code='Q'
select concurrent_program_id from apps.fnd_concurrent_programs_tl where user_concurrent_program_name like
Phase_Code :
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
Status_Code :
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
++++++++++++++++++++++++++++ find command usage
find . -size +1000000000c -exec ls -l {} \;
find ./ -size +1000000000c -exec ls -l {} \; hp unix
find . -name *.trc -mtime +2 -exec ls -ltr {} \;
find ./ -size +500000000c -exec ls -l {} \;
+++++++++++++++++++++++++++ Parallel sessions that are running
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pagesize 100
SELECT DECODE(px.qcinst_id, NULL, username, ' - '||LOWER(SUBSTR(s.program, LENGTH(s.program)-4,4) ) ) "Username",
DECODE(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave",
TO_CHAR(px.server_set) "Slave Set",
TO_CHAR(s.sid) "SID",
DECODE(px.qcinst_id, NULL, TO_CHAR(s.sid), px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
FROM v$px_session px,
v$session s
WHERE px.sid=s.sid (+)
AND px.serial#=s.serial#
ORDER BY 5, 1 DESC
/
++++++++++++++++++++++++++++++++ Disable trigger for db link
ALTER TRIGGER CISCO_DBLINKADM.DB_LINK_CHECK enable;
+++++++++++++++++++++++++++++++ checking restricted db info
SET LINESIZE 165
COLUMN "Host Name" FORMAT a20
COLUMN "Start Time" FORMAT a25
SELECT instance_number,instance_name,instance_role,host_name "Host Name",TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MM:SS') "Start Time",status,logins,database_status,thread#,archiver from gv$instance;
++++++++++++++++++++++++++++++ srs services start/stop
sudo /oracle/product/crs_home/bin/crsctl stop crs
sudo /oracle/product/crs_home/bin/crsctl start crs
++++++++++++++++++++++++++++++ To check How many Redo log swithces happened on hour
select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/
+++++++++++++++++++++++++++++ To check you own session id
select distinct sid from v$mystat
++++++++++++++++++++++++++++++ To get SID for particular sql running
SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%EXTR_BE_GEO_QUAL_HIERARCHY%';
+++++++++++++++++++++++++++++++++++ DB link info
col OWNER for a15
col DB_LINK for a45
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK like '%&DB_LINK_NAME%';
++++++++++++++++++++++++++++++++++++ To check concurrent Queue status
select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));
++++++++++++++++++++++++++++++++++++++++ How to enable write mode to form to add responsibtility
QUERY_ONLY="YES"
Goto Application
+++++++++++++++++++++++++++++++++++++++ Compile package body in bulk
select 'alter ' || owner || '.' || object_name || ' compile body;' from dba_objects where object_type='PACKAGE BODY' and owner='XXCD';
+++++++++++++++++++++++++++++++++++++++++++ Get the high rbs consumption info:-
select KTUXEUSN,KTUXESIZ,KTUXESTA,KTUXECFL from x$ktuxe where KTUXESTA='ACTIVE' and KTUXESIZ>10000 order by KTUXESIZ;
Get Session info (Enter the ktuxeusn obtained from the previous query):-
SELECT s.inst_id,s.sid, s.serial#, s.username, s.program,t.xidusn,t.used_ublk, t.used_urec FROM gv$session s, gv$transaction t WHERE s.taddr = t.addr and xidusn in(&rbsn);
+++++++++++++++++++++++++++++++++++++++++++ FND_STATS usage
exec FND_STATS.GATHER_TABLE_STATS (ownname => '&owner', tabname => '&table_name', percent => 20 ,degree => 30 , granularity => 'ALL', cascade => TRUE);
------------AFOCPS8R.sql
++++++++++++++++++++++++++++++++++++++++++++ Parallism query example
Select /*+ parallel (a,32) */ * from dba_segments a;
+++++++++++++++++++++++++++++++++++++++++++++ Tablespace usage in percentage
select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like 'OKCD1'
++++++++++++++++++++++++++++++++++++++++++++ To check form or program from sid
select FND_USER_NAME,sid,module,process,action,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') from apps.XXV$SESSION where sid=&no;
+++++++++++++++++++++++++++++++++ Node Details +++++++++++++++++++++++++++++++
SELECT node_name,
support_cp,
support_forms,
support_web,
support_admin,
status
FROM apps.fnd_nodes
ORDER BY 2,3,4;
+++++++++++++++++++++++++++++++++++ Find URL +++++++++++++++++++++++++++++++
select name from v$database;
select home_url from apps.icx_parameters;
grep login $CONTEXT_FILE
+++++++++++++++++++++++++++++++++++ tkprof usage +++++++++++++++++++++++++++++++
tkprof coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc_tkprof sys=no explain=apps/b1ue2uth sort='(fchela,exeela,prsela)'
+++++++++++++++++++++++++++++++++++ FNDCPASS usage +++++++++++++++++++++++++++++++
FNDCPASS apps/g00g1e 0 Y system/dv1s4seme USER sysadmin dv1admeme
+++++++++++++++++++++++++++++++++++ zip usage +++++++++++++++++++++++++++++++
zip -rl foldername.zip folder
+++++++++++++++++++++++++++++++++++ Scp usage +++++++++++++++++++++++++++++++
scp trace_aug1.zip /tmp@dev-db3, scrp trace_aug1 bgembali@dev-db3:/tmp
+++++++++++++++++++++++++++++++++++ If you give concurrent request name it will show concurrent program details
col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;
select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';
+++++++++++++++++++++++++++++++++ Get oracle process id for a request for trace +++++++++++++++++++++++++++++++
select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;
+++++++++++++++++++++++++++++++++ Kill Sessions syntax +++++++++++++++++++++++++++++++
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='XXNTF' AND status='ACTIVE';
+++++++++++++++++++++++++++++++++ Find Which manager took care of request +++++++++++++++++
$FND_TOP/sql/@afcmcreq.sql
+++++++++++++++++++++++++++++++++ Know front end nodes from database node +++++++++++++++++
select distinct machine from gv$session where osuser='oa&sid';
+++++++++++++++++++++++++++++++++ Script Locations ++++++++++++++++++++++++++++++++++++++
/usr/tools/oracle/scripts - Script location
/usr/tools/oracle/Standard/onCall/core - Another script location for core db
/usr/tools/oracle/Standard/onCall/11i - Script location for Apps db
setdb <sid> -- For CTS and COE instances
eg: setdb CTSPRD
cd $APPL_TOP/admin/$TWO_TASK/log
+++++++++++++++++++++++++++++++++ Gather Table level stats ++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(OWNNAME=>'MRP',TABNAME=>'MRP_FORECAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
++++++++++++++++++++++++++++++++++ Screen Usage ++++++++++++++++++++++++++++++++++++++++++++
screen -S ganesh1
screen -x screen1
screen -list
++++++++++++++++++++++++++++++++++ Gather Schema level stats ++++++++++++++++++++++++++++++++
set time on;
set timing on;
select name from v$database;
exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'JTF','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'OKC','ESTIMATE_PERCENT' =>23, DEGREE=>20,CASCADE=>TRUE) ;
exit;
++++++++++++++++++++++++++++++++++ Recompile invalid objects +++++++++++++++++++++++++++++++++
exec sys.utl_recomp.recomp_parallel(32);
++++++++++++++++++++++++++++++++++ Temp File Info ++++++++++++++++++++++++++++++++++++++++++++
select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;
+++++++++++++++++++++++++++++++++++ Grants to apps schema ++++++++++++++++++++++++++++++++++++
col grantee format a20
col owner format a15
col TABLE_NAME format a15
col GRANTOR format a15
col PRIVILEGE format a10
set pagesize 100
select grantee,owner, table_name, grantor,PRIVILEGE from dba_tab_privs where TABLE_NAME='RVTAB';
/usr/tools/oracle/.refresh/password_TS1KPR.sql --- Get the pwd of the schema
grant select on AQADM.RVTAB to apps_read_only;
+++++++++++++++++++++++++++++++++++++++ start oracle script usage +++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
/usr/tools/oracle/Standard/script/start_oracle -s YCODV1
/usr/tools/oracle/Standard/script/stop_oracle -s TS3ECP -t 300 -m immediate
++++++++++++++++++++++++++++++++++++++++++++++ Concurrent mangers Log locatoin +++++++++++++++++++++++++++++++++++++++++++++
cd $FND_TOP/log -- concurrent program log
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1. @lock_holder
2. Note process id which causes lock say 1214
3. @sid
4. Give Enter value for sid: 1214
5. NOte os process id say 2803
6. select request_id,phase_code,status_code from apps.fnd_concurrent_requests where oracle_process_id=2803;
++++++++++++++++++++++++++++++++++++++++++++++++++++++
TS1OAS - tst-db5/tst-db6
R3 Internal BPEL - oa1-itst-16/oa1-itst-17 (oaoasts1)
sudo su - oaoasts1
status
stop
status
start
status
R3 Internal J2EE - oa1-itst-03/oa1-itst-04 (oaj2ets1)
R3 Internal OHS - oa1-itst-05/oa1-itst-06 (oaj2ets1/oaoasts1)
++++++++++++++++++++++To create new user for dart request+++++++++++
/usr/tools/oracle/Standard/script/Create_User.ksh
++++++++++++++++++++++ Password Reset ++++++++++++++++++++++++++++++
SQL> select name from v$database;
NAME
---------
TS1BVP
SQL> select USERNAME,PASSWORD,PROFILE from dba_users where username like 'UNI%';
USERNAME PASSWORD PROFILE
------------------------------ ------------------------------
UNIVDB F84281E3BA66D865 SOX_PROFILE
SQL> select * from dba_profiles where PROFILE='SOX_PROFILE' and RESOURCE_NAME like 'PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
PASSWORD_DONTCHANGE_FUNCTION
SQL> alter profile default limit password_verify_function null;
Profile altered.
SQL> alter user UNIVDB identified by "univdb123";
User altered.
SQL> alter profile SOX_PROFILE limit password_verify_function PASSWORD_DONTCHANGE_FUNCTION;
Profile altered.
++++++++++++++++++++++++++ create soft link +++++++++++++++++++++++++++++++++
ln -s /usr/tools/oracle/Standard/script/free_vol.sh .
+++++++++++++++++++++++++++++ When Jiniatior crashes
C:\Program Files\Oracle\JInitiator 1.1.8.7\bin\symcjit_old.dll
++++++++++++++++++++++++++++++ To know whether trace enabled or not
select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Cisco%RMA%and%2Tier%Report%';
select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id in (45394,45395);
+++++++++++++++++++++++++++++++ Create Index Syntax
CREATE UNIQUE INDEX "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" ON "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG" ("PROC_ID", "QUOTE_LINE_ID") PCTFREE 10 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "XXCSSD1" PARALLEL 32;
drop index "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" force;
++++++++++++++++++++++++++++++++++++++++= oracle session trace (ora debug)++++++++++++++++++++
oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
++++++++++++++++++++++++++++++++++++++++++++++++++++
Make an entry in jserv.properties under Classpath section.
Add a new liine as "wrapper.classpath=<path>".
Bounce the Apache.
++++++++++++++++++++++++++++++++++++++++++++++++++++Concurrent requests show Inactive NoManager
Get the request id from the user,
Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.
If the Manager screen does not show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers
+++++++++++++++++++++++++++++++++++++++++++++++++++ Analyzing a table partition ++++++++++++++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)
++++++++++++++++++++++ Temp Usage ++++++++++++++++++++++++++++++.Give DB block size as input. show parameter db_block_size
SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;
++++++++++++++++++++++++++++++++++ Tablespace Info
select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
+++++++++++++++++++++++++++++++++++++== LDAP
select fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') from dual;
+++++++++++++++++++++++++++++++++++++ package locks
select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%CTS_CSS_QOT_LINES_ALL%');
select inst_id,sid,serial#,status from v$session where sid in ( select sid from v$access where object='CTS_CSS_QOT_LINES_ALL');
select inst_id,session_id from gv$locked_object where object_id=3253564
+++++++++++++++++++++++++++++++++++++ R12 URL
http://wwwin-dv2coe.cisco.com:8044/OA_HTML/dare2use.jsp
++++++++++++++++++++++++++++++++++++++++ Bulk concurrent program termination
update apps.fnd_concurrent_requests set phase_code='C',status_code='T' where concurrent_program_id= and phase_code='P' and status_code='Q'
select concurrent_program_id from apps.fnd_concurrent_programs_tl where user_concurrent_program_name like
Phase_Code :
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
Status_Code :
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
++++++++++++++++++++++++++++ find command usage
find . -size +1000000000c -exec ls -l {} \;
find ./ -size +1000000000c -exec ls -l {} \; hp unix
find . -name *.trc -mtime +2 -exec ls -ltr {} \;
find ./ -size +500000000c -exec ls -l {} \;
+++++++++++++++++++++++++++ Parallel sessions that are running
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pagesize 100
SELECT DECODE(px.qcinst_id, NULL, username, ' - '||LOWER(SUBSTR(s.program, LENGTH(s.program)-4,4) ) ) "Username",
DECODE(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave",
TO_CHAR(px.server_set) "Slave Set",
TO_CHAR(s.sid) "SID",
DECODE(px.qcinst_id, NULL, TO_CHAR(s.sid), px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
FROM v$px_session px,
v$session s
WHERE px.sid=s.sid (+)
AND px.serial#=s.serial#
ORDER BY 5, 1 DESC
/
++++++++++++++++++++++++++++++++ Disable trigger for db link
ALTER TRIGGER CISCO_DBLINKADM.DB_LINK_CHECK enable;
+++++++++++++++++++++++++++++++ checking restricted db info
SET LINESIZE 165
COLUMN "Host Name" FORMAT a20
COLUMN "Start Time" FORMAT a25
SELECT instance_number,instance_name,instance_role,host_name "Host Name",TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MM:SS') "Start Time",status,logins,database_status,thread#,archiver from gv$instance;
++++++++++++++++++++++++++++++ srs services start/stop
sudo /oracle/product/crs_home/bin/crsctl stop crs
sudo /oracle/product/crs_home/bin/crsctl start crs
++++++++++++++++++++++++++++++ To check How many Redo log swithces happened on hour
select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/
+++++++++++++++++++++++++++++ To check you own session id
select distinct sid from v$mystat
++++++++++++++++++++++++++++++ To get SID for particular sql running
SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%EXTR_BE_GEO_QUAL_HIERARCHY%';
+++++++++++++++++++++++++++++++++++ DB link info
col OWNER for a15
col DB_LINK for a45
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK like '%&DB_LINK_NAME%';
++++++++++++++++++++++++++++++++++++ To check concurrent Queue status
select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));
++++++++++++++++++++++++++++++++++++++++ How to enable write mode to form to add responsibtility
QUERY_ONLY="YES"
Goto Application
+++++++++++++++++++++++++++++++++++++++ Compile package body in bulk
select 'alter ' || owner || '.' || object_name || ' compile body;' from dba_objects where object_type='PACKAGE BODY' and owner='XXCD';
+++++++++++++++++++++++++++++++++++++++++++ Get the high rbs consumption info:-
select KTUXEUSN,KTUXESIZ,KTUXESTA,KTUXECFL from x$ktuxe where KTUXESTA='ACTIVE' and KTUXESIZ>10000 order by KTUXESIZ;
Get Session info (Enter the ktuxeusn obtained from the previous query):-
SELECT s.inst_id,s.sid, s.serial#, s.username, s.program,t.xidusn,t.used_ublk, t.used_urec FROM gv$session s, gv$transaction t WHERE s.taddr = t.addr and xidusn in(&rbsn);
+++++++++++++++++++++++++++++++++++++++++++ FND_STATS usage
exec FND_STATS.GATHER_TABLE_STATS (ownname => '&owner', tabname => '&table_name', percent => 20 ,degree => 30 , granularity => 'ALL', cascade => TRUE);
------------AFOCPS8R.sql
++++++++++++++++++++++++++++++++++++++++++++ Parallism query example
Select /*+ parallel (a,32) */ * from dba_segments a;
+++++++++++++++++++++++++++++++++++++++++++++ Tablespace usage in percentage
select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like 'OKCD1'
++++++++++++++++++++++++++++++++++++++++++++ To check form or program from sid
select FND_USER_NAME,sid,module,process,action,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') from apps.XXV$SESSION where sid=&no;
Subscribe to:
Posts (Atom)