Friday, 25 April 2008

Invoking and Executing a Script from OEM

When including script names in OEM you must carefully include the fully-qualified path for the script: (i.e. /u01/app/oracle/scripts/myscript.sql). Placing custom scripts within OEM is a great way to improve reporting. Here is a quick overview of script execution from OEM:

  1. Create a "new event" from the OEM console
  2. Choose "enable unsolicited event" and then "test unsolicited event"
  3. Enter the script name with the full path.
You can also use the oemevent command to invoke a SQL script from OEM:

root> oemevent /u01/app/oracle/scripts/myscript.sql destination alert "Monitored destination message"

Thursday, 24 April 2008

Database Link From Oracle to SQL Server

After you succeed connecting Oracle with SQL Server probably you want to create database link between it. Oracle heterogeneous services allow you to define a database link between Oracle and SQL Server, as well as links to DB2 and other inferior databases.

Here are complete notes in creating a database link between Oracle and SQL Server.

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.
2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.
4. Ensure that your global_names parameter is set to False.
5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

6. Modify the Listener.ora file.


SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
(PROGRAM = hsodbc) ) )



7. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers

(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
)


8. Reload the listener on local Oracle database
9. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.
10. Run a SQL Server Select statement from the Oracle installation using the database link.

Accessing Oracle Alert Log via SQL with External Tables

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alrt_mysid.log')
)
reject limit 1000;


Now we can easily extract important Oracle alert log information without leaving SQL*Plus:
select msg from alert_log where msg like '%ORA-00600%';

ORA-00600: internal error code, arguments: [17034], [2940981512], [0], [], [], [ ], [], []
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8], [], [], [], [], []
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

Tuesday, 22 April 2008

Computing date differences

One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.

To get the values for data differences, you must choose you unit of measurement, and this is done with the data format mask:

It might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date))– elapsed days



round(to_number(end-date-start_date)*24)– elapsed hours


round(to_number(end-date-start_date)*1440)– elapsed minutes

How are elapsed time data displayed by default? To find out, we issue a simple SQL*plus query:

SQL> select sysdate-(sysdate-3) from dual;



SYSDATE-(SYSDATE-3)

-------------------

3

Here we see that elapsed times are expressed in days. Hence, we can use easy conversion functions to convert this to hours or minutes:

However, when the minutes are not a whole number, we have the problem of trailing decimal places:
select

(sysdate-(sysdate-3.111))*1440

from

dual;


(SYSDATE-(SYSDATE-3.111))*1440

------------------------------

4479.83333

Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:
select

round(to_number(sysdate-(sysdate-3.111))*1440)

from

dual;



ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)

----------------------------------------------

4480

Hence, we can use these functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table. In this example, we have a logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:
update

perfstat.stats$user_log

set

elapsed_minutes =

round(to_number(logoff_time-logon_time)*1440)

where

user = user_id

and

elapsed_minutes is NULL;

Oracle SQL : Coding a matrix of display values

Oracle has a fascinating non-ANSI feature called an in-line view. The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.

prompt Display of Salary by Department



SELECT

*

FROM (SELECT

ename,

sum(decode(deptno,10,sal)) DEPT10,

sum(decode(deptno,20,sal)) DEPT20,

sum(decode(deptno,30,sal)) DEPT30,

sum(decode(deptno,40,sal)) DEPT40

FROM

emp

GROUP BY

ename)

ORDER BY 1;



Display of Salary by Department



ENAME DEPT10 DEPT20 DEPT30 DEPT40

---------- ---------- ---------- ---------- ----------

ADAMS 1100

ALLEN 1600

BLAKE 2850

CLARK 2450

FORD 3000

JAMES 950

JONES 2975

KING 5000

MARTIN 1250

MILLER 1300

SCOTT 3000

SMITH 800

TURNER 1500

WARD 1250


This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

Monday, 17 March 2008

Oracle Package Initialization

Did you notice that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:

CREATE PACKAGE emp_actions AS
/* Declare externally callable subprograms. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL) RETURN INT;
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package

/* Fully define subprograms specified in package. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL) RETURN INT IS
new_empno INT;
BEGIN
SELECT empno_seq.NEXTVAL INTO new_empno
FROM dual;
INSERT INTO emp VALUES (new_empno, ename, job,
mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;

BEGIN -- initialization part starts here
INSERT INTO emp_audit
VALUES (SYSDATE, USER, 'EMP_ACTIONS');
number_hired := 0;
END emp_actions;


The initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.