Technology TIPS

Useful UNIX Shell Commands

2010-02-12T11:21:00.000-08:00

Useful UNIX Shell Commands
=========================================

It seems I keep bumping into situations which repeat. I list below some UNIX shell commands I've used on many occasions.

grep
----

Case insensitive search:
grep -i
Demo: grep -i bizVigyan danny.txt

List files which contain pattern:
grep -l
Demo: grep -l bizVigyan *.txt

Return lines which do not match:
grep -v
Demo: grep -v bizVigyan billy.txt

find
----

Find may be used to find a file. Also I use it to generate lists of files which
fit some kind of pattern. The lists can than be fed to other shell commands.

Self Explanatory Demos:
---------------------------------

bizVigyan04-oracle-% /bin/find . -name initbizVigyandev.ora -print

./dbs/initbizVigyandev.ora
bizVigyan04-oracle-%

bizVigyan04-oracle-% /bin/find . -name 'init*.ora' -print
./dbs/initbizVigyandev.ora
./dbs/initbizVigyantest.ora
./dbs/initbizVigyanrc.ora
./hs/admin/inithsodbc.ora
bizVigyan04-oracle-%

bizVigyan04-oracle-% /bin/find . -type l -print
./bin/runInstaller
./lib/libagtsh.so
./lib/libobk.so
./lib/libclntsh.so
./precomp/public/SQLCA.H
bizVigyan04-oracle-%

bizVigyan04-oracle-% /bin/find . -type l -exec /bin/ls -l {} \;

lrwxrwxrwx 1 oracle dba 56 Jul 18 2000 ./bin/runInstaller ->

disk/u01/app/oracle/product/oui/install/runInstaller.sh

lrwxrwxrwx 1 oracle dba 15 Jul 18 2000 ./lib/libagtsh.so -> libagtsh.so.1.0
lrwxrwxrwx 1 oracle dba 13 Jul 18 2000 ./lib/libobk.so -> libdsbtsh8.so
lrwxrwxrwx 1 oracle dba 16 Jul 18 2000 ./lib/libclntsh.so -> libclntsh.so.8.0
lrwxrwxrwx 1 oracle dba 7 Jul 18 2000 ./precomp/public/SQLCA.H -> sqlca.h
bizVigyan04-oracle-%

I think of the 'xargs' keyword as a magic wand which moves a group of tokens
from the left to the right and squish them onto one line.

Here is ASCII art which describes my mental picture:

aabbcc
xxyyzz
112233 -> xargs -> aabbcc xxyyzz 112233 uu bye
uu
bye

The 'xargs' keyword works well with the 'find' command because the 'find' command
lists file names in a single column.

This demo uses 'xargs' keyword to do the same thing as the
/bin/find . -type l -exec /bin/ls -l {} \;
demo.

bizVigyan04-oracle-% /bin/find . -type l -print | xargs /bin/ls -l
lrwxrwxrwx 1 oracle dba 56 Jul 18 2000 ./bin/runInstaller ->

disk/u01/app/oracle/product/oui/install/runInstaller.sh lrwxrwxrwx 1 oracle dba 15 Jul 18
2000 ./lib/libagtsh.so -> libagtsh.so.1.0
lrwxrwxrwx 1 oracle dba 16 Jul 18 2000 ./lib/libclntsh.so -> libclntsh.so.8.0
lrwxrwxrwx 1 oracle dba 7 Jul 18 2000 ./precomp/public/SQLCA.H -> sqlca.h
bizVigyan04-oracle-%

Notice how /bin/ls does the opposite of xargs; it takes a list of
files on a single command line and transforms the list into a column of file names.

Now we demonstrate 'find' sending a bunch of file names to grep:

/bin/find . -type f -print | xargs | grep -l bizVigyan

The above command finds all files (in a tree!) which contain the string 'bizVigyan'.

The 'find' command is also useful for finding files based on date.

The command below finds all files younger than 7 days:

/bin/find . -type f -mtime -7 -print

The command below finds all files older than 7 days:

/bin/find . -type f -mtime +7 -print

Since 'find' can find files based on date, it works well with cpio:

/bin/find . -type f -mtime -7 -print | /bin/cpio -o > /tmp/newerFiles.cpio

The above shell command could be considered a low-tech incremental backup.
To check the contents of the backup, we'd do something like this:

cat /tmp/newerFiles.cpio | /bin/cpio -it

perl
----

Perl is a great language. Often, I use it to edit collections of files.

If I want to edit just one file I'd do something like this:

perl -e 's/Smith/Bikle/gi' -p -i.bak aFileIwantChanged.txt

If I want to edit all the files in a tree of directories, I'd use find, grep, and xargs to locate the names and then
feed the list to a perl command similar to the one above:

/bin/find . -type f -print | xargs /bin/grep -l Smith | xargs perl -e 's/Smith/Bikle/gi' -p -i.bak

