Monday, December 15, 2008

Running dbms_jobs within a timing interval

If you are using the dbms_job and you want to scheduled the jobs to run at a certain time example from 6 am until 11 pm you can use the following example,

This will run the statspack between 6 am until 11 pm.


dbms_job.submit(:jobid,'if to_char(sysdate,''HH24'') > 5 and to_char(sysdate,''HH24'') <>


To purge the statspack, you can use the following example, statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60);

In prior releases, Statspack identifier tables which contained SQL Text, SQL Execution plans, and Segment identifiers were not purged. It is now possible to purge the unreferenced data in these tables. This is done by requesting the 'extended purge' simply by setting the input parameter i_extended_purge to TRUE when calling the regular purge.

statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60, i_extended_purge=>TRUE);

Tuesday, December 02, 2008

Oracle - Setting up ODBC / Heterogeneous Database connection to SQL server/Other DB

Basically to setup the connection to the ODBC / Heterogenenous connection, you need to
1) Setup the ODBC system DSN data sources. Remember to put the default database name in the setup. Test the connections after the setup.

2) Goto the $ORACLE_HOME/hs/admin/ folder. Copy the file initodbc.ora and rename it to other name e.g. initHS1.ora. Changed the trace level to OFF and the connect info to your ODBC system DSN name.


# HS init parameters

3) Then we configure the listener.ora. Create a new SID entry and put the SID_NAME into the ODBC system DSN name.

(SID_NAME=HS1) -- Enter the DSN on this line
(ORACLE_HOME = c:\ora10g) -- Enter your Oracle home on this line
(PROGRAM = hsodbc)

4) Add the hs entry into the tnsnames.ora. e.g. = -- This name can be customized
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=HS1)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services

5) Stop and then start the Listener. Check to make sure the services is inside the listener.

6) create a database link, to link to the HS services. Remember in SQL server, the " is needed for the username , password and also to specify column name.

create [public] database link link_name connect to "user_name" identified by "password" using 'HS1';

7) If you need to setup additional ODBC / HS setup, just follow the same steps with different DSN name.

For detail setup, follow the guide here.