Patch release history in oracle apps.

SELECT (bug_number),
decode((bug_number),
'4440000','Release 12.0.0 - provides R12 baseline code for PRC_PF',
'5082400','Release 12.0.1',
'5484000','Release 12.0.2 - provides R12.PRC_PF.A.delta.2',
'6141000','Release 12.0.3 - provides R12.PRC_PF.A.delta.3',
'6435000','Release 12.0.4 - provides R12.PRC_PF.A.delta.4',
'6728000','Release 12.0.6 - provides R12.PRC_PF.A.delta.6',
'7015582','Procurement Release 12.0 Rollup Patch 5',
'7218243','Procurement R12.0 Update July 2008',
'7291462','Procurement R12.0 Update August 2008',
'7355145','Procurement R12.0 Update Sept 2008',
'7433336','Procurement R12.0 Update Oct 2008',
'7505241','Procurement R12.0 Update Nov 2008',
'7600636','Procurement R12.0 Update Dec 2008',
'7691702','Procurement R12.0 Update Jan 2009',
'8298073','Procurement R12.0 Update Mar 2009',
'Other') "Description"
FROM ad_bugs
WHERE bug_number IN
('4440000','5082400','5484000','6141000','6435000',
'6728000','7015582','7218243','7291462','7355145',
'7433336','7505241','7600636','7691702','8298073')
order by 2

Query to find currently running sql in database

select s.sid,
s.status,
s.process,
s.osuser,
a.sql_text,
p.program
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 s.schemaname=upper('&1')
and s.status='ACTIVE'


This query will find the currently running sqls in the database.

v$database

SQL> select * from v$database;

DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION SWITCHOVER_STATUS DATAGUARD_BROKER GUARD_STATUS SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI FORCE_LOGGING PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAILOVER_OBSERVER_PRESENT FS_FAILOVER_OBSERVER_HOST
---- --------- ----------- ----------------- -------------- ----------------------- -------------------- ------------ ------------------ --------------- ---------------- ------------------- --------------------- ------------------- ---------------- -------------- ------------ ---------- -------------------- -------------------- -------------- ----------- ----------- ---------------- ------------------ ---------------------- -------------------- ---------------- ------------ ------------------------- ------------------------ ------------------------ ------------- ----------- -------------------------------------------------------------------------------- ---------------------------- ---------------------- ----------- ------------------ ------------------------ ------------------------- ------------------------------ -------------------------- --------------------- ------------------------------ --------------------- ---------------------------- --------------------------------------------------------------------------------
1663 TEST 6/29/2009 1 303084659 6/29/2009 10:5 303080709 6/29/2009 10:48:10 A NOARCHIVELOG 324317325 323554083 CURRENT 6/29/2009 10:50:00 109123 324551350 7/24/2009 9:31:4 NOT ALLOWED 6/29/2009 10 READ WRITE MAXIMUM PERFORMANCE UNPROTECTED ENABLED 1663464279 1663464279 PRIMARY 303084659 DISABLED SESSIONS ACTIVE DISABLED NONE NO NO NO NO 4 HP-UX IA (64-bit) 2 2 324582596 NO NO NO TEST 0 DISABLED 0


Describe V$database


