Unix Tutorials

df: Free disk space

df command reports free disk space for each file system separately

df -h: Reporting large units like MB/GB.

df -h / /usr : Reports on / and /usr file systems

du: Disk usage in a directory(not entire file system)
du -s: summary of disk usage

du -s /home/*
It will return space consumed by each user

uname: What's my Unix system name, OS,CPU? [anandy@traninteractive-1101]~% uname -a
Linux traninteractive-1101.vdc.amazon.com 2.6.18-128.1.14.el5a02xen #1 SMP Thu Jun 25 08:09:23 UTC 2009 i686 athlon i386 GNU/Linux
finger : Account info of logged in users Difference b/w echo ** and echo * * [anandy@traninteractive-1101]~% echo **
afiedt.buf db dbtool dbtool.tar drop.xsl ed.hup lat1.sql lat.sql myqueue.sql mytool new_dbtool out progname.pl remedy remote-command Shipping Sqllogin.sh sqlnet.log
[anandy@traninteractive-1101]~% echo * *
afiedt.buf db dbtool dbtool.tar drop.xsl ed.hup lat1.sql lat.sql myqueue.sql mytool new_dbtool out progname.pl remedy remote-command Shipping Sqllogin.sh sqlnet.log afiedt.buf db dbtool dbtool.tar drop.xsl ed.hup lat1.sql lat.sql myqueue.sql mytool new_dbtool out progname.pl remedy remote-command Shipping Sqllogin.sh sqlnet.log
Create group and user: Group: sudo groupadd -g 241 amazon User: sudo useradd -u 211 -g amazon -c "amazon group" -d /home/anand -s /bin/ksh -m anand
cd /home/anand sudo mkdir perl chown -hR anand:amazon perl Inode:
how to find out inode of a file?
ls -1

How to find out hard links of a file?
ls -i

Example:
Anand> ls -li
Hard Links:
How to create hard link:
ln original_file Linked_file
ln Anand.txt linkedAnand.txt
ln Anand.txt ../Anand.txt

If two files have same inode, it means those file are same but different link created for that file.If you make changes to one file , it will automatically reflect to another file.

Copy of same file will have different inode number.

Benefits of hard links:
Re-usability,maintainability,security

Limitations of Hard link:
1. You can't have two linked file names in two different systems. Example one in /usr and another in /home.
2. You can't link a directory even within the same file system.

Symbolic links:(Soft links):Symbolic links can be created across the file systems. Symbolic link will have different inode number.

How to create symbolic link: ln -s File1 sm_Files

Symbolic link can be identified by character l in the permissions field. It's length is characters of actual file.
Permissions of a directory:
read(r): if you don't have read permissions, you cannot ls a directory.

Write(w): If you don't have write permissions to a directory, you cannot create or remove files from a directory.

Execute (x): If you don't have exe permissions of a dir. you cannot cd inside it or cat inside directories.It is kind of search permissions.

* To create or remove a file inside a dir. you should have write and execute both permissions.

Modifications and access time:Time of last modification: ls -l
Time of last access: ls -lu
Time of last inode modification: ls -lc

Touch:touch emp.lst: creates new file if it does not exists.

find: command to find files satisfying specified criteria.

It recursively examines a directory tree to look for files matching criteria and take some action on the selected files.

Syntax: find path_list selection_criteria action

Example seach name saratrag in all files come under home directory.
[anandy@traninteractive-1101]/home% pwd
/home
[anandy@traninteractive-1101]/home% find ./-name saratrag -print

Note*: all file operators start with - and path_list can never contain one.

Find selection criteria:find command to return files above 1 MB.
find /home -size +2048 -print
find command to return files above 1 MB and below 4 MB
find /home -size +2048 size -8192 -print

Q.Search all files with extension .c starting from current dir.
Ans: find . -name "*.c" print

Q. Search all files starting with upper letter.
Ans: find . -name '[A-Z]*' -print

Q. Find out all hard links for a file?
Ans: find / -inum 23323 -print
Explanation: -inum is a option to search all files having same inode number.

Q.Search file by type or permissions?
Ans:
type:
cd ; find . -type d -print 2>/dev/null
Explanation: -type option followed by letter -f(file), -d(dir.) or l(sim link)
If you do not have directory permissions you will get errors , you can redirect standard errors to /dev/null dir.

Permissions:
find $HOME -perm 777 -type d -print

Q. Find out all directories having all permissons to everyone?
cd; find . -perm 777 -type d -print

Find more options:

-user uname: if owned by uname
-group gname: if owned by group
-size +x[c]
-mtime -x: if modified after x time
-atime x: if accessed after x time

Find command actions:
-print :print selected file on standard output
-ls: list files
-exec cmd: Executes unix command cmd followed by {} \;

Q. List all files modified b/w 2 to 5 days?
Ans: find $HOME -type f -mtime +2 -mtime -5 -ls

Q. why you use {} \; in -exec action of find command?
Ans:{} is a place holder of file names. \; is for reuse previous find command.

Q. Remove all files not accessed from last one year.
Ans: find ./ -type f -atime +365 -exec rm {} \;

Note : -ok action will be used in place of -exec if you want to take action interactively.

Filters in Unix:

Head:display from the top of file(default 10 lines from top if no value given)

head -n 3 emp.lst
It will display top 3 lines from a file.

Tail:It will display end of the file.(default 10 lines from top if no value given)
tail -n 3 emp.lst: display last 3 lines
tail +11 emp.lst: Display 11th line onwards

To check logs or file growth:
-f option:

tail -2000f abc.log: display last 2000 lines from the file
cut: Cut columns from a fileAssume file is :Deployment.lst
Deployment to CarrierRoutingService/US/ABE2/Prod succeeded.
Deployment to CarrierRoutingService/US/ABE3/Prod succeeded.
Deployment to CarrierRoutingService/US/AVP1/Prod succeeded.
Deployment to CarrierRoutingService/US/BOS1/Prod succeeded.
Deployment to CarrierRoutingService/US/BWI1/Prod succeeded.
Deployment to CarrierRoutingService/US/CVG1/Prod succeeded.
Deployment to CarrierRoutingService/US/CVG2/Prod succeeded.
Deployment to CarrierRoutingService/US/DFW1/Prod succeeded.
Deployment to CarrierRoutingService/US/IND1/Prod succeeded.
Deployment to CarrierRoutingService/US/IND2/Prod succeeded.
Deployment to CarrierRoutingService/US/IND3/Prod succeeded.
Deployment to CarrierRoutingService/US/LAS2/Prod succeeded.
Deployment to CarrierRoutingService/US/LEX1/Prod succeeded.
Deployment to CarrierRoutingService/US/LEX2/Prod succeeded.
Deployment to CarrierRoutingService/US/PHL1/Prod succeeded.
Deployment to CarrierRoutingService/US/PHL3/Prod succeeded.
Deployment to CarrierRoutingService/US/PHL4/Prod succeeded.
Deployment to CarrierRoutingService/US/PHL5/Prod succeeded.
Deployment to CarrierRoutingService/US/PHL6/Prod succeeded.
Deployment to CarrierRoutingService/US/PHX3/Prod succeeded.
Deployment to CarrierRoutingService/US/PHX5/Prod succeeded.
Deployment to CarrierRoutingService/US/PHX6/Prod succeeded.
Deployment to CarrierRoutingService/US/RNO1/Prod succeeded.
Deployment to CarrierRoutingService/US/SDF1/Prod succeeded.
Deployment to CarrierRoutingService/US/SDF2/Prod succeeded.
Deployment to CarrierRoutingService/US/SEA6/Prod succeeded.
Deployment to CarrierRoutingService/US/TFC1/Prod succeeded.
Deployment to CarrierRoutingService/US/TUL1/Prod succeeded.

Cutting columns:-c optionNow get only environment name from this file:
cut -c 15-48 Deployment.lst

Get envirorment name and status:
cut -c 15-48,50-58 Deployment.lst

both together:It will display all after 15th character.
cut -c 15- Deployment.lst

cutting fileds:-f option.(Default delimiter is tab)
Here you have to use two options
-d for delimiter and -f for field list

Select all environments using cut fields:
cut -d" " -f 3 Deployment.lst

Q. Display only users names for this system.
Ans:who |cut -d " " -f1

tee:It saves the output to a file as well display it on terminal.
cut -d" " -f 3 Deployment.lst | tee envlist

paste:It is just opposite to cut .(Default delimiter is tab)
Example:
paste Deployment.lst envlist
It display both files side by side.
Use delimiter while pasting:
paste -d "|" Deployment.lst envlist


cut -d " " -f4 Deployment.lst | paste -d " " envlist -It will add 4th field from Deployment.lst file to the end of envlist file.
cut -d " " -f4 Deployment.lst | paste -d " " - envlist
It will add 4th field from Deployment.lst file to the starting of envlist file.


Joining lines using paste command:(-s) option
We can join multiple lines using paste command.
paste -s -d "||\n" envlist
It will join three consecutive lines in a single line.

Unix Questions

Q. What search pattern you use inside a file?;
Ans:/pattern if you have file open. grep if file not open.

Q.Search pattern in more files?<Polaris>
Ans:grep

Q. Search pattern in a zip file?<Polaris>
Ans:zgrep

Q.Explain egrep and fgrep?<Polaris>
Ans:egrep: Grep -e: Used  for regular expressions
fgrep: grep -f
Q.Write a command/script which takes first 4 characters as input from output of another command?<Polaris>
Ans: echo "Anand" | cut -c 1-4

Q.How you will check server performance?<Amazon>
Ans: Top command will return all statistics
Sar:This command will return usage statistics for every 15 minute.
PRSTAT: This command used in Solaris to return server performance statistics
VMSTAT
IOSTAT
MPTAT
For memory consumption specially
free -m or free


Q.Find a pattern Anand in file Name.txt using find command?<Amazon>
Ans:find . -type f -print | xargs grep -n "Anand"
Q. Explain grep command usage?<Amazon>

Q. Search 10 digit numbers in a file:

grep '[0-9]\{10\}' anandy.txt | sed 's| |\n|g' | grep '[0-9]\{10\}'

Q.How you will select top 5 rows returned by sar command?<Amazon>
Ans:sar | head -8 | tail -5
Q.Select top 5 processes running on host?<Amazon>
Ans:ps aux | head -6 | tail -5
 top | head -12 | tail -5

Q.What you do on unix box?<Amazon>

Q.What is crontab and give a crontab entry to run a job for every minute?<Amazon>

Q.How you will move a program to run in background?<Amazon>
Ans: run.sh &
(bg also explain)

Q.Give command that run job even machine is logged off?<Amazon>
Ans:nohup

Q.How to find disk free space?<Amazon>
Ans:df -lh

Q.What is shell?<Amazon>
Ans: Interface b/w user and kernel.

Q.What is ssh?<Amazon>
Ans: It is used to connect remote machine.

Q.How you move big files from one machine to another machine?<Amazon>
Ans;sftp wldtsvcs/password@host ip
Then put command to put files on remote host or get command to get files from remote host.

scp ./* anandy@host:/home/anandy
rsync ./* anandy@host:/home/anandy

Q.How you check bugs in code without tool?<Amazon>
Ans: Log into Unix machine and check logs.

Q.How do i schedule a cron job that runs every last Saturday of the month @ 2000 hours?
Ans: These are the lines needed for running the script every last saturday of the month at 8pm.

00 20 25-31 1,3,5,7,8,10,12 6 my-script.sh
00 20 24-30 4,6,9,11 6 my-script.sh
00 30 22-29 2 6 my-script.sh

Q.How to rename a set of *.txt files to *.c?

Ans:mv cant be used because it will try to find a file with name *.c. So need to use for loop kind of thing. for example:

1)for i in `find / -type f -name '*.txt' -print`
do
x=`cut -d'.' -f1`
mv $i $x.c
done

2)
ls *.txt|sed -e 's/.*/mv & &/' -e 's/.txt/.c/2'|sh

