List of Database users about to expire in 'n' days.

select * from dba_users where expiry_date  > sysdate - 10 and expiry_date < sysdate + 10

Currently Logged in Forms users

SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v
ORDER BY 1


Sessions opened by each user

select user_name, count(1) "Sessions Opened"
from apps.fnd_form_sessions_v
group by user_name order by count(1)desc

Database size with breakdown of datafiles, tempfiles, redologs and archivelogs

select
a.bytes "Data Files GB",
b.bytes "Temp Files GB",
c.bytes "Log Files GB",
d.bytes "Archive Logfiles 30 Days",
(a.bytes + b.bytes + c.bytes + d.bytes) "Total GB"
from
(select sum(bytes/1024/1024/1024) bytes from dba_data_files) a ,
(select sum(bytes/1024/1024/1024) bytes from dba_temp_files) b,
(select count(1) * 0.05859375 bytes from v$logfile) c,
(SELECT count(1) * 0.05859375 bytes from v$log_history
where to_date(first_time) > sysdate - 30) d

Tablespace Free size for E-business suite


I have included only those tablespaces which frequently become full

select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB,  
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
and a.tablespace_name in
(
'SYSTEM',
'APPLSYSD',
'APPLSYSX',
'GLX',
'GLD',
'APD',
'APX',
'ARD',
'ARX',
'UNDOTS1'
)
group by a.tablespace_name

Temp Tablespace error's and resolution

ora-01652

Troubleshooting Details
Error explanation
There is no enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error
There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

In order for the approach to be relevant, the following investigation steps should be followed immediately after the error occurs : Check the status of the sort segment utilization :
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following: (Note 317441.1 - How Do You Find Who And What SQL Is Using Temp Segments) In some cases, you may find that the ORA-1652 is not reported for a temporary tablespace, but a permanent one. This is not an abnormal behaviour and it can occur for example while creating or dropping objects like tables and indexes in permanent tablespaces. (Reference : Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s) In such cases the following note will be of use :
( Note 100492.1 - ORA-01652 : Estimate Space Needed to CREATE INDEX)

In the process of diagnostic and tuning, the resumable statement feature can be useful. It allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended transaction which does not loose all the work done previously. By querying dba_resumable one can find the statement that is executed when ORA-1652 occurs. See Note 136941.1 - Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors.

There are two ways of solving this error

Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Tune the queries/statements so that the sort operations are done in memory and not on the disk.
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons. The FREE extents can be further used by other users that are executing sort
operations.  After the database restart the allocated extens are NOT released either but are FREE to be reused.
Hence, seeing  the physical space of the temporary tablespace fully allocated is not a reason to be concerned per se. The above query of V$SORT_SEGMENT should be used to establish the free space inside the temporary tablespace.

Known issues
Note 463819.1 - Database HANG After Migrating to 10.2 : ORA-1652
Note 164850.1 - ORA-01652 in Resumable Statements Prevents any SELECT on DBA_RESUMABLE View

SQL Queries

Temp Tablespace usage Report

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

Temp Space usage session wise

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
--AND      T.blocks * TBS.block_size / 1024 / 1024  > 5024
ORDER BY S.sid

Regards
Manoj

Check Database Memory Usage SGA+PGA

UNIX tools which look at UNIX process memory are unable to distinguish between private and shared memory and thus over-report memory usage for Oracle processes.

Check memory usage from Oracle Database

Total memory usage
select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union all select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory' );

Process wise listing
select a.sid,value/(1024*1024) "MB",program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory') order by value/(1024*1024) desc ;


source: oracle communities

JBOSS certificate deployment

Create Private Certificate store

keytool -genkey alias mysite.com keyalg RSA -keystore key.store


create cretificate request

keytool -certreq -alias mysite.com -file certreq.csr -keystore key.store



You should receive 3+ certificates

root


intermidiate ( can be more than one )


certificate generated against your CSR


Import root CA

keytool -import -alias root -keystore key.store -trustcacerts -file /root/root.txt


Import intermediate CA

