Some Useful Oracle Commands

Currently running Jobs in Oracle
SELECT sid, r.job, log_user, r.this_date, r.this_sec
FROM dba_jobs_running r, dba_jobs j
WHERE r.job = j.job
/

Lists all of the supported INIT.ORA parameters in Oracle

SELECT a.ksppinm name, b.ksppstvl VALUE, b.ksppstdf isdefault,
       DECODE(a.ksppity, 1, ‘boolean’, 2, ‘string’, 3, ‘number’, 4, ‘file’,
           a.ksppity) TYPE,
       a.ksppdesc description
FROM   sys.x$ksppi a, sys.x$ksppcv b
WHERE  a.indx = b.indx
  AND  a.ksppinm NOT LIKE ‘_%’ escape ”
order  by name
/

Find details of dba_data_files in Oracle

SET pages 99
col file_name format a45
col tablespace_name format a13
col tablespace_name heading ts_name
col blocks format 999,999
SELECT file_name,tablespace_name,bytes,blocks
FROM dba_data_files
ORDER BY tablespace_name,file_name
/

SQL query  to find Oracle Active Processes

SET pagesize 55;
SET linesize 170;
col SQL format a80;
col SERVER heading ‘SVR’ format a3;
col EVENT heading ‘WAITING’ format a30 fold_after;
col OSUSER heading ‘OSUSER’ format a8;
col USERNAME heading ‘USERNAME’ format a8;
col PID heading ‘OSPID’ format 99999;
col DISK_READS heading ‘DISK I/O’ format 99999999;
col BUFFER_GETS heading ‘BUFFER|GETS’ format 99999999;

SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
     V$SESSION.OSUSER OSUSER,
–        DECODE(V$SESSION.SERVER,’DEDICATED’,'D’,'SHARED’,'S’,'O’) SERVER,
     V$SQLAREA.DISK_READS DISK_READS,
     V$SQLAREA.BUFFER_GETS BUFFER_GETS,
        SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
     V$SESSION.PROCESS PID,
     V$SESSION_WAIT.EVENT EVENT,
        V$SQLAREA.SQL_TEXT SQL
FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE   V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
     V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
        V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
     V$SESSION.STATUS = ‘ACTIVE’ AND
     V$SESSION_WAIT.EVENT != ‘client message’
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

Useful UNIX Command For Oracle DBA

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory

find . -print |grep -i test.sql

 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

 Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

 Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

 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

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

Swap space allocation and usage

Solaris : swap -s or swap -l

Aix : lsps -a

 Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''

 Show mount points for a disk in AIX

lspv -l hdisk13

 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

 Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

 Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

 Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

 Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Data Type Comparison (Oracle and MySQL)

Whenever you work Database design, Database Migration, You should be aware about data type, Storage required by data type.   Whenever you work on migration, you should know the equivalent data type between existing definitions.

Below is the quick comparison of Oracle Data type with MySQL Data type.

Default Data Type Mappings

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19, 0)
BIT	RAW
BLOB	BLOB, RAW
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT (24)
DOUBLE	FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM	VARCHAR2
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
LONGBLOB	BLOB, RAW
LONGTEXT	CLOB, RAW
MEDIUMBLOB	BLOB, RAW
MEDIUMINT	NUMBER(7, 0)
MEDIUMTEXT	CLOB, RAW
NUMERIC	NUMBER
REAL	FLOAT (24)
SET	VARCHAR2
SMALLINT	NUMBER(5, 0)
TEXT	VARCHAR2, CLOB
TIME	DATE
TIMESTAMP	DATE
TINYBLOB	RAW
TINYINT	NUMBER(3, 0)
TINYTEXT	VARCHAR2
VARCHAR	VARCHAR2, CLOB
YEAR	NUMBER

Numeric Types

MySQL	Size	Oracle
BIGINT	8 Bytes	NUMBER (19,0)
BIT	approximately (M+7)/8 Bytes	RAW
DECIMAL(M,D)	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)	FLOAT(24), BINARY_FLOAT
DOUBLE	8 Bytes	FLOAT(24), BINARY_FLOAT, BINARY_DOUBLE
DOUBLE PRECION	8 Bytes	FLOAT(24), BINARY_DOUBLE
FLOAT(25<=X <=53)	8 Bytes	FLOAT(24), BINARY_FLOAT
FLOAT(X<=24)	4 Bytes	FLOAT, BINARY_FLOAT
INT	4 Bytes	NUMBER (10,0)
INTEGER	4 Bytes	NUMBER (10,0)
MEDIUMINT	3 Bytes	NUMBER (7,0)
NUMERIC	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)	NUMBER
REAL	8 Bytes	FLOAT(24), BINARY_FLOAT
SMALLINT	2 Bytes	NUMBER(5,0)
TINYINT	1 Byte	NUMBER(3,0)

Date and Time Types

MySQL	Size	Oracle
DATE	3 Bytes	DATE
DATETIME	8 Bytes	DATE
TIMESTAMP	4 Bytes	DATE
TIME	3 Bytes	DATE
YEAR	1 Byte	NUMBER

String Types

MySQL	Size	Oracle
BLOB	L + 2 Bytes whereas L<2^16	RAW, BLOB
CHAR(m)	M Bytes, 0<=M<=255	CHAR
ENUM (VALUE1, VALUE2, ...)	1 or 2 Bytes depending on the number of enum. values (65535 values max)
LONGBLOB	L + 4 Bytes whereas L < 2 ^ 32	RAW, BLOB
LONGTEXT	L + 4 Bytes whereas L < 2 ^ 32	RAW, CLOB
MEDIUMBLOB	L + 3 Bytes whereas L < 2^ 24	RAW, BLOB
MEDIUMTEXT	L + 3 Bytes whereas L < 2^ 24	RAW, CLOB
SET (VALUE1, VALUE2, ...)	1, 2, 3, 4 or 8 Bytes depending on the number of set members (64 members maximum)
TEXT	L + 2 Bytes whereas L<2^16	VARCHAR2, CLOB
TINYBLOB	L + 1 Bytes whereas L<2 ^8	RAW, BLOB
TINYTEXT	L + 1 Bytes whereas L<2 ^8	VARCHAR2
VARCHAR(m)	L+1 Bytes whereas L<=M and0<=M<=255 before MySQL 5.0.3 (0 <= M <= 65535 in MySQL 5.0.3 and later; effective maximum length is 65,532 bytes)	VARCHAR2, CLOB