SQL> describe v$database
Name Type Nullable Default Comments
---------------------------- ------------- -------- ------- --------
DBID NUMBER Y
NAME VARCHAR2(9) Y
CREATED DATE Y
RESETLOGS_CHANGE# NUMBER Y
RESETLOGS_TIME DATE Y
PRIOR_RESETLOGS_CHANGE# NUMBER Y
PRIOR_RESETLOGS_TIME DATE Y
LOG_MODE VARCHAR2(12) Y
CHECKPOINT_CHANGE# NUMBER Y
ARCHIVE_CHANGE# NUMBER Y
CONTROLFILE_TYPE VARCHAR2(7) Y
CONTROLFILE_CREATED DATE Y
CONTROLFILE_SEQUENCE# NUMBER Y
CONTROLFILE_CHANGE# NUMBER Y
CONTROLFILE_TIME DATE Y
OPEN_RESETLOGS VARCHAR2(11) Y
VERSION_TIME DATE Y
OPEN_MODE VARCHAR2(10) Y
PROTECTION_MODE VARCHAR2(20) Y
PROTECTION_LEVEL VARCHAR2(20) Y
REMOTE_ARCHIVE VARCHAR2(8) Y
ACTIVATION# NUMBER Y
SWITCHOVER# NUMBER Y
DATABASE_ROLE VARCHAR2(16) Y
ARCHIVELOG_CHANGE# NUMBER Y
ARCHIVELOG_COMPRESSION VARCHAR2(8) Y
SWITCHOVER_STATUS VARCHAR2(20) Y
DATAGUARD_BROKER VARCHAR2(8) Y
GUARD_STATUS VARCHAR2(7) Y
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) Y
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3) Y
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3) Y
FORCE_LOGGING VARCHAR2(3) Y
PLATFORM_ID NUMBER Y
PLATFORM_NAME VARCHAR2(101) Y
RECOVERY_TARGET_INCARNATION# NUMBER Y
LAST_OPEN_INCARNATION# NUMBER Y
CURRENT_SCN NUMBER Y
FLASHBACK_ON VARCHAR2(18) Y
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3) Y
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3) Y
DB_UNIQUE_NAME VARCHAR2(30) Y
STANDBY_BECAME_PRIMARY_SCN NUMBER Y
FS_FAILOVER_STATUS VARCHAR2(21) Y
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30) Y
FS_FAILOVER_THRESHOLD NUMBER Y
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7) Y
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512) Y

SQL>

R12 Output Post Processor Service Not Coming Up.

After cloning in R12 Output Post Processor Service Not Coming Up.

Solution
To implement the solution, please execute the following steps:
1. Shutdown all the services.
2. Log in as applmgr
cd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql
3. Relink FNDSM and FNDLIBR executables as mentioned below:
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"
4. Run cmclean.sql
5. Start up the Services and retest.

Error Dld.So: Unsatisfied Code Symbol 'Nnftboot' Signal 9 When Running Reports Concurrent Requests

After a fresh installation of the e-Business suite R12 on HP-UX Itanium, all the concurrent requests end with the following error :

+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
FNDSCURS module: Active Users
+---------------------------------------------------------------------------+
Current system time is 31-MAR-2008 09:56:59
+---------------------------------------------------------------------------+
+-----------------------------
| Starting concurrent program execution...
+-----------------------------
Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.CL8ISO8859P5

'.,'

stat_low = 9
stat_high = 0
emsg:was terminated by signal 9
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnftboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnfoboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnfoboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnfhboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnfhboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nnflboot' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'nttini' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'ntusini' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'ntpini' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol 'ntzini' in load module
'/oracle/phpe/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1'.

Program was terminated by signal 9
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 352246.

Solution:

$make -f ins_reports.mk install

Retest the issue.

Forms Session Failed During Startup: No Response From Runtime Process

ERROR
The forms windows contains the following error:
proxyHost=null
proxyPort=0
connectMode=HTTP, native.
oracle.forms.net.ConnectionException: Forms session <1> failed during startup: no response
from runtime process
at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)
at oracle.forms.net.HTTPNStream.getResponse(Unknown Source)
at oracle.forms.net.HTTPNStream.doFlush(Unknown Source)
at oracle.forms.net.HTTPNStream.flush(Unknown Source)
at java.io.DataOutputStream.flush(Unknown Source)
at oracle.forms.net.HTTPConnection.connect(Unknown Source)
at oracle.forms.engine.FormsDispatcher.initConnection(Unknown Source)
at oracle.forms.engine.FormsDispatcher.init(Unknown Source)
at oracle.forms.engine.Runform.initConnection(Unknown Source)
at oracle.forms.engine.Runform.startRunform(Unknown Source)
at oracle.forms.engine.Main.createRunform(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


The above error Occur after cloning from multi node to single node and at the time of opening form session.

Solutions.

$ cd $ORACLE_HOME/lib32
$ rm ldflags
$ ln -s $ORACLE_HOME/lib/ldflags ldflags
$ cd $ORACLE_HOME/forms/lib32/
$ make -f ins_forms.mk install

Retest the issue.

What Is Included in the July 17, 2009 Global Practices Update?

What Is Included in the July 17, 2009 Global Practices Update?
Technology Update
Oracle Technology Global Price Lists Update
Applications Update
Business Intelligent Applications Global Price Lists Update
Oracle E-Business Suite Applications Global Price Lists Update
Siebel Applications Global Price Lists Update
PeopleSoft Applications Global Price Lists Update

Oracle FAQ

How to convert local management tablespace to dictionary managed tablespace
Ans.
Local to Dictionary managed tablespcaes:
exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tablespace');

Dictionary to locally managed tablespaces:
exec dbms_space_admin.Tablespace_Migrate_to_Local('tablespace');

What is an oracle instance?
Ans.
Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory

structure and the background processes used to access data from a database.

An instance has two major memory structures:
The System Global Area, also known as the Shared Global Area (SGA) stores information in memory that is shared by the various processes in Oracle.
The Program Global Area, also known as the Private Global Area (PGA) contains information that is private to a particular process.


What is a view?
Ans.
A View in Oracle database system is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

What is referential integrity?
Ans.
Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the

concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked

table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the

primary table.

Name the data dictionary that stores constraints details?
Ans.
SYS ALL_CONSTRAINTS
SYS APPLY$_CONSTRAINT_COLUMNS
SYS DBA_CONSTRAINTS
SYS DBA_IAS_CONSTRAINT_EXP
SYS KU$_CONSTRAINT0_VIEW
SYS KU$_CONSTRAINT1_VIEW
SYS KU$_CONSTRAINT2_VIEW
SYS KU$_CONSTRAINT_COL_VIEW
SYS KU$_CONSTRAINT_VIEW
SYS KU$_PKREF_CONSTRAINT_VIEW
SYS KU$_REF_CONSTRAINT_VIEW
SYS LOADER_CONSTRAINT_INFO
SYS USER_CONSTRAINTS
SYS _DBA_APPLY_CONSTRAINT_COLUMNS
SYS _DBA_APPLY_OBJECT_CONSTRAINTS


What is a collection of privileges?
Ans.
Role

What is a snapshot?
Ans.


What is a synonym?
Ans.
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

What is a cursor?
Ans.
When a query is executed in oracle, a result set is produced and stored in the memory. Oracle allows the programmer to access this result set in the memory through

cursors.
What is a sequence?
Ans.
Sequence are nothing but which • Automatically generates unique numbers• Is a sharable object• Is typically used to create a primary key value• Replaces

application code• Speeds up the efficiency of accessing sequencevalues when cached in memory

What is a trigger?
Ans.
Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger

is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either

BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the

latter fires once for the whole statement.

What is an exception?
Ans.
An Exception is an error situation, which arises during program execution. When an error occurs exception is raised, normal execution is stopped and control

transfers to exception-handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined (system-defined or

pre-defined) or User-defined exception.

What is a partition of table?
Ans.
Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition".

From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to

access a partitioned table if that application was initially written on a non partitioned tables.

What are pseudo-columns in SQL? Provide examples.
Ans.
Oracle supports several special-purpose data elements that are not actually contained in a table, but are available for use in SQL statements as if they were part of

the table.

Pseudo columns in Oracle
rowid
versions_xid
versions_operation
versions_startscn
versions_starttime
versions_endscn
versions_endtime
sysdate
systimestamp
rownum
ora_rowscn
object_value
level (only in hierarchical queries with connect by
user
..... etc

What are the Data Control statements?
Ans.

What is a schema?
Ans.
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures

created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes.

What is a type?
Ans.

What is a data model?
Ans.

What is a relation?
Ans.

Advantages of redo log files?
Ans.

What is an Archiver?
Ans.

What is a database buffer cache?
Ans.

What are the background processes in Oracle?
Ans.

%type and %rowtype are attributes for…?
Ans.

What are the steps in a two-phase commit?
Ans.

What is a union, intersect, minus?
Ans.
UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION

ALL operator does not eliminate duplicate selected rows:

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id FROM locations
UNION ALL
SELECT location_id FROM departments;

A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the

UNION ALL operator.

INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;
MINUS Example The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;


What is a join, explain the types of joins?
Ans.
Basically Joins are used to get result from two or more tables and there are two types of joins
inner join and outer join
Inner join : a join of two or more tables which omits the blank rows while checking

Outer join is subcatogorised in to left outer join and right outer join. Which includes blank rows in specifed side if condition satisfies.
Simple outer join is combination of left and right outerjoins.
Apart from these there are
Natural join : cartisian product
Equi join : which includes operator in condition
NonEqui join : All conditional joins which doesn't uses in there conditions.

What is a co-related sub-query?
Ans.
The main differencre between subquery and co-related subquery is that in subquery child query is executed first n then parent,but in co-related subquery main query

is executed first(even though parenthesis are present) and then child query.

Example of co-related subquery

select dname from dept where exists
(select deptno from emp
where dept.deptno=emp.deptno);

select dname from dept where not exists
(select deptno from emp
where dept.deptno=emp.deptno);

ODBC stands for…?
Ans.
Open Database Connectivity

Data-type used to work with integers is?
Ans.

Describe data models?
Ans.
A data model in software engineering is an abstract model that describes how data is represented and accessed. Data models formally define data elements and

relationships among data elements for a domain of interest.

Data model explicitly determines the meaning of data, which in this case is known as structured data (as opposed to unstructured data, for example an image, a

binary file or a natural language text, where the meaning has to be elaborated). Typical applications of data models include database models, design of information

systems, and enabling exchange of data. Usually data models are specified in a data modeling language

Describe the Normalization principles?
Ans.
http://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html

What are the types of Normalization?
Ans.
There are 3 types of Normalization. Normalization helps in reducing data redundancy. As we move towards higher normalization

1NF: This type of normalization states that there must not be any duplicates in the tables that we use. In other words, all the tables used must have a primary key

defined.
2NF: This type of normalization states that data redundancy can be reduced if attributes those are dependent on one of the keys of a composite primary key are

isolated to a separate table. Not only does this reduces data redundancy but also helps in increasing data retention when a delete is done. For example, consider a

table that has the following columns: Part Id, State, City, and Country. Here, assume Part Id & Country form the composite primary key. The attributes state & city

depend only on the country. 2NF states that if such is the case then split the table into 2 tables. One with Part Id & country as the columns. Other with Country,

state & city as the columns. In the 1st table if a delete is made to all the rows with Part Id = ‘X’ then we would lose country related data too. But in the 2nd case

this would not happen.
3NF: This type of normalization states that if a dependency exists on certain attributes other than the primary key then the table split depending on the dependency

has to be done. Consider the same example above. In the present case consider that Part Id is the only primary key. Now state, city depend only on country & not

on Part Id. This table is already in 1NF & 2NF. But to achieve 3NF we would do the same split as above.

What is de-normalization?
Ans.
Denormalization is usually done to decrease the time required to execute complex queries. Drawbacks of a normalized database are mostly in performance. In a

normalized database, more joins are required to gather all the information from multiple entities, as data is divided and stored in multiple entities rather than in one

large table. Queries that have a lot of complex joins will require more CPU usage and will adversely affect performance. Sometimes, it is good to denormalize parts

of the database. Examples of design changes to denormalize the database and improve performance are:

Add a column (or columns) to the table that contains pre-aggregated data to be used only for a report.
Partition the table with many columns to multiple tables.
Add duplicate keys to tables. This will reduce the number of joins required to get complete information.

ORA-06553: PLS-213: package STANDARD not accessible.

While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object

Please make sure to set the following init parameters as below in the spfile/init file or comment them out to their default values, at the time of upgrading the database.

PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_CODE_TYPE = INTERPRETED # Only applicable to 10gR1
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0

Refer to Note 170282.1 PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile

@

Always disconnect from the session which issues the STARTUP and connect as a fresh session before doing any further SQL. eg: On upgrade to 10.2 startup the instance with the upgrade option, exit sqlplus , reconnect a fresh SQLPLUS session as SYSDBA and then run the upgrade scripts.


If this is not a RAC instance, but DBA_REGISTRY shows the RAC component and it is invalid the following bite can be used to remove the reference from DBA_REGISTRY.

Note 312071.1 - RAC Option Invalid After Migration

Initialization Parameters Obsolete in 10g

Initialization Parameters Obsolete in 10g
ENQUEUE_RESOURCES
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS

What is the difference between REF Cursor & Normal Cursor?

What is the difference between REF Cursor & Normal Cursor?

Ans:

In case of an normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself. In case of REF Cursor, the cursor declartion is not associated with any SQL query, it is associated with a query at a later stage this brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time, offcourse) programatically. REF Cursors also provide the feature of passing parameters. Though there is something dynamic with REF Cursor when compared to a normal explicit cursor, it is not a truly perfect dynamic cursor. Truly perfect dynamic cursors are the one constructed using DBMS_SQL package.

Link: http://www.geekinterview.com/question_details/17355

List of Database Properties

List of Database Properties

• DBTIMEZONE -07:00 DB time zone
• DEFAULT_PERMANENT_TABLESPACE SYSTEM Default Permanent Tablespace ID
• DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
• DEFAULT_TEMP_TABLESPACE TEMP1 Name of default temporary tablespace
• DICT.BASE 2 dictionary base tables version #
• EXPORT_VIEWS_VERSION 8 Export views revision #
• GLOBAL_DB_NAME TESTING.KNM.COM Global database name
• NLS_CALENDAR GREGORIAN Calendar system
• NLS_CHARACTERSET US7ASCII Character set
• NLS_COMP BINARY NLS comparison
• NLS_CURRENCY $ Local currency
• NLS_DATE_FORMAT DD-MON-RR Date format
• NLS_DATE_LANGUAGE AMERICAN Date language
• NLS_DUAL_CURRENCY $ Dual currency symbol
• NLS_ISO_CURRENCY AMERICA ISO currency
• NLS_LANGUAGE AMERICAN Language
• NLS_LENGTH_SEMANTICS BYTE NLS length semantics
• NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
• NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
• NLS_NUMERIC_CHARACTERS ., Numeric characters
• NLS_RDBMS_VERSION 10.2.0.2.0 RDBMS version for NLS parameters
• NLS_SORT BINARY Linguistic definition
• NLS_TERRITORY AMERICA Territory
• NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
• NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
• NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
• NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format

Displays space usage for each datafile

SQL> SET SERVEROUTPUT ON
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 255
SQL> SET FEEDBACK OFF
SQL> SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
2 Substr(df.file_name,1,40) "File Name",
3 Round(df.bytes/1024/1024,2) "Size (M)",
4 Round(e.used_bytes/1024/1024,2) "Used (M)",
5 Round(f.free_bytes/1024/1024,2) "Free (M)",
6 Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
7 FROM DBA_DATA_FILES DF,
8 (SELECT file_id,
9 Sum(Decode(bytes,NULL,0,bytes)) used_bytes
10 FROM dba_extents
11 GROUP by file_id) E,
12 (SELECT Max(bytes) free_bytes,
13 file_id
14 FROM dba_free_space
15 GROUP BY file_id) f
16 WHERE e.file_id (+) = df.file_id
17 AND df.file_id = f.file_id (+)
18 ORDER BY df.tablespace_name,
19 df.file_name;

Tablespace Name File Name Size (M) Used (M) Free (M) % Used
-------------------- ---------------------------------------- ---------- ---------- ---------- -----------
APPS_TS_ARCHIVE /dev02/TESTONE/db/apps_st/data/a_archive 2000 1020.63 874.5 XXXXX-----
APPS_TS_INTERFACE /dev02/TESTONE/db/apps_st/data/a_int01.d 2000 908.88 1071.13 XXXXX-----
APPS_TS_MEDIA /dev02/TESTONE/db/apps_st/data/a_media01 2000 1166.13 833.75 XXXXXX----
APPS_TS_NOLOGGING /dev02/TESTONE/db/apps_st/data/a_nolog01 60 39.75 11.88 XXXXXXX---
APPS_TS_QUEUES /dev02/TESTONE/db/apps_st/data/a_queue01 500 137 362.88 XXX-------
APPS_TS_QUEUES /dev02/TESTONE/db/apps_st/data/a_queue02 500 162.63 337 XXX-------
APPS_TS_SEED /dev02/TESTONE/db/apps_st/data/a_ref01.d 1500 1396.5 95.63 XXXXXXXXX-
APPS_TS_SEED /dev02/TESTONE/db/apps_st/data/a_ref02.d 1500 1029.5 464.75 XXXXXXX---
APPS_TS_SUMMARY /dev02/TESTONE/db/apps_st/data/a_summ01. 1000 966.75 6.75 XXXXXXXXXX
APPS_TS_TOOLS /dev02/TESTONE/db/apps_st/data/apps_ts_t 500 499.88 ----------
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 4000 2686 1276.63 XXXXXXX---
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 2000 1997.13 0.13 XXXXXXXXXX
APPS_TS_TX_DATA /dev02/TESTONE/db/apps_st/data/a_txn_dat 2000 1991 0.13 XXXXXXXXXX
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1341.5 652.38 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1496.13 503 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1340.5 656.38 XXXXXXX---
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1234.63 764.38 XXXXXX----
APPS_TS_TX_IDX /dev02/TESTONE/db/apps_st/data/a_txn_ind 2000 1609.38 390.25 XXXXXXXX--
APPS_UNDOTS1 /dev02/TESTONE/db/apps_st/data/undo01.db 4000 3967.94 8 XXXXXXXXXX
CTXD /dev02/TESTONE/db/apps_st/data/ctxd01.db 20 17.13 2.75 XXXXXXXXX-
INTERIM /dev02/TESTONE/db/apps_st/data/interim.d 200 199.88 ----------
ODM /dev02/TESTONE/db/apps_st/data/odm.dbf 100 14.38 85.5 X---------
OLAP /dev02/TESTONE/db/apps_st/data/olap.dbf 100 31 68.88 XXX-------
OWAPUB /dev02/TESTONE/db/apps_st/data/owad01.db 10 9.88 ----------
PORTAL /dev02/TESTONE/db/apps_st/data/portal01. 100 1.25 98.63 ----------
SYSAUX /dev02/TESTONE/db/apps_st/data/sysaux01. 2000 1996.38 0.81 XXXXXXXXXX
SYSAUX /dev02/TESTONE/db/apps_st/data/sysaux02. 2493.5 2493.44 XXXXXXXXXX
SYSTEM /dev02/TESTONE/db/apps_st/data/system01. 1000 919.98 78.32 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system02. 1000 927.05 70.34 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system03. 1000 980.36 19.2 XXXXXXXXXX
SYSTEM /dev02/TESTONE/db/apps_st/data/system04. 1000 937.44 59.72 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system05. 1000 924.8 75.01 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system06. 1250 768.82 481.05 XXXXXX----
SYSTEM /dev02/TESTONE/db/apps_st/data/system07. 1250 768.06 481.82 XXXXXX----
SYSTEM /dev02/TESTONE/db/apps_st/data/system08. 1100 768.17 331.73 XXXXXXX---
SYSTEM /dev02/TESTONE/db/apps_st/data/system09. 1000 494.59 504.95 XXXXX-----
SYSTEM /dev02/TESTONE/db/apps_st/data/system10. 1000 869.55 129.02 XXXXXXXXX-
SYSTEM /dev02/TESTONE/db/apps_st/data/system11. 1000 906.73 91.38 XXXXXXXXX-

SQL>

What is the difference between APPCORE and APPCORE2?

What is the difference between APPCORE and APPCORE2?

APPCORE2 is the duplicate of APPCORE which is used with custom pll And it contains all the object related to standard toolbar & menu.

Scripts to compile invalid objects in Oracle

sqlplus -s / as sysdba << EOF
set heading off;
set verify off;
set feedback off;
set trims on;
spool compile_objects.sql
prompt set echo on
select 'Alter '||rtrim(object_type, ' BODY')||' '||owner||'.'||object_name
||' compile'||decode(object_type, 'PACKAGE BODY', ' BODY', NULL)||chr(10)||'/'
from dba_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE',
'PACKAGE BODY', 'TRIGGER', 'VIEW','MATERIALIZED VIEW')
and status = 'INVALID'
and owner='APPS'

