I Love Tips

Nice Tips & Tricks made easy

Archive for May, 2008

Testing Conditions in Shell Programming

Posted by Rajkiran Ghanta on May 18, 2008

In the shell programming, conditional expressions are used for performing tests by
- evaluating and comparing integers or strings
- testing file attributes

Testing Conditional expressions is done by using the following three different forms of syntax.
test expression
[ expression ]
[[ expression ]]

These expressions will return an exit status of 0 for true and 1 for false. It is preferrable to use [[ ]] command, as it will result in fewer syntax errors.

Example :
To compare two integers, by checking if one is less than the other with one of the following :
[[ $a gt $b ]]
[ $a gt $b ]
test $a gt $b

You can also combine two or more expressions (aka. compound expressions) using the following operators
- Double ampersand, && ( which means “AND” )
- Double Pipe, || (which means “OR”)

Example :
the syntax for for compound expressions using || is :
[[ expression1 || expression2 ]]

If you would like to test student’s subject mark with minimum required mark and print pass if either of the subject is more than the required score, the sample code would be

$[[ $SubjectA > $ReqScore || $SubjectB > $ReqScore ]] && print “PASS”

[[ .... ]] syntax can also be used for the following tests :
To test variables with string operators
-z true if length is zero
-n will be true if the string is not zero
-o will be true if option is set

example:
if [[ -z $StringA ]]; then
print “Length is zero”
fi

To compare one string with another string
[[ string1 = string2 ]]

Example :
[[ $Subject = "MATH A" ]] && print “Optional Subject”

To compare strings with String operators
=, !=, < , >

To test files with options
-a (file exists)
-d (file is a dir)
-f (file is regular file)
-r (file readable)
-w (file writable)
-x (file executable)
-s (file non-empty)
-u (file has userID bit set)

To compare files
[[ file1 -ef file2]] for same files
[[ file1 -nt file2]] for finding new file
[[ file1 -ot file2]] for finding older file

To compare expressions involving integers using integer operators
-eq, -ne, -le, -ge, -gt

Posted in Shell Programming, UNIX/LINUX | Tagged: , | Leave a Comment »

Temporarily Temporary Tablespace

Posted by Rajkiran Ghanta on May 10, 2008

Oracle uses temporary tablespace to perform any sorting required by the queries that have clauses mainly like order by, distinct, group by, union, interset and minus. Index creation and certain correlated subqueries will also use the temporary tablespace.

By default, if the temporary tablespace is not explicitly defined, oracle will use SYSTEM tablespace as the temporary tablespace.

Here is an example to create a temporary tablespace :

SQL> create temporary tablespace TEMP tempfile ‘/ora0001/oradata/temp_1.dbf’ size 500 M extent management local uniform size 256k;

A temporary tablespace can be made as default temporary tablespace for all the users in the database or can be assigned to specific users exclusively as the default one.

To make temp tablespace as default temporary tablespace for all the users in the database :
SQL> alter database default temporary tablespace temp;

To alter a user (e.g.scott) to use temp tablespace as temporary tablespace :
SQL> alter user scott temporary tablespace temp;

To specify temporary tablespace clause while creating a new user :
SQL> create user john identified by changeme
default tablespace users temporary tablespace temp1;

If you omit the TEMPORARY TABLESPACE clause, the temporary segments default to the SYSTEM tablespace. DBA can resize the temporary tablespace depending on the requirement for the temp space usage.

To resize the temp tablespace by adding new tempfile :
SQL> alter tablespace temp add tempfile ‘/ora0001/oradata/temp_2.dbf’ size 512m;

To resize an existing tempfile :
SQL> alter database tempfile ‘/ora0001/oradata/temp_1.dbf’ resize 1024M;

Sometimes, shrinking a temp tablespace may error out with
Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value

In such cases, it may be advisable to create a new temp tablespace with the required temp size and drop the old one using drop tablespace command. If the old tablespace is the default temporary, reassign the new tablespace as default one before dropping the old one.

The command for drop tablespace is :
SQL> drop tablespace temp including contents and datafiles;

Normally, sort operation will be done in memory based on the SORT_AREA_SIZE parameter size in the init.ora file. Any sorts exceeding the SORT_AREA_SIZE limit will acquire temporary segments.

Since temporary tablespaces do not contain any permanent objects, these tablespaces need not be backed up. Infact, tempfile information is not recorded in control file and tempfiles can be recreated any time.

If needed, DBA can make the tempfile offline or even can drop the tempfile as shown below :
SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ offline;

SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ drop including datafiles;

Posted in DBA | Tagged: | Leave a Comment »