Bitmap Indexes: Understanding Bitmap Indexes

Bitmap indexes are a great boon to certain kinds of application, but there is  a lot of mis-information in the field about how they work, when to use them, and  the side-effects. This article examines the structure of bitmap indexes, and  tries to explain how some of the more commonly repeated misconceptions came into  existence.

Everybody Knows …

If you did a quick survey to discover the understanding that people had of  bitmap indexes, you would probably find the following comments being quoted  fairly frequently:

a) When there are bitmap indexes on tables then updates will take out full  table locks.

b) Bitmap indexes are good for low-cardinality columns.

c) Bitmap index scans are more efficient than tablescans even when returning  a large fraction of a table.

Continue reading

Advertisements

Fine Grained Auditing of Oracle Database

Why use Oracle Database Security Fine Grained Auditing feature?

In addition to the Oracle Standard auditing, Oracle Database Security also provides Fine Grain Auditing to audit use of the objects at a more granular level. Some of the benefits when using this method are below.

  • It is able to record access of specific columns, for a specific user.
  • It can audit for exact statement, collect the complete SQL text and bind values.
  • Because of being able audit at a granular level, auditing specific columns, in most cases it will thus store less rows and result in lesser resource usage on the database server.
  • Unlike the standard auditing, no parameter setting or a database restart is required.
  • It can easily be turned on-off with a database restart.

Continue reading

PASSWORD FILE AUTHENTICATION IN ORACLE DATABASE

Introduction

As a DBA we must have used sqlplus “/as sysdba” to connect to database, atleast hundred times a day. Never bothered about the password to provide !!!

This is because we were using OS level authentication. We can change the configuration and make Oracle to ask for the password. Well, “/as sysdba” works fine if we are connecting to the host where the database is actually installed.

For example I have installed a database as oracle01 user (which belongs to DBA group) on one of my host called “host1″. I telnet to host1 as oracle01 user and provide the password to login. Once I successfully login to the host, there ends the authentication part. Not for administering the database all I have to do is to use our famous command to connect to database – “sqlplus /as sysdba”.

The reason above thing work is because I was using Operating System level authentication. If I try to connect to same database as sysdba from some other host, I wont be able to connect. Because the authentication is done based on host login password. Since I haven’t logged into host, authentication will fail and connect as sysdba will fail. So for OS authentication its mandatory that you are always logged into the host where the oracle is installed (oracle database resides). Continue reading

ORA-00845: MEMORY_TARGET not supported on this system

Problem:
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system Continue reading

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

Problem:

We have an application running locally which two days worked fine and now we get the: TNS:listener does not currently know of service requested in connect descriptor error.

I’ve tested the connection using TNSPing which resolved correctly and Tried SQLPlus to try connecting, which failed with the same error. (i.e. Sqlplus username/passowrd@addressname[or host name] The TNS Listener on the server is running. Oracle itself on the server is running. Continue reading