cmcclean.sql

REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+
set verify off;
set head off;
set timing off
set pagesize 1000
column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'
WHENEVER SQLERROR EXIT ROLLBACK;

set feed off
select null from &answer;
set feed on


REM Update process status codes to TERMINATED

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;

set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');



REM Set all managers to 0 processes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;




REM Reset control codes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;


REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;

set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';





REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin

select substr(release_name, 1, 2)
into vers
from fnd_product_groups;

if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;


statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/



prompt

prompt ------------------------------------------------------------------------

prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------

prompt

set feedback on

REM <= Last REM statment -----------------------------------------------------

Procedure to create Apps Users from Backend

declare
cursor c8 is
select name l_name,
id l_id,
res_name l_res_name,
description l_description
from list_of_employees;
l_responsibility_key varchar2(100) := NULL;
l_person_id number;
begin
-- Call the procedure
for r_c8 in c8 loop
/*select person_id
into l_person_id
from per_all_people_f
where employee_number = r_c8.l_id;

fnd_user_pkg.createuser(x_user_name => r_c8.l_name,
x_owner => 'CUST',
x_unencrypted_password => 'welcome',
x_description => r_c8.l_description,
x_employee_id => l_person_id);
select responsibility_key
into l_responsibility_key
from fnd_responsibility_vl
where responsibility_name = r_c8.l_res_name;

begin
fnd_user_pkg.addresp(r_c8.l_name,
'JA',
l_responsibility_key,
'STANDARD',
r_c8.l_res_name,
sysdate,
null);
end;*/
begin
-- Call the procedure
fnd_user_pkg.updateuser(x_user_name => r_c8.l_name,
x_owner => NULL,
x_unencrypted_password => NULL,
x_session_number => NULL,
x_start_date => NULL,
x_end_date => NULL,
x_last_logon_date => NULL,
x_description => r_c8.l_description,
x_password_date => NULL,
x_password_accesses_left => NULL,
x_password_lifespan_accesses => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL,
x_fax => NULL,
x_customer_id => NULL,
x_supplier_id => NULL,
x_old_password => NULL);
end;
end loop;
end;

