SQL SERVER SPECIALIST
This Blog provide you the knowledge regarding DBMS and RDBMS.Specially for SQL SERVER and Oracle. Thanks A Lot .......for your cooperation and support.
Friday, 22 March 2013
Wednesday, 11 January 2012
Check how much space used by Data file in Sql Server
select
a.FILEID,
NAME = left(a.NAME,15),
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a;
a.FILEID,
NAME = left(a.NAME,15),
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a;
Check when last time server re-start
SELECT
[ms_ticks] AS ms_since_restart,
[ms_ticks]/1000 AS seconds_since_restart,
CAST([ms_ticks]/1000/60.0 AS DECIMAL(15,2)) AS minutes_since_restart,
CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2)) AS hours_since_restart,
CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2)) AS days_since_restart,
DATEADD(s,((-1)*([ms_ticks]/1000)),GETDATE()) AS time_of_last_restart
FROM sys.[dm_os_sys_info];
=========================================================================
SELECT
DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()) AS last_SERVER_restart,
D.create_date,
(DATEDIFF(s, DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()), D.create_date)) AS recovery_time_seconds
FROM sys.[dm_os_sys_info] DOSI
CROSS JOIN sys.[databases] D
WHERE D.[name] = 'tempdb';
[ms_ticks] AS ms_since_restart,
[ms_ticks]/1000 AS seconds_since_restart,
CAST([ms_ticks]/1000/60.0 AS DECIMAL(15,2)) AS minutes_since_restart,
CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2)) AS hours_since_restart,
CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2)) AS days_since_restart,
DATEADD(s,((-1)*([ms_ticks]/1000)),GETDATE()) AS time_of_last_restart
FROM sys.[dm_os_sys_info];
=========================================================================
SELECT
DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()) AS last_SERVER_restart,
D.create_date,
(DATEDIFF(s, DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()), D.create_date)) AS recovery_time_seconds
FROM sys.[dm_os_sys_info] DOSI
CROSS JOIN sys.[databases] D
WHERE D.[name] = 'tempdb';
Thursday, 18 August 2011
How to fine out last DML operation on table in oracle
SELECT * FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER='schemaname' AND TABLE_NAME='tablename'
How to fine dump file location for alert log of database
sql> show parameter dump;
please select the bdump location and check the error in latest alter log
How much free space avilable in table spaces into Oracle
How much free space avilable in table spaces
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
Find out table space in auto extend mode or not
select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,status from dba_data_files order by tablespace_name;
How to change open cursor value in Pfile into oracle
1. Create pfile=’/tmp/initcisora01.ora’ from spfile;
2. shutdown the database;
3. edit the open_cursors=400 in your pfile that is created on /tmp newly and save it.
4. open the database using newly created pfile i.e.
Startup pfile=’ /tmp/initcisora01.ora’;
5. check whether open_cursor is reflecting 400 now.
Show parameter open_cursors;
6. now create new spfile from newly created pfile.
Create spfile from pfile=’/tmp/initcisora01.ora’;
7. normal shutdown and normal startup the database;
How to find overall size of database and actual size of database in oracle
SQL> select sum(bytes)/1024/1024/1024 "overall size in GB" from dba_data_files;
SQL> select sum(bytes)/1024/1024/1024 "used space size in GB" FROM dba_segments;
update statistics for all objects in one paticular schema into oracle 10 G
Exec dbms_stats.gather_schema_stats(ownname=>'schemaname',cascade=>true,options=>'gather',degree=>4);
Thursday, 4 August 2011
Most usableDMV in SQL SERVER 2008/2005
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_exec_query_stats
sys.dm_os_wait_stats
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
sys.dm_db_missing_index_details
Please take a close look on all above dmv's
Tuesday, 2 August 2011
DB Link creation in Oracle
The type of database links that oracle lets you create are private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database.
A private link creates a link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.
The syntax for creating a private database link is:
CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
e.g.
CREATE DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';
If the above statement is created in scott schema of the local database then only scott user of the local database can use the database link.
A public link creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.
The syntax for creating a public database link is:
CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
e.g.
CREATE PUBLIC DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';
All the users in the database inclusive of the user scott can use the database link.
A Global database link creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. To create Global database links we must ensure that:
§ Global database link names must match database global names
§ Your GLOBAL_NAMES initialization parameter should be set to TRUE
§ You must enable the plug and play setting for your listeners
The initialization parameter file should contain the following entries:
DB_NAME = <DB NAME>
DB_DOMAIN = <DOMAIN NAME>
GLOBAL_NAMES = TRUE
USE_PLUG_AND_PLAY_LISTENER = ON
CREATE DATABASE LINK <database link name> USING '<database name>'; |
When you create a database link, you determine which user should connect to the remote database to access the data. There are three different types of users and they are:
§ Connected user
§ Current user
§ Fixed user
This link always connects as the currently connected user. This requires the user using the link to have an ID in both databases. When using a connected user link, your access is in the remote database is defined by the privileges of that ID in the remote database.
CREATE DATABASE LINK <database link name> USING '<remote database>'; |
e.g.
CREATE PUBLIC DATABASE LINK TEST USING 'BEADMISC';
A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link.
Current user links are an aspect of the Oracle Advanced Security option.
CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO CURRENT_USER using '<remote database>'; |
This is the normal database link were we provide the credential of the remote database when we create the database link.
CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
The privileges required to create, drop, alter database link are:
§ Create database link
§ Create public database link
§ Alter database link
§ Drop database link
§ Alter public database link
§ Drop public database link
The important views that can be used to monitor database links are:
§ ALL_DB_LINKS
§ DBA_DB_LINKS
§ USER_DB_LINKS
§ V$DBLINK
§ GV$DBLINK
The views ALL_DB_LINKS, DBA_DB_LINKS, USER_DB_LINKS lists all database links for the connected user, whole database and schema owner.
The views V$DBLINK and GV$DBLINK show the database link connections that are currently open in your current session:
CREATE DATABASE LINK test_db_link CONNECT TO
eserv IDENTIFIED BY vre10g USING 'beadmisc';
Step 1:
Access the remote database using the link:
select * from dba_db_links;
Step 2:
select * from v$dblink;
The output will be displayed as in the below screen shot.
Some of the important columns are:
DB_LINK | The name of the database link. |
HETROGENEOUS | If yes that means the database link is pointing to a database other than oracle. |
OPEN_CURSORS | Number of remote connections established or queries that are executed. |
IN_TRANSACTION | If Y it means the transaction has not been committed. |
LOGGED_ON | Is the current session open. |
Step 3:
Issue a COMMIT;
Run the below query:
select db_link,in_transaction from v$dblink;
You will see that in_transaction column has value of NO.
If you access a database link in a session, then the link remains open until you close the session. For example:
§ If 5 users/ schemas access the same public link in a local database, then 5 database link connections are open are open in the remote database.
§ If 5 users/ schemas and each user access a private link, then 5 database link connections are open.
§ If one user accesses 5 different links, then 5 database link connections are open.
When you close a session, the links that were active in the session are automatically closed.
To close the database link manually please execute the below command.
ALTER SESSION CLOSE DATABASE LINK <database link name>; |
Note that this statement only closes the links that are active in your current session and the user/schema must have the alter session privilege.
The statement to drop a database link is given below:
DROP DATABASE LINK <database link name>; |
You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.
The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.
To view the OPEN_LINKS value executes the query below:
select * from v$parameter where upper(name) ='OPEN_LINKS'
The changes can be made to the init<db_name>.ora or can be changed dynamically by the DBAs.
After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. To create local transparency we can use
§ Views
§ Synonyms
§ Procedures
Syntax using synonym:
CREATE synonym <synonym_name> FOR [schema.]object_name[@database_link_name]; |
e.g.
CREATE SYNONYM test_syn FOR scott.emp@db_link_test;
Similarly we can create synonyms for packages and procedures in the remote database and use them as our local copy.
The following operations cannot be used using database links:
1. Grant privileges on remote objects.
2. Execute DESCRIBE operations on some remote objects except for the following objects:
§ Tables
§ Views
§ Procedures
§ Functions
3. Analyze remote objects.
4. Define or enforce referential integrity.
5. Grant roles to users in a remote database.
6. Execute hash query joins that use shared server connections.
7. Use a current user link without authentication through SSL, password, or NT native authentication.
A database must guarantee that all statements in a transaction, distributed or non-distributed, either commit or roll back as a unit. The effects of an ongoing transaction should be invisible to all other transactions at all nodes; this transparency should be true for transactions that include any type of operation, including queries, updates, or remote procedure calls.
The general mechanisms of transaction control in a non-distributed database are discussed in the Oracle Database Concepts. In a distributed database, the database must coordinate transaction control with the same characteristics over a network and maintain data consistency, even if a network or system failure occurs. The database two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. A two-phase commit mechanism also protects implicit DML operations performed by integrity constraints, remote procedure calls, and triggers.
The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database. The database link provides local transparency by creating synonyms for tables, packages and procedures in the remote database. The user can access these objects just as there local objects. Overall database links provide lot of flexibility in distributed computing they must be used judiciously to gain most advantages.
Unic Shell Script for Oracle DBA
Introduction:
In Unix there are many ways to accomplish a given task. Given a problem to solve, we may be able to get to a solution in any number of ways. Of course, some will be more efficient, be more readable, use less disk space or memory, may or may not give the user feedback on what is going on or give more accurate details and more precision to the result. We are going describe the usage of various commands in Unix in Oracle point of view.
What is a Shell Script?
A shell is an environment in which we can run our commands, programs, and shell scripts. There are different flavors of shells, just as there are different flavors of operating systems. Each flavor of shell has its own set of recognized commands and functions.
The basic concept of a shell script is a list of commands, which are listed in the order of execution. A good shell script will have comments, preceded by a pound sign, #, describing the steps. Unix is case sensitive. Because Unix is case sensitive our shell scripts are also case sensitive.
Shell scripts and functions are both interpreted. This means they are not compiled. Both shell scripts and functions are ASCII text that is read by the Korn shell command interpreter. When we execute a shell script, or function, a command interpreter goes through the ASCII text line by line, loop by loop, test by test and executes each statement, as each line is reached from the top to the bottom.
Unix Special Characters
All of the following characters have a special meaning or function. If they are used in a way that their special meaning is not needed then they must be escaped. To escape, or remove its special function, the character must be immediately preceded with a backslash, \, or enclosed within ‘ ‘ forward tic marks (single quotes).
\ ( ; # $ ? & * ( ) [ ] ` ‘ “ +
Different Types of Shells to Declare
#!/usr/bin/sh OR #!/bin/sh Declares a Bourne shell
#!/usr/bin/ksh OR #!/bin/ksh Declares a Korn shell
#!/usr/bin/csh OR #!/bin/csh Declares a C shell
#!/usr/bin/bash OR #!/bin/bash Declares a Bourne-Again shell
Control Structures
Similar to C language, The following control structures will be used extensively.
if ... then Statement if ... then ... else Statement
if [ test_command ] if [ test_command ]
then then
commands commands
fi else
commands
fi
if ... then ... elif ... (else) Statement
if [ test_command ]
then
commands
elif [ test_command ]
then
commands
elif [ test_command ]
then
commands
.
.
.
else (Optional)
commands
fi
for ... in Statement
for loop_variable in argument_list
do
commands
done
while Statement
while test_command_is_true
do
commands
done
Until Statement
until test_command_is_true
do
commands
done
Case Statement
case $variable in
match_1)
commands_to_execute_for_1
;;
match_2)
commands_to_execute_for_2
;;
match_3)
commands_to_execute_for_3
;;
.
.
.
*) (Optional - any other value)
commands_to_execute_for_no_match
;;
esac
Using break, continue, exit, and return
It is sometimes necessary to break out of a for or while loop, continue in the next block of code, exit completely out of the script, or return a function’s result back to the script that called the function. break is used to terminate the execution of the entire loop, after completing the execution of all of the lines of code up to the break statement. It then steps down to the code following the end of the loop.
continue is used to transfer control to the next set of code, but it continues execution of the loop.
exit will do just what one would expect: It exits the entire script. An integer may be added to an exit command (for example, exit 0), which will be sent as the return code.
return is used in a function to send data back, or return a result, to the calling script.
Shell Script Commands
The basis for the shell script is the automation of a series of commands. We can execute most any command in a shell script that we can execute from the command line.
COMMAND DESCRIPTION
passwd Change user password
pwd Print current directory
cd Change directory
ls List of files in a directory
wildcards * matches any number of characters, ? matches a single character
file Print the type of file
cat Display the contents of a file
pr Display the contents of a file
pg or page Display the contents of a file one page at a time
more Display the contents of a file one page at a time
clear Clear the screen
cp or copy Copy a file
chown Change the owner of a file
chgrp Change the group of a file
chmod Change file modes, permissions
rm Remove a file from the system
mv Rename a file
mkdir Create a directory
rmdir Remove a directory
grep Pattern matching
egrep grep command for extended regular expressions
find Used to locate files and directories
>> Append to the end of a file
> Redirect, create, or overwrite a file
| Pipe, used to string commands together
|| Logical OR—command1 || command2—execute command2 if command1 fails
& Execute in background
&& Logical AND—command1 && command2—execute command2 if command1 succeeds
date Display the system date and time
echo Write strings to standard output
sleep Execution halts for the specified number of seconds
wc Count the number of words, lines, and characters in a file
head View the top of a file
tail View the end of a file
diff Compare two files
sdiff Compare two files side by side (requires 132-character display)
spell Spell checker
lp, lpr, enq, qprt Print a file
lpstat Status of system print queues
enable Enable, or start, a print queue
disable Disable, or stop, a print queue
cal Display a calendar
who Display information about users on the system
w Extended who command
whoami Display $LOGNAME or $USER environment parameter
who am I Display login name, terminal, login date/time, and where logged in
f, finger Information about logged-in users including the users .plan and .project
talk Two users have a split screen conversation
write Display a message on a user’s screen
wall Display a message on all logged-in users’ screens
rwall Display a message to all users on a remote host
rsh or remsh Execute a command, or log in, on a remote host
df Filesystems statistics
ps Information on currently running processes
netstat Show network status
vmstat Show virtual memory status
iostat Show input/output status
uname Name of the current operating system, as well as machine information
sar System activity report
basename Base filename of a string parameter
man Display the on-line reference manual
su Switch to another user, also known as super-user
cut Write out selected characters
awk Programming language to parse characters
sed Programming language for character substitution
vi Start the vi editor
emacs Start the emacs editor
Variables
Avariable is a character string to which we assign a value. The value assigned could be a number, text, filename, device, or any other type of data. A variable is nothing more than a pointer to the actual data.
Symbol Commands
( ) Run the enclosed command in a sub-shell
(( )) Evaluate and assign value to variable and do math in a shell
$(( )) Evaluate the enclosed expression
[ ] Same as the test command
[[ ]] Used for string comparison
$( ) Command substitution
`command` Command substitution
Math in a Shell Script
We can do arithmetic in a shell script easily. The Korn shell let command and the ((expr)) command expressions are the most commonly used methods to evaluate an integer expression. Later we will also cover the bc function to do floating-point arithmetic.
Cron Tables
A cron table is a system file that is read every minute by the system and will execute any entry that is scheduled to execute in that minute. By default, any user can create a cron table with the crontab -e command, but the Systems Administrator can control which users are allowed to create and edit cron tables with the cron.allow and cron.deny files. When a user creates his or her own cron table the commands, programs, or scripts will execute in that user’s environment. It is the same thing as running the user’s $HOME/.profile before executing the command.
The crontab -e command starts the default text editor, vi or emacs, on the user’s cron table.
How to use ftp in a shell script:
Sometimes we want to FTP a file from one machine to another. Usually, we can do the transfer interactively, but every so often, a shell script do the file transfer.
The problem we always encountered in scripting ftp transfers involved getting a password to the ftp server. Typical ftp client programs under Unix, Linux, Solaris and NetBSD all read the ftp password from /dev/tty.
Example Working Script
#!/bin/sh
HOST='ftp.users.qwest.net'
USER='yourid'
PASSWD='yourpw'
FILE='file.txt'
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put $FILE
quit
END_SCRIPT
exit 0
The Tricks
Getting the password to the ftp server without having the ftp client program read the password from /dev/tty requires two tricks:
Using the -n option on the ftp client program to prevent the ftp client from trying to log in immediately. That way, the ftp client does not ask for a user ID and password. No use of /dev/tty.
Use the ftp client program command quote to send user ID and password to the ftp server.
Further Refinements
The above sh script will spew lots of ftp client output to standard output. Even if everything works perfectly, the user running the above script will see lots of incomprehensible text scrolling by quite rapidly. One refinement would send output to different places:
ftp -n $HOST > /tmp/ftp.worked 2> /tmp/ftp.failed <<END_SCRIPT
One could further refine error handling by acting on the ftp client program's exit status:
ftp -n $HOST > /tmp/ftp.worked 2> /tmp/ftp.failed <<END_SCRIPT
blah blah
END_SCRIPT
EXITSTATUS=$?
if [ $EXITSTATUS != "0" ]
then
# handle the error...
fi
Except that the above doesn't always work - most FTP clients always exit with a status of 0. This leads to ugly "false negatives": the file transfer fails, but the script doesn't detect the problem.
One way to verify that a file transfer took place - transfer it back:
#!/bin/sh
ftp -n << END_SCRIPT
open $1
user $2 $3
put $4
get $4 retrieval.$$
bye
END_SCRIPT
if [ -f retrieval.$$ ]
then
echo "FTP of $4 to $1 worked"
rm -f retrieval.$$
else
echo "FTP of $4 did not work"
fi
Regular FTPs there and back of large files can consume a lot of time.
Control of ftp by a shell script
One obvious improvement would have the ftp client program controlled by the shell script. I don't think that would comprise an impossible task, but we also don't think that it would have much value. Scripting ftp transfer using expect might cause you less pain.
How to Submit Concurrent Program Using CONCSUB:
Syntax:
CONCSUB <ORACLE ID>
<ResponsIbility Application Short Name>
<ResponsIbility Name>
<User Name>
CONCURRENT
<Concurrent Program Application Short Name>
<Concurrent Program Name>
[START=<Requested Start Date>]
[REPEAT_DAYS=<Repeat Interval>]
[REPEAT_END=<Request Resubmission End Date>]
<Concurrent Program Arguments ...>
Example to shutdown the concurrent manager
CONCSUB APPLSYS/FND SYSADMIN 'System Administrator' SYSADMIN [WAIT={Y|N}]
CONCURRENT FND [SHUTDOWN|ABORT]
ORACLE ID: Enter the username and password of the ORACLE ID you want your concurrent program to
run in. Enter the username and password without any spaces and separated by a slash ("/").
Responsibility Application Short Name: Enter the short name of the application for your responsibility.
This name, along with your responsibility name, will be used to select a responsibility for
your concurrent request to run in.
Responsibility Name: Enter the name of your responsibility. This name, along with your responsibility
application short name, will be used to select a responsibility for your concurrent request to
run in.
User Name: Enter the name of your Application ObjectLibrary user. This name will be used to update the
Who information for any data your concurrent program changes and to create report output
file name for this request.
Concurrent Program Application Short Name: Enter the short name of the application for your concurrent
program. This name, along with the concurrent program name, uniquely identifies the
concurrent program for your concurrent request.
Concurrent Program Name: Enter the name of your concurrent program. This name, along with the
concurrent program application short name, uniquely identifies the concurrent program for your
concurrent request.
Start Date: Specify the token START and enter the date you want the concurrent request to run, in DD-
MON-YY HH24:MI:SS format. Omit this argument if you want to run your concurrent request
immediately.^M^M<Repeat Interval> Specify the token REPEAT_DAYS and enter the time
interval, in days, for request resubmission. Use fractional number for intervals less than a day.
Omit this argument if you want to run your concurrent request only once.
Request Resubmission End Date: Specify the token REPEAT_END and enter the date you want the request
resubmission to stop, in DD-MON-YY HH24:MI:SS format. This argument is used in conjunction
with "Repeat Interval". Omit this argument if you want your request to repeat forever at an interval
specified by REPEAT_DAYS.
Concurrent Program Arguments: Enter the arguments for your concurrent program.
Sample HOST File to Submit Two concurrent Programs Using CONCSUB:
########################################################################
# Set the variables for running concurrent program.
########################################################################
CONCPRG="SQL_CONC_TEST"
CONCAPP="SEAR"
RESPAPP="SEAR"
RESP="SMK-AR-AFFILIATE"
USERNAM=`echo $1 | cut -d ' ' -f 5 | cut -d '"' -f 2`
echo $CONCPRG
echo $CONCAPP
echo $RESPAPP
echo $RESP
echo $USERNAM
########################################################################
CONCMSG=`noarg CONCSUB $FCP_LOGIN \
$RESPAPP \
$RESP \
$USERNAM \
WAIT=10 \
CONCURRENT \
$CONCAPP \
$CONCPRG`
########################################################################
# Set the variables for running concurrent program.
########################################################################
CONCPRG="PLSQL_CONC_TEST"
########################################################################
CONCMSG=`noarg CONCSUB $FCP_LOGIN \
$RESPAPP \
$RESP \
$USERNAM \
WAIT=10 \
CONCURRENT \
$CONCAPP \
$CONCPRG`
reqid=""
reqid=`echo $CONCMSG | cut -d " " -f 3`
CONCMSG=`echo $CONCMSG | cut -d'"' -f3`
y=""
y=`echo $reqid | grep "^[0-9]"`
if [ $? -ne 0 ]
then
echo "Failed to schedule SMARCONSS thru Control-M option ...\n"
exit 1
else
exit 0
fi
In the above script we have taken two concurrent programs named SQL_CONC_TEST and PL/SQL_CONC_TEST both consists simple PL/SQL code. These are assigned to “SMK-AR-AFFILIATE” responsibility and its short name is “SEAR”. Here we have taken two CONCSUB commands to submit the concurrent programs.
Mailing using UNIX Shell Script:
mailx - interactive message processing system
SYNOPSIS
mailx [-BdeHiInNURvV~] [ -f [file | +folder] ] [-T file] [-u user]
mailx [-BdFintUv~] [-b bcc] [-c cc] [-h number] [-r address] [-s subject] recipient...
/usr/ucb/mail ...
/usr/ucb/Mail ...
DESCRIPTION
The mail utilities listed above provide a comfortable, flexible environment for sending and receiving mail messages electronically.
When reading mail, the mail utilities provide commands to facilitate saving, deleting, and responding to messages. When sending mail, the mail utilities allow editing, reviewing and other modification of the message as it is entered. Incoming mail is stored in a standard file for each user, called the mailbox for that user. When the mail utilities are called to read messages, the mailbox is the default place to find them. As messages are read, they are marked to be moved to a secondary file for storage, unless specific action is taken, so that the messages need not be seen again. This secondary file is called the mbox and is normally located in the user's HOME directory (see MBOX in ENVIRONMENT VARIABLES for a description of this file). Messages can be saved in other secondary files named by the user. Messages remain in a secondary file until forcibly removed.
The user can access a secondary file by using the -f option. Messages in the secondary file can then be read or otherwise processed using the same Commands as in the primary mailbox. This gives rise within these pages to the notion of a current mailbox.
OPTIONS
On the command line options start with a dash (-). Any other arguments are taken to be destinations (recipients). If no recipients are specified, mailx attempts to read messages from the mailbox.
-B Do not buffer standard input or standard output.
-b bcc Set the blind carbon copy list to bcc. Bcc should be enclosed in quotes if it
contains more than one name.
-c cc Set the carbon copy list to cc. cc should be enclosed in quotes if it contains more
than one name.
-d Turn on debugging output. (Neither particularly interesting nor recommended.)
-e Test for the presence of mail. mailx prints nothing and exits with a successful
return code if there is mail to read.
-F Record the message in a file named after the first recipient. Overrides the record
variable, if set (see Internal Variables).
-f [file] Read messages from file instead of mailbox. If no file is specified, the mbox
is used.
-f [ +folder] Use the file folder in the folder directory (same as the folder command).
The name of this directory is listed in the folder variable.
-H Print header summary only.
-h number The number of network "hops" made so far. This is provided for network
software to avoid infinite delivery loops. This option and its argument are passed
to the delivery program.
-I Include the newsgroup and article-id header lines when printing mail messages.
This option requires the –f option to be specified.
-i Ignore interrupts. See also ignore in Internal Variables.
-N Do not print initial header summary.
-n Do not initialize from the system default mailx.rc or Mail.rc file.
See USAGE.: Set the Subject header field to subject. Subject should be enclosed in quotes if it contains embedded white space.
-T file Message-id and article-id header lines are recorded in file after the message is
read. This option also sets the -I option.
-t Scan the input for To:, Cc:, and Bcc: fields. Any recipients on the command
line will be ignored.
-U Convert UUCP-style addresses to internet standards. Overrides the conv
environment variable.
-u user
Read user's mailbox. This is only effective if user's mailbox is not read protected.
-V Print the mailx version number and exit.
-v Pass the -v flag to send mail (1M).
-~ Interpret tilde escapes in the input even if not reading from a tty.
INT_CODE=`echo $1 | cut -f1 -d' '`
INT_REQID=`echo $1 | cut -f2 -d' ' | cut -f2 -d'='`
INT_REQID=`expr $INT_REQID \* 1`
LOGIN=`echo $1 | cut -f3 -d' ' | cut -f2 -d '"'`
USERNAME=`echo $1 | cut -f5 -d' ' | cut -f2 -d'"'`
FUSER=`echo $USERNAME | cut -c1-8`
PER_NAME=`echo $1 | cut -f9 -d' ' | cut -f2 -d '"'`
echo $INT_CODE
echo $INT_REQID
echo $LOGIN
echo $USERNAME
echo $FUSER
echo $PER_NAME
var=`echo $INT_CODE $INT_REQID $LOGIN $USERNAME $FUSER $PER_NAME`
mailx -m -s $var giridhar.gedela@wipro.com
Reference:
Mastering Unix Shell Scripting by Randal K. Michael
Subscribe to:
Posts (Atom)