What are triggers in oracle ?
Oracle lets us define procedures called triggers that run implicitly when an
INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database
Triggers are procedures that are stored in the database and implicitly run, or fired, when some event occurs.
SQL Statements Allowed in Trigger Bodies
The body of a trigger can contain DML SQL statements. It can also contain
SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor.
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger.
ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are allowed.
Types of Triggers
Sample script of triggers:
create or replace trigger trigchk
before insert or update or delete
on emp
begin
if(to_char(sysdate,'DY') in ('SAT','SUN')) then
raise_application_error(-20101,'u r not allowed to do any transaction on weekend');
end if;
end;
-------------------------------------------------------------------------------------------------------
create or replace trigger trigsal
before update
on emp for each row
begin
if(:old.sal>:new.sal) then
raise_application_error(-20103,'nat a valid action');
end if;
end;
----------------------------------------------------------------------------
Create or replace trigger trigview
INSTEAD OF INSERT on vwempdept
for each row
begin
insert into dept(deptno,dname) values (seq.nextval,new.dname);
insert into emp(empno,ename,sal,deptno) values(:new.empno,:new.ename,
:new.sal,:new.deptno);
end;
how to find space usage in Linux ?
Linux offers two commands for checking out free disk space:
(a) df command : Report file system disk space usage
(b) du command : Estimate file space usage
df :- to check free disk space
Write df -h or df -k to list free disk space:
$ df -h
OR
$ df -k
du:- to check space consumption
du shows how much space one ore more files or directories is using.
$ du -shHow to find out memory utilization in Linux ?
Memory utilization means to measure the memory usage on machine. It can be done in various ways and using following commands:
1. Free
To display free memory size in MB (megabytes):
$ free -m
Sample outputs:
total used free shared buffers cached Mem: 750 625 125 0 35 335 -/+ buffers/cache: 254 496 Swap: 956 0 956
2. VMSTAT
Vmstat shows memory and other resources utilization at that point in time since last boot.
If vmstat fired with repetition then it will show usage with respect to last interval.
$ vmstat
3. TOP
The top command generates a frequently-updated list of processes. It shows usage of Memory in addition to CPU and swap space.
$ topHow to find Out Linux CPU Utilization?
Finding CPU utilization means to find out the CPU usage for every process or application.Linux provides lots of utilities and information regarding CPU usage.
* CPU utilization
* Display the utilization of each CPU individually (SMP cpu)
* Find out your system's average CPU utilization since the last reboot etc
* Determine which process is eating the CPU(s)
* Display the utilization of each CPU individually (SMP cpu)
* Find out your system's average CPU utilization since the last reboot etc
* Determine which process is eating the CPU(s)
Most useful commands are:
- TOP
- SAR
TOP
The top command generates a frequently-updated list of processes. By default, the processes are ordered by percentage of CPU usage, with only the "top" CPU consumers shown. The top command shows how much processing power and memory are being used, as well as other information about the running processes.
SAR
CPU activity, with SAR command:
# sar12:00:02 AM CPU %user %nice %system %iowait %idle
12:10:01 AM all 1.05 0.00 0.28 0.04 98.64
How to Run CRON JOB every 5 Minutes, Hours, Seconds, Months ?
How to Run Cron Every 5 Minutes, Hours, Seconds, Months
A cron job consists out of six fields:
<minute> <hour> <day of month> <month> <day of week> <command>
field allowed values
----- --------------
minute 0-59
hour 0-23
day of month 1-31
month 1-12 (or names, see below)
day of week 0-7 (0 or 7 is Sun, or use names)
----- --------------
minute 0-59
hour 0-23
day of month 1-31
month 1-12 (or names, see below)
day of week 0-7 (0 or 7 is Sun, or use names)
1. Execute a cron job every 5 Minutes
The first field is for Minutes. If you specify * in this field, it runs every minutes.
*/5 * * * * /home/user/test.sh
2. Execute a cron job every 5 Hours
The second field is for hours. If you specify * in this field, it runs every hour.
0 */5 * * * /home/user/test.sh
3. Execute a job every 5 Seconds
There is not direct method to write cron job to be scheduled every 5 seconds. The alternate way is to write a shell script that uses ‘sleep 5′ command in it. Example is below:
$ cat every-5-seconds.sh
#!/bin/bash
while true
do
/home/user/test.sh
sleep 5
done
Now, execute this shell script in the background using nohup as shown below. This will keep executing the script even after you logout from your session. This will execute your backup.sh shell script every 5 seconds.
$ nohup ./every-5-seconds.sh &
4. Execute a job every 5 months
There is no direct way of saying ‘every 5 months’, instead you have to specify what specific months you want to run the job. The following example runs the test.sh twice a year. i.e 1st May at midnight, and 1st Oct at midnight.
0 0 1 5,10 * /home/user/test.sh
What is Linux cron job ?
Cron is the time-based job scheduler in Unix-like computer operating systems. Cron enables users to schedule jobs (commands or shell scripts) to run periodically at certain times or dates. It is commonly used to automate system maintenance or administration.
Linux Cron utility is an effective way to schedule a routine background job at a specific time and/or day on an on-going basis.
Linux Cron utility is an effective way to schedule a routine background job at a specific time and/or day on an on-going basis.
crontab
The command to create/edit, list, and remove cron jobs is crontab. If you call it with the -u option, it specifies the name of the user whose crontab is to be tweaked. If this option is not given, crontab examines crontab
Examples:
crontab -l
lists the cron jobs of the user as that you are currently logged in:
lists all cron jobs of exampleuser.
crontab -e
let's you create/modify the cron jobs of the user as that you are currently logged in (I'll come to the syntax in the next chapter).
crontab -u OSuser -e
let's you create/modify the cron jobs of OSuser
crontab -r
deletes all cron jobs of the user as that you're currently logged in.
crontab -u OSuser -r
deletes all cron jobs of OSuser
A cron job consists out of six fields:
<minute> <hour> <day of month> <month> <day of week> <command>
Linux Crontab Format
MIN HOUR DOM MON DOW CMD
* * * * * command to be executed
┬ ┬ ┬ ┬ ┬
│ │ │ │ │
│ │ │ │ │
│ │ │ │ └───── day of week (0 - 7) (0 or 7 are Sunday, or use names)
│ │ │ └────────── month (1 - 12)
│ │ └─────────────── day of month (1 - 31)
│ └──────────────────── hour (0 - 23)
└───────────────────────── min (0 - 59)
How to turn off and restart Linux ?
Shutting down, rebooting the computer from the shell or command line
Below is a list the commands that will enable a user to shut down, turn off, reboot, etc. their Linux computer from the command line.
- halt
- poweroff
- reboot
- shutdown
Commands with options:
/sbin/halt [-n] [-w] [-d] [-f] [-i] [-p] [-h]
/sbin/reboot [-n] [-w] [-d] [-f] [-i]
/sbin/poweroff [-n] [-w] [-d] [-f] [-i] [-h]
/sbin/reboot [-n] [-w] [-d] [-f] [-i]
/sbin/poweroff [-n] [-w] [-d] [-f] [-i] [-h]
OPTIONS
- -n
- Don't sync before reboot or halt.
- -w
- Don't actually reboot or halt but only write the wtmp record (in the /var/log/wtmp file).
- -d
- Don't write the wtmp record. The -n flag implies -d.
- -f
- Force halt or reboot, don't call shutdown
- -i
- Shut down all network interfaces just before halt or reboot.
- -h
- Put all harddrives on the system in standby mode just before halt or poweroff.
- -p
- When halting the system, do a poweroff. This is the default when halt is called as poweroff.
- Simply, Reboot command restart the Linux servers.
What are common Linux commands ?
List of Common Linux commands
Linux is most commonly used operating system. below is the cheat sheet for commonly used Linux commands.
Linux is most commonly used operating system. below is the cheat sheet for commonly used Linux commands.
|
Command
|
Description
|
|
Awk
[options] pattern [filenames]
|
Search files or output for a
particular pattern.
|
|
cat [filename]
|
Display file’s contents.
|
|
cd /directorypath
|
Change to directory.
|
|
chmod [options like -R] mode
filename
|
Change a file’s permissions.
|
|
chown [options like -R]
filename
|
Change who owns a file.
|
|
Clear
|
Clear a command line screen.
|
|
cp [options like -R] source
destination
|
Copy files and directories.
|
|
date [options]
|
Display or set the system
date and time.
|
|
df [options like -h]
|
Display used and available
disk space.
|
|
du [options like -s]
|
Show how much space each file
takes up.
|
|
file [options] filename
|
Determine what type of data
is within a file.
|
|
find [pathname] [expression]
|
Search for files matching a
provided pattern.
|
|
grep [options] pattern
[filesname]
|
Search files or output for a
particular pattern.
|
|
id
|
Shows detail about user
logged-Id including UID and Groups assigned.
|
|
kill [options] pid
|
Stop a process. If the
process refuses to stop, use kill -9 pid.
|
|
less [options] [filename]
|
View the contents of a file
one page at a time.
|
|
ln [options like -s] source
[destination]
|
Create a shortcut which is
called symlink.
|
|
locate filename
|
Search a copy of your file
system for the specified filename.
|
|
lpr [options]
|
Send a print job.
|
|
ls [options like -lrt]
|
List directory contents.
|
|
man [command]
|
Display the help information
for the specified command.
|
|
mkdir [options] directory
|
Create a new directory.
|
|
mv [options] source
destination
|
Rename or move file(s) or
directories.
|
|
passwd [name [password]]
|
Change the password or allow
(System administrator) to change any password.
|
|
ps [options]
|
Display a snapshot of the
currently running processes.
|
|
Pwd
|
Display the pathname for the
current directory.
|
|
rm [options like -rf]
directory
|
Remove (delete) file(s)
and/or directories.
|
|
rmdir [options] directory
|
Delete empty directories.
|
|
ssh [options] user@machine
|
Remotely log in to another
Linux machine, over the network. Close an ssh session by typing exit.
|
|
su [options] [user
[arguments]]
|
Switch to another user
account.
|
|
tail [options] [filename]
|
Display the last n lines of a file (the default is 10).
|
|
tar [options] filename
|
Store and extract files from
a tarfile (.tar) or tarball (.tar.gz or .tgz).
|
|
Top
|
Displays the resources being
used on your system. Press q to exit.
|
|
touch filename
|
Create an empty file with the
specified name.
|
|
who [options]
|
Display who is logged on i.e.
which user.
|
|
Scp [options]
user@machine:path
|
Copies file/directory to
other Linux machine
|
What is Linux ?
Linux
Distribution
Linux is
computer operating system based on the model of free
and open source software development.
It is leading operating system for servers based machines.It is followed by thousands of people each day.
Linux is an
increasingly popular operating system that is proving to be a viable
alternative to Microsoft’s Windows and Apple’s Mac operating systems. Linux is
open source.
Distribution
Although Linux is originally
the kernel created by Linus, it does not function as an OS generally available
with only the kernel. To function as an OS, various software applications must
be combined with the kernel. Thus, the combination of the kernel and some
application software supporting it for use as a OS is called a “distribution”.
Linux Advantages:
·
Optimized
for highly scalable/Multicore systems
·
Manages
system complexity
·
Reduces
data bottlenecks
·
Improves
application performance
·
Reduce
power consumption
·
Ensures
end-to-end data integrity
Types of Linux systems:
·
Red-hat
enterprise linux
·
Ubuntu
·
Oracle
enterprise linux
How to stop oracle database ?
Stopping/Shutdown the Oracle Database
There are certain ways to start oracle database like:- Using SQLPLUS
- Using Oracle Enterprise manager (If DB is configured with EM)
- Using Oracle Grid/cloud control (If DB is configured with GC)
1. Logon to the database server as the administrator/OS user 2. To start the database instance, go to Start -> Settings -> Control Panel -> Administrative Tools -> Services.
3. Scroll down to the service for the database, for example: OracleServiceORCL
4. Right click and select Stop. A dialog box appears showing the progress of the shut-donw and after a few seconds it will disappear.
The database instance service is now stopped
Linux Database Shutdown
1. Logon to the database server as the administrator/OS user
2. To start the database instance, go to Terminal and set below environment variable if already not.
On Bash shell,
export ORACLE_HOME=<ORACLE_HOME value like /u01/app/oracle/product/11.2/db_1>
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=<DB Name like orcl>
3. Connect to the database:
sqlplus / as sysdba OR Sqlplus sys/<pwd>@<TNS> as sysdba
4.enter the following command:
shutdown;
OR
Shutdown immediate;
OR
Shutdown abort; (for instant DB shutdown)
