No . of Insertions in the Database

 select substr(sql_text,instr(sql_text,'INTO "PRO'),50) Inserts,
         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from   sys.v_$sqlarea
  where  upper(sql_text) like 'INSERT %' and sql_text like '%SL%';

UNIX Commands For ORACLE

list commands:

ls –ltr

cat alert_LIN1.log | grep -i ORA-

cat /proc/cpuinfo

process commands:

ps –ef |grep “ora_”

ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID

ps -ef |awk '{print $1}'|sort -u

change dir :

cd ..

view command:

vi

view

more

Find the CPU count :

grep -c processor /proc/cpuinfo

uname –X

/usr/sbin/psrinfo |wc –l

/usr/sbin/psrinfo -p

Memory chechecking

dmesg | grep Memory

cat /proc/meminfo

AIX $ /usr/sbin/lsattr -E -l sys0 -a realmem
Linux $ /usr/sbin/dmesg | grep "Physical:"
HP $ grep MemTotal /proc/meminfo
Solaris $ /usr/sbin/prtconf | grep "Memory size"
Tru64 $ /bin/vmstat -P | grep "Total Physical Memory"

Display RAM Memory size on Solaris
prtconf |grep -i mem

:

Display RAM memory size on AIX
First determine name of memory device
lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’
lsattr -El mem0


Display the number of CPU’s in Solaris
psrinfo -v | grep “Status of processor”|wc –l

Display the number of CPU’s in AIX
lsdev –C | grep Process|wc –l

cpu performance:

TOP

TOPAS

SAR

$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):

Time	%usr	%sys	%wio	%idle
11:57:31	72	28	0	0
11:57:41	70	30	0	0
11:57:51	70	30	0	0
11:58:01	68	32	0	0
11:58:11	67	33	0	0
11:58:21	65	28	0	7
11:58:31	73	27	0	0
11:58:41	69	31	0	0
Average	69	30	0	1

%usr: Percent of CPU in user mode

%sys: Percent of CPU in system mode

%wio: Percent of CPU running idle with a process waiting for block I/O

%idle: Percent of CPU that is idle

SAR  -u  <CPU report >

Sar –d <disk usage >

to create the file :

touch

cat

disk free:

df -k

disk usage:

du -sg

remove the file

rm -rf

remove dir:

rmdir

rm -rf

copy or move

cp <source to dist>

move <sorce to dest>

cp -r <inclusing sub dir>

last and first lines:

tail -n filename

head -n filename

echo <variable>

chmode -R 777 <filename>

chown oracle:dba <dir name>

chgrp filename

FIND and REMOVE :

find /oracle/sftw -name <filename>

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;

FTP commands:

ftp servername

ftp>binary

get

put

cd

!pwd

pwd

bye

ls

Mail commands:

mail -s "Test mail"  "sss@gmail.com" < msg.log

who

kill -9 <process id>

hostname

uname -a

telnet <hostname>

ifconfig

jobs

sysreserve

User management :

useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

• The "-G" flag specifies the primary group.
• The "-g" flag specifies the secondary group.
• The "-d" flag specifies the default directory.
• The "-m" flag creates the default directory.
• The "-s" flag specifies the default shell.

usermod -s /bin/csh my_user

userdel -r my_user

passwd my_user

wc –l ===è gives the count                         

COMPRESS the files :

Gzip <filename >

Zip <filename>

Compress <filename>

Gunzip < filename >

Unzip <filename>

Uncompress < filename >

SYSTEM statistics :

VMSTAT

DISPLAY < hostname > :<port no >

Export DISPLAY

Tar –cvf <filename>.tar <file/dir loc>

Tar –xvf <filrname>.tar

History

Date

Time

Whoiam

Diff

At

Crontab

Uptime --- server uptime

Export Exit Codes

INTRODUCTION
============
This is a short reference note describing the EXIT codes (errorlevel)
used by the Export and Import utilities.

EXIT CODES
==========
A. Successful export/import.
   -------------------------
   Result    : Export terminated successfully without warnings
               Import terminated successfully without warnings
   Exit Code : EX_SUCC
   Exit Level: 0
