How to Get Detail of Locks with Object Locked

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

Advertisements

Regenerating Forms, Libraries, and Menus by using f60gen

Some times it is necessary to regenerate forms, libraries, or menus to fix issues with them, to synchronize the generated object with the underlying database object, or after applying patch. ADADMIN can be used for same

The f60gen utility can be used to manually regenerate forms, libraries, and menus.

The syntax for the f60gen command is as

f60gen module=<source form name> userid=APPS/<APPS password> output_file=<executable form name> module_type=form batch=yes compile_all=special

For Instance I want to generate sale order forms in ONT schema using f60gen syntax would be like

OEXOEORD.fmb form:

$cd $AU_TOP/forms/US

$f60gen module= OEXOEORD.fmb module_type=form \

output_file=$ONT_TOP/forms/US/OEXOEORD.fmx userid=APPS/APPS module_type=form batch=yes compile_all=special

Note:- Before generating the form, menu, or library, locate the source and generated files using the Linux command or by using WINSCP.

How to Install STATSPACK

To install STATSPACK follow the steps below:

1. Create PERFSTAT Tablespace by using the below command:
I. Logon to SQLPLUS by Sys User

II. Pass the following command

III. SQL> CREATE TABLESPACE statspack DATAFILE ‘c: \oracle\datafile\statspack.dbf’ SIZE 400M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT AUTO PERMANENT ONLINE;

2. Run catdbsyn.sql and dbmspool.sql as SYS from SQLPLUS

I. $ sqlplus “/ as sysdba”
II. SQL> @?/rdbms/admin/catdbsyn.sql
III. SQL> @?/rdbms/admin/dbmspool.sql

3. Run the create script
I. $ sqlplus “/ as sysdba”
II. SQL> @?/rdbms/admin/spcreate

You can now start using Oracle STATSPACK.

The explain plan command

The explain plan command

For select, update, delete, and insert statements, the explain plan command generates and stores information about the execution plan chosen by the query optimizer in a table called PLAN TABLE. This table is created when you run the SQL script $ORACLE HOME/rdbms/admin/utlxplan.sql in SQL*Plus. The PLAN TABLE then can be queried using a select statement.

The syntax of the explain plan command is as follows:

explain plan set statement id = ’’ for ;

Example: We want to generate the execution plan for the query “List the name, job, salary and department of all employees whose

salary is not within the job’s salary range.”

explain plan set statement id = ’MYPLAN’ for select ENAME, JOB, SAL, DNAME from EMP, DEPT where EMP.DEPTNO = DEPT.DEPTNO and not exists (select _ from SALGRADE where EMP.SAL between LOSAL and HISAL);

Note that every execution plan gets a statement id, which is just some string. The rest is just a “normal” select statement.

The above explain plan statement causes the query optimizer to insert data about the execution plan for the query into PLAN TABLE. The following query can be used to view to content of the plan table in an indented format, which reflects the tree structure of the plan (the right-most entries correspond to the leafs of the query tree).

Note that before creating a new plan with the same id, you have to delete all entries from PLAN TABLE (delete from PLAN TABLE;)

select substr(lpad(’ ’,2*(level-1)),1,8)|| substr(operation,1,18) “OPERAsubstr(options,1,12) “OPTIONS”, substr(object_name,1,16) object_name, id, parent_id, cost, cardinality, bytes, filter_predicates

from plan_table

start with id=0 and statement_id = ’MYPLAN’

connect by prior id = parent_id and statement_id = ’MYPLAN’;