keytool -import -alias intermediate -keystore key.store -trustcacerts -file /root/intermediate.txt

Import signed CA

keytool -import -alias mysite.com -keystore key.store -trustcacerts -file /root/mysite.com.txt


Steps to generate pfx ( private key+ certificate) to send it backup to CA ( windows )

create keystore of PKCS12

keytool -genkey -alias pkc -keystore key.store.p12 -storetype pkcs12

truncate the pkcs keystore

keytool -delete -alias pkc -keystore key.store.p12 -storetype pkcs12

copy/import der keystore to pkcs keystore

keytool -importkeystore -srckeystore key.store -destkeystore key.store.p12 -srcstoretype jks -deststoretype pkcs12 -scrstorepass 123455 -deststorepass 2323233 -srcalias mysite.com -destalias mysite.com

export the pfx bundle

keytool -exportcert -keystore key.store.p12 -storetype pkcs12 -alias mysite.com -file mysite.pfx

Generate private key from the key store

syntax: java -jar exportprivatekey.zip [pkcs12 or jks]

example: java -jar exportprivatekey.zip /root/key.store jks hello mysite.com mysite.key


Bundle private and public key in pkcs12 format

openssl pkcs12 -export -out mm.e.pfx -inkey mysite.key -in my.txt

Oracle database Moving tempfiles without shutdown

Before 10g

alter database tempfile 'temp26.dbf' offline ;

host mv temp26.dbf /temp/temp26.dbf

host ln -s temp26.dbf /temp/temp26.dbf

alter database tempfile 'temp26.dbf' online;



After 10g

alter database tempfile 'temp26.dbf' offline ;

host mv temp26.dbf /temp/temp26.dbf

alter database rename file 'temp26.dbf' to '/temp/temp26.dbf'

Script to find free space in tablesapces

Script to find free space in tablesapces



select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB, 
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
group by a.tablespace_name;

32bit to 64bit migration on same oracle database version 9.2.0.6

Plan
-----

1. Raise an SR with oracle support to get 9.2.0.6 patch set.
2. Download base release 9.2.0.1 from edelivery.oracle.com
3. Create a new folder and install 9.2.0.1 base release
Note: OPatch folder will not be available with 9.2.0.1 base release

4. Apply 9.2.0.6 patch set on top of 9.2.0.1 base release.
5. set environment to point to new oracle home and apply patches which are already there on 32 bit oracle home

opatch apply may fail with error 73

OPatch detects your platform as 453 while this patch is for 23

To resolve this you have to set env  variable OPATCH_PLATFORM_ID=23 <>

6. Shut down 32bit db, copy directories dbs, admin, network make appropriate changes (oracle home location) to configuration files, increase SGA and PGA size .

7. startup migration and run utlrp.sql

8. run catpatch.sql

9. Bounce DB and release for testing.


Regards
Manoj

Find current SCN number in Database

select current_scn from v$database;
select dbms_flashback.get_system_change_number() from dual;

-- find where current SCN lives in SGA:

SQL> select ksmfsadr from x$ksmfsv where ksmfsnam = 'kcsgscn_';

KSMFSADR
--------
20009104

-- on 32bit environments this is the most significant half word of 8 byte SCN. To get the other, least significant half, you have to add 4 to the address and combine the results:

SQL> l
 1  select dbms_flashback.get_system_change_number flashback_scn,
 2         current_scn,
 3         (select to_number(ksmmmval,'XXXXXXXX')
 4         from x$ksmmem where addr = hextoraw('20009104')) * power(2,32) +
 5         (select to_number(ksmmmval,'XXXXXXXX')
 6         from x$ksmmem where addr = hextoraw('20009108')) direct_scn
 7* from v$database
SQL> /

FLASHBACK_SCN CURRENT_SCN DIRECT_SCN
------------- ----------- ----------
     2633692     2633692    2633692

The SCN lives in fixed part of SGA, thus its location doesn't change over instance bounces or SGA size changes. It might change only if you relink Oracle binary or change SGA mapped base address.


source:freelists.org 
Regards
Manoj