The oraenv and coraenv utilities both aid in setting the Oracle environment on UNIX systems (other utilities exist on Windows platform that enable the Oracle Home to be set.) The coraenv utility is appropriate for the UNIX C Shell; oraenv should be used with either the Bourne or Korn shells.
Database operations require the ORACLE_HOME to be set before the user may access the database. If ORACLE_HOME is not set, commands such as sqlplus, exp, or any other utility for that matter, will not be found.
Both utilities are shell scripts that do the same thing in the different UNIX shells. They will prompt for a SID of the database unless ORAENV_ASK is set to N. The utility will also append the ORACLE_HOME value to the path, marking the location of the utility.
The oraenv command will prompt for the SID of the database that you wish $ORACLE_HOME to access.
$ . oraenv
ORACLE_SID = [] ? ASG920
The dbhome utility can now be used to verify that $ORACLE_HOME is correct.
$ dbhome
/usr/oracle/9.2.0
The “dot space” part of the command is required to make the environment change with the parent shell, as opposed to entering a command without it which would only affect the subshell running that process.
These commands can be used to avoid specifying the network service name when issuing commands. For instance, without using oraenv, an sqlplus command would look like:
$ sqlplus system/manager@nameofservice as sysdba
whereas after oraenv has been executed, the following command would work:
$ sqlplus system/manager as sysdba
Wednesday, December 3, 2008
Tuesday, December 2, 2008
Wait events in Oracle
When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen befor it can proceed. For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding database block is not currently in the SGA, Oracle waits for this block to be available for modification. All possible wait events can be found in v$event_name. In Oracle 10g R1, there are some 806 different wait events. What Oracle waits for and how long it has totally waited for these events can be monitored through the following views:
v$session_event
v$session_wait
v$system_event
Important events
Important events are:
.buffer busy waits
.db file scattered read
.db file sequential read
.free buffer waits
.latch free
.log buffer space
.log file sync
.enqueue
.SQL*Net more data from client
.SQL*Net more data to client
buffer busy waits
If two processes try (almost) simultaneausly the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache and lock it and the read the block into the buffer. The other process is locked until the block is read. This wait is refered to as buffer busy wait.
db file scattered read
A process reads multiple blocks (mostly as part of a full table scan or an index fast full scan). It can also indicate a multiblock read when the process reads parts of a sort segement.
db file single block read
db file sequential read
In most cases, this event means that a foreground process reads a single block (because it reads a block from an index or because it reads a block by rowid).
direct path read
enqueue
The enqueue wait event can be queried through v$enqueue_stat. See also enqueue types in x$ksqst
free buffer waits
See also optimal size of block buffer.
latch free
log buffer space
This wait event indicates that the size of the log buffer is chosen too small.
log file sync
SQL*Net more data from client
SQL*Net more data to dblink
Wait classes
Wait events can be categorized by wait classes. These classes are exposed through v$session_wait_class. The following wait classes exist:
.Administrative
.Application
.Cluster
.Concurrency
.Configuration
.Commit
.Idle Waits
.Network
.Other
.System I/O
.Scheduler
.User I/O
Parameters
The parameters P1, P2 and P3 in v$session_wait are dependent on the wait. P1 refers sometimes to the datafile number.
If this number is greater than db_files, it refers to a temp file. The name of the datafile for a number can be retrieved through v$datafiles.
v$session_event
v$session_wait
v$system_event
Important events
Important events are:
.buffer busy waits
.db file scattered read
.db file sequential read
.free buffer waits
.latch free
.log buffer space
.log file sync
.enqueue
.SQL*Net more data from client
.SQL*Net more data to client
buffer busy waits
If two processes try (almost) simultaneausly the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache and lock it and the read the block into the buffer. The other process is locked until the block is read. This wait is refered to as buffer busy wait.
db file scattered read
A process reads multiple blocks (mostly as part of a full table scan or an index fast full scan). It can also indicate a multiblock read when the process reads parts of a sort segement.
db file single block read
db file sequential read
In most cases, this event means that a foreground process reads a single block (because it reads a block from an index or because it reads a block by rowid).
direct path read
enqueue
The enqueue wait event can be queried through v$enqueue_stat. See also enqueue types in x$ksqst
free buffer waits
See also optimal size of block buffer.
latch free
log buffer space
This wait event indicates that the size of the log buffer is chosen too small.
log file sync
SQL*Net more data from client
SQL*Net more data to dblink
Wait classes
Wait events can be categorized by wait classes. These classes are exposed through v$session_wait_class. The following wait classes exist:
.Administrative
.Application
.Cluster
.Concurrency
.Configuration
.Commit
.Idle Waits
.Network
.Other
.System I/O
.Scheduler
.User I/O
Parameters
The parameters P1, P2 and P3 in v$session_wait are dependent on the wait. P1 refers sometimes to the datafile number.
If this number is greater than db_files, it refers to a temp file. The name of the datafile for a number can be retrieved through v$datafiles.
About your Database
select banner "Version of Your Data Base" from v$version;
set pause on
select name "Data Base Name" from v$database;
set pages 80
select log_mode "Your DB in " from v$database;
col "Parameters Mention in SP File" for a35
col value for a25
select name "Parameters Mention in SP File",value from v$spparameter
where ordinal=1 order by name;
col "Parameters of All Dumps" for a25
col value for a30
select upper(NAME) "Parameters of All Dumps",value from v$spparameter
where name in ('user_dump_dest','background_dump_dest','core_dump_dest');
select round(sum(bytes)/1024/1024,2) as "Size of DB in MB" from
(select sum(bytes) bytes
from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(l.bytes) bytes from v$log l,v$logfile f where l.group#=f.group#);
select round(sum(bytes)/1024/1024,2) "Used Space of DB in MB's" from sys.sm$ts_used;
select round(sum(bytes)/1024/1024,2) "Free Space of DB in MB's" from sys.sm$ts_free;
select tablespace_name,bytes/1024/1024 "SIZE IN MB" from sm$ts_avail;
col "Default Temporary Tablespace" for a35
select property_value "Default Temporary Tablespace" from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
select name "Control files Location in DB" from v$controlfile;
select count(*) as "Number of Redo Groups" from v$log;
col "Redo Members Location in DB" for a50
select group# "Group No.",member "Redo Members Location in DB" from v$logfile;
select username "Data Base Default Users" from dba_users where rownum<5;
select username "Users Created by SYS" from dba_users
where username not in ('SYS','SYSTEM','DBSNMP','OUTLN');
select sum(decode(object_type,'TABLE',1,0)) "SYS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYS';
select sum(decode(object_type,'TABLE',1,0)) "SYSTEM :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYSTEM';
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
col b.tablespace for a15
col SIZE for a5
col SID_SERIAL for a10
col a.username for a15
col a.program for a15
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,a.program FROM sys.v_$session a,
sys.v_$sort_usage b,sys.v_$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
col sid_serial for a10
col orauser for a10
col program for a25
col undoseg for a10
col undo for a10
SELECT TO_CHAR(s.sid)||'-'||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
set verify off
select sum(decode(object_type,'TABLE',1,0)) "USERS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCE',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYM',1,0)) "SYNONYMS"
from dba_objects where owner='&USER';
set pause on
select name "Data Base Name" from v$database;
set pages 80
select log_mode "Your DB in " from v$database;
col "Parameters Mention in SP File" for a35
col value for a25
select name "Parameters Mention in SP File",value from v$spparameter
where ordinal=1 order by name;
col "Parameters of All Dumps" for a25
col value for a30
select upper(NAME) "Parameters of All Dumps",value from v$spparameter
where name in ('user_dump_dest','background_dump_dest','core_dump_dest');
select round(sum(bytes)/1024/1024,2) as "Size of DB in MB" from
(select sum(bytes) bytes
from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(l.bytes) bytes from v$log l,v$logfile f where l.group#=f.group#);
select round(sum(bytes)/1024/1024,2) "Used Space of DB in MB's" from sys.sm$ts_used;
select round(sum(bytes)/1024/1024,2) "Free Space of DB in MB's" from sys.sm$ts_free;
select tablespace_name,bytes/1024/1024 "SIZE IN MB" from sm$ts_avail;
col "Default Temporary Tablespace" for a35
select property_value "Default Temporary Tablespace" from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
select name "Control files Location in DB" from v$controlfile;
select count(*) as "Number of Redo Groups" from v$log;
col "Redo Members Location in DB" for a50
select group# "Group No.",member "Redo Members Location in DB" from v$logfile;
select username "Data Base Default Users" from dba_users where rownum<5;
select username "Users Created by SYS" from dba_users
where username not in ('SYS','SYSTEM','DBSNMP','OUTLN');
select sum(decode(object_type,'TABLE',1,0)) "SYS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYS';
select sum(decode(object_type,'TABLE',1,0)) "SYSTEM :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYSTEM';
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
col b.tablespace for a15
col SIZE for a5
col SID_SERIAL for a10
col a.username for a15
col a.program for a15
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,a.program FROM sys.v_$session a,
sys.v_$sort_usage b,sys.v_$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
col sid_serial for a10
col orauser for a10
col program for a25
col undoseg for a10
col undo for a10
SELECT TO_CHAR(s.sid)||'-'||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
set verify off
select sum(decode(object_type,'TABLE',1,0)) "USERS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCE',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYM',1,0)) "SYNONYMS"
from dba_objects where owner='&USER';
ORAINSTROOT.SH
ORAINSTROOT.SH
Inventory Location
/oracle/oraInventory
(OK]
UNIX Group Name (permission for updating Oracle software):
oinstall
[Next)
a popup appears, saying:
Certain actions need to be performed with root privileges before the install can continue.
These actions are stored in a Bourne Shell script called /tmp/orainstRoot.sh
Please execute the /tmp/orainstRoot.sh script now from another window
and press "Continue" to continue the install.
in a root shell:
# /tmp/orainstRoot.shThe script is verbosing:
Creating Oracle Inventory pointer file (/etc/oraInst.loc)Changing groupname of /oracle/oraInventory to oinstall.Then ( OK ) on the popup window
Inventory Location
/oracle/oraInventory
(OK]
UNIX Group Name (permission for updating Oracle software):
oinstall
[Next)
a popup appears, saying:
Certain actions need to be performed with root privileges before the install can continue.
These actions are stored in a Bourne Shell script called /tmp/orainstRoot.sh
Please execute the /tmp/orainstRoot.sh script now from another window
and press "Continue" to continue the install.
in a root shell:
# /tmp/orainstRoot.shThe script is verbosing:
Creating Oracle Inventory pointer file (/etc/oraInst.loc)Changing groupname of /oracle/oraInventory to oinstall.Then ( OK ) on the popup window
Subscribe to:
Comments (Atom)