SQL ID Information

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select * from
(select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'SQL ID..................................................: '||s.sql_id,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||''||s.process||'' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'CLIENT_IDENTIFIER...................................: '||s.client_identifier,
'LOGON_TIME..........................................: '||s.logon_time,
 'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600,
'OS USER ...............................................'||s.OSUSER,
'P.USERNAME .............................................'||p.username,
'SCHEMANAME......................................'||s.SCHEMANAME
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid))
,(select /*+ CHOOSE*/
'SQL Text...: '||sql_fulltext
from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&sid');

Query to find out modules which will be affected by a specific patch

Query to find out modules which will be affected by a specific patch.

select distinct aprb.application_short_name as "Affected Modules" from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_patch_run_bugs aprb where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and apr.patch_run_id = aprb.patch_run_id and aprb.applied_flag = 'Y' and aap.patch_name in ('1234','5678')



Query to find out patches nodewise.

select aap.patch_name, aat.name, apr.end_date,apr.SUCCESS_FLAG from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_appl_tops aat where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and aat.appl_top_id = apr.appl_top_id and aap.patch_name like '%12345%'

TKPROF

TKPROF

Obtain the trace file. The below will give the location on the database server:

select name, value from v$diag_info where name like '%Trace%';

OR

select name, value from v$parameter where name like 'user_dump_dest'

2. Convert to TKPROF

tkprof <filename.trc> <output_filename_SORT.txt> explain=apps/<password> sort='(prsela,exeela,fchela)' 

Oracle Listener (LSNRCTL)

>> Display Oracle Listener Status

$ lsnrctl status
>> Start Oracle Listener
$ lsnrctl start

>> Stop Oracle Listener
$ lsnrctl stop

>> Restart Oracle Listener
$ lsnrctl reload

>> Available Listener Commands 
$ lsnrctl help

• start - Start the Oracle listener
• stop - Stop the Oracle listener
• status - Display the current status of the Oracle listener
• services - Retrieve the listener services information
• version - Display the oracle listener version information
• reload - This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
• save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
• trace - Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
• spawn - Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
• change_password – Set the new password to the oracle listener (or) change the existing listener password.
• show - Display log files and other relevant listener information.

>> Help menu for specific listener command
$ lsnrctl help show

Grant ACL to APPS account Oracle 11g

Grant ACL to APPS account Oracle 11g
 
1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = '<SERVER NAME>' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','<SERVER NAME>');
END;
/

SQL> commit;


SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;

Below two queries should return 1:

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;

NOTE: If view dba_network_acls has no data. You will need to create ACL for the user:
SQL> show user
USER is "SYS"

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','<SERVER NAME>');
END;
/

Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','SERVER_NAME.domain.com');
END;
/

SQL> commit;

NOTE:  If things does not work, you can start over by dropping entries in dba_network_acls.

SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/

Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;