Note that if perl changes any files it will back up the original copies to files with a '.bak' suffix. If we are confident the perl edits are okay, we may remove the .bak files with a command like

this:

/bin/find . -type f -name '*.bak' -print | xargs /bin/grep -l Smith | xargs /bin/rm

Yes, perl is a useful tool; I may put together a directory full of useful perl scripts when I get some free time.

Miscellaneous
--------------

If I want to edit a file and put the results in another file, I'd use sed rather than perl:

cat initSMITH.ora|/bin/sed '1,$s,SMITH,bizVigyan,g'>initbizVigyan.ora

Here is a command I use to look at some hardware configuration information on a Solaris host:

/usr/platform/sun4u/sbin/prtdiag

Here is a demo command I use to kill a set of processes which fit a grepable pattern:

/bin/ps -ef |/bin/grep oraclebizVigyan|/bin/awk '{print $2}' | xargs kill

Sometimes /var/adm/messages can contain interesting information:

/bin/tail /var/adm/messages

Netstat contains connection information between your host and others.
For example it can help answer, "Who is connected on port 1521?":

/bin/netstat -a|/bin/grep 1521

Here is another awk demo which I've not actually used yet but does
demonstrate 2 good ideas. The first idea is that I can use the '-F' option to specify a field separator in the file I'm passing to awk.
For example, the /etc/passwd file contains fields separated by ':'.

The second idea is that awk has some SQL-like functionality. For example, if I want to select (perhaps display would be a better verb)
the value of the first column for all lines in /etc/passwd where the third column value is "0", I'd type this:

cat /etc/passwd | /bin/awk -F: '$3 == "0" {print $1}'

A rough SQL equivalent would be this:

SELECT column1 FROM /etc/passwd WHERE column3 = '0';

Here is another awk demo which acts like the SQL function named SUM().

cat /etc/group | /bin/awk -F: '{sum+=$3}END{print sum}'

In SQL terms it would be like this:

SELECT SUM(column3) FROM /etc/group;

A Recap
-------

/bin/find . -name 'init*.ora' -print

/bin/find . -type l -print

/bin/find . -type l -exec /bin/ls -l {} \;

/bin/find . -type l -print | xargs /bin/ls -l

/bin/find . -type f -print | xargs grep -l bizVigyan

/bin/find . -type f -mtime -7 -print

/bin/find . -type f -mtime +7 -print

/bin/find . -type f -mtime -7 -print | /bin/cpio -o > /tmp/newerFiles.cpio

cat /tmp/newerFiles.cpio | /bin/cpio -it

perl -e 's/Smith/Bikle/gi' -p -i.bak aFileIwantChanged.txt

/bin/find . -type f -print | xargs /bin/grep -l Smith | xargs perl -e 's/Smith/Bikle/gi' -p -i.bak

/bin/find . -type f -name '*.bak' -print | /bin/grep -l Smith | xargs /bin/rm

cat initSMITH.ora|/bin/sed '1,$s,SMITH,bizVigyan,g'>initbizVigyan.ora

/usr/platform/sun4u/sbin/prtdiag

/bin/ps -ef |/bin/grep oraclebizVigyan|/bin/awk '{print $2}' | xargs kill

/bin/tail /var/adm/messages

/bin/netstat -a|/bin/grep 1521

cat /etc/passwd | /bin/awk -F: '$3 == "0" {print $1}'

cat /etc/group | /bin/awk -F: '{sum+=$3}END{print sum}'

Oracle sql: Generate/INSERT Random data to a table for testing purposes

2010-02-12T10:56:00.001-08:00

Oracle sql: Generate/INSERT Random data to a table for testing purposes

****************************** Code Begins **************************************************

-- ------------------------------------------------------------------------------------------
-- First of all, create the procedure given below
--
-- Secondly, call the newly create procedure from "sqlplus" as follows:
-- SQL> execute gen_data( 'EMP', 10000 );
--
-- where EMP is the table name and 10000 is the number of rows you want to insert
--
-- ------------------------------------------------------------------------------------------

create or replace
procedure gen_data( p_tname in varchar2, p_records in number )
authid current_user
as
l_insert long;
l_rows number default 0;
begin

dbms_application_info.set_client_info( 'gen_data ' || p_tname );
l_insert := 'insert /*+ append */ into ' || p_tname ||
' select ';

for x in ( select data_type, data_length,
nvl(rpad( '9',data_precision,'9')/power(10,data_scale),9999999999) maxval
from user_tab_columns
where table_name = upper(p_tname)
order by column_id )
loop
if ( x.data_type in ('NUMBER', 'FLOAT' ))
then
l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
elsif ( x.data_type = 'DATE' )
then
l_insert := l_insert ||
'sysdate+dbms_random.value+dbms_random.value(1,1000),';
else
l_insert := l_insert || 'dbms_random.string(''A'',' ||
x.data_length || '),';
end if;
end loop;
l_insert := rtrim(l_insert,',') ||
' from all_objects where rownum <= :n';

loop
execute immediate l_insert using p_records - l_rows;
l_rows := l_rows + sql%rowcount;
commit;
dbms_application_info.set_module
( l_rows || ' rows of ' || p_records, '' );
exit when ( l_rows >= p_records );
end loop;
end;
/

****************************** Code Ends **************************************************

Oracle: Enable Archive Log and prepare for mining

2010-02-12T10:18:00.000-08:00

Oracle: Enable Archive Log and prepare for mining

Database Parameters to be changed:
----------------------------------
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/v1x11510/oracle/archive'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

Login as SYS (As sysdba) and do the following:
------------------------------------------------
CONNECT sys AS SYSDBA
STARTUP PFILE=/v1x11510/v1x11510db/9.2.0/dbs/initv1x11510.ora MOUNT EXCLUSIVE;
ALTER DATABASE ARCHIVELOG;
ARCHIVE LOG START;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
-------------------------------------------
-- The following stmt collects supplementa ldata for primary keys and unique keys.
ALTER DATABASE ADD SUPPLEMENTAL DATA (PRIMARY KEY, UNIQUE KEY) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

To store dictionary in the REDO logs, do
-----------------------------------------
EXECUTE dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

To store dictionary in file system, do
--------------------------------------
begin
dbms_logmnr_d.build (
dictionary_filename => 'vxgwy1_ora_dict.txt',
dictionary_location => '/tmp'
);
end;

Oracle PL/SQL: MD5 checksum of an PL/SQL object

2010-02-12T09:48:00.000-08:00

Oracle PL/SQL: MD5 checksum of an PL/SQL object

Here comes the Code to find the MD5 checksum of an PL/SQL object named VERIFY_FUNCTION.

This DBMS_CRYPTO is available in 10GR2. In 8i and 9i, we may have to call DBMS_OBFUSCATION_TOOLKIT instead..

**************** Code Begins ************************
set serveroutput on size 1000000

declare
hash varchar2(32);
code clob;
begin
for frec in (
select text
from dba_source
where owner = 'SYS'
and type = 'FUNCTION'
and name = 'VERIFY_FUNCTION'
order by line
)
loop
code := code || frec.text;
end loop;
hash := rawtohex(
dbms_crypto.hash (
typ => dbms_crypto.hash_md5,
src => code
)
);

dbms_output.put_line('MD5 HASH of VERIFY_FUNCTION : ' || hash);

end;
/

************** Code Ends **********************

Oracle PL/SQL: Create functions to join and split strings in SQL

2010-02-12T09:41:00.000-08:00

Reference: http://articles.techrepublic.com.com/5100-10878_11-5259821.html

Excellent article. Recognized well by the development community.

Reproduced below from the above URL: TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that's almost as common is the need to do the reverse: Take a comma delimited list of values in a single string and use it as a table of values.

Many scripting languages, such as Perl and Python, provide functions that do this with their own language-specific list of values; so it's surprising that, as of yet, this functionality isn't a standard part of SQL functions. I've seen some pretty ugly looking code that involved complex declarations with MAX and DECODE, but that solution usually only returns a limited set of values. With some of the new Oracle9i and above features, it's possible to do this yourself.
I'd like to use a "join" functionality to specify a query that returns a single column and a delimiter, and then receive a simple string that contains a list of those values separated by my delimiter. The query part can be passed to the function as a REF CURSOR using the new SQL CURSOR function. The delimiter should default to a comma, since that is the most commonly used delimiter. So, the syntax should be:

SQL> select join(cursor(select ename from emp)) from dual;

SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,
JAMES,FORD,MILLER

The following code will perform this function:

create or replace function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
/
show errors;

The PL/SQL User's Guide says you always have to declare a package that defines a ref cursor; however, the database already defines this as SYS_REFCURSOR in the STANDARD package. The PL/SQL code should be fairly straightforward. There is a limit of 32,767 characters on the output string and the input column.

Since all datatypes can be automatically converted to character strings, you can use any datatype in the cursor--as long as it's one column. For example:

SQL> select join(cursor(select trunc(hiredate,'month') from emp),'|') from
dual;

01-DEC-80|01-FEB-81|01-FEB-81|01-APR-81|01-SEP-81|01-MAY-81|01-JUN-81|01-APR-87|01-NOV-81|01-SEP-81|01-MAY-87|01-DEC-81|
01-DEC-81|01-JAN-82

There's another extra benefit. Since the cursor is part of the SQL statement, you can easily join the query inside the join with the outer query. Here is a query that returns each table and a list of the columns that make up its primary key:

