Tip

Database settings at a glance

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

    Requires Free Membership to View

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.

This was first published in July 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

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.