List applied patche details in Oracle apps

select patch_drivers.orig_patch_name "Patch Name",
patch_runs.start_date "Patch Applied Date",
patch_runs.patch_top "Patch Top",
patch_drivers.driver_file_name "Driver File Name",
patch_drivers.patch_abstract "Patch Details",
patch_bugs.no_of_bugs "Bugs"
from applsys.ad_patch_runs patch_runs,
(select patch_run_id, count(patch_run_id) no_of_bugs
from applsys.ad_patch_run_bugs
group by patch_run_id) patch_bugs,
applsys.ad_patch_drivers patch_drivers
where patch_runs.patch_run_id = patch_bugs.patch_run_id and
patch_drivers.patch_driver_id = patch_runs.patch_driver_id /*and
patch_runs.start_date > (select created from v$database) - 1*/
order by patch_runs.start_date desc

Query to check value of Profile Options in R12

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
to_char(v.last_update_date,'DD-MON-RR') "Last Updated",
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set

Query to Count open cursor in Oracle

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine, s.SID
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid --AND s.username = 'KMC'
and b.name = 'opened cursors current'
group by s.username, s.machine, s.SID
order by 4 DESC

Script Oracle Apps Frontend Password from backend

CREATE OR REPLACE PROCEDURE kmc_koushik_ex IS
CURSOR cur_user_dtl IS
SELECT USER_ID, USER_NAME FROM fnd_user WHERE user_id >1000
ORDER BY creation_DATE DESC;
result boolean;
BEGIN
FOR c_user_dtl IN cur_user_dtl LOOP
result := fnd_user_pkg.changepassword(username => c_user_dtl.user_name,
newpassword => 'welcome1');
IF result THEN
NULL;
ELSE
dbms_output.put_line('The password of ' || c_user_dtl.user_name || ' has failed to reset.');
END IF;
END LOOP;
result := fnd_user_pkg.changepassword(username => 'SYSADMIN',
newpassword => 'welcome1');
END kmc_koushik_ex;

