Sakarya University Information Technologies Festival

Ferhat Şengönül, Gürcan Orhan and I, as members of TROUG (Turkish Oracle User Group) will be in Sakarya University to present in IT Festival organized by Computer Science Community at March 24th. We will share our experience & knowledge about Oracle Technologies.

Here’s the schedule:

10:00 – 10:50 How To Become an Oracle DBA Gökhan Atıl
11:00 – 11:50 Datawarehouse and ETL Gürcan Orhan
12:00 – 14:00 Launch Break
14:00 – 14:50 DBA in Business Life Ferhat Şengönül
15:00 – 15:50 Questions and Answers Panel

How to Find Unused Indexes

You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:

ALTER INDEX index_name MONITORING USAGE;

After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:

You can run the following query to disable monitoring of an index:

ALTER INDEX index_name NOMONITORING USAGE;

Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.

SYS/SYSTEM Users and ORA-01031 (Prior to Oracle 9.2)

SYS and SYSTEM users have DBA roles so they manipulate any object on database, but if you are using an Oracle DB prior to 9.2, you’ll see that DBA role is not enough to manipulate the privileges of other users’ objects.

Because before Oracle 9.2, there was not a system privilege called “GRANT ANY OBJECT PRIVILEGE”. For example, you can not run the following query on Oracle 8i (even with SYS):

Here’s a trick to overcome this problem: We create a temporary procedure under the target schema:

After we grant the required privileges, we can drop the procedure:

Oracle Linux 6 Has Been Released

Oracle Linux 6 has been released and the installation DVD images are available on edelivery web site. Oracle Linux 6 is free to download, install and use. As is the case with Oracle Linux 5.6, the default kernel on x86_64 platform in Oracle Linux 6 is the Unbreakable Enterprise Kernel. On the other hand, Oracle Linux 6 also includes a Red Hat compatible kernel built directly from RHEL source. It’s already installed, and you can use it if modify /etc/grub.conf file.

Unbreakable Linux Network: http://linux.oracle.com
Oracle Public Yum Server: http://public-yum.oracle.com
Oracle E-Delivery: https://edelivery.oracle.com/linux

ASP, ADODB and ORA-01013

One of our software developers told me that there’s a problem with database server, and they get ORA-01013 while trying to fetch data from a web page (using ASP). Although ORA-01013 error is related with client side, I checked the database and saw that everything is OK.

So I started to examine the web page with the developer. The page was executing a query which takes 1 minute to return the result. I noticed that they got the error after 30 seconds, so I wanted to check the timeout settings of IIS (Internet Information Services – Application server of Microsoft). We see that the timeout was 10 minutes. I examined the connection strings and see that ConnectionTimeout value of ADODB was set to 900. These settings look OK but the error was still there.

In almost every case, DBAs are expected to know to handle all kind of database connection problems, even if the problem is related with programming. After reading the documents of ADODB, I noticed that there’s a paramtere called “CommandTimeout”. So the developer added the following line and the problem is solved: