There’re different ways to get the values of the parameter file using PL/SQL. One of them is using DBMS_UTILITY.GET_PARAMETER_VALUE function. In the following example, I print the value of the compatible parameter of the parameter file:

[sourcecode language=”css”] 

SQL>set serveroutput on;
 declare
    id number;
    str varchar2(40);
 begin
    id:=dbms_utility.get_parameter_value(‘compatible’,id,str);
    dbms_output.put_line(str);
 end;
 /
SQL>   10.2.0.2.0
PL/SQL procedure successfully completed. [/sourcecode]

Today, I’ve seen a nice example at www.psoug.org on how to prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

Today, my Turkish friend Emre Baransel asked a question in OTN Forum related to limiting access of a user to a database based on time interval. He want to prevent a specific user to access to a database between 08 and 22.  I’ve created a logon script and showed how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kam identified by kam;
 
User created.
 
SQL> grant connect, resource to kam;
 
Grant succeeded.
 
SQL> conn kam/kam
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAM' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kam/kam
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kam/kam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAM! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>

Note: Before reading the post, you can have a look to my latest VIDEO instruction on Installing OEL and Oracle 10gR2

 

In order to use Oracle Database, first of all we need to setup Oracle Software. Installation of Oracle Database on Windows is very easy. By running setup.exe from installation CD of Oracle for Windows, we can invoke a setup and by clicking NEXT buttons we can install Oracle Software and Database without any problem. We don’t need any prerequisite actions before installation. But in Linux it’s different. If we want to install Oracle Database on Linux OS, we should follow some prerequisite actions.

Today, we’re going to install Oracle Database on CentOS. To do it, we use last version of CentOS (the latest release for now) – “CentOS-5.2” and mostly used release of Oracle Database – “Oracle Database 10g Release 2”. And we’re going to practice this whole project on VMware 6.0.0

Before starting, we need to install VMware. Then, we need to install CentOS on VMware. After that, we are going to install Oracle Database. You should refer to my previous posts in order to install VMware and Centos

Step by step installing VMware

Step by Step Installing CentOS on VMware

But there’s one thing we should keep in mind. During installation of CentOS, on the “package lists” page, we should behave differently. It will be discussed in the next paragraphs.  

This project covers following steps:

1. Checking minimum hardware requirements
2. Installing rpm packages which are required for Oracle installation
3. Configuring kernel parameters
4. Creating groups and user for Oracle Installation
5. Installing Oracle Software
6. Creating an Oracle Database
7. Connecting to Database with Enterprise Manager
8. AUTOMATING all processes and steps of installation Oracle 10g R2 on Centos using Shell Script

As you see from the list above, in order to setup Oracle Database, we need to change some parameters in the system. Changing these parameters each time could lead to mistakes and waste of time. The main purpose of our article is to automate all these processes and save your time. For this purpose we’re going to use “Shell Script”

Now I’m going to explain above mentioned steps one by one

1. Checking minimum hardware requirements

At least, your system should meet the following requirements:

–           1GB RAM 

–           Requirement for swap space in Oracle installation is as follows:

                Available RAM                                      Swap Space Required

                Between 1 GB and 2 GB        1.5 times the size of RAM

                Between 2 GB and 8 GB       Equal to the size of RAM

                More than 8 GB                     .75 times the size of RAM

–           400MB free space in /tmp directory

–           Depending on type of the installation, 1.5-3.5 GB free space for Oracle Software

–           1.5GB free space if new Oracle Database is created

Getting familiar with requirements mentioned above, we need to get hardware information of our system. To check the size of RAM, Swap space and tmp directory, we run these commands:

–           To check the size of physical memory, execute                             grep MemTotal     /proc/meminfo

–           To check the size of swap space, execute                                          grep SwapTotal     /proc/meminfo

–           To check the space in  /tmp directory, execute                            df –h /tmp

2. Installing rpm packages which are required for Oracle installation

While installing CentOS, we have to install some rpm packages. During the installation, on the installation window you get list of packages. Here, we select “Customize” choice

On “Customized” window, we check required packages and uncheck packages that are not required for Oracle Installation

In the packages list, check following packages. Uncheck all packages that are not in the list below

