• Login
• Become a member
• RSS
• Part of the TechTarget network

This script presents some frequently queried parameters to know in order to resolve and support any database issues. I always find this script handy to check database setting quickly and it executes every time you login to a specific database. You can put this in either login.sql or glogin.sql.

rem ******************************************************* 
rem   File       : login.sql 
rem                Local login.sql, it activate at current directory only. 
rem                global glogin.sql is located at sqlplus:admin directory. 
rem 
rem ******************************************************* 
clear column breaks 
set pagesize 66 
set heading  on 
set pause    off 
set feedback off 
set verify   off 

rem ORACLE V8.0.4, default 15 
set arraysize 12 

column global_name       format a16      heading "Global Name" 
column created           format a9       heading "Database|Creation Date" 
column log_mode          format a10      heading Database|Mode 
column startup_time      format a13      heading "Database|Startup Time" 
column db_name           format a8       heading Database|Name 
column machine           format a7       heading Login|Host 
column logins            format a10      heading Access|Restricted 
column db_block_sizes    format a10      heading "Database|Block Size" 
column archiver          format a7       heading Arch|Process 
column audits            format a6       heading Audit|Trail 
column sql_traces        format a6       heading SQL|Trace

 
column statistics        format a10      heading Timed|Statistics 
column sessions_current  format 9,999    heading Total|Users 
column shared_mbytes     format 999,990  heading "Free SGA|(SH)Mbyte" 
column large_mbytes      format 999,990  heading "Free SGA|(LG)Mbyte" 
column optimizer_modes   format a10      heading Optimizer|Mode 
column remote_os_authent format a6       heading "Rmt OS|Auth" 
column rmt_password      format a5       heading Rmt|Paswd 
column os_authent_prefix format a7       heading "OS Auth|Prefix" 
column os_roles          format a5       heading OS|Roles 
column db_domain         format a15      heading Database|Domain 
column global_names      format a6       heading Global|Names 
column total_mbytes      format 999,990  heading "Total SGA|Mbyte" 
column user              format a10      heading Login|User 
column parallel_q_server format a8       heading "Parallel|Qry Svr" 
column mts_servers       format a5       heading MTS|Svr 

column total noprint new_value  total format 9,999  heading "Total|Mbytes" 
rem clear screen 

rem    Total SGA 
select sum(s1.value)/1048576 total 
from   v$sga s1; 

rem PMON : sid=1 
select p1.value db_name , substr(d.log_mode,1,10) log_mode, g.global_name, 
       s.machine, 
       to_char(i.startup_time,'dd-MON HH24:MI') startup_time, 
       d.created, p2.value db_block_sizes 
from v$session s,global_name g, v$database d, v$parameter p1,v$parameter p2,v$instance i 
where s.sid=1 
and   p1.name = 'db_name' 
and   p2.name = 'db_block_size'; 

select substr(i.logins,1,10) logins, i.archiver, p1.value audits, 
       p2.value sql_traces, p3.value statistics, p4.value optimizer_modes, 
       p5.value os_roles, 
       l.sessions_current, user 
from   v$instance i, v$parameter p1, v$parameter p2, v$parameter p3, 
       v$parameter p4, v$parameter p5, v$license l 
where p1.name = 'audit_trail' 
and   p2.name = 'sql_trace' 
and   p3.name = 'timed_statistics' 
and   p4.name = 'optimizer_mode' 
and   p5.name = 'os_roles'; 

column value$   format a12 heading "Current|Value" 
column comment$ format a13 heading "Comment" word_wrapped 
column name format a17 heading "Name" 
rem set linesize 130 
Prompt 
select d.name||' is in '||open_mode ||' mode.'"DB Mode",a.name,a.value$,a.comment$ 
FROM sys.props$ a,v$database d 
WHERE a.name like 'NLS_CHAR%'; 
Prompt 

select p1.value db_domain, p2.value global_names, 
       p3.value os_authent_prefix, 
       p4.value remote_os_authent, 
       p5.value rmt_password, 
       p6.value parallel_q_server, 
       p7.value mts_servers, 
       &total total_mbytes, 
       s1.bytes/1048576 shared_mbytes 
from   v$parameter p1, v$parameter p2, v$parameter p3, 
       v$parameter p4, v$parameter p5, v$parameter p6, v$parameter p7, 
       v$sgastat s1 
where p1.name = 'db_domain' 
and   p2.name = 'global_names' 
and   p3.name = 'os_authent_prefix' 
and   p4.name = 'remote_os_authent' 
and   p5.name = 'remote_login_passwordfile' 
and   p6.name = 'parallel_min_servers' 
and   p7.name = 'mts_servers' 
and   s1.name = 'free memory' 
and   s1.pool ='shared pool'; 

rem This may be missing 
select s2.bytes/1048576 large_mbytes 
from v$sgastat s2 
where s2.name = 'free memory' 
and   s2.pool ='large pool'; 

set heading  on 
set feedback on 
set verify   on 

Reader Feedback:

Ugo M. writes: I've found an issue in this tip. The "Total SGA Mbyte" appears to be wrong due to the use of the variable total. To correct this, I've modified the script in this way:

clear column breaks 
set pagesize 66 
set heading  on 
set pause    off 
set feedback off 
set verify   off 
set lines    100
rem ORACLE V8.0.4, default 15 
set arraysize 12 

column global_name       format a16      heading "Global Name" 
column created           format a9       heading "Database|Creation Date" 
column log_mode          format a10      heading Database|Mode 
column startup_time      format a13      heading "Database|Startup Time" 
column db_name           format a8       heading Database|Name 
column machine           format a7       heading Login|Host 
column logins            format a10      heading Access|Restricted 
column db_block_sizes    format a10      heading "Database|Block Size" 
column archiver          format a7       heading Arch|Process 
column audits            format a6       heading Audit|Trail 
column sql_traces        format a6       heading SQL|Trace 
column statistics        format a10      heading Timed|Statistics 
column sessions_current  format 9,999    heading Total|Users 
column shared_mbytes     format 999,990  heading "Free SGA|(SH)Mbyte" 
column large_mbytes      format 999,990  heading "Free SGA|(LG)Mbyte" 
column optimizer_modes   format a10      heading Optimizer|Mode 
column remote_os_authent format a6       heading "Rmt OS|Auth" 
column rmt_password      format a5       heading Rmt|Paswd 
column os_authent_prefix format a7       heading "OS Auth|Prefix" 
column os_roles          format a5       heading OS|Roles 
column db_domain         format a15      heading Database|Domain 
column global_names      format a6       heading Global|Names 
column total_mbytes      format 999,990  heading "Total SGA|Mbyte" 
column user              format a10      heading Login|User 
column parallel_q_server format a8       heading "Parallel|Qry Svr" 
column mts_servers       format a5       heading MTS|Svr 

column total noprint new_value  total format 9,999  heading "Total|Mbytes" 

rem clear screen 

rem    Total SGA 
select sum(s1.value)/1048576 total 
from   v$sga s1; 

rem PMON : sid=1 
select p1.value db_name , substr(d.log_mode,1,10) log_mode, g.global_name, 
       s.machine, 
       to_char(i.startup_time,'dd-MON HH24:MI') startup_time, 
       d.created, p2.value db_block_sizes 
from v$session s,global_name g, v$database d, v$parameter p1,v$parameter p2,v$instance i 
where s.sid=1 
and   p1.name = 'db_name' 
and   p2.name = 'db_block_size'; 

select substr(i.logins,1,10) logins, i.archiver, p1.value audits, 
       p2.value sql_traces, p3.value statistics, p4.value optimizer_modes, 
       p5.value os_roles, 
       l.sessions_current, user 
from   v$instance i, v$parameter p1, v$parameter p2, v$parameter p3, 
       v$parameter p4, v$parameter p5, v$license l 
where p1.name = 'audit_trail' 
and   p2.name = 'sql_trace' 
and   p3.name = 'timed_statistics' 
and   p4.name = 'optimizer_mode' 
and   p5.name = 'os_roles'; 

column value$   format a12 heading "Current|Value" 
column comment$ format a13 heading "Comment" word_wrapped 
column name format a17 heading "Name" 
rem set linesize 130 
Prompt 
select d.name||' is in '||open_mode ||' mode.'"DB Mode",a.name,a.value$,a.comment$ 
FROM sys.props$ a,v$database d 
WHERE a.name like 'NLS_CHAR%'; 
Prompt 

select p1.value db_domain, 
       p2.value global_names, 
       p3.value os_authent_prefix, 
       p4.value remote_os_authent, 
       p5.value rmt_password, 
       p6.value parallel_q_server, 
       p7.value mts_servers, 
       total1 total_mbytes, 
       s1.bytes/1048576 shared_mbytes 
from   v$parameter p1, v$parameter p2, v$parameter p3, 
       v$parameter p4, v$parameter p5, v$parameter p6, v$parameter p7, 
       v$sgastat s1, (select sum(s2.value)/1048576 total1 from   v$sga s2)
where p1.name = 'db_domain' 
and   p2.name = 'global_names' 
and   p3.name = 'os_authent_prefix' 
and   p4.name = 'remote_os_authent' 
and   p5.name = 'remote_login_passwordfile' 
and   p6.name = 'parallel_min_servers' 
and   p7.name = 'mts_servers' 
and   s1.name = 'free memory' 
and   s1.pool ='shared pool'; 

rem This may be missing 
select s2.bytes/1048576 large_mbytes 
from v$sgastat s2 
where s2.name = 'free memory' 
and   s2.pool ='large pool'; 

set heading  on 
set feedback on 
set verify   on 

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, PL/SQL, Oracle administration and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions -- or help out your peers by answering them -- in our forums.
• Best Web Links: Oracle tips, tutorials and scripts from around the Web.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.