How to identify Oracle sessions consuming CPU time

Identifying sessions consuming CPU time is a common task in Oracle performance tuning. However, as simple as it sounds, it is not that straightforward. Oracle recommends using Enterprise Manager or Automatic Workload Repository for that. The problem is that in real-life situations Enterprise Manager is often not installed, or you may not have access to it. You may not also have necessary privileges to run AWS. Besides, running AWS reports for such a simple task sounds like overkill. Read more →

OzBsidian colour scheme for Oracle SQL Developer

This is a spinoff of Obsidian colour scheme for Oracle SQL Developer. It is based on Obsidian Eclipse colour scheme by Morinar. Unfortunately Oracle doesn’t make it easy to import a new colour scheme into SQL Developer, thus a little bit of hacking is required. Close SQL Developer. This is important. If you modify the scheme file while SQL Developer is open, your changes won’t be saved. Locate file dtcache. Read more →

How to get size of all tables in an Oracle database schema

If you ever wanted to know how what’s taking space in an Oracle database, or how large is the table you’re working on, here’s a script which answers these questions. It shows the size of all the database objects large than 10 Mb in a particular database schema. The following columns are returned: Owner schema. Object name and type (INDEX, TABLE, etc.). Name of the table this object is associated with. Read more →

Don’t mess with LIKE

As I have already shown you, implicit type conversion is one of the most dangerous features of Oracle SQL and PL/SQL. It is dangerous because it happens automatically without your knowledge and may lead to unpredictable results. These problems are most common when dealing with DATE conversions, but not limited to them. For example, let’s have a look at this question by James Collins. James had a problem, the following query was slow: Read more →

Subscribe and receive an email when I publish a new article

* indicates required