How to find the character set of oracle database

Below script to find out the character set of oracle database.

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
-----------------
US7ASCII

Script:

SELECT * FROM NLS_DATABASE_PARAMETERS;

Output:

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               US7ASCII
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CSMIG_SCHEMA_VERSION       2
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_SAVED_NCHAR_CS             US7ASCII
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              11.2.0.3.0

How to know the size of oracle database 11g

This post will helps you to find out the total,free and used size of  oracle database.

To know the total size of your database.

Script:

select 
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Output:

Size in GB
----------

1115.77302

To know the Total, Used and Free space of your Database.

Script:

col "Database Size" format a20 

col "Free space" format a20 

col "Used space" format a20 

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" 

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" 

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" 

from (select bytes 

from v$datafile 

union all 

select bytes 

from v$tempfile 

union all 

select bytes 

from v$log) used 

, (select sum(bytes) as p 

from dba_free_space) free 

group by free.p 

/

Output:

Database Size        Used space           Free space

-------------------- -------------------- --------------------

1116 GB              580 GB               536 GB

Below script to know the particular datafile size of your database.

Script:

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT df.file_name,

df.tablespace_name,

df. status,

(df.bytes/1024000) t,

(fs.s/df.bytes*100) p,

decode (ae.y,1,'YES','NO') a

FROM dba_data_files df,

(SELECT file_id,SUM(bytes) s

FROM dba_free_space

GROUP BY file_id) fs,

(SELECT file#, 1 y

FROM sys.filext$

GROUP BY file#) ae

WHERE df.file_id = fs.file_id

AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

Output:

FILE_NAME                        TABLESPACE_NAME STA     Total MB  % Free Aext

-------------------------------- --------------- --- ------------ ------- ----

/d05/oracle/proddata/absl01.dbf  ABSL            AVA    1,142.000    6.52 YES

/d05/oracle/proddata/absl02.dbf  ABSL            AVA    1,806.336    6.94 YES

Hope this post will helps you :)

How to know the concurrent request details using Request id in oracle apps R12

Below script to find the concurrent request details using Request Id.

Script:

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Request_ID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600,
'E.SQL_TEXT .............................................: '||E.SQL_TEXT
from v$session s, v$process p, V$SQL E
where p.addr=s.paddr AND s.SQL_ADDRESS = E.ADDRESS and
s.sid in (SELECT d.sid FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b, v$process c,v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID);

Output:


Enter value for request_id: 31321705

Session  Id.............................................: 2289
Serial Num..............................................: 8075
User Name ..............................................: APPS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *57551*
Server Process ID ......................................: 4203
Sql_Address ............................................: 00000006B1BC9010
Sql_hash_value .........................................: 3255635558
Schema Name ..... ......................................: APPS
Program  ...............................................: STANDARD@erpappsp1 (TNS V1-V3)
Module .................................................: CSICORSY
Action .................................................: Concurrent Request
Terminal ...............................................:
Client Machine .........................................: erpappsp1
LAST_CALL_ET ...........................................: 287646
S.LAST_CALL_ET/3600 ....................................: 79.9016666666666666666666666666666666667
E.SQL_TEXT .............................................: BEGIN CSI_DIAGNOSTICS_PKG.IB_SYNC(:errbuf,:rc,:A0,:A1,:A2); EN

D;