Technical - UNIX

Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
1. How do you list the files in an UNIX directory while also showing hidden files?

ls -ltra

2. How do you execute a UNIX command in the background?
Use the "&"
3. What UNIX command will control the default file permissions when files are created?
Umask
4. Explain the read, write, and execute permissions on a UNIX directory.
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.
5. the difference between a soft link and a hard link?
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.
6. Give the command to display space usage on the UNIX file system.

df -lk

7. Explain iostat, vmstat and netstat.
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
8. How would you change all occurrences of a value using VI?
Use :%s///g
9. Give two UNIX kernel parameters that effect an Oracle install
SHMMAX & SHMMNI
10. Command to delete large number of files in one go.

find ./* -type f -delete

11.Copy multiple files from one directory to another.

find ./* -type f -exec cp {} ./temp_dir \;

Efficient command:

find ./* -type f | xargs -i cp {} ./temp_dir/

Move large files to another directory.

ls | tail -10000 | sudo xargs mv --target ../sideline/TT-0010158611

12.Delete files older than 30 days

sudo find ./* -mtime +30 -exec rm -f {} \;

$ find . -type f -name "*.bak" -exec rm -f {} \;

13.Uptime :uptime

14.While vim,Ignore case in search pattern

set ic

Move large files:
ls | tail -10000 | sudo -u tranadm xargs mv --target ../sideline/TT-0010158611

ls | sudo -u tranadm xargs mv --target /local/transportation/NA/status-loader/redrive/new

to copy large files
sudo -u tranadm xargs cp --target /local/transportation/NA/status-loader/redrive/new

ls | sudo -u tranadm xargs mv --target /local/transportation/NA/status-loader/redrive/new

Copy files with : in file name from one host to another:
scp ./Error*  anandy@gmp-parser-na-7001.iad7.amazon.com:/tmp/0009934353
rsync ./Error* anandy@gmp-parser-na-1001.vdc.amazon.com:/tmp/0013689869

Remove large files:
sudo find ./Error* -type f –delete
ls -1 | sudo xargs rm -rf
sudo find ./Error* -type f -exec rm -rf {} \;

To remove files(Including space separated files)
sudo rm -rf *.xml

To move files(Including space separated files)
sudo mv *.xml /local/transportation/NA/status-loader/redrive/new/

sudo chown tranadm 'Error_0311 2550 0003 7257 5981_2012-03-17-22:09:57622d65d9f39c4dbd9d4689e8a70f3bab.xml'

cp -r 0013689869_temp 0013689869_temp_a
if 0013689869_temp_a not exists, it will create new directory and copy all files into it. If 0013689869_temp_a exists it will copy dir 0013689869_temp into it.

To find out owner of files:

hostname% ls -lhrt | awk '{print $3}' | uniq

fangulo
hostname% ls -lhrt | cut -d' ' -f 3 | uniq

fangulo

To search only first occurrence using grep command.

grep -im 1  error Service.log.2012-03-31-11

Find out exact 10 digit numbers in a file:

egrep -o "\<[0-9]{10}\>" tt.txt

Q.What is differences b/w ps -ef and  ps -auxwww?

Ans:This is indeed a good Unix Interview Command Question and I have faced this issue while ago where one culprit process was not visible by execute ps –ef command and we are wondering which process is holding the file.

ps -ef will omit process with very long command line while ps -auxwww will list those process as well.

4. What is Zombie process in UNIX? How do you find Zombie process in UNIX?

When a program forks and the child finishes before the parent, the kernel still keeps some of its information about the child in case the parent might need it - for example, the parent may need to check the child's exit status. To be able to get this information, the parent calls 'wait()'; In the interval between the child terminating and the parent calling 'wait()', the child is said to be a 'zombie' (If you do 'ps', the child will have a 'Z' in its status field to indicate this.)

Zombie : The process is dead but have not been removed from the process table.

1. How do you find which processes are using a particular file?

By using lsof command in UNIX. It wills list down PID of all the process which is using a particular file.

2. How do you find which remote hosts are connecting to your host on a particular port say 10123?

By using netstat command execute netstat -a | grep "port" and it will list the entire host which is connected to this host on port 10123.

10. You have an IP address in your network how will you find hostname and vice versa?

This is a standard UNIX command interview question asked by everybody and I guess everybody knows its answer as well. By using nslookup command in UNIX

Q.sort a csv file based on 2nd column<GE>

Ans: sort -t ',' -k3 Prod1_sre_props_JDA77_90115.txt | cut -d ',' -f3

File:
ORACLE 12102@in2npdlnxdb09#cat Prod1_sre_props_JDA77_90115.txt
OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED,HISTOGRAM,DEFAULT_ON_NULL,IDENTITY_COLUMN,EVALUATION_EDITION,UNUSABLE_BEFORE,UNUSABLE_BEGINNING
WWFMGR,HAROON,IS_FE_ENABLED,NUMBER,,,22,1,0,N,15,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,PRIMARY_ATTRIBUTE_GROUP_NAME,VARCHAR2,,,200,,,Y,14,,10,4446554D6F64656C,50726F6D6F416E616C79736973506172616D73,0.0217391304347826,996,10,12-09-2016 03:04:16 PM,23,CHAR_CS,200,YES,NO,2,50,C,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,PRIMARY_BO_NAME,VARCHAR2,,,200,,,Y,13,,10,4446554D6F64656C,50726F6D6F416E616C79736973506172616D73,0.0217391304347826,996,10,12-09-2016 03:04:16 PM,23,CHAR_CS,200,YES,NO,2,50,C,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,SHORT_NAME,VARCHAR2,,,72,,,Y,12,,325,41415050524D,574D4150454D455452494353,0.00307692307692308,694,1,12-09-2016 03:04:16 PM,325,CHAR_CS,72,YES,NO,6,18,C,NO,YES,NONE,NO,NO,,,
WWFMGR,MD_TABLE_INFO,TABLE_TYPE,VARCHAR2,,,44,,,Y,11,,2,5441424C45,56494557,0.0053763440860215,926,2,12-09-2016 03:04:16 PM,93,CHAR_CS,44,YES,NO,2,11,C,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,USE_SCHEMA_PK,NUMBER,,,22,1,0,N,10,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,IS_MONITOR_ENABLED,NUMBER,,,22,1,0,N,9,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,IS_USER_UPDATEABLE,NUMBER,,,22,1,0,N,8,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,IS_CONFIGURED,NUMBER,,,22,1,0,N,7,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,IS_TIME_ALLOCATABLE,NUMBER,,,22,1,0,N,6,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,IS_USER_DEFINED,NUMBER,,,22,1,0,N,5,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,CONFIG_CODE,NUMBER,,,22,,0,Y,4,,143,80,C80A561112391F0748,0.000538793103448276,91,143,12-09-2016 03:04:16 PM,928,,,YES,NO,7,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,SYSTEM_TABLE,NUMBER,,,22,1,0,N,3,2,2,80,C102,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,,,YES,NO,3,0,,NO,YES,FREQUENCY,NO,NO,,,
WWFMGR,MD_TABLE_INFO,TABLE_NAME,VARCHAR2,,,120,,,N,2,,1019,414354494F4E46494C544552,574F524B4F5244455250524F46494C45,0.000981,0,254,12-09-2016 03:04:16 PM,1019,CHAR_CS,120,YES,NO,16,30,C,NO,YES,HYBRID,NO,NO,,,
WWFMGR,ANAND,SCHEMA_NAME,VARCHAR2,,,120,,,N,1,,2,5343504F4D4752,5757464D4752,0.000490677134445535,0,2,12-09-2016 03:04:16 PM,1019,CHAR_CS,120,YES,NO,8,30,C,NO,YES,FREQUENCY,NO,NO,,,

how-hash-partition-algorithm-works in oracle

--From
http://ocpdba.wordpress.com/2010/11/09/how-hash-partition-algorithm-works/

SQL> create table hashtab (
  2  col1 number,
  3  col2 number,
  4  col3 number)
  5  partition by hash (col1) partitions 4;

Table created.

SQL> @C:\Users\anandy\Scripts\loop.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','HASHTAB');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from user_tab_partitions where table_name='
HASHTAB';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
SYS_P41                              2473
SYS_P42                              2488
SYS_P43                              2532
SYS_P44                              2507

SQL> drop table hashtab;

Table dropped.

SQL> create table hashtab (
  2  col1 number,
  3  col2 number,
  4  col3 number)
  5  partition by hash (col2) partitions 4;

Table created.

SQL> @C:\Users\anandy\Scripts\loop.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','HASHTAB');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from user_tab_partitions where table_name='
HASHTAB';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
SYS_P45                              2330
SYS_P46                              2450
SYS_P47                              2610
SYS_P48                              2610

SQL> drop table hashtab;

Table dropped.

SQL> create table hashtab (
  2  col1 number,
  3  col2 number,
  4  col3 number)
  5  partition by hash (col3) partitions 4;

Table created.

SQL> @C:\Users\anandy\Scripts\loop.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','HASHTAB');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from user_tab_partitions where table_name='
HASHTAB';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
SYS_P49                                 0
SYS_P50                              2500
SYS_P51                              2500
SYS_P52                              5000

SQL>

--Script to insert data

begin
for i in 1..10000 loop
insert into hashtab values(dbms_random.random,mod(i,1000),mod(i,4));
end loop;
end;
/

Find all tablespaces, indexes

References:

Dynamic views: v$

http://www.dba-oracle.com/menu_v$_views_list.htm

All user tables:

http://www.techonthenet.com/oracle/sys_tables/

All user/all/dba tables:

http://www.dba-oracle.com/concepts/data_dictionary.htm

-- Check all available partitions in database

SQL> select tablespace_name from user_tablespaces;

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

6 rows selected.

--Check all indexes in Database

SQL> select index_name ,table_name from user_indexes;

T_INV_IND_IDX1                 T_INV_IND

T_INV_IND_IDX2                 T_INV_IND

TRANS_EMP_IDX1                 TRANS_EMP

T1_IDX                         T1

PK_EMP                         EMP

PK_DEPT                        DEPT

6 rows selected.

--Check all user objects in Database

SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects where upper(object_type) i

n ('TABLE','PROCEDURE','TRIGGER','FUNCTION');

--for all v$ table , we need to login with system as login

For all active (= connected) users,

select user from v$session ;

--For all user accounts

SQL> select distinct username from dba_users;

--Find out object text like procedure code in DB

SQL> select text from ALL_SOURCE where lower(name)='my_create';

PROCEDURE my_create AS

v_maxtime usermeta.instime%TYPE;

v_tbname usermeta.uname%TYPE;

v_Sqlstring VARCHAR2(200);

BEGIN

SELECT MAX(instime) INTO v_maxtime FROM usermeta;

SELECT uname INTO v_tbname FROM usermeta WHERE instime=v_maxtime;

DBMS_OUTPUT.PUT_LINE('Table Name:  '||v_tbname);

v_SqlString:='CREATE TABLE ' || v_tbname || '(' || v_tbname || 'Date TIMESTAMP,

'

||v_tbname||'Values NUMBER)';

DBMS_OUTPUT.PUT_LINE('DDL Query:  '||v_sqlstring);

EXECUTE IMMEDIATE v_sqlstring;

END;

13 rows selected.

--So how does one find out what all of the dynamic performance views are within Oracle 11g?

SELECT
   NAME,
    TYPE
FROM
   V$FIXED_TABLE
WHERE
    NAME LIKE 'V$%';

Performance Tuning Oracle 11g

Performance tuning is a complex task that daunts many Oracle DBAs. It is as much an art as a science. While GUI tools such as Oracle Enterprise Manager and Quest TOAD provide a nice slick graphical interface, to dig into the internal nuts and bolts of database performance, the serious Oracle performance analyst relies on reports generated by v$ dynamic performance views from within the Oracle 11g data dictionary. The following v$ views provide insight into database tuning for Oracle 11g.

Wait Events for 11g

v$session
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v$wait_chains

Oracle 11g Concurrency and SQL Tuning

v$lock
v$sql
v$sqlarea
v$sesstat
v$mystat
v$sess_io
v$sysstat
v$statname
v$osstat
v$active_session_history
v$active_sess_pool_mth
v$session_wait
v$session_wait_class
v$system_wait_class
v$transaction
v$locked_object
v$latch
v$latch_children
v$latch_parent
v$latchname
v$latchholder
v$latch_misses
v$enqueue_lock
v$transaction_enqueue
v$sys_optimizer_env
v$ses_optimizer_env
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all


Oracle 11g Memory Tuning

v$sga
v$sgastat
v$sgainfo
v$sga_current_resize_ops
v$sga_resize_ops
v$sga_dynamic_components
v$sga_dynamic_free_memory
v$pgastat
v$sql_workarea_histogram
v$pga_target_advice_histogram                                              
v$pga_target_advice
v$memory_current_resize_ops
v$memory_resize_ops
v$memory_dynamic_components
v$library_cache_memory
v$shared_pool_advice
v$java_library_cache_memory
v$java_pool_advice
v$streams_pool_advice

Thank you,

-Anand

DDL in trigger

--create table usermeta(instime date, uname varchar2(20));
--insert into usermeta values(sysdate,'Aannd');
--insert into usermeta values(sysdate-4,'Yadav');
--insert into usermeta values(sysdate-32,'Prakash');

CREATE OR REPLACE PROCEDURE my_create AS
v_maxtime usermeta.instime%TYPE;
v_tbname usermeta.uname%TYPE;
v_Sqlstring VARCHAR2(200);
BEGIN
SELECT MAX(instime) INTO v_maxtime FROM usermeta;
SELECT uname INTO v_tbname FROM usermeta WHERE instime=v_maxtime;
DBMS_OUTPUT.PUT_LINE('Table Name:  '||v_tbname);
v_SqlString:='CREATE TABLE ' || v_tbname || '(' || v_tbname || 'Date TIMESTAMP, '
||v_tbname||'Values NUMBER)';
DBMS_OUTPUT.PUT_LINE('DDL Query:  '||v_sqlstring);
EXECUTE IMMEDIATE v_sqlstring;
END;
/


create or replace trigger create_ddl
AFTER INSERT ON usermeta
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--your statements
my_create;
commit;
end;
/

Create table in procedure , table name as input.

--Run this script with system privileges.

set serveroutput on;
spool C:\Users\anandy\Scripts\ddl_create_proc.txt
CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
authid current_user
AS

l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from usermeta )';
DBMS_OUTPUT.put_line(l_stmt);
execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;
/

declare
p_table_name varchar2(20):='Anand';
begin
ddl_create_proc(p_table_name);
end;
/

spool off;

External tables in oracle

---Create directory to store data file.

SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> create or replace directory xtern_data_dir as 'C:\Users\anandy\Scripts\Data
';

Directory created.

SQL> grant read,write on directory xtern_data_dir to scott;

Grant succeeded.

SQL> connect scott;
Enter password:
Connected.


--data file-employee_report.csv

001,Hutt,Jabba,896743856,jabba@thecompany.com,18
002,Simpson,Homer,382947382,homer@thecompany.com,20
003,Kent,Clark,082736194,superman@thecompany.com,5
004,Kid,Billy,928743627,billythkid@thecompany.com,9
005,Stranger,Perfect,389209831,nobody@thecompany.com,23,
006,Zoidberg,Dr,094510283,crustacean@thecompany.com,1

---Create external table for data load.

drop table xtern_empl_rpt;

create table xtern_empl_rpt
( empl_id varchar2(3),
last_name varchar2(50),
first_name varchar2(50),
ssn varchar2(9),
email_addr varchar2(100),
years_of_service number(2))
organization external
(TYPE oracle_loader
default directory xtern_data_dir
access parameters
( records delimited by newline
LOAD WHEN ((1:1) != "#")
fields terminated by ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS) location ('employee_report.csv'))
REJECT LIMIT UNLIMITED;

---Load data or create another table with data

create table empl_rpt as
select * from xtern_empl_rpt;


---Create external table for data export.

create table empl_rpt as select * from xtern_empl_rpt;

create table export_empl_info
organization external
( type oracle_datapump
default directory xtern_data_dir
location ('empl_info_rpt.dmp'))
as select * from empl_rpt;

Trigger Examples in Oracle

create or replace trigger upsert before  insert or delete or update
on trans_emp for each row

DECLARE
  osuser        VARCHAR2(8);
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  --
  -- get who is making the DML change from the session
  --
  osuser := user;
  -- get who is making the DML change from the session
  --
  osuser := NVL(SUBSTR(user,1,8),'oracle');

--
  IF INSERTING THEN
    :new.created_by := osuser;
    :new.creation_date := sysdate;
    :new.last_updated_by := osuser;
    :new.last_updated_date := sysdate;
    --insert into audit_trail values(:new.created_by,:new.creation_date,:new.last_updated_by,:new.last_updated_date,'Insert');
    commit;
  END IF;
  IF UPDATING THEN
    :new.last_updated_by := osuser;
    :new.last_updated_date := sysdate;
    --update audit_trail set last_updated_by=:new.last_updated_by,and last_updated_date=:new.last_updated_date,and action='update';
    commit;
  END IF;
  IF DELETING THEN
  NULL;
    --:new.last_updated_by := osuser;
    --:new.last_updated_date := sysdate;
    --:old_val := old.mgr_id;
    --:new_val := new.mgr_id;
    --update audit_trail set last_updated_by=:new.last_updated_by, and last_updated_date=:new.last_updated_date,and action='Insert';
    --insert into audit_trail values(osuser,sysdate,'','','Delete',:old_val,:new_val);
    commit;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- raise error if we cannot set auditing fields
    raise_application_error(-20505, 'audit_crud_coll_action_log trigger
    failed.  Rolling back.'  || SQLERRM);
    rollback;
end upsert;
/

create table anand(id number, name varchar2(100));

create table logging_anand(id number, old_name varchar2(100),new_name varchar2(100), modified date , operation varchar2(10), user_name varchar2(50));


create or replace trigger log_anand
before insert or update or delete on anand FOR EACH ROW
DECLARE
USER_NAME VARCHAR2(20);
begin

SELECT USER INTO USER_NAME FROM DUAL;

if INSERTING THEN
INSERT INTO logging_anand VALUES(:NEW.id,'',:NEW.name,sysdate,'INSERT',USER_NAME);
elsif UPDATING THEN
INSERT INTO logging_anand VALUES(:NEW.id,:OLD.name,:NEW.name,sysdate,'UPDATE',USER_NAME);
elsif DELETING THEN
INSERT INTO logging_anand VALUES(:OLD.id,:OLD.name,'',sysdate,'DELETE',USER_NAME);
END IF;
end;
/

SHOW ERRORS;


INSERT INTO ANAND VALUES(1,'Anand');


update anand set name='Ved' where id=1;

delete from anand;

select * from anand;

select * from logging_ANAND

commit;

Must know things in Unix

1.How to Start the Desktop in Linux from the Prompt
Just type in startx and the GUI will boot up.


2.How To Split Large Files in Unix http://unix-simple.blogspot.com/2011/06/how-to-split-large-files-in-unix.html
split -1000 my_list my_list

then the output files would have been my_listaa, mylistab, etc.

3.How To Run A Unix Command In A Directory With Too Many Files
http://unix-simple.blogspot.com/2011/05/how-to-run-unix-command-in-directory.html
He was trying to run grep pattern * > /tmp/tmpfile in a directory containing 240,695 files and he got the error "ksh: /bin/grep: arg list too long".

He thought the limitation was in grep and so asked me to provide him with the equivalent awk script. I told him that it was not agrep problem. The issue is with too many arguments on the command line - so the problem would happen with awk also.

You simply cannot put 240,695 arguments on a command line.

The solution is to use a for loop, so you are actually running the command 240,695 times with only one argument:

for i in *

do

grep pattern $i >> /tmp/tmpfile

done

With the xargs example, the command would be:

find . -print | xargs grep pattern >/tmp/tmpfile

The print returns the log commands, and xargs takes the standard input and uses it (piece by piece) as the argument.

More compact and elegant!

4. Network tracking

mtr, better than traceroute and ping combined

/usr/sbin/mtr google.com

5.
http://www.commandlinefu.com/commands/browse
http://en.wikipedia.org/wiki/List_of_Unix_programs

6.How To Find Large Files and Directories in Unix

The find command accepts a size parameter, and you can specify the limits for file sizes in your command line.
This example finds all the files under /etc directory which are larger than 100k:

root@ubuntu# find /etc -size +100k /etc/ssh/moduli /etc/ssl/certs/ca-certificates.crt /etc/bash_completion

Find files within specified size limits
The real beauty of using find command is that you can specify both  the lower and the upper file size limit in one command line. Working off  the previous example, we can limit the search to find only files with  the size of 100k-150k, quite easily:
root@ubuntu# find /etc -size +100k -size -150k /etc/ssl/certs/ca-certificates.crt /etc/bash_completion

Show directory sizes  using du
du command  takes a little while to run, depending on what directory you pass it as a  parameter, but then prints you a list of all the subdirectories along  with their sizes. Most common usage is shown below, -s parameter makes the command report a summary of disk usage stats for only the specified directories matching the /usr/* mask (and not their subdirectories), and -k specifies that we want to see the results in kilobytes:
greys@ubuntu$ du -sk /usr/* 4       /usr/X11R6 97664   /usr/bin 24      /usr/games 11628   /usr/include 167812  /usr/lib 0       /usr/lib64 96      /usr/local 25076   /usr/sbin 201500  /usr/share 4       /usr/src
In most Linux systems, this command had been updated to support a -h parameter, which makes sizes even easier to interpret:
greys@ubuntu$ du -sh /usr/* 4.0K    /usr/X11R6 96M     /usr/bin 24K     /usr/games 12M     /usr/include 164M    /usr/lib 0       /usr/lib64 96K     /usr/local 25M     /usr/sbin 197M    /usr/share 4.0K    /usr/src

Command to find out command location:
which:
Usage:~% which find
/usr/bin/find 

Rank() and dense_rank() example

---http://orafaq.com/node/55

RANK and DENSE_RANK both provide rank to the records based on some column value or expression. In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
Query-6 shows the usage of both RANK and DENSE_RANK. For DEPTNO 20 there are two contenders for the first position (EMPNO 7788 and 7902). Both RANK and DENSE_RANK declares them as joint toppers. RANK skips the next value that is 2 and next employee EMPNO 7566 is given the position 3. For DENSE_RANK there are no such gaps.

SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;

EMPNO  DEPTNO   SAL  RANK DENSE_RANK
------ ------- ----- ----- ----------
  7839      10  5000     1          1
  7782      10  2450     2          2
  7934      10  1300     3          3
  7788      20  3000     1          1
  7902      20  3000     1          1
  7566      20  2975     3          2
  7876      20  1100     4          3
  7369      20   800     5          4

8 rows selected.

2nd Highest salary from each department

CREATE SEQUENCE EmpSequence
MINVALUE 1
MAXVALUE 9999
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLC2_Employees (
EmployeeID INT ,
EmployeeName VARCHAR2(15),
Department VARCHAR2(15),
Salary NUMBER(16,2)
);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'T Cook','Finance', 40000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'D Michael','Finance', 25000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'A Smith','Finance', 25000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'D Adams','Finance', 15000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'M Williams','IT', 80000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'D Jones','IT', 40000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'J Miller','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'L Lewis','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'A Anderson','Back-Office', 25000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'S Martin','Back-Office', 15000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'J Garcia','Back-Office', 15000);

INSERT INTO PLC2_Employees(EmployeeID,EmployeeName, Department, Salary)  VALUES(EmpSequence.NextVal,'T Clerk','Back-Office', 10000);

select EmployeeName,a.salary,a.department from plc2_employees a,(select sal
ary,department from (select row_number() over (partition by department order by
salary desc) as row_dt, salary, department from plc2_employees) a where a.row_dt
=2) b where a.department=b.department and a.salary=b.salary;

Exception example in oracle

set heading off;

set serveroutput on;
spool C:\Users\anandy\Scripts\except.txt;
declare
v_mgr number:=12345;
begin
delete from emp where mgr = 1234;
if SQL%NOTFOUND then
RAISE_APPLICATION_ERROR(-20023,'This is not a valid manager');
end if;
exception
   when no_data_found
   then
dbms_output.put_line('There is no record found');
end;
/
spool off;

Cube and RollUp and Connect by(using LEVEL) Example

RollUp: Display aggregate and super aggregate within group by.

Qube: Display all possible combinations with in group by.

Q. Display total salary given to department Trans,total salary given to each project with in it and total salary given to each employee in trans.?
Data:
Table

SQL> create table amz_dept(dept_id number,dept_name varchar2(10),proj_id number,
proj_name varchar2(10),emp_id number,emp_name varchar2(20),salary number,BS char
(2));

Insert:

insert into amz_dept values(1,'Trans',10,'Support',3872,'Anand',2000,'B');
insert into amz_dept values(1,'Trans',10,'Support',3872,'Anand',3000,'S');
insert into amz_dept values(1,'Trans',10,'CCS',3875,'Anand',2000,'S');
insert into amz_dept values(1,'Trans',10,'Support',3874,'Sarath',4000,'B');
insert into amz_dept values(1,'Trans',11,'Shipping',3876,'Kevin',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3878,'Gireesh',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3879,'Lynda',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3832,'Steven',3000,'S');
insert into amz_dept values(1,'Trans',12,'GTS',3845,'Sahid',3000,'S');
insert into amz_dept values(1,'Trans',12,'GTS',3834,'Amar',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3812,'Anil',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3839,'Putla',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3812,'Kolimi',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3898,'bhanu',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3867,'sridhar',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3858,'Rajat',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3809,'JJJJ',3000,'S');

Results:



SQL> set lin 20000;
SQL> set pagesize 200;

SQL> select proj_name,emp_name,sum(salary) from amz_dept group by rollup(proj_na
me,emp_name);

PROJ_NAME  EMP_NAME             SUM(SALARY)
---------- -------------------- -----------
AVS        JJJJ                        3000
AVS        Rajat                       3000
AVS        bhanu                       3000
AVS        sridhar                     3000
AVS                                   12000
CCS        Anil                        3000
CCS        Anand                       2000
CCS        Putla                       3000
CCS        Kolimi                      3000
CCS                                   11000
GTS        Amar                        3000
GTS        Sahid                       3000
GTS                                    6000
Support    Anand                       5000
Support    Sarath                      4000
Support                                9000
Shipping   Kevin                       3000
Shipping   Lynda                       3000
Shipping   Steven                      3000
Shipping   Gireesh                     3000
Shipping                              12000
                                      50000

22 rows selected.

SQL> select proj_name,emp_name,sum(salary) from amz_dept group by cube(proj_name
,emp_name);

PROJ_NAME  EMP_NAME             SUM(SALARY)
---------- -------------------- -----------
                                      50000
           Amar                        3000
           Anil                        3000
           JJJJ                        3000
           Anand                       7000
           Kevin                       3000
           Lynda                       3000
           Putla                       3000
           Rajat                       3000
           Sahid                       3000
           bhanu                       3000
           Kolimi                      3000
           Sarath                      4000
           Steven                      3000
           Gireesh                     3000
           sridhar                     3000
AVS                                   12000
AVS        JJJJ                        3000
AVS        Rajat                       3000
AVS        bhanu                       3000
AVS        sridhar                     3000
CCS                                   11000
CCS        Anil                        3000
CCS        Anand                       2000
CCS        Putla                       3000
CCS        Kolimi                      3000
GTS                                    6000
GTS        Amar                        3000
GTS        Sahid                       3000
Support                                9000
Support    Anand                       5000
Support    Sarath                      4000
Shipping                              12000
Shipping   Kevin                       3000
Shipping   Lynda                       3000
Shipping   Steven                      3000
Shipping   Gireesh                     3000

37 rows selected.

SQL>

Connect By Prior:It will display tree structured query. It will start tree with START WITH condition and display all it's child nodes.


SQL> select * from trans_emp;

EMP_NAME                 EMP_ID     MGR_ID
-------------------- ---------- ----------
Jay                        1000
Sekhar                     1001       1000
Shiva                      1002       1000
Ramesh                     1003       1001
Sravani                    1004       1003
Anand                      1005       1004
Sarat                      1006       1004
Debo                       1007       1002
Ankit                      1008       1007

9 rows selected.

SQL> select lpad(' ', LEVEL*4)||emp_name emp_hirarchy from trans_emp connect by
prior emp_id=mgr_id start with mgr_id is null;

EMP_HIRARCHY
--------------------------------------------------------------------------------

    Jay
        Sekhar
            Ramesh
                Sravani
                    Anand
                    Sarat
        Shiva
            Debo
                Ankit

9 rows selected.

SQL>

Different results when we use where condition or not.



SQL> SELECT lpad('  ',level*4)||emp_name FROM trans_emp START WITH emp_id = 1000
 CONNECT BY PRIOR emp_id = mgr_id AND mgr_id != 1004 ;

    Jay
        Sekhar
            Ramesh
                Sravani
        Shiva
            Debo
                Ankit

7 rows selected.

SQL> SELECT lpad('  ',level*4)||emp_name FROM trans_emp START WITH emp_id = 1000
 CONNECT BY PRIOR emp_id = mgr_id-- AND mgr_id != 1004 ;

    Jay
        Sekhar
            Ramesh
                Sravani
                    Anand
                        Anand:Shalini
                    Anand
                        Anand:Shalini
                    Sarat
        Shiva
            Debo
                Ankit

12 rows selected.

SQL> SELECT lpad('  ',level*4)||emp_name FROM trans_emp where mgr_id!=1004 START
 WITH emp_id = 1000 CONNECT BY PRIOR emp_id = mgr_id ;

        Sekhar
            Ramesh
                Sravani
                        Anand:Shalini
                        Anand:Shalini
        Shiva
            Debo
                Ankit

8 rows selected.

Unix-Day2Day

Find if host is ssh'able or not:

#!/bin/bash
i=`traceroute $1 | grep -v "traceroute to" | grep -c $1`
##echo $i
if [ $i -eq 1 ];
then
echo " $1 Host is SSH'able";
else
echo " $1 Host is not SSH'able";
fi



Find if host is pingable or not.

Solution1:

for host in host1 host2 ; do ping -c2 $host >/dev/null && echo $host is up || echo $host is down; done

Solution2:
ping -c 1 "$1" > /dev/null

if [ "$?" -eq 0 ] ; then
    echo "$1 Host is up"
else
    echo "$1 Host down"
fi

Solution3:

#!/bin/bash
HOSTS=" $1 $2 $3 $4"

COUNT=4

for myHost in $HOSTS
do
        count=$(ping -c $COUNT $myHost | grep 'received' | awk -F',' '{print $2}' | awk '{print $1}')
        if [ $count -eq 4 ]
                then echo  "$myHost  is up!"
                else echo  "$myHost  is having ping issues!"
        fi
done

 Sed command usage:

% _now="$(sed -e 's/\//_/g;s/:/_/g' <<<$(date +'%D_%T'))"
% echo $_now
02_08_12_15_49_26
% _now=$(sed 's/[\/:]/_/g' <<<$(date +'%D_%T'))
% echo "$_now"

Move error files generated by find command to /dev/null:

find / -name cat.txt -print 2>/dev/null

Negation of a particular file type:
$ find ./temp/* ! -name cat.txt -print 2>/dev/null


Remove files from a directory:
find ./temp/* ! -name cat.txt -exec rm -f {} \;


Move files from one directory to another:
find ./Test1/* -exec mv {} ./temp/ \;

tr command usage:Most useful command

who | tr -s ' ' | cut -d ' ' -f3 | tr '-' '/' |tr -d '/'

tr command to count frequency of words in a document:

 tr ' ' '\n' < user4 | sort | grep -v ^$ | uniq -c

To print only the frequency of characters and number in the file
tr ' ' '\n' < user3.txt | tr -cd "a-z A-Z 0-9 \n" | sort | grep -v ^$ | uniq -c

Print multi-column to single column and Single column to multi-column:
tr ' ' '\n' < user3.txt | tr -cd "a-z A-Z 0-9 \n" | sort -nr | grep -v ^$ | uniq -c |pr -t -5

^$ is a special regex that represents white row.

How do you create a file in UNIX

using touch command

using cat command

If you want to see top 3 lines from each file in your directory:
head -3 *

Show 1st line of ps command without header:

 ps | tail -3 | head -1

Sort file by date and count number of occurrences by each date:
cat user2.txt | sort -k 3 | tr -s ' ' | cut -d ' ' -f3 | uniq -c

Search specific line in the file:
$ cat -n user3.txt | head -4 | tail -1 | grep anandy

Hint:quoting is essential if the search string consists of more then 1 word or usage any of the shell characters like *,$ etc.

Hint: Grep command return fail ($?=1)when it is not able to locate file but sed/awk are not considered to fail.

Hint: Grep -l option display only file names those having search pattern.

Retrieve various elements from date.
date '+%a'