content top

How to Stop the Job Scheduler

You can prevent the Job Scheduler from running at startup by

This method will not prevent the use of DBMS_SCHEDULER.RUN_JOB. If someone use DBMS_SCHEDULER.RUN_JOB, the job will execute regardless of the setting of the SCHEDULER_DISABLED attribute

If you want to also stop the jobs which is created by DMBS_JOBS, you need to issue:

Installing and Configuring StatsPack

Statspack is a performance tuning package provided by Oracle. It’s free to use and a reliable tuning tool since Oracle 8i. You can install it on Oracle 10g and 11g if you don’t have license for tuning pack. Oracle recommend installing the statspack package into a different tablespace than system tablespaces. “TOOLS” tablespace of Oracle 9i is  because I’ll install it to . If you use Oracle 10g+, you need to create this tablespace.

During the installation, the user PERFSTAT will be created. This user will own all objects of the STATSPACK package. The installation SQL script will prompt for the PERFSTAT user’s password and default and temporary tablespaces.

You need to check spcpkg.lis for any errors. You can test your statspack install by issuing:

You can create spauto.sql script to automatically schedule an hourly data collection for statspack:

If you want to remove statspack, run spdrop:

Split String Function in MySQL

I saw a question about how to split delimited string in MySQL. MySQL does not include a function to split a delimited string. So I decided to write a simple one:

When we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 3 ), we get the 3rd item “mehmet”. If we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 5 ), it will return an empty string.

MINUS and INTERSECT in MySQL

MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:

Sample query with the MINUS operator:

In MySQL:

Read More

How to Make a Table Read-only in Oracle

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:

To make it writable again, issue the following command:

In previous versions of Oracle, there was no command to make a table read-only, but we can use a little trick to do it. All we need is to create a constraint and make it disable:

Because the constraint is disabled, it is not enforced but it will not let you change the data to ensure that data still conforms to the constraint’s requirements. Unfortunately, direct path loads will bypass this, so we also need to issue the following command:

This will also prevent user to drop this table. Another option is to mark the tablespace which contains the table as read-only, but it’s not practical because it wll make all the objects read-only residing in the tablespace.

Page 38 of 43« First...3637383940...Last »
content top