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.

OzBsidian colour shceme

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.xml in the SQL Developer’s settings directory. On my system it is located in directory C:\Users\sergey\AppData\Roaming\SQL Developer\system4.0.3.16.84\o.ide.12.1.3.2.41.140908.1359

  • Locate <schemeMap> tag inside dtcache.xml file. Insert the content of ozbsidian-scheme.xml file inside <schemeMap> alongside the other colour schemes. Be careful not to break the XML.

Insert the contents of ozbsidian-scheme.xml after opening schemeMap tag

  • Launch SQL Developer. Navigate to menu Tools->Preferences, then select item Code Editor -> PL/SQL Syntax Colours in the left pane.

  • Select “OzBsidian” in the “Scheme” drop down list on the top.

SQL Developer Settings

The colours are mostly match Obsidian scheme, although not exactly. For instance, the background is a bit darker. Hence the name is OzBsidian to differentiate it from the original scheme.

You can download the colour scheme with the installation instruction from my GitHub account.

Enjoy!

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. E.g. indexes are associated with their parent tables.
  • Database space occupied by the object in megabytes.
  • Tablespace this object is in.
  • Number of extents allocated for the object.
  • Size of the initial extent in bytes.
  • Total database size occupied by the parent table. E.g. for indexes it will be the size of the parent * table plus sizes of all the indexes on that table.

This script is based on the Stackoverflow.com discussion.

Troubleshooting eth0 in Oracle Linux

A few days ago I installed Oracle Linux in an Oracle VirtualBox VM. Once it was installed I found that eth0 interface wasn’t starting upon boot.

ifconfig eth0 isn’t starting

That was unusual. I am myself a Linux enthusiast, and I regularly download and install lots of distributions, and, by and large, network works in them out of the box.

Today I finally got around to troubleshooting this problem. It took me a couple of hours digging through the scripts, setting trace points, and reading logs; and here’s what I found.

For the reference, this was my configuration:

  • Oracle VM VirtualBox 4.1.22 r80657
  • Host: Windows 7 64-bit
  • Linux: Oracle Linux Server release 6.3
  • Kernel 2.6.39-200.24.1.el6uek.x86_64 on x86_64

As I said, upon boot eth0 was down. If I tried to bring it up with ifconfig eth0 up it came up in IPV6 mode only, no IPV4:

eth0 only comes up as IPV6

Hm, weird. What was even more weird was that if I brought it up with ifup eth0 instead of ifconfig eth0 up, IPV4 network started succesfully:

IPV4 started successfully

Obviously, these 2 were are different. The reason ifup worked was because it called dhclient to obtain an IP address from a DHCP server

dhclient

Whereas ifconfig didn’t make that call. And because the network interface did not have an IP address, it stayed down.

So, what is the difference between ifup and ifconfig up?

Well, ifup is actually a script located in /etc/sysconfig/network-scripts.

During the system boot the network subsystem is brought up via startup script /etc/rc2.d/S10network. That script goes through all the network interfaces it can find and brings them up during the boot. What’s interesting, I found that it uses a set of configuration files /etc/sysconfig/network-scripts/ifcfg-*<interface_name></interface_name>* to determine if a particular interface needs to be brought up during the boot time. There is one config file for each interface. I’m not sure when they are created, maybe at install time.

In my case I found that one of the parameters in ifcfg-eth0 file was ONBOOT=no. Turned out the network startup script uses that parameter to determine if the particular interface should be brought up at the boot time.

So, I changed it to ONBOOT=yes and everything worked. Now when the system starts, eth0 is up and running.

Problem solved!

Update:

So, the mystery is finally solved. There is “Connect automatically” checkbox on the installer’s network configuration screen (item 10 here). If this checkbox is unchecked, network interfaces do not come up at system start-up.

Many thanks to the Oracle Linix team for helping me in this investigation.

What is a data warehouse?

Data warehouse boxes

All database applications can be divided into 2 classes: OLTP and data warehouses. OLTP stands for Online Transactions Processing. It’s easy to explain the difference between them by an example.

Imagine yourself buying groceries at your local supermarket. When you are done filling your shopping basket and finally come to checkout, here’s what happens:

  • A shopping assistant at the checkout register picks up your first item and scans its barcode. The computer built into the register turns the barcode into a series of characters and digits and queries the store’s database for the item’s description, price and picture. Then it adds the item into your virtual shopping cart.

  • You reach for your next item and all of those steps happen again: a few queries sent to the database, a few tables updated. And then again and again, until your basket is empty.

  • You swipe your credit card. The shop’s system sends a request to the credit card processing centre. It checks that you card is valid and that you are not over your credit limit. Then it asks you for your pin code, verifies it, and if all the checks are successful, withdraws the money.

  • Then the groceries you bought are marked as sold in the inventory control system. A receipt is printed, and the virtual shopping basket is cleared. That’s it. Thank you for shopping with us.

It probably took you less than 5 minutes to go through the register. But the number of database queries and updates it resulted in was likely in hundreds. Now multiply that by the number of checkout registers in the shop, and you’ll get the picture. Checkout register is a typical OLTP system.

OLTP applications are characterized by large volume of atomic database transactions: retrieve 1 row from a database table here, 3 there, insert a few lines into a third table or update a row of yet another table.

Most of OLTP-type applications are interactive: you press a button and expect the system to respond within a few seconds at most. That presents a challenge if the application is a multi-user system with hundreds of users working at the same time. It is not uncommon for an OLTP system to crunch tens of thousands, even millions of database operations per minute. And because of that these operations need to be extremely fast.

Therefore OLTP systems are specifically engineered for a low latency in conditions of high volume of elementary database operations.

Now imagine that at the end of the financial year the COO of the supermarket chain requires a detailed sales report. He wants to know which products were selling faster comparing to the previous 10 years with a breakdown by geographical areas. He also wants to know the correlation between the increased prices and sales volumes aggregated by product categories, and so on. Every business requires hundreds and hundreds of such reports, and not just at the end of the financial year but sometimes every day. Financial departments, sales, marketing, and management – they all want to know how the business is doing. In fact, there is a special buzzword for that kind of reporting – “Business Intelligence”. Another buzzword “Data mining” means “Looking for answers for which we don’t know questions yet”. Data mining looks for trends within the data and tries to find correlations to the known parameters. An example of a data mining query is “Find out if the sales volume changed within the last year and what else is changed that could explain that”.

Business Intelligence and Data Mining reports tend to be highly complex. Most of times they require querying data across very large datasets, such as all sales over the last 10 years,  then aggregating it, then calculating summaries, averages, correlations and so on. These are very different types of operations from which OLTP systems are designed for. And that is why running them against OLTP databases is usually not a good idea. This is what data warehouses are for.

Data warehouses are database systems optimised for complex database queries over large data sets.  They are predominantly used for Business Intelligence and Data Mining reporting. Within large corporate data warehouses it is common to have “sections” designed for specific purpose, for example for particular types of financial reports. These sections along with their business intelligence logic are called Datamarts.

Data warehouses usually source data from multiple systems. For example, sales data, financial data, inventory, vendors, etc. may come from different geographically distributed platforms which otherwise don’t talk to each other. Having all this information in one data warehouse enables business users to get reports such as profits on sales of certain goods per supplier.

Databases matter

Database

Databases are at the core of virtually all modern information technology systems. Sometimes these databases are exposed, for example, as in data warehouse systems. The centrepiece of a system like that is a powerful industrial database such as Oracle, or Sybase, or Microsoft SQL Server, or something similar. The presence of a database is pretty obvious in that case – only these powerful databases can crunch the immense amounts of data processed by data warehouses. In other cases databases are hidden. For example, Apple iTunes uses a SQLite database to store the details of music tracks on your computer. That database is not obvious; it does not advertise itself, but it’s there. It makes sure that the ratings you assigned to songs are saved and can be synchronised between all your iPhones and iPads. It counts the number of times every song was played so that you don’t listen to the same song twice when you put your iPhone on shuffle. Databases are everywhere. They all serve the same purpose – to store data and make its retrieval as easy and fast as possible – but they are also vastly different from each other.

Let’s take another example. When you log into your Google account and bring up your Gmail inbox, all the emails you see are actually stored in the remote database. That database is called BigTable, and it contains not only your emails, but all the emails of all Gmail users in the world, and also virtually all of Google’s data. While your iTunes SQLite database may be about 50 megabytes in size (and that’s assuming you have A LOT of songs), Google’s BigTable contains petabytes of data. That’s your iTunes database times one billion.

If you think about it, it becomes obvious that these databases require vastly different approaches to the way the data are stored and retrieved: You can fit an iTunes database into memory and query it whichever way you like without a performance penalty. At the same time, no machine has been built yet that could apply the same approach to Google’s BigTable.

Unfortunately, not all software developers understand that. Databases once were an inspiring topic but in recent years they went out of fashion. Software developers are geeks; they like new toys; they all want to work on something latest and greatest and cutting-edge. So many new exciting things are happening in the area of Information Technology – Web 2.0, HTML5 and Apple iOS to name just a few – that databases just fade in comparison, despite the fact that they make all these new shiny things tick. Most of the developers these days take a database just as generic data storage: “We’ll just stuff the data in and we don’t care what’s inside.” 10 years ago SQL language was a necessary skill for database application developers. Nowadays the majority of programmers don’t know SQL. They rely on frameworks such as Hibernate to produce SQL statements for them. They think that all databases are the same and therefore, if necessary, they can take one system that uses MS SQL Server as a backend and put it onto Oracle and it will work just fine.

Well, that may be true for very simple applications. The myth that all databases are the same is flawed, especially when it comes down to performance. Today cloud computing is a buzzword, and Google is the patriarch of the cloud. Google’s servers process billions of requests every day, crunching petabytes of data. Yet, every request made to a Google search engine is served within seconds. This places such a high demand on the Google’s database layer, that Google’s engineers couldn’t afford using even the most powerful of industrial databases, and they had to develop their own – the aforementioned BigTable. If you told these guys that “all databases are the same”, they would laugh into your face, and rightfully so, because Google knows that performance matters and they try to squeeze every bit of performance out of their systems.

Databases matter, and if you consider yourself a decent software developer, you need to learn how to tame them. Learn the differences between them. Learn what they are, what makes them tick, and the most importantly, how to make them tick faster, because writing applications that are slow is just bad taste.

Subscribe and receive an email when I publish a new article

* indicates required