What queries are running in the database

What queries are running in the database

What are the queries that are running ?

select sesion.sid,
 sesion.username,
 optimizer_mode,
 hash_value,
 address,
 cpu_time,
 elapsed_time,
 sql_text
 from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
 and sesion.sql_address = sqlarea.address
 and sesion.username is not null;

Get the rows fetched, if there is difference it means processing is happening ?

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%';

Get the sql_hash_value ?

select sql_hash_value from v$session where sid='&sid';
SQL> select sql_hash_value from v$session where sid='&sid';
Enter value for sid: 1075
old 1: select sql_hash_value from v$session where sid='&sid'
new 1: select sql_hash_value from v$session where sid='1075'
SQL_HASH_VALUE
--------------
 928832585

Get the sql_Text ?

SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;
Enter value for enter_hash_value: 928832585

Get the explain_plan ?

set lines 190
col XMS_PLAN_STEP format a40
set pages 100
select
 case when access_predicates is not null then 'A' else ' ' end ||
 case when filter_predicates is not null then 'F' else ' ' end xms_pred,
 id xms_id,
 lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
 object_name xms_object_name,
 cost xms_opt_cost,
 cardinality xms_opt_card,
 bytes xms_opt_bytes,
 optimizer xms_optimizer
from
 v$sql_plan
where
 hash_value in (&SQL_HASH_VALUE)
 and to_char(child_number) like '%';

Based the cost u can decide what to be done.
One of the solutions is to analyse the statistics

How to Perform AOLJ Test

How to Perform AOLJ Test

AOLJ test can be executed when we need to determine the if the webserver is configure properly or not. It also verifies DBC file.

You need to pass few parameter as below to perform the test.

1.Apps Schema Name
2.Apps Schema Password
3.Oracle SID - Database Oracle SID
4.HostName - Database hostname
5.PortNo - Database Port No

Syntax:
<host_name>:<port_number>/OA_HTML/jsp/fnd/aoljtest.jsp

If https is configured then you need to use https instead of http.
<host_name>:<port_number>/OA_HTML/jsp/fnd/aoljtest.jsp

Example:
testhost.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp

For 12.2.4+, or 12.2.x with R12.AD.C.Delta.5 and R12.TXK.C.Delta.5 Release Update Packs applied
For these releases, Direct access to Forms has been disabled by default for security reasons.

The following patch needs to be applied to enable direct access to Forms again:
Patch 19503289 : FORMS DIRECT CONNECT NO LONGER WORKS

Note: These URLs are to be used strictly for diagnostic purposes only, when advised by Oracle Support, and should not be used as an alternative Login mechanism which is not supported.

For 12.1.x, 12.2.2, 12.2.3
One can use the following URL to access Forms directly in R12:

When using Forms Servlet Mode:
http://<host>.<domain>:<port>/forms/frmservlet

When using Forms Socket Mode:
http://<host>.<domain>:<port>/OA_HTML/frmservlet

Validating Guest user password

Validating Guest user password

Steps to validate your Guest user password.

1. Check Value in DBC File
grep -i GUEST_USER_PWD $FND_SECURE/hostname_SID.dbc
GUEST_USER_PWD=GUEST/ORACLE

2. Check profile option value
sqlplus apps/passwd
SQL> select fnd_profile.value(’GUEST_USER_PWD’) from dual;
FND_PROFILE.VALUE(’GUEST_USER_PWD’)
——————————————————————————–
GUEST/ORACLE

Value for step 1 and 2 must be sync.

3. Guest user connectivity check
sqlplus apps/passwd
SQL> select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
——————————————————————————–-----
Y
Above is the value, then everything is perfect.

Profiles In Oracle

Profiles In Oracle

Profiles are used for restricting access to Oracle Database. Default Profile of Oracle Database is "DEFAULT". We can also create our own profile with our own user defined restrictions.

In order to use Profiles limits on a Oracle User, Parameter resource_limit must be set to true.
Once we assign a profile to user then that user cannot exceeds limits defined in that profile.

There are two type of parameters in Profile :-

1. Resource Parameters :-  Parameters related to sessions, CPU, connect_time, idle_time, Logical_reads , private_sga comes under this category.

