| 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:
You can also use the oemevent command to invoke a SQL script from OEM:
- Create a "new event" from the OEM console
- Choose "enable unsolicited event" and then "test unsolicited event"
- Enter the script name with the full path.
root> oemevent /u01/app/oracle/scripts/myscript.sql destination alert "Monitored destination message"
Labels: oracle dba
|posted by popo @ 00:59
| 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_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
-- (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.
Labels: oracle dba
|posted by popo @ 17:07
| 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';
alert_log ( msg varchar2(80) )
organization external (
default directory BDUMP
access parameters (
records delimited by newline
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: , , , , , [ ], , 
ORA-00600: internal error code, arguments: , [0xC0000000210D8BF8], , , , , 
ORA-00600: internal error code, arguments: , , , , 
Labels: oracle dba, SQL, SQL*Plus
|posted by popo @ 16:57
| 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;
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:
Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:
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:
user = user_id
elapsed_minutes is NULL;
|posted by popo @ 03:13
| 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
ORDER BY 1;
Display of Salary by Department
ENAME DEPT10 DEPT20 DEPT30 DEPT40
---------- ---------- ---------- ---------- ----------
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.
|posted by popo @ 03:00
| 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 (
deptno REAL) RETURN INT;
CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package
/* Fully define subprograms specified in package. */
FUNCTION hire_employee (
deptno REAL) RETURN INT IS
SELECT empno_seq.NEXTVAL INTO new_empno
INSERT INTO emp VALUES (new_empno, ename, job,
mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
BEGIN -- initialization part starts here
INSERT INTO emp_audit
VALUES (SYSDATE, USER, 'EMP_ACTIONS');
number_hired := 0;
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.
|posted by popo @ 23:53