B. Successful export/import with warnings.
   ---------------------------------------
   Result    : Export terminated successfully with warnings
               Import terminated successfully with warnings
   Exit Code : EX_OKWARN
   Exit Level: 0 (for Unix platforms)
               1 (for Windows platform with Oracle9i, Oracle8i, and below)
               3 (for Windows platform with Oracle10g and higher)
C. Unsuccessful export/import.
   ---------------------------
   Result    : Export terminated unsuccessfully
               Import terminated unsuccessfully
   Exit Code : EX_FAIL
   Exit Level: 1 (for Unix platforms, and for Windows platforms with
                  Oracle10g and higher)
               3 (for Windows platform with Oracle9i, Oracle8i, and below)

CHECK
=====
To determine the errorlevel at the commandline after a command ends:
- Unix Bourne/Korn shell:
 echo $?
- Unix C Shell:
 echo $status
- Windows:
 echo %ERRORLEVEL%

echo %ORACLE_SID%

SET COMMANDS

ECHO {OFF|ON}
   Display commands as they are executed

HEA[DING] {OFF|ON}
   print column headings

LIN[ESIZE] {150|n}
   Width of a line (before wrapping to the next line)
   Earlier versions default to 80, Oracle 9 is 150

LONG {80|n}
   Set the maximum width (in chars) for displaying and copying LONG values.

PAGES[IZE] {14|n}
   The height of the page - number of lines.
   0 will suppress all headings, page breaks, titles

set timing on;
set time off;

DBMS_JOBS examples

--
-- Schedule a snapshot to be run on this instance every hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 'statspack.snap;',
trunc(sysdate)+6/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+9/24,
'trunc(SYSDATE+12/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/144,'MI'),
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);
-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE,''MONDAY''),
next_day(SYSDATE,''TUESDAY''),
next_day(SYSDATE,''WEDNESDAY''),
next_day(SYSDATE,''THURSDAY''),
next_day(SYSDATE,''FRIDAY'')
)
+1/24,''HH'')',
TRUE,
:instno);
commit;
end;
/

Piped EXPORT/IMPORT

mknod is used to create special files,

*  Create a block-type special file
*  Create a character-type special file
*  Create a FIFO (named pipe)


$mknod name b major minor
Creates a block-type special file
major, minor are device numbers

$mknod name c major minor
Creates a character-type special file

$mknod name p
Creates a FIFO named pipe.

Examples:
If datafiles are larger than 2GB, pipes can be used to read or extract information
from compressed datafiles.

In Oracle a table can contain more than 2GB of data, but lower versions of Solaris 2.x
doesn't support larger files. We can use unix pipes to export data and compress. Similar
way we can import data without uncomressing an export dump file.

Below is an example of how to use UNIX's named pipes for an exp/imp:

Creating a compressed export file

   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ compress < /tmp/exp_pipe > export.dmp.Z & # Compress
   $ exp file=/tmp/exp_pipe &ltother options>   # Export to the pipe
 
Reading a compressed export file

   $ mknod /tmp/imp_pipe p                       # Create pipe
   $ uncompress < export.dmp.Z > /tmp/imp_pipe & # Uncompress
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe
 
Exporting to tape via unix named pipes
  
   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ dd if=/tmp/exp_pipe of=< tape device> &    # Write from pipe to tape
   $ exp file=/tmp/exp_pipe < other options >  # Export to the pipe 

Importing from tape via unix named pipes

   $ mknod /tmp/imp_pipe p                     # Create pipe
   $ dd of=/tmp/imp_pipe if=< tape device> &    # Write from tape to pipe
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe 

SQL*Loading from tape via unix named pipes

   $ mknod /tmp/ldr_pipe p                         # Create pipe
   $ dd of=/tmp/ldr_pipe if=< tape device> &        # Write from tape to pipe
   $ sqlload data=/tmp/ldr_pipe < other options >  # Load from the pipe 

To Check Last analyzed table date ...........

select count(1),owner,trunc(last_analyzed) from dba_tables where owner not in ('SYS','SYSTEM','OUTLN','PERSTAT') group by owner,trunc(last_analyzed) order by owner,trunc(last_analyzed);