Oracle SQLPlus Query Output to a File
| Posted by watashii | Filed under Database, Programming, Unix
Here is a simple way to output SQL query output into a file, via command line based SQLPlus.
Step 1: Nominate the output file location. In this case this is a Unix location.
SQL> spool /tmp/output.txt
Step 2: Perform your SQL query.
SQL> select * from tab;
Step 3: Close the file.
SQL> spool off
Tip: Use set lines statement to adjust output display
Tags: log, oracle, sql, sqlplus, unix
Starting / Shutdown Oracle Database in Unix
| Posted by watashii | Filed under Database, Unix
This simple guide to shows how to start /stop your Oracle database from Unix. You must have logon access to do this.
Step 1
Unix login as oracle (usually the user that installed the database)
Step 2
Make sure your environment variables are set, eg:
ORACLE_SID=MYDBSID
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1/lib
PATH=/usr/bin:/usr/sbin:/usr/ucb:/usr/local:/usr/local/bin:/usr/local/sbin:/opt/oracle/product/11.2.0/dbhome_1/binStep 3 – Start DB Instance
On the Unix command line, run sqlplus with sysdba, and simply type startup in the prompt to start it up.
oracle@db-srv-01 % sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 30 12:06:47 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3207790592 bytes
Fixed Size 2152368 bytes
Variable Size 2449475664 bytes
Database Buffers 738197504 bytes
Redo Buffers 17965056 bytes
Database mounted.
Database opened.Step 4 – Start Listener
To start the oracle listener (assuming listener.ora is setup), run lsnrctl start
oracle@db-srv-01 % lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 30-NOV-2010 12:00:34
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /opt/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
System parameter file is /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/db-srv-01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-srv-01)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-srv-01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2010 12:00:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/db-srv-01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-srv-01)(PORT=1521)))
The listener supports no services
The command completed successfullyStep 4 – Shutdown
Similarly, to shutdown the instance, type shudown normal within sqlplus.
Stop the listener, by running lsnrctl stop
Tags: Database, oracle, shutdown, startup, unix
Unix Shell – Check If Directory Is Empty
| Posted by watashii | Filed under Programming, Unix
Here is a simple unix shell command to check if a directory, $dir, is empty.
export $dir=/tmp/myfiles/
if [ "$(ls -A $dir)" ];
then
echo "$dir NOT Empty";
else
echo "$dir IS Empty";
fi
Tags: directory, empty, shell, unix
Unix LS Command With Colors… Without the —color Option
| Posted by watashii | Filed under Programming, Unix
Having command line text in colors makes things easier to read. For example the ls command for listing directories and files. In some Unix environments, the ls command may not support the –color option to let us do that (as it requires GNU ls). So how can we workaround this to add color to ls?
Here are my steps to make it happen. Note i’m no Unix guru, and I didn’t have time to perfect it.. but it gets the job done!
Exercise Your Idle System Doing Nothing
| Posted by watashii | Filed under Unix
Via Unix/Linux:
cat /dev/zero > /dev/nullor
cp /dev/zero /dev/nullExplaination:
/dev/null is basically a black hole (a special null file), anything written to it goes down the drain. Anything read from it will result in nothing returned. /dev/zero is basically the same as /dev/null when writing to it, however reading it will result in a continuous stream of zeros without EOF.
Tags: null, performance, unix, zero
Unix Split, Join & Validate Files
| Posted by watashii | Filed under Programming, UnixProblem
I have a large file.
How can i split it into multiple parts, then join it back together later?
How do i make sure my files don’t corrupt?
I am using Unix.
$ ls -l *.pkg
-rw-rw-r-- 1 watashii watashii 648773362 Nov 28 18:01 largefile.pkgSplitting the files
The following Unix command splits a large file into 50MB (52,428,800 bytes) pieces. The output is a list of files named xaa, xab etc
$ split -b 52428800 largefile.pkg
$ ls -l x*
-rw-rw-r-- 1 watashii watashii 52428800 Nov 28 18:01 xaa
-rw-rw-r-- 1 watashii watashii 52428800 Nov 28 18:01 xab
...
-rw-rw-r-- 1 watashii watashii 19627762 Nov 28 18:01 xamJoining the files
To join the files together, we simply use the cat command over each file in ascending order:
$ cat x* > mergedlargefile.pkgValidating the files
To validate the files, below are some suggestions. File compare it, diff it, run a checksum to compare it.
$ ls -l *.pkg
-rw-rw-r-- 1 watashii watashii 648773362 Nov 28 18:01 largefile.pkg
-rw-rw-r-- 1 watashii watashii 648773362 Nov 28 18:13 mergedlargefile.pkg
$ cmp largefile.pkg mergedlargefile.pkg
$ cksum largefile.pkg
3332922138 648773362 largefile.pkg
$ cksum mergedlargefile.pkg
3332922138 648773362 mergedlargefile.pkg
$ diff largefile.pkg mergedlargefile.pkgTags: join, merge, split, unix
SSH Passwordless Authentication
| Posted by watashii | Filed under Software, Unix
This post shows how to use PuTTY (Windows) to login to a remote server (UNIX) over SSH without password.
Tags: authentication, passwordless, putty, ssh, unix
Unix ps command – Long Process Listing
| Posted by watashii | Filed under Programming, UnixWhen viewing full process listings with ps -ef command, sometimes the command name is too long and gets chopped off at the end of the screen. This command name has a limit of 80 characters.
netcool@sun61 [/opt/netcool] 688 % ps -ef|grep 6176 netcool 6176 1 0 Sep 12 ? 31:55 /opt/netcool/platform/solaris2/jre_1.5.0/bin/java -Xmx1024m -Xms256m -Djava.awt
So how can we show the entire command? The solution is to use the /usr/ucb/ps -auxxwww command:
netcool@sun61 [/opt/netcool] 689 % /usr/ucb/ps -auxxwww|grep -i 6176 netcool 6176 0.1 10.5610440428552 ? R Sep 12 31:55 /opt/netcool/platform/solaris2/jre_1.5.0/bin/java -Xmx1024m -Xms256m -Djava.awt.headless=true -Dnchome=/opt/netcool -Dngfhome=/opt/netcool/guifoundation -Dfile.encoding=UTF-8 -Dsun.jnu.encoding=UTF-8 -Dbase.directory=/opt/netcool -Dsm.props.directory=/opt/netcool/etc/sm -Djava.security.auth.login.config=/opt/netcool/guifoundation/conf/mach_jaas.config -Djava.endorsed.dirs=/opt/netcool/guifoundation/common/endorsed -classpath /opt/netcool/platform/solaris2/jre_1.5.0/lib/tools.jar:/opt/netcool/guifoundation/bin/bootstrap.jar -Dcatalina.base=/opt/netcool/guifoundation -Dcatalina.home=/opt/netcool/guifoundation -Djava.io.tmpdir=/opt/netcool/guifoundation/temp org.apache.catalina.startup.Bootstrap start
UNIX file size listing – du command
| Posted by watashii | Filed under Programming, Unix
Question: How can I list the true file and directory sizes on UNIX?
boadmin@nshost1>$ ls -la
total 12130522
drwxr-xr-x 5 boadmin boadmin 512 Aug 4 14:30 .
drwxr-xr-x 31 boadmin boadmin 1024 Aug 1 22:00 ..
drwxr-xr-x 5 boadmin boadmin 512 May 7 05:48 DISK_1
drwxr-xr-x 3 boadmin boadmin 512 May 7 05:47 DISK_2
drwxr-xr-x 3 boadmin boadmin 512 May 31 14:02 sp3
-rw-r--r-- 1 boadmin boadmin 6207767040 Aug 4 14:33 sp3.tarTags: directory size, du, file size, size listing, unix
Unix – Creating Cron Jobs with Crontab
| Posted by watashii | Filed under Programming, UnixCron is a Unix (*nix) utility for scheduling tasks to run in the background. These are known as cron jobs. A crontab is basically a text file (cron table) containing a list of commands to be run, under the current logged-in user.
- Commands
crontab -e # Edit (or create) the crontab file
crontab -l # View the crontab file
crontab -r # Remove the crontab file - Restrictions
Crontab access is controlled with a cron.allow and a cron.deny file, listing the associated usernames. If cron.deny exists, and cron.allow is missing, all users can use crontab. If both missing then only root is allowed.
whereis cron - Crontab file syntax
The syntax contains 5 fields specifying the scheduling datetime values, and the last portion contains the Unix command to run.
# +---------------- minute (0 - 59)
# | +------------- hour (0 - 23)
# | | +---------- day of month (1 - 31)
# | | | +------- month (1 - 12)
# | | | | +---- day of week (0 - 7) (Sunday=0 or 7)
# | | | | |
* * * * * command to be executed
On each of the 5 fields, 3 operators can be used to specify the datetime selection.
# Comma (',') specifies a list of values, eg: "1,3,4,7"
# Dash ('-') specifies a range, eg: "1-3" or "1,2,3"
# Asterisk ('*') specifies all possible values for a field. - Crontab file example
The following job lists the tmp directory structure and appends the output to a log file every minute.
0-59 * * * * ls -la /tmp >> /tmp/log.txt
