RDBMS Server

Oracle RDBMS Server Articles

Reference partitioning and partitionwise joins


Why do you sometimes not get partitionwise joins? Because the optimizer isn't clever enough. Reference partitioning has many benefits, one of which is that the optimizer understands it. You will always get a partitionwise join if your tables are reference partitioned.

David Lozano Lucas's picture

Progress status of an index rebuild or creation, or other long operations.


When we launch a long operation, such as a RMAN backup or rebuild of a large index, we can come to despair of not having an estimate of the time it may take. We can even come to think that is doing nothing.

For the progress of a long operation we can query the view V$SESSION_LONGOPS, first obtaining the process ID from V$SESSION. In the case of DBA, we know exactly which user is rebuilding the index, so we can simplify it into a single query.

Constraints and performance: you want as many constraints as possible


Several times I have had to deal with people who do not want to define constraints. I have never understood why they don't, because my experience is that the more constraints you can define, the better Oracle will perform. If anyone knows where the idea that not defining constraints is a Good Thing comes from, I would be interested to know.

Following are two very simple examples of constraints allowing the optimizer to develop better plans.

Oracle RMAN Reporting


For any company the most important asset is data and the most challenging job is to recovery the database with less downtime with out any data loss, in the event of database failure. In many situations users end up with incomplete recovery of the database with out knowing which data files backed and which data files need to backup. You should ensure that your database is backed up efficiently and should restore successfully when needed. The RMAN reporting provides effective and easy way to determine database backup for a successful recovery.

ORA-1555 and SQL Tuning


Often DBAs may look to tuning the UNDO* parameters as a solution towards the infamous "ORA-1555 snapshot too old" error. In most cases, before looking to tune UNDO* parameters, the best solution is to tune the query that's running into the ORA-1555 error so the query will not error out with ORA-1555 to begin with!

Hope this helps a fellow DBA or two trying to resolve the ORA-1555(s) !

AbidMalik's picture

Let's save some space by using COMPRESSED INDEXES


Summary: An index created on column that has many duplicated rows can be tuned to save space as well as I/O trips by compressing the index defined on it.

Details: By default when we create index in Oracle, the compression is disabled on it. What if we have an index defined on a column that contains last name of all the customers, some of the names are very common as a last name. We can take advantage of this duplicated data by compressing the index defined on it. This is where the KEY-COMPRESS INDEXES comes in handy.

Blue Core Research - DBA Guide to database Auditing


The Problem
The longer you've been working as a DBA, the more you've encountered things like these:

The time on a table changed and you don't remember doing it. You poke around but can't figure out who changed it.

There's someone you don't trust that you have to give access to. They are the kind of person that will look at things they are not supposed to, but you have to give them access.

There's someone who always messes things up when they get access. You know they'll do it again and you'll have to undo the damage. Would be nice to know what they are doing.

There's a contractor or a new employee that need access. You don't know them yet and not sure how reliable they are. It would be nice to be able to keep an eye on them.

Too many people have too much access and you're losing control. You try to figure out how to reduce the access they have or revoke some privileges, but this is pretty much what everyone claims they need to do their jobs.

You've been through an audit and the auditor wanted to know all sorts of things about the activity. Questions you just don't have the information needed to answer.

You probably spent many days chasing down the answers to these questions or try to find ways to collect it. The security in the database is set properly. The problem is that you need to know what people are doing with the access they have. This knowledge will give you more control.

mikerajendran's picture

Oracle Grid Infrastructure - Reboot less Node Fencing



Oracle Grid Infrastructure has many features including Cluster Node Membership, Cluster Resource Management and Cluster Resources monitoring. One of the key area where DBA need to have expert knowledge on how the cluster node membership works and how the cluster decides to take out node should there be a heart beat network, voting disk or node specific issues. I have written about this before and this article specifically focuses on the 11g R2 features and I will also try to explain the reboot less node fencing.

Standby database using RMAN duplicate


Steps to create standby database using RMAN:

primary database name:taurus
host name:sony

standby database name:taurus

-force logging is enabled on the primary database.
-password file is created for primary database
-primary database is in archivelog mode

Blue Core Research's "NO BULL" buyers guide to Database Auditing products - Part 11: Rule Engine


The Rule engine is one of the critical pieces in an auditing solution. It sits between the data collection and the reporting output. It is the heart of the functionality that will take the job of reviewing the reports from impossible to manageable to easy. The reason it is so important is the vast amount of SQLs that go through a database engine. A good rule engine will reduce the amount of SQLs in the report and increase their relevance.


Subscribe to RSS - RDBMS Server