SQL> select table_name,join(cursor(select column_name from user_cons_columns
where constraint_name = user_constraints.constraint_name
order by position)) columns
from user_constraints where constraint_type = 'P';

View the output in Table A.

You can also use this "join" function to compare two sets of ordered data. For example, the following query will check that an index has been created on a foreign key (which helps prevent locking the table and aids master-detail queries):

column status format a7
column table_name format a30
column columns format a40 word_wrapped

select decode(indexes.table_name,null,'missing','ok') status,
constraints.table_name,
constraints.columns
from
(select table_name,
constraint_name,
join(cursor
(
select column_name
from user_cons_columns
where constraint_name = user_constraints.constraint_name
)) columns
from user_constraints
where constraint_type = 'R'
) constraints,
(select table_name, index_name,
join(cursor
(
select column_name
from user_ind_columns
where index_name = user_indexes.index_name
)) columns
from user_indexes) indexes
where constraints.table_name = indexes.table_name (+)
and constraints.columns = indexes.columns (+);

This query works by executing two subqueries: one that queries foreign keys and another that queries indexes. The join between these two queries is on the table name and the list of columns used in creating the foreign key and the index, taken as an ordered list of values.

We'd also like the reverse functionality: to have the ability to take a single comma-delimited value and treat it as if it were a column in a table. We can take advantage of the TABLE SQL function and PL/SQL function tables to do this quite easily, but first, we must define the result type to be a TABLE type of the largest possible string.

create or replace type split_tbl as table of varchar2(32767);
/
show errors;

create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));

else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;

With this function, I can run a query like this:

SQL> select * from table(split('one,two,three'));

one
two
three

The PL/SQL procedure will parse its argument and return each part through a PIPELINE; the TABLE function allows it to be used in the FROM statement, so it appears to SQL as if it is a table with one column and three rows. (Remember that the column being returned is named COLUMN_VALUE if you want to use the value elsewhere.)

Here's an example query, which shows a dynamic IN condition in a query. The split function generates a table of values, which can be used on a row-by-row basis.

SQL> select ename from emp
where to_char(hiredate,'YY')
in (select column_value from table(split('81,82')));

View the output in Table B.

If you want, you can join a column and then split it, too:

SQL> select * from table(split(join(cursor(select ename from emp))));

And, you can use this method to merge sets of values:

create table t(a varchar2(200));
insert into t values('81,82');
insert into t values('84,85');

SQL> select * from table(split(join(cursor(select a from t))));

81
82
84
85

These are just simple example functions. You could extend join to enclose values in quotes and escape quotes inside the values. You could extend split to allow a REF CURSOR parameter instead of a single VARCHAR2, so it could split up sets of columns as well.

Oracle PL/SQL: BULK COLLECT and FORALL usage

2010-02-12T09:34:00.000-08:00

Oracle PL/SQL: BULK COLLECT and FORALL usage

create table t1 as select * from all_objects where 1=2;

create or replace procedure fast_proc is
TYPE TObjectTable is table of ALL_OBJECTS%ROWTYPE;
ObjectTable$ TObjectTable;
BEGIN
select *
BULK COLLECT INTO ObjectTable$
from ALL_OBJECTS;

forall rowX in ObjectTable$.First..ObjectTable$.Last
insert into t1 values ObjectTable$(rowX);
END;
/

HDD Write Test

2010-02-12T08:39:00.000-08:00

HDD Write Test

To write 50G of nothing
cd /mnt
dd if=/dev/zero of=my50Gfile bs=1024M count=50

To time it, do

time dd if=/dev/zero of=my50Gfile bs=1024M count=50

More workaround:
dd if=/dev/zero of=diskfiller.tmpfile bs=1000M count=99999999

==> Takes 5 hrs for a 400G strip on 2 drives.

Mounting USB flash drive with NTFS -f FS on to RHEL 4/5.x

2010-02-12T08:29:00.000-08:00

Mounting USB flash drive with NTFS -f FS on to RHEL 4/5.x

First login as "root" and do:

fdisk -l

find whether the device is /dev/sda or /dev/sda1

In the following case, it is /dev/sda1:

Device Boot Start End Blocks Id System
/dev/sda1 1 15740 4029424 c W95 FAT32 (LBA)

Then, create a dir:
mkdir /v03/flash

then, mount it as follows:

mount -t auto /dev/sda1 /v03/flash/

Download the following 2 tar balls:
fuse-2.7.4.tar.gz
ntfsprogs-2.0.0.tar.gz

Then, install fuse first
tar xfz fuse-2.7.4.tar.gz
cd fuse-2.7.4
./configure
make install

Then, install ntfsprogs, next
tar xfz ntfsprogs-2.0.0.tar.gz
cd ntfsprogs-2.0.0
./configure
make install

Then, you should be able to mount the NTFS for READ and WRITE as follows:

ntfsmount /dev/sda1 /mnt -o rw