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
  • Row Triggers and Statement Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD OF Triggers
  • Triggers on System Events and User Events

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;


Read More Add your Comment 0 comments

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 -sh



Read More Add your Comment 0 comments

How 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.
$ top


Read More Add your Comment 0 comments

How 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)
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:# sar
12:00:02 AM       CPU     %user     %nice   %system   %iowait     %idle
12:10:01 AM       all      1.05      0.00      0.28      0.04     98.64


Read More Add your Comment 0 comments

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)

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


Read More Add your Comment 0 comments

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.


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)


Read More Add your Comment 0 comments

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]

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.


Read More Add your Comment 0 comments

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.

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


Read More Add your Comment 0 comments

What is Linux ?



Linux


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


Read More Add your Comment 0 comments

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)
Windows Database Shutdown

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)


Read More Add your Comment 0 comments
 

© 2010 Codes & Concepts All Rights Reserved Thesis WordPress Theme Converted into Blogger Template by Hack Tutors.info