Tablespace info using Command Prompt

In the absence of any UI based Oracle Database access tools, one has to access the same using SQL PLUS.

The following tables are helpful in getting information about tablespaces

Referred from http://www.cs.uvm.edu/oracle9doc/server.901/a90117/tspaces.htm#570

I have used the following tables:

V$TABLESPACE : Name and number of all tablespaces from the control file.

V$DATAFILE : Information about all datafiles, including tablespace number of owning tablespace.

DBA_DATA_FILES : Shows files (datafiles) belonging to tablespaces.

 

Calling stored Procedures from shell script

To call an oracle stored procedure from shell script you have to use sqlplus

sqlplus -SILENT "user/pwd@sid" <<!
exec "myProc";

using the SILENT option on sqlloader does not output anything related to sqlloader. You only get an output saying the procedure has successfully completed (or any error messages pertaining to the code)

 

Split a line in Shell Script

One of the ways to splitting a line in shell script is to use the IFS variable. This defines on what character to split the input. By default its space

Usage:
shell script

TEST="folder/filename";
# You want the filename part
IFS=/;
val="";
for i in $TEST
do
val=$i; # This stores the last value
done

echo $val; # This prints filename


If you want to get the name of the file only without the extension change the IFS to “.” and extract the first part of the name

This is as I found one of the ways. If there is any other way, please let me know.

 

Formatting date in Unix

One of the requirements of my task was to format the return value of `date` as YYYY-MM-DD format.

This is really simple.
In my shell script I wrote
echo `date +"%F"`
And the output came as 2008-09-29.

Doing a man or help on date will give you a list of all available options.

 

Returning from Perl File

I had a situation where I had to return some data from a Perl file. Not from a perl function but from the file itself to a shell script from where I was calling the Perl file.

The way to return from this is to write to the standard output.

This is how we do it:

Perl File HelloWorld.pl:

open (OUT, '>-');

This opens the standard output for writing

print OUT "Hello World"

Shell script:

RETVAL=`perl HelloWorld.pl`

echo $RETVAL;

 

xmllint: Good tool for xml check

In LINUX, xmllint is a good tool for checking the syntax of an xml file.

Usage: xmllint filename
This shows if there are any errors in the xml like missing tags, tags not matching etc.

To format the xml in the branched way, use xmllint format
xmllint --format filename
This gives the formatted xml file.

 

Getting Memory and CPU Info on LINUX

To get the memory and CPU information on LINUX machine use the following commands on your command prompt:

cat /proc/meminfo
cat /proc/cpuinfo

You need not login as ROOT user for this

 

SQL Loader options

The following are the options available for SQL Loader.

The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n — Number of logical records to skip (Default 0)

LOAD = n — Number of logical records to load (Default all)

ERRORS = n — Number of errors to allow (Default 50)

ROWS = n — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n — Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run

(header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE)

 

Varchar length in SQL Loader

SQL Loader by default limits the length of any varchar to 255 . Hence it throws an error that the maximum length has been exceeded though the column in the database has larger size and the text should fit in the length.

The fix is to specify the length of the field in the control file.
Ex:

....,
....,
NAME,
DESCRIPTION CHAR(1000),
....,


Similar fix should work for other data types as well

 

About Oracle Jobs

When creating oracle jobs for running stored procedures, we have to create them as the user who has the procedure ownership. We should not create them as SYSTEM user.
If we create the job as SYSTEM user, it will not be able to execute the proc. As far as I know this is because it uses the roles to get access to execute the procedures.
We did this way and then the job failed. Then we recreated it using the correct schema/user and it started working fine.

Note: If you execute the procedure using exec :proc_name: while logged in as SYSTEM user it would probably succeed, but it does not do so when created as a job. Not sure though why it succeeds when you do an exec :proc_name: