General Tips

Delete 10 days old files

i=0
for FILE in `find /home1/amritesh/dbAdmin/dumps -name '*.dmp' -mtime +10 -print`
do
echo "Deleting $FILE ..."
rm -f $FILE
((i=i + 1))
done
#echo "Deleted $i files"

Table/Index reorganization by using procedure

CREATE OR REPLACE PROCEDURE USP_TAB_REORG
AS
CURSOR TAB_CUR IS
SELECT OWNER '.' TABLE_NAME AS TAB FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM');
v_sqlstring varchar2(4000);
v_sqlstring1 varchar2(4000);
BEGIN
FOR I IN TAB_CUR loop
SELECT 'ALTER TABLE' ' ' I.TAB ' ' ' ENABLE ROW MOVEMENT' INTO v_sqlstring FROM DUAL;
SELECT 'ALTER TABLE' ' ' I.TAB ' ' ' SHRINK SPACE' INTO v_sqlstring1
FROM DUAL;
If v_sqlstring is not null then
dbms_output.put_line(v_sqlstring);
dbms_output.put_line(v_sqlstring);
EXECUTE IMMEDIATE v_sqlstring;
EXECUTE IMMEDIATE v_sqlstring1;
End if;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Unable to execute.');
END;
/

View Oracle command column within v$session

Question: How do I translate the "command" column within v$session to see the command that is associated with the session.

Answer: You can use the CASE or DECODE command to translate these values and here is a sample script to see the command columns within v$session. For a full listing of scripts, see the Oracle scripts collection:

select
s.sid,
p.spid,
substr(s.program,1,20) program,
decode(s.command,
1,'Create table' , 2,'Insert',
3,'Select' , 6,'Update',
7,'Delete' , 9,'Create index',
10,'Drop index' ,11,'Alter index',
12,'Drop table' ,13,'Create seq',
14,'Alter sequence' ,15,'Alter table',
16,'Drop sequ.' ,17,'Grant',
19,'Create syn.' ,20,'Drop synonym',
21,'Create view' ,22,'Drop view',
23,'Validate index' ,24,'Create procedure',
25,'Alter procedure' ,26,'Lock table',
42,'Alter session' ,44,'Commit',
45,'Rollback' ,46,'Savepoint',
47,'PL/SQL Exec' ,48,'Set Transaction',
60,'Alter trigger' ,62,'Analyze Table',
63,'Analyze index' ,71,'Create Snapshot Log',
72,'Alter Snapshot Log' ,73,'Drop Snapshot Log',
74,'Create Snapshot' ,75,'Alter Snapshot',
76,'drop Snapshot' ,85,'Truncate table',
0,'No command', '? : '||s.command) nocommand
from
v$session s;

Useful Unix command for oracle DBA beginners:

Basic File Navigation

The "pwd" command displays the current directory:

root> pwd

 /u01/app/oracle/product/9.2.0.1.0

The "ls" command lists all files and directories in the specifieddirectory.

If no location is defined it acts on the current directory:
root> ls
root> ls /u01
root> ls -al
                      The "-a" flag lists hidden "." files.
                      The "-l" flag lists file details.

The "cd" command is used to change directories:

root> cd /u01/app/oracle

The "touch" command is used to create a new empty file with the default permissions:

root> touch my.log

The "rm" command is used to delete files and directories:

root> rm my.log
root>rm -R /archive

The "-R" flag tells the command to recurse through subdirectories.

The "mv" command is used to move or rename files and directories:

root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .

The "." represents the current directory.

The "cp" command is used to copy files and directories:

root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .

The "mkdir" command is used to create new directories:

root> mkdir archive

The "rmdir" command is used to delete directories:

root> rmdir archive

The "find" command can be used to find the location of specific files:

root> find / -name dbmspool.sql
root> find / -print grep -i dbmspool.sql
The "/" flag represents the staring directory for the search.
Wildcards such as "dbms*" can be used for the filename.

The "which" command can be used to find the location of an executableyou are using:

oracle> which sqlplus
The "which" command searches your PATH setting for occurrences of the specified executable.

File Permissions

The "umask" command can be used to read or set default file permissions for the current user:
root> umask 022
The umask value is subtracted from the default permissions (666) to give the final permission:
666 : Default permission
022 : - umask value
644 : final permission

The "chmod" command is used to alter file permissions after the file has been created:

root>chmod 777 *.log

Owner Group World Permission

7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute
5 (u+Rx) 5 (g+Rx) 5 (o+Rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character eqivalents can be used in the chmod command:
root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.log

The "chown" command is used to reset the ownership of files after creation:

root> chown -R oinstall.dba *
The "-R" flag causes the command ro recurse through any subdirectories.

OS Users Management

The "useradd" command is used to add OS users:
root> 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.

The "usermod" command is used to modify the user settings after a user has been created:

root> usermod -s /bin/csh my_user

The "userdel" command is used to delete existing users:

root>userdel -r my_user
The "-r" flag removes the default directory.

The "passwd" command is used to set, or reset, the users login password:

root>passwd my_user

The "who" command can be used to list all users who have OS connections:

root> who
root> who head -5
root> who tail -5
root> who grep -i ora
root> who wc -l

* The "head -5" command restricts the output to the first 5 lines of the who command.
* The "tail -5" command restricts the output to the last 5 lines of the who command.
* The "grep -i ora" command restricts the output to lines containing "ora".
* The "wc -l" command returns the number of lines from "who", and hence the number of
connected users.

Process Management

The "ps" command lists current process information:
root> ps
root> ps -ef grep -i ora

Specific processes can be killed by specifying the process id in the kill command:

root> kill -9 12345

uname and hostname

The "uname" and "hostname" commands can be used to get informationabout the host:
root> uname -a
rhel12 rhel12.inida.in Vxxxx xxxxx

root> uname -a awk '{ print $2 }'
rhel12.inida.in

root>hostname
rhel12.inida.in

Error Lines in Files

You can return the error lines in a file using:
root>cat alert_LIN1.log grep -i ORA-
The "grep -i ORA-" command limits the output to lines containing "ORA-".
The "-i" flag makes the comparison case insensitive.

A count of the error lines can be returned using the "wc" command.

This normally give a word count, but the "-l" flag alteres it to give a line count:
root>cat alert_LIN1.log grep -i ORA- wc -l

File Exists Check

The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:
#!/bin/ksh
if test -s /backup/daily_backup.log
then
DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
mv /backup/daily_backup.log
/backup/archive/daily_backup$DATE_SUFFIX.log

Mount Point Addition

step 1:login as a root
step 2:df -g
step 3:if not there,lsvg (display like:rootvg ; not /backup)
step 4:lsvg -o
step 5:varyonvg backupvg
step 6:lsvg -o

step 7:mount /backup