/

Script to find Roll out patch in Oracle Apps

set heading off
set feedback off
set line 132
set trimspool on
prompt
prompt ATG Patches
select decode(bug_number,
'6077487','R12.TXK.A.DELTA.3',
'6141000','12.0.3- RUP3',
'6077669','R12.ATG_PF.A.Delta.3',
'5484000’,’12.0.2 RUP2’,
'6510214',' R12.AD.A.DELTA.4',
'5484000','Oracle E-Business Suite 12.0.2 Release Update Pack (RUP2)',
'6145693','R12 RAPIDCLONE CONSOLIDATED FIXES JAN/2008',
‘6776948’,’R12 RAPIDCLONE CONSOLIDATED FIXES JUL/2008’,
'7237006', 'R12.ATG_PF.A.delta.6' ,
'Other / error'
) ||
' is installed'
from apps.ad_bugs where
bug_number in ('6077487','6141000','6077669'.’5484000’,'6510214','5484000','6145693',’67769
48’, '7237006')
/

Oracle Database Tuning Guide

1)Try find out where there is memory available or not in the machine.
2) within SGA you can increase individual component like log buffer,
shared pool or any other if Sufficient memory is available with SGA.
user
SQL> show parameter sga_max_size (for check total sga allocation)

2) you can increase or decrease component size with ALTER SYSTEM command
if you database is 9.0 or abobe because 8i is not possible because spfile is not there.
In 8i you have to shutdown the database and edit the parameter file then startup.

3) DB_BLOCK_BUFFER is the parameter for buffer cache size defination in 8i or below.
from oracle 9i or 10g or above it is DB_CACHE_SIZE.
To find out hit ratio of Database buffer cache

select (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100 "Buffer cache hit ratio"
from v$buffer_pool_statistics


4) Rollback segment are the concept of oracle 8i.
From 9i using the Undo tablespace.
Undo segment tuning done by the orcale server itself you have to just monitor
that the undo tablespace are the proper size or not or you have to add datafiles.

4) Log_buffer parameter for redo log size upto 10g alteration of this parameter need
shutdown and then startup the database.
You have to find out the log buffer is currently proper sized or not

select *
from v$sysstat
where name like 'redo%'

from this query you have to find out "redo entries" and "redo buffer allocation retries"
vales

log ratio= redo buffer allocation retries/redo entries

This ratio if <1 or 0 then it is proper size. Other wise you have increase parameter value.

5) for sorting it is good if your 98% sorting going on in memory
you can fing out by using.

select *from v$sysstat
where name like 'sort%'

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
344 sorts (memory) 64 306941 2091983730
345 sorts (disk) 64 2 2533123502
346 sorts (rows) 64 28458010 3757672740

-- See only 2 time going to disk for sorting and (306941-2) so 306939 times in memory.
Because first sorting done in memory so in this case 2 times disk mean it after sorting memory
due to insufficient memory it gone into disk.
---------------------------------------------------------------------------------------

1) The following query identifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2) The following query identifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text FROM v$sqlarea WHERE buffer_gets > 100000

Query to find out hit ratio of Oracle

To find out Library cache hit ratio

select sum(PINS-RELOADS)/sum(PINS)*100 "Library hit ratio"
from v$librarycache

Details Library Cache Hit Ratio
Query more details about the hit ratio from each library cache item.
SQL> SELECT namespace, gethitratio
FROM v$librarycache
WHERE gethitratio > 0


To find out Dictionary cache hit ratio

select sum(GETS-GETMISSES)/sum(GETS)*100 "dictionary cahe ration"
from v$rowcache;

To find out Buffer cache hit ratio

select (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100 "Buffer cache hit ratio"
from v$buffer_pool_statistics;