To find the Products installed in R12

Run the following query to find out the products installed in EBS R12.

select a.oracle_id, a.last_update_date, a.product_version,a.patch_level, decode(a.status, 'I', 'Installed', 'S', 'Shared', 'N', 'Not Installed',a.status) Status, a.industry, b.application_name, c.application_short_name from fnd_product_installations a, fnd_application_tl b, fnd_application cwhere a.application_id = b.application_id and a.application_id = c.application_id and b.language = 'US' order by c.application_short_name;

To find patches applied in R12

Run the following query

SELECT AAP.patch_name, AAP.patch_type, AAP.maint_pack_level, TO_CHAR(AAP.creation_date,'DD-MON-YYYY HH:MI:SS') Applied_date, APD.driver_file_name, APD.platformFROM ad_applied_patches AAP, ad_patch_drivers APDWHERE AAP.applied_patch_id = APD.applied_patch_idORDER BY AAP.creation_date ;

Find out database growth in a year

Script:

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024
"Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');


Sample Output from the script:
==============================
Month Growth in Meg
-------------------------------- -------------
2010 February 1200
2010 January 1000

How to find out Temp tablespace usage information

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Find out concurrent users in a instance

Calculate number of concurrent users of an existing instance
The view v$license keeps track of concurrent sessions and users.


SQL> desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER


select sessions_current from v$license;


The above query will give you the number of concurrent users right now.


You can write a small job which will capture this information every hour for a week. Once you have this data, you can take an average of this data to get the number of concurrent users.


select SESSIONS_CURRENT,SESSIONS_HIGHWATER,CPU_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

How to license a new product through OAM

Here's how you can license a new product through OAM:

1. Login to OAM
2. Click on Site Map (The link near Applications Dashboard link)
3. Click on License Manager
4. Under License heading, click Products
5. Click on blue plus icon of More options
6. Click on the radio button: License Applications Product
7. Click on continue button
8. You'll get a list of all the individual products
9. Check the box on the left of the product you want to license
10. Click on Next button
11. Click on Submit button
12. You'll get a message: Sucessfully licensed the selected product(s). Click OK

That's it you are done. You may regenerate all the forms of the newly licensed product just to make sure.

How to compile Java Class in Oracle database

How to compile a java class in Oracle database

alter java class "java class name" resolve;

For example: alter java class "/e6bc455_DocumentGenerator" resolve;