a. Session_per_user :- Specify number of concurrent sessions for a user.
b. Cpu_per_session :- Specify CPU time limit for a session, expressed in hundredth of second.
c. Cpu_per_call       :- Specify CPU time limit for a call,  expressed in hundredth of second.
d. Connect_time      :- Specify the time limit for a session, expressed in minutes.
e. Logical_reads_per_session :- Specify number of data blocks reads in a session.
f.  Logical_reads_per_cal       :- Specify number of data blocks read for a call to process a sql statement.
g. Private_SGA       :-  Specify the amount of private space a session can have.

2. Password Parameters :- Parameters sets length of time are defined in number of days, however we can specify minutes(n/1440) or seconds (n/86400) also.

a. Failed_login_attempts :- Specify number of failed attempts before a account is locked.
b. Password_life_time    :-  Specify the life time of a password in number of days.
c. Password_lock_time  :-  Specify number of days account will be locked after failed login attempts.
d. Password_grace_time :- Specify the grace period given to a user after it exceeds failed login attempts, if in grace time user has not changed its password, it will be lock.
e. Password_verify_function :- its a PL/SQL function which checks that complexity of a password.
f. Password_reuse_time and Password_reuse_max :- both are used in conjunction, three values can be possible for both of them :-
1. Both can be set as integer. For eg Password_reuse_time is 50 and Password_reuse_max is 5. In this case user can reuse old password after 50 days and after changing 5 times.
2. One is set to integer and another to unlimited, In this case user cannot reuse a password.
3. Both set to unlimited then database ignores both of them.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=TRUE;

System altered.

Profile creation :-

CREATE PROFILE MQM_PROFILE LIMIT
SESSIONS_PER_USER unlimited
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME unlimited
IDLE_TIME unlimited
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
COMPOSITE_LIMIT DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS unlimited
PASSWORD_LIFE_TIME unlimited
PASSWORD_REUSE_TIME unlimited
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION NULL;

A resource in a profile can have three different values :-

1. UNLIMITED :- when a resource has value as UNLIMITED , then user can use unlimited amount of this resource.
2. DEFAULT :- If value of a resource is DEFAULT, then that resource is assigned value as it has in DEFAULT profile
3. Number (1,2,3) :- If a value is assigned to a resource, then that resource cannot exceeds that value.

How to Alter a Profile :-

alter Profile DBA LIMIT <profile_item_name> <value> ;
eg:- Alter Profile MQM_profile LIMIT SESSIONS_PER_USER  10;

How to assign a Profile to a user :-

a. Assigning a profile along with user creation :-
Create user MQM identified by MQM profile MQM_profile;

b. Assigning a profile after user creation :-
Alter user MQM profile MQM_profile;

How to purge/flush a single SQL PLAN from shared pool in Oracle

How to purge/flush a single SQL PLAN from shared pool in Oracle

Purging a SQL PLAN from shared pool is not a frequent activity , we generally do it when a query is constantly picking up the bad plan and we want the sql to go for a hard parse next time it runs in database.

Obviously we can pass a hint in the query to force it for a Hard Parse but that will require a change in query , indirectly change in the application code , which is generally not possible in a business critical application.

We can flush the entire shared pool but that will invalidate all the sql plans available in the database and all sql queries will go for a hard parse. Flushing shared pool can have adverse affect on your database performance.

Flush the entire shared pool :-

Alter system flush shared_pool;

Flushing a single SQL plan from database will require certain details for that sql statement like address of the handle and hash value of the cursor holding the SQL plan.

Steps to Flush/purge a particular sql plan from Shared pool :-

SQL>  select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like 'cv6zspbpkzzka';

ADDRESS   HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

Now we have the address of the handle and hash value of the cursor holding the sql. Flush this from shared pool.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL>  select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'cv6zspbpkzzka';

no rows selected

SQL plan flushed for above particlar sql, Now next time above sql/query will go for a hard parse in database.

Creating a table and inserting 1 hundred thousand record

Creating a table and inserting 1 hundred thousand record

create table mqm1 (id varchar2(20));

begin
for i in 1..100000 loop
insert into mqm1 values(i);
end loop;
commit;
end;
/