Desktop Environments

                                                  GNOME Desktop Environment  

Applications

                                                  Graphical Internet

Development

                                                  Development Libraries

                                                  Development Tools

                                                  GNOME Software Development

                                                  Java Development

                                                  Legacy Software Development

                                                  X Software Development

Servers

                                                  Server Configuration Tools

                                                  Web Server

                                                  Windows File Server

Base System

                                                  Administration Tools

                                                  Base

                                                  Java

                                                  Legacy Software Support

                                                  System Tools

                                                  X Window System

Furthermore, after installation of CentOS, we have manually to install these four rpm packages. Surely, you can select them (except libaio-devel package) from the package list during setup, for many people who don’t want to find these packages in the package list, installing it manually after system installation is the best option. These are packages which should be installed before Oracle installation

–           compat-db-4.2.52-5.1.i386.rpm

–           sysstat-7.0.2-1.el5.i386.rpm

–           libaio-devel-0.3.106-3.2.i386.rpm

–           libXp-1.0.0-8.1.el5.i386.rpm

In order to install these packages, you can use rpm –Uvh command by changing directory to CentOS directory inside the CD (DVD) of CentOS installation as shown below

3. Changes to be made to Kernel parameters

After installing above mentioned packages, we need to change some Kernel parameters and make them match to Oracle requirements. Parameters which should be changed are shown below

shmmax	2147483648
shmmni	4096
shmall	2097152
shmmin	1
semmsl	250
semmns	32000
semopm	100
semmni	128
file-max	65536
ip_local_port_range	1024   65000
rmem_default	1048576
rmem_max	1048576
wmem_default	262144
wmem_max	262144

 

We do all these changes in the /etc/sysctl.conf file by adding these lines to that file:

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni=4096

kernel.sem=250 32000 100 128

fs.file-max=65536

net.ipv4.ip_local_port_range=1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

After appending those lines we save that file and run the following command to make these changes effective immediately in the running system

/sbin/sysctl –p

Setting Shell limits for the Oracle UserTo improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:

1. Add the following lines to /etc/security/limits.conf file

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  1024

oracle              hard    nofile  65536

2. Add the following lines to /etc/pam.d/login file

session    required     /lib/security/pam_limits.so

session    required     pam_limits.so

3. In order to use Oracle Software, we need to make a change in “oracle” user’s buffer size and number of opened file descriptors. In order to do it, we add below lines to /etc/profile file

if [ $USER = “oracle” ]; then

        if [ $SHELL = “/bin/ksh” ]; then

              ulimit -p 16384

              ulimit -n 65536

        else

              ulimit -u 16384 -n 65536

        fi

fi

4. Changing redhat-release file

One of the first checks performed by the Oracle Universal Installer (OUI) is to determine if the host platform is supported. The OUI uses the file /etc/redhat-release to determine the platform. For the case of Red Hat Enterprise Linux, Oracle Database 10g Release 2 expects either RHEL 3 or RHEL/OEL 4.

The easiest way to get around this error is to modify the /etc/redhat-release file replacing the current release information (CentOS release 5 (Final)) with the following:

redhat-4

Before modifying /etc/redhat-release, make a backup copy of the file and ensure to replace the original one after the Oracle installation and patch process has been completed.

cp /etc/redhat-release /etc/redhat-release.original

echo “redhat-4” > /etc/redhat-release

After all these configurations, you should get this result:

4. Create groups and user for Oracle Installation

In this step, we create “oinstall” and “dba” groups and “oracle” user to install Oracle Software, and create new Database  

groupadd  oinstall

groupadd  dba

useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle

passwd oracle

5. Installing Oracle Database 10g Release 2

First of all, we need to download Oracle Database 10g R2. To download it, use this link:

http://download.oracle.com/otn/linux/oracle10g/10201/10201_database_linux32.zip

After download finish, we need to copy this file to the Virtual Machine. To copy it, we have two choices:

• Copy it using USB Flash Drive
• Copy it using Samba service
• Create an image file (.iso) from zipped installation and mount it to Virtual Machine

Here, we’re going to copy the zipped file using second technique. With any ISO creator program, create .iso file from zipped installation file of Oracle Database. And then mount it to the Virtual Machine as shown below:

Now switch to the desktop of CentOS, right click on DVD of CentOS installation on the desktop and click “Eject” as shown below:

Now enter to “Computer” and double click on “CD-ROM” icon.

Installation file of Oracle Database will be opened:

Now, create install folder on the /tmp directory,  change owner of this folder to “oracle”, copy this file into /tmp/install directory

Then unzip this file and begin installation as follows:

After unzip completes, installation will begin automatically

Oracle Database 10g Installation

If you want to create new database after software installation, check “Create Starter Database” checkbox and enter database name and password, then click Next

Specify Inventory directory (keep it as default) and click Next

Here we see that all Prerequisite Checks succeeded.

Click Install to begin installation

Now, we’re installing Oracle 10g Software.

6.  Creating an Oracle Database

As we’ve checked “Create Starter Database” at the first page of the installation, new database will be created automatically after software installation

After database created, you’ll get information about your database, Enterprise Manager and Spfile

Click Ok. In the below window, you’ll get location for two scripts which you should run as a root user to complete last configurations. Open new Terminal window and run those two scripts:

Installation of Oracle Database 10g completed successfully! Congratulations!

Now reboot your server and login as oracle user and start newly created database.  

If we want to login to our database, we run sqlplus. But before it, we need to set Environment Variables. We can do it automatically by adding them to .bashrc file in the /home/oracle directory as below:

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

alias database =’export ORACLE_SID=kamran;sqlplus “/ as sysdba”‘

Now, let’s open new terminal and try again

As you see, after setting Environment Variables to their correct values, I was able to login to SQL*Plus using two ways.

7.   Connecting to Database with Enterprise Manager

Now, let’s start Oracle Enterprise Manager. In order to use EM, we should firstly start listener. In production environment, you need automate database, listener and EM startup. To automate them, please refer to one of my previous blogs – “Automatically StartUp and Shutdown an Oracle Database in Linux OS”

Start the listener

Then, start Enterprise Manager

Now, we can login to EM page to administer our database using above given address:

http://localhost.localdomain:1158/em/

Enter user sys and its password, then select “SYSDBA” as a role and click Login

Using Enterprise Manager, you can administer your database in very easy steps

That’s all!! Our Database and EM is ready for use! Congratulations!!!

By following above mentioned steps we were able to install Oracle 10g R2 on CentOS 5. But if we carry out frequent tests and as a result of these tests each time we are to install Oracle Database, then we need to automate installation of Oracle Database. I would suggest two options to overcome this problem:

1. To install CentOS+Oracle on VMware and copy image of VMware to elsewhere, then each time use this image to get fresh copy of Oracle Database
2. To automate installation of Oracle Database by using Shell Script

Let’s explain each option in details: 

1. Firstly, I usually create new Virtual Machine with 10GB size. Then, I install CentOS on it and create an Oracle Database with all its configurations. Then I shut down Virtual Machine and copy all the folders to another directory. Usually, I create two copies of the Virtual Machine. Then I do all my tests on the first Virtual Machine. When it becomes useless, I shut down and delete it as a whole folder, open second Virtual Machine which I’ve copied and continue my tests.
2. Second way is the best method of approach to the problem. For this, I create a shell script and write all steps and do all configuration changes from this script. By running this script once, all configurations needed for Oracle Installation will be changed automatically and we will only be asked for new oracle user’s password and next we’ll see installation page open.

 

Automating installation of Oracle Database 10g Release 2 on Centos 5 using Shell Script

To automate this job, we wrote a Shell Script. Before running this script, we should follow some steps as shown below: 

1. Firstly you should know that before running this script we should create install directory in /tmp directory and copy zipped installation file of Oracle to this directory  with this installation script

2. Before running this script we must be sure that installation DVD of CentOS has been inserted or installation DVD image of CentOS mounted to the system. You can check it by running this code:

 

3. After installation completes, change the following Environment Variables and alias name into proper names which you’ve used during installation in the .bashrc file

1. ORACLE_HOME  – If it is different folder, change it to correct value
2. ORACLE_SID – If it is different SID, change it to correct SID  
3. As an alias, you can assign whatever you want

If everything is OK as mentioned above, we change directory to /tmp/install folder and run install.sh file. By running this shell script, all configurations needed for installation will be automatically changed and only thing asked will be oracle user’s password. After that we’ll see Oracle Database installation page. By clicking Next buttons we’ll be able to install Oracle Software and Database very easily

Now, let’s install Oracle Database 10g using this Shell Script

First of all, we need to create install folder in the /tmp directory and copy zipped installation file of Oracle Database 10g to /tmp/install directory with install.sh script and check their existence. After that we need to mount Centos DVD once more, because we’re going to install some packages that were not installed with CentOS. After getting below screen, you can start installation

Here we see that

• We have zipped installation file of Oracle Database 10g in the /tmp/install directory
• We mounted CentOS DVD

 Now, switch to /tmp/install folder and run install.sh script

Here, enter oracle user’s password. Then click ok. After this step, zipped file will be unzipped

After it finishes unzipping, we’ll get Installation Window

That’s all! After getting this page, you should follow above mentioned installation steps in which we installed Oracle Database manually.

Using automatic install script we’ve avoided of all configuration settings and got Oracle 10gR2 installer page opened successfully

The automatic installation script for Oracle Database on Linux is as follows:

 

#########———— Installing Rpm files —–########

 

#Change directory to /tmp/install

cd /tmp/install

#Install all packages that are not installed during OS installation and that are required packages for Oracle Database 10gR2

echo “Installing rpm packages …”

 

rpm -Uvh “$(find /media/ -name compat-db*)”

rpm -Uvh “$(find /media/ -name sysstat*)”

rpm -Uvh “$(find /media/ -name libaio-devel*)”

rpm -Uvh “$(find /media/ -name libXp-1*)”

 

echo “Rpm packages installed

“

 

#Add lines to limits.conf file

echo “Changing limits.conf file”

cat >> /etc/security/limits.conf <<EOF

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

EOF

echo “limits.conf file changed successfully

“

 

#Add lines to profile to give maximum limit for Oracle user

echo “Changing /etc/profile file ….”

cat >> /etc/profile <<EOF

if [ \$USER = “oracle” ]; then

                                                  if [ \$SHELL = “bin/ksh” ]; then

                                                                ulimit -p 16384

                                                                ulimit -n 65536

                                                  else

                                                                ulimit -u 16384 -n 65536

                                                  fi

                                                  umask 022

fi

EOF

echo “/etc/profile file changed successfully

“

 

#Add line to /etc/pam.d/login file

echo “Changing /etc/pam.d/login file …”

cat >> /etc/pam.d/login <<EOF

session required /lib/security/pam_limits.so

EOF

echo “/etc/pam.d/login file changed successfuly

“

 

#Add some kernel parameters to /etc/sysctl.conf file

echo “Changing kernel parameters … “

 

cat >> /etc/sysctl.conf <<EOF

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni=4096

kernel.sem=250 32000 100 128

fs.file-max=65536

net.ipv4.ip_local_port_range=1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

EOF

 

echo “Kernel parameters changed successfully

“

#Save all new kernel parameters

 

/sbin/sysctl -p

 

#Add “redhat-4” line to /etc/redhat-release file

 

echo “Changing /etc/redhat-release file …”

cp /etc/redhat-release /etc/redhat-release.original

echo “redhat-4” > /etc/redhat-release

echo “/etc/redhat-release file changed successfully

“

 

#Create new groups and “oracle” user and add this user to group

echo “Creating new groups and ‘oracle’ user …”

groupadd oinstall

groupadd dba

useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle

passwd oracle

echo “Groups and user created successfully

”

#Adding Environment Variables

#Adding Environment Variables

cat >> /home/oracle/.bashrc <<EOF

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

alias mydb=’export ORACLE_SID=mydb;sqlplus “/ as sysdba”‘

export ORACLE_SID=mydb

EOF

EOF

 

 

#Unzip setup of Oracle

echo “Unzipping setup of Oracle 10g Release 2…. “

unzip 10201_database_linux32.zip

echo “Setup file successfully unzipped

“

#Enter to installation directory and run the installation …

echo “Installation begins …”

cd /tmp/install/database

chmod 755 runInstaller

