Problem Description
Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.
•The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... .
•No one from outside can connect to database.
•Local connection without listener was ok.
•Listener process takes high cpu than normal usage.
•Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below.
$ ps -ef | grep tnslsnr
oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
From the output first one is parent listener process and second line is child listener process. For child listener process parent id is 3102.
Just killing the child process allows new connections to work until the problem reoccurs. So after seeing above and if listener hangs then do,
$kill -9 5012 3102
Cause of the Problem
This problem remains in oracle 10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2. The listener hangs if the child listener process is not closed i.e after creating child process it persists. Note that, child listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than 5 seconds) is not normal and may be a result of this referenced problem.
This listener hanging event can happen on a standalone server or on a RAC server.
Solution of the Problem
1)The issue is fixed in patchset 10.2.0.3 and in 10.2.0.4. So apply patchset.
2)Apply Patch 4518443 which is available in metalink. Download from metalink and apply on your databse server.
3)As a workaround, you can follow the following two steps if you don't like to apply patch now.
Step 01: Add the following entry in your listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Step 02: Go to directory cd $ORACLE_HOME/opmn/conf , find ons.config and move it to another location. Like,
cd $ORACLE_HOME/opmn/conf
mv ons.config ons.config.bak
After completing above two steps bounce the listener.
lsnrctl stop
lsnrctl start
Alternatively, you can simply issue,
$lsnrctl reload
if database availability is important.
Note that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. So, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN.
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
Tuesday, March 31, 2009
Changing a DBA user to a normal user in oracle
Many times you have granted a user DBA super role instead of giving individual privilege to a user. Later whenever you want to revoke DBA role you need to care of which privilege you need to give the user.
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
2)USER_SYS_PRIVS describes system privileges granted to the current user.
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
Let's see the privilege assigned to user OMS.
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
2)USER_SYS_PRIVS describes system privileges granted to the current user.
SQL> desc user_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
SQL> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
SQL> desc dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
SQL> select * from dba_role_privs where grantee='OMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
OMS RESOURCE NO YES
OMS JAVAUSERPRIV NO YES
OMS DBA NO YES
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
Let's see the privilege assigned to user OMS.
SQL> select * from dba_sys_privs where grantee='OMS';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
OMS UNLIMITED TABLESPACE NO
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
SQL> DEFINE owner='OMS'
SQL> select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
from dba_tables where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_indexes
where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_tab_partitions
where table_owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_ind_partitions
where index_owner='&OWNER';
old 1: select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
new 1: select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';'
old 2: from dba_tables where owner='&OWNER'
new 2: from dba_tables where owner='OMS'
old 3: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_indexes
new 3: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_indexes
old 4: where owner='&OWNER'
new 4: where owner='OMS'
old 5: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
new 5: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
old 6: where table_owner='&OWNER'
new 6: where table_owner='OMS'
old 7: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
new 7: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
old 8: where index_owner='&OWNER'
new 8: where index_owner='OMS'
'ALTERUSEROMSQUOTAUNLIMITEDON'||TABLESPACE_NAME||';'
-----------------------------------------------------------------
Alter user OMS quota unlimited on OMS_INDX_SPC;
Alter user OMS quota unlimited on OMS_SPC;
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
Thursday, January 29, 2009
Updating a table based on another table
In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task.
Let's show example by creating table and entering values into it.
Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');
After above statements let's look at the data on the table.
Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated.
Method 01:
Method 02:
Method 03:
In order to apply method 03 you need a primary or unique key column in the source table i.e in the table from where we are fetching data for update. It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.
So, I am adding an unique constraint in table_2.
In most cases method 03 will perform better than other method.
Let's show example by creating table and entering values into it.
Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');
After above statements let's look at the data on the table.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Rows to be updated
SQL> select * from table_2;
ID CODE
---------- --------------------
2 Second Row
Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated.
Method 01:
SQL> update table_1 set code=
(select t2.code from table_2 t2 JOIN table_1 t1 ON t1.id=t2.id)
where table_1.id in(select id from table_2);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
Method 02:
SQL> update table_1 t1 set code=
(select t2.code from table_2 t2 JOIN table_1 t1 ON t2.id=t1.id)
where exists
(select t2.code from table_2 t2 where t1.id=t2.id);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
Method 03:
In order to apply method 03 you need a primary or unique key column in the source table i.e in the table from where we are fetching data for update. It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.
So, I am adding an unique constraint in table_2.
SQL> alter table table_2 add constraint table_2_UK UNIQUE (id);
Table altered.
SQL> update
(select t1.code col1, t2.code col2 from table_1 t1
JOIN table_2 t2 ON t1.id=t2.id)
set col1=col2;
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
In most cases method 03 will perform better than other method.
Updating a table based on another table
In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task.
Let's show example by creating table and entering values into it.
Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');
After above statements let's look at the data on the table.
Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated.
Method 01:
Method 02:
Method 03:
In order to apply method 03 you need a primary or unique key column in the source table i.e in the table from where we are fetching data for update. It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.
So, I am adding an unique constraint in table_2.
In most cases method 03 will perform better than other method.
Let's show example by creating table and entering values into it.
Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');
After above statements let's look at the data on the table.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Rows to be updated
SQL> select * from table_2;
ID CODE
---------- --------------------
2 Second Row
Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated.
Method 01:
SQL> update table_1 set code=
(select t2.code from table_2 t2 JOIN table_1 t1 ON t1.id=t2.id)
where table_1.id in(select id from table_2);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
Method 02:
SQL> update table_1 t1 set code=
(select t2.code from table_2 t2 JOIN table_1 t1 ON t2.id=t1.id)
where exists
(select t2.code from table_2 t2 where t1.id=t2.id);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
Method 03:
In order to apply method 03 you need a primary or unique key column in the source table i.e in the table from where we are fetching data for update. It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.
So, I am adding an unique constraint in table_2.
SQL> alter table table_2 add constraint table_2_UK UNIQUE (id);
Table altered.
SQL> update
(select t1.code col1, t2.code col2 from table_1 t1
JOIN table_2 t2 ON t1.id=t2.id)
set col1=col2;
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row
In most cases method 03 will perform better than other method.
Friday, January 23, 2009
Block or Accept Oracle access by IP Address
You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user.
With oracle this scenario can be achieved, however this seems to me a bit of fun.
The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.
In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,
tcp.validnode_checking = yes
This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:
tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)
Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.
Some rules for entering invited/excluded nodes:
1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.
Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.
To do so,
$lsnrctl stop
$lsnrctl start
A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)
With oracle this scenario can be achieved, however this seems to me a bit of fun.
The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.
In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,
tcp.validnode_checking = yes
This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:
tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)
Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.
Some rules for entering invited/excluded nodes:
1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.
Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.
To do so,
$lsnrctl stop
$lsnrctl start
A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)
Block or Accept Oracle access by IP Address
You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user.
With oracle this scenario can be achieved, however this seems to me a bit of fun.
The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.
In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,
tcp.validnode_checking = yes
This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:
tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)
Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.
Some rules for entering invited/excluded nodes:
1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.
Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.
To do so,
$lsnrctl stop
$lsnrctl start
A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)
With oracle this scenario can be achieved, however this seems to me a bit of fun.
The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.
In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,
tcp.validnode_checking = yes
This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:
tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)
Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.
Some rules for entering invited/excluded nodes:
1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.
Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.
To do so,
$lsnrctl stop
$lsnrctl start
A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)
Thursday, January 22, 2009
Convert Decimal to hexadecimal on Oracle
Way 01
Way 02:
Way 03:
Way 04:
Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.
Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78
SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
SQL> create or replace package number_utils as
2 function d_to_hex(decimal_num in integer) return varchar2;
3 pragma restrict_references (d_to_hex, wnds, wnps, rnps);
4 end;
5 /
Package created.
SQL> create or replace package body number_utils as
2 function d_to_hex (decimal_num in integer)
3 return varchar2 is
4 v_result varchar2(12);
5 v_hex_digit varchar2(1);
6 v_quotient pls_integer;
7 v_remainder pls_integer;
8 begin
9 if (decimal_num < 10) then
10 v_result := to_char(decimal_num);
11 elsif (decimal_num < 16) then
12 v_result := chr(65+(decimal_num-10));
13 else
14 v_remainder := mod(decimal_num,16);
15 v_quotient := round((decimal_num - v_remainder) /16);
16 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
17 end if;
18 return v_result;
19 end d_to_hex;
20 end number_utils;
21 /
Package body created.
SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05
Way 02:
SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
2 hex_num varchar2(64);
3 digit pls_integer;
4 decimal_num pls_integer:=decimal_val;
5 hexdigit char;
6 BEGIN
7 while ( decimal_num > 0 ) loop
8 digit := mod(decimal_num, 16);
9 if digit > 9 then
10 hexdigit := chr(ascii('A') + digit - 10);
11 else
12 hexdigit := to_char(digit);
13 end if;
14 hex_num := hexdigit || hex_num;
15 decimal_num := trunc( decimal_num / 16 );
16 end loop;
17 return hex_num;
18 END dec_to_hex;
19 /
Function created.
SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78
Way 03:
SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
2 v_decin NUMBER;
3 v_next_digit NUMBER;
4 v_result varchar(2000);
5 BEGIN
6 v_decin := dec_num;
7 WHILE v_decin > 0 LOOP
8 v_next_digit := mod(v_decin,16);
9 IF v_next_digit > 9 THEN
10 IF v_next_digit = 10 THEN v_result := 'A' || v_result;
11 ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
12 ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
13 ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
14 ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
15 ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
16 ELSE raise_application_error(-20600,'Untrapped exception');
17 END IF;
18 ELSE
19 v_result := to_char(v_next_digit) || v_result;
20 END IF;
21 v_decin := floor(v_decin / 16);
22 END LOOP;
23 RETURN v_result;
24 END dec_to_hex;
25 /
Function created.
SQL> select dec_to_hex(17) from dual;
DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11
Way 04:
SQL> create or replace function dec_to_hex(dec_num in number )
2 return varchar2
3 is
4 l_str varchar2(255) default NULL;
5 l_num number default dec_num;
6 l_hex varchar2(16) default '0123456789ABCDEF';
7 p_base number:=16;
8 begin
9 if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
10 raise PROGRAM_ERROR;
11 end if;
12 loop
13 l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
14 l_num := trunc( l_num/p_base );
15 exit when ( l_num = 0 );
16 end loop;
17 return l_str;
18 end dec_to_hex;
19 /
Function created.
SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78
Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.
Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78
SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
Convert Decimal to hexadecimal on Oracle
Way 01
Way 02:
Way 03:
Way 04:
Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.
Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78
SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
SQL> create or replace package number_utils as
2 function d_to_hex(decimal_num in integer) return varchar2;
3 pragma restrict_references (d_to_hex, wnds, wnps, rnps);
4 end;
5 /
Package created.
SQL> create or replace package body number_utils as
2 function d_to_hex (decimal_num in integer)
3 return varchar2 is
4 v_result varchar2(12);
5 v_hex_digit varchar2(1);
6 v_quotient pls_integer;
7 v_remainder pls_integer;
8 begin
9 if (decimal_num < 10) then
10 v_result := to_char(decimal_num);
11 elsif (decimal_num < 16) then
12 v_result := chr(65+(decimal_num-10));
13 else
14 v_remainder := mod(decimal_num,16);
15 v_quotient := round((decimal_num - v_remainder) /16);
16 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
17 end if;
18 return v_result;
19 end d_to_hex;
20 end number_utils;
21 /
Package body created.
SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05
Way 02:
SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
2 hex_num varchar2(64);
3 digit pls_integer;
4 decimal_num pls_integer:=decimal_val;
5 hexdigit char;
6 BEGIN
7 while ( decimal_num > 0 ) loop
8 digit := mod(decimal_num, 16);
9 if digit > 9 then
10 hexdigit := chr(ascii('A') + digit - 10);
11 else
12 hexdigit := to_char(digit);
13 end if;
14 hex_num := hexdigit || hex_num;
15 decimal_num := trunc( decimal_num / 16 );
16 end loop;
17 return hex_num;
18 END dec_to_hex;
19 /
Function created.
SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78
Way 03:
SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
2 v_decin NUMBER;
3 v_next_digit NUMBER;
4 v_result varchar(2000);
5 BEGIN
6 v_decin := dec_num;
7 WHILE v_decin > 0 LOOP
8 v_next_digit := mod(v_decin,16);
9 IF v_next_digit > 9 THEN
10 IF v_next_digit = 10 THEN v_result := 'A' || v_result;
11 ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
12 ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
13 ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
14 ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
15 ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
16 ELSE raise_application_error(-20600,'Untrapped exception');
17 END IF;
18 ELSE
19 v_result := to_char(v_next_digit) || v_result;
20 END IF;
21 v_decin := floor(v_decin / 16);
22 END LOOP;
23 RETURN v_result;
24 END dec_to_hex;
25 /
Function created.
SQL> select dec_to_hex(17) from dual;
DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11
Way 04:
SQL> create or replace function dec_to_hex(dec_num in number )
2 return varchar2
3 is
4 l_str varchar2(255) default NULL;
5 l_num number default dec_num;
6 l_hex varchar2(16) default '0123456789ABCDEF';
7 p_base number:=16;
8 begin
9 if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
10 raise PROGRAM_ERROR;
11 end if;
12 loop
13 l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
14 l_num := trunc( l_num/p_base );
15 exit when ( l_num = 0 );
16 end loop;
17 return l_str;
18 end dec_to_hex;
19 /
Function created.
SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78
Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.
Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78
SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
Which Options are installed on your oracle database
There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.
2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.
Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.
3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID
17 rows selected.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.
2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.
Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.
3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID
17 rows selected.
Which Options are installed on your oracle database
There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.
2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.
Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.
3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID
17 rows selected.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.
2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.
Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.
3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID
17 rows selected.
Monday, January 19, 2009
New features of oracle perfomance in 10.2g
In oracle database 10.2g release a lots of performance feature are added in it.
1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.
2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.
3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.
4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.
5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.
6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.
7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.
8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.
9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.
10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.
11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .
12)SQL Profiles:
13)SQL Tuning Advisor:
14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.
15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.
1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.
2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.
3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.
4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.
5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.
6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.
7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.
8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.
9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.
10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.
11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .
12)SQL Profiles:
13)SQL Tuning Advisor:
14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.
15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.
New features of oracle perfomance in 10.2g
In oracle database 10.2g release a lots of performance feature are added in it.
1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.
2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.
3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.
4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.
5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.
6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.
7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.
8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.
9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.
10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.
11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .
12)SQL Profiles:
13)SQL Tuning Advisor:
14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.
15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.
1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.
2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.
3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.
4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.
5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.
6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.
7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.
8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.
9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.
10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.
11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .
12)SQL Profiles:
13)SQL Tuning Advisor:
14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.
15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.
Automatic startup and shutdown oracle on linux
Oracle database server provides two scripts to configure automatic database startup and shutdown process.
The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named shauk AND shaikdup then my oratab file will contain the entry like,
shaik:/var/opt/oracle/product/10.2.0/db_1:Y
shaikdup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.
# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.
3.As root perform the following to create symbolic links:
# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle
Alternatively you can register the Service using
/sbin/chkconfig --add dbora
This action registers the service to the Linux service mechanism.
4. Test the script to see if it works.
The real test is to reboot unix box and then see whether oracle is started up automatically or not.
However to test the script created in step 2, without rebooting, do the following:
Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)
If you restart start and stop oracle database is successful then you are almost done.
The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named shauk AND shaikdup then my oratab file will contain the entry like,
shaik:/var/opt/oracle/product/10.2.0/db_1:Y
shaikdup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.
# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.
#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
case "$1" in
'start')
# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora
3.As root perform the following to create symbolic links:
# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle
Alternatively you can register the Service using
/sbin/chkconfig --add dbora
This action registers the service to the Linux service mechanism.
4. Test the script to see if it works.
The real test is to reboot unix box and then see whether oracle is started up automatically or not.
However to test the script created in step 2, without rebooting, do the following:
Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)
If you restart start and stop oracle database is successful then you are almost done.
Automatic startup and shutdown oracle on linux
Oracle database server provides two scripts to configure automatic database startup and shutdown process.
The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named shauk AND shaikdup then my oratab file will contain the entry like,
shaik:/var/opt/oracle/product/10.2.0/db_1:Y
shaikdup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.
# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.
3.As root perform the following to create symbolic links:
# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle
Alternatively you can register the Service using
/sbin/chkconfig --add dbora
This action registers the service to the Linux service mechanism.
4. Test the script to see if it works.
The real test is to reboot unix box and then see whether oracle is started up automatically or not.
However to test the script created in step 2, without rebooting, do the following:
Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)
If you restart start and stop oracle database is successful then you are almost done.
The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named shauk AND shaikdup then my oratab file will contain the entry like,
shaik:/var/opt/oracle/product/10.2.0/db_1:Y
shaikdup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.
# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.
#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
case "$1" in
'start')
# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora
3.As root perform the following to create symbolic links:
# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle
Alternatively you can register the Service using
/sbin/chkconfig --add dbora
This action registers the service to the Linux service mechanism.
4. Test the script to see if it works.
The real test is to reboot unix box and then see whether oracle is started up automatically or not.
However to test the script created in step 2, without rebooting, do the following:
Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)
If you restart start and stop oracle database is successful then you are almost done.
How to generate fibonacci series in Oracle.
Way 1:
Way 2:
Just a variant of way 1,
Way 3: Using Math Formula
with data as (select level levels from dual
connect by level <= &how_may_rows)
select f from data
model dimension by (levels)
measures ( 0 f)
rules ( f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);
Enter value for how_may_rows: 10
old 2: connect by level <= &how_may_rows)
new 2: connect by level <= 10)
F
----------
0
1
1
2
3
5
8
13
21
34
10 rows selected.
Way 2:
Just a variant of way 1,
SQL> select s seq from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (&n) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/
Enter value for n: 8
old 4: rules iterate (&n) (
new 4: rules iterate (8) (
SEQ
----------
0
1
1
2
3
5
8
13
8 rows selected.
Way 3: Using Math Formula
SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
from dual
connect by level <=&n;
Enter value for n: 8
old 3: connect by level <=&n
new 3: connect by level <=8
FIB
----------
0
1
1
2
3
5
8
13
8 rows selected.
How to generate fibonacci series in Oracle.
Way 1:
Way 2:
Just a variant of way 1,
Way 3: Using Math Formula
with data as (select level levels from dual
connect by level <= &how_may_rows)
select f from data
model dimension by (levels)
measures ( 0 f)
rules ( f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);
Enter value for how_may_rows: 10
old 2: connect by level <= &how_may_rows)
new 2: connect by level <= 10)
F
----------
0
1
1
2
3
5
8
13
21
34
10 rows selected.
Way 2:
Just a variant of way 1,
SQL> select s seq from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (&n) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/
Enter value for n: 8
old 4: rules iterate (&n) (
new 4: rules iterate (8) (
SEQ
----------
0
1
1
2
3
5
8
13
8 rows selected.
Way 3: Using Math Formula
SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
from dual
connect by level <=&n;
Enter value for n: 8
old 3: connect by level <=&n
new 3: connect by level <=8
FIB
----------
0
1
1
2
3
5
8
13
8 rows selected.
Tuesday, January 13, 2009
Minimum privilege needed to take data pump export
In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database.
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Minimum privilege needed to take data pump export
In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database.
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Audit Trigger Activity in Oracle
Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
SQL> select username,sql_text from dba_audit_trail;
USERNAME SQL_TEXT
-------------- --------------------------
TEST insert into test values(2,'Test','Test2')
Audit Trigger Activity in Oracle
Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
SQL> select username,sql_text from dba_audit_trail;
USERNAME SQL_TEXT
-------------- --------------------------
TEST insert into test values(2,'Test','Test2')
Subscribe to:
Posts (Atom)
