A few days ago I was browsing the documentation and suddenly realized that I haven’t gone over the 12.2 books. One of the things I like to do is reading the “New Features” guide, so I did. After writing about the major features, here are some smaller ones, but still important enough to know.
When writing a query with order by, we can use the column position instead of its name. This order by”trick” is easy to use, but should be handled carefully as it affects the order of rows if the column list changes.
The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific use case that I’ve seen a few times, and is related to index scans and performance.
Many people are using DBMS_STATS to gather optimizer statistics. This package has many different options, and I wanted to tell you about one option for disabling automatic statistics gathering. When we need to disable the automatic statistics gathering we usually do one of the following:
When user A creates a procedure and grants execute permissions to user B, user B can execute the procedure (obviously), but he can also see the code (in ALL_SOURCE view).
Lately I had a weird problem with one of my customers regarding timezone in the database. They copied a database server from US (eastern time zone) to Europe (GMT) and changed all the OS setting to the new timezone. The application uses “sysdate” to insert data to the database, and they saw that the sysdate returns the wrong time. When I checked that, it seemed that when we used sqlplus locally on the server everything worked perfectly, sysdate, current_timestamp and systimestamp all returned the correct times with GMT. In SQL developer, however, even when running from the same timezone (GMT) the result of sysdate and systimestamp were still eastern time and not the GMT (current_timestamp returned the correct time).