Imagine arriving at your workplace one morning, opening up your computer, and logging into your Oracle Database only to get the dreaded ‘error’ report, which says that all the information you saved the previous day has been wiped away.
The mere thought probably makes your skin crawl, right?
Last week I published an article about filtered indexes in SQL Server. This feature allows to save disk space by giving up on indexing all rows in a table when only a relatively small subset is going to be used. I also showed how it can improve query performance. Result I achieved was quite satisfying. This time I want to focus on features available in Oracle database that could help me do the same. There is no point to hide it - Oracle does not have filtered indexes feature. But do not worry! There is a workaround ...
Would you like to know who is connected to your Oracle database? Is there anybody currently connected? How to check where the connections come from? If you ask yourself such questions, you can find answers in this short article. Identifying a source of connections is a quite common task for a database administrator. As it is one of troubleshooting steps for various problems, it is crucial to know how to do it. Here you can quickly possess this necessary peace of knowledge.
I have had a pleasure to work with various database engines. As you probably noticed I write mostly about SQL Server and Oracle so I am familiar with those two at least. But I have also had a chance to develop systems in MySQL and PostgreSQL. All those database engines except Oracle have had a feature allowing to limit a number of returned rows from a SELECT query.
Oracle Database 12c contains ANSI standard for such functionality. Let me show you how it works. As there is a huge field for performance optmizations, I will also take a look from that perspective.
One of my most often used queries is the one that returns a list of tablespaces and datafiles with their size, amount of used space and free space. Although, this information is returned nicely by most of DBA tools like OEM, Toad, SQL Developer, somehow I have often got this information by querying the database manually. Queries give much flexibility including customizing the output like grouping by tablespace, sorting by space left, converting units from MBs to GBs, filtering other users' tablespaces out and much more. Also these queries can be a part of a more complex script ... but it is out of topic (if you are interested, see the article about scripts vs tools).