chmod 755 install/.oui

chmod 755 install/unzip

xhost +

sudo -u oracle /tmp/install/database/runInstaller

As I’m active user in Oracle forums, in one of the posts, OP asked a question about DML triggers to be written for all schemas. OP wanted to write a schema level DML trigger. As we now, it’s impossible to write schema level DML trigger. We can write DDL trigger to be fired on schema level.

Now, we have to choices.
1. To track all DML statements using Audit
2. To create one (INSERT, UPDATE, DELETE) trigger for all tables of the schema automatically

Its better to create audit for that tables, but as the OP wanted it to be solved in triggering way, I provided this type of solution:

In order to create trigger for each object, I followed below steps:
–I create a new user
SQL> create user test_trigger identified by kamran;
User created.

–Give it dba role (Don’t do it in production database)
SQL> grant dba to test_trigger;
Grant succeeded.

— Connect with that user
SQL> conn test_trigger/kamran
Connected.

— Create a new table
SQL> create table tab1 (id number);
Table created.

— Create a second table
SQL> create table tab2 (id number);
Table created.

— Create another table to log information which UPDATED, DELETED and INSERTED on two other tables
SQL> create table tab1_2_log (information varchar2(20));
Table created.

— Write a PL/SQL block to create a trigger for each table of the newly created user automatically
SQL> DECLARE
2 CURSOR all_tables
3 IS
4 SELECT table_name
5 FROM user_tables
6 WHERE table_name ‘TAB1_2_LOG’;
7
8
9 BEGIN
10 FOR rec_cur IN all_tables
11 LOOP
12 EXECUTE IMMEDIATE ‘create or replace trigger trg_’
13 || rec_cur.table_name
14 || ‘
15 before insert or update or delete on ‘
16 || rec_cur.table_name
17 || ‘
18 declare
19 begin
20 if UPDATING then
21 insert into tab1_2_log values(”UPDATING on ‘||rec_cur.table_name||”’);
22 elsif DELETING then
23 insert into tab1_2_log values(”DELETING on ‘||rec_cur.table_name||”’);
24 elsIF INSERTING then
25 insert into tab1_2_log values(”INSERTING on ‘||rec_cur.table_name||”’);
26 end if;
27 end;’;
28 END LOOP;
29 END;
30 /

PL/SQL procedure successfully completed.

1. Here, I create a cursor to take all tables in newly created schema
2. Open the cursor and get name of tables in my schema
3. Create EXECUTE IMMEDIATE statement and run creation of trigger by passing it the name of each table

Now, I begin to test my triggers by inserting to, updating and deleting data from tables

SQL> insert into tab1 values(1);
1 row created.

SQL> update tab1 set id=1;
1 row updated.

SQL> delete from tab1;
1 row deleted.

SQL> insert into tab2 values(1);
1 row created.

SQL> update tab2 set id=1;
1 row updated.

SQL> delete from tab2 ;
1 row deleted.

— Now, let’s check what’s in our log table
SQL> select * from tab1_2_log;

INFORMATION
——————–
INSERTING on TAB1
UPDATING on TAB1
DELETING on TAB1
INSERTING on TAB2
UPDATING on TAB2
DELETING on TAB2
6 rows selected.

— As it seen, all information related DELETING, UPDATING and INSERTING on tables have been gathered. Now, check name of triggers which was created on that schema to track all DML statement of each table
SQL> SELECT trigger_name, table_name FROM all_triggers WHERE table_owner=’TEST_TRIGGER’;

TRIGGER_NAME TABLE_NAME
—————————— ——————————
TRG_TAB1 TAB1
TRG_TAB2 TAB2

To get more information related Triggers, please refer to documentation :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#i6061

In this example, we’ll change Sql*Plus view and display our database name and conneted user name instead of word “SQL ”

If you’re using Sql*Plus , you know that every time you type a command, you’re typing it after “SQL>”

SQL>show user
USER is “SYS”
SQL>

If you want to display your SID, your connected USER name instead of “SQL>” you’ve to change “glogin.sql” in your $ORACLE_SID/sqlplus/admin directory as below

set termout off
define gname=idle
column global_name new_value gname
select lower(user)||’@’ ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,’.’) dot from global_name);
set sqlprompt ‘&gname>’
set termout on

And after that adding an alias to your /home/oracle/.bashrc file:

alias sqlplus=’export ORACLE_SID=kam;sqlplus “/ as sysdba”‘
every time from Linux terminal when you type sqlplus, you’ll connect automatically to database kam as sysdba and your SQL> prompt will be changed to SYS@KAM>
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.1.0.3.0 – Production on Mon Dec 8 06:29:29 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

sys@KAM>show user
USER is “SYS”
sys@KAM>

Sometimes you need to copy some OS file from one location to another. If you’re using only Sql*Plus and you can’t connect directly to OS, you can user Oracle’s UTL_FILE.FCOPY function to achieve this goal.

Firstly, we’ll create two directories and one file. Then we’ll create a user and copy this file from one folder to another using UTL_FILE package

[oracle@localhost /]$ cd /tmp
[oracle@localhost tmp]$ mkdir copy_dir
[oracle@localhost tmp]$ mkdir backup_dir
[oracle@localhost tmp]$ ls backup_dir/
[oracle@localhost tmp]$
[oracle@localhost tmp]$ cd copy_dir
[oracle@localhost copy_dir]$ vi test.dat (We are adding a line with word MY_TEST to file test.dat)
[oracle@localhost copy_dir]$ ls -lh
total 4.0K
-rw-r–r–  1 oracle oinstall 8 Dec  7 04:11 test.dat
[oracle@localhost copy_dir]$ cat test.dat
MY_TEST
[oracle@localhost copy_dir]$sqlplus “/ as sysdba”

SQL> CREATE USER kamran IDENTIFIED BY kamran;

User created.

SQL> grant connect, resource to kamran;

Grant succeeded.

SQL> grant create any directory to kamran;

Grant succeeded.

SQL> grant execute on utl_file to kamran;

Grant succeeded.

SQL> conn kamran/kamran
Connected.
SQL> create directory file_source as ‘/tmp/copy_dir’;

Directory created.
SQL> create directory backup_source as ‘/tmp/backup_dir’;

Directory created.

SQL> exec utl_file.fcopy(‘FILE_SOURCE’,’test.dat’,’BACKUP_SOURCE’,’copied_test.dat’);

PL/SQL procedure successfully completed.

SQL>exit
[oracle@localhost tmp]$ cd backup_dir/
[oracle@localhost backup_dir]$ ls -lh
total 4.0K
-rw-r–r–  1 oracle oinstall 8 Dec  7 04:32 copied_test.dat
[oracle@localhost backup_dir]$

Today I’ll show how we can make a table read only using database triggers

In this example, we’ll create a table, insert data in it, and then create trigger to prevent any insert, update or delete statement on table

SQL> CREATE TABLE tbl_read_only (id NUMBER);

Table created.

SQL> INSERT INTO tbl_read_only VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tbl_read_only;

        ID
———-
         1

SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE
  2  ON tbl_read_only
  3  BEGIN
  4  RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);
  5  END;
  6  /

Trigger created.

SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
            *
ERROR at line 1:
ORA-20202: Table Status: READ ONLY!!!
ORA-06512: at “KAMRAN.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘KAMRAN.TRG_TBL_READ_ONLY’
SQL>

Today I want to give you an example of how delete duplicate rows from a table
Let’s create new table with name students:
CREATE TABLE students (ID NUMBER, NAME VARCHAR2(10), surname VARCHAR2(10))
Now let’s add some duplicate rows to it:
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
And now let’s look to the data of our table:
SELECT * FROM students
1  Kamran  Agayev
2  Elcin   Mammedov
1  Kamran  Agayev
2  Elcin   Mammedov
1  Kamran  Agayev
2  Elcin   Mammedov
Now let’s delete duplicate rows using this script:
 
DELETE FROM students   
WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM students GROUP BY ID) 
And now let’s look to students table
SELECT * FROM students
1  Kamran  Agayev
2  Elcin   Mammedov

These two examples shows you how to run SQL command directly from Linux in a single command

 [oracle@localhost~] sqlplus -s username/pass <filename.sql

 COUNT(*)

————-

 [oracle@localhost~]