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;

How to resize archive logs

Modifying the redo logs:
------------------------
SQL> select group#, bytes, status from v$log;

GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

SQL> select group#, member,status from v$logfile;

GROUP# MEMBER
--------------------------------------------------------------------------------
3 /TESTDATA/TEST/testdata/redo03.log
2 /TESTDATA/TEST/testdata/redo02.log
1 /TESTDATA/TEST/testdata/redo01.log


SQL> alter database add logfile group 4 '/TESTDATA/TEST/testdata/redo04.log' size 125M;

alter database add logfile group 5 '/TESTDATA/TEST/testdata/redo05.log' size 125M;

alter database add logfile group 6 '/TESTDATA/TEST/testdata/redo06.log' size 125M;


select group#, bytes, status from v$log;

Switch until we are into log group 4

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

remove the files at OS level

How to relink AD utilities

You cannot relink ad utilities using adadmin.
Instead use the following

cd $AD_TOP/bin
$ adrelink.sh force=y “ad all”

Active Responsibilities Report SQL Query

select application_name,responsibility_name,
security_group_name, user_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u,fnd_user_resp_groups ur,
fnd_responsibility_vl r,fnd_application_vl a,
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and nvl(ur.end_date, sysdate + 1) > sysdate
and u.start_date <= sysdate
and nvl(u.end_date, sysdate + 1) > sysdate
and r.start_date <= sysdate
and nvl(r.end_date, sysdate + 1) > sysdate
and ur.security_group_id = s.security_group_id
order by application_name,responsibility_name,security_group_name, user_name

Active Users report SQL query

select user_name, application_name, responsibility_name,
security_group_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u, fnd_user_resp_groups ur,
fnd_responsibility_vl r, fnd_application_vl a,
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and nvl(ur.end_date, sysdate + 1) > sysdate
and u.start_date <= sysdate
and nvl(u.end_date, sysdate + 1) > sysdate
and r.start_date <= sysdate
and nvl(r.end_date, sysdate + 1) > sysdate
and ur.security_group_id = s.security_group_id
order by user_name, application_name, responsibility_name,
security_group_name

How to relink 8.0.6 Oracle Home

Change directory to the 8.0.6 ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk install