Detect numbers with TRANSLATE() – Take two

Last week I wrote about using TRANSLATE to detect numbers in data Using The TRANSLATE() function...

Andrew Clarke at Radio Free Tooting pointed out the shortcomings of using TRANSLATE() to detect numbers.

As I said earlier, all I needed to do was detect if the characters in a string were all digits or not, and I wanted it to be very fast.

But Andrew's remarks got me thinking - could translate be used to detect more complex numbers?

Here's the short list of requirements:

* Detect integers
* Detect numbers with decimal point ( 4.6, 0.2, .7)
* (more...)

Rolling invalidations

There have been discussions which I have seen related to the feature of auto invalidation in dbms_stats. A couple of references are

I have tested the relevant parameter “_optimizer_invalidation_period” on and believe that this is working as expected

Let us take the below testcase where the parameter (it is dynamic) is set to a value of 120

SQL> show parameter optimizer_inva

------------------------------------ ----------- ------------------------------
_optimizer_invalidation_period integer 120

We have the following sql statement

11:00:00 SQL> select * from source where rownum<2;

OBJ# LINE (more...)

Estimating the Network Band Width Required for Standby Database

| Jan 29, 2008
For Better DR (Disaster Recovery) Site setup it is important to know the required Bandwidth Link Between the Primary and DR Site.

By using the Below formula, we can estimate the required Bandwidth Based on the Peak redo rate.

Required bandwidth = ((Redo rate in bytes per second / 0.7) * 8) / 1,000,000

= bandwidth in Mbps.

How to find a Redo Rate for a Database:-

Redo Rate can be found out from the Statspack report. During the peak duration of your business, run a Statspack snapshot at periodic intervals. For example, you may run it three (more...)

Introducing RuleGen

I've been working with CDM Ruleframe for a few years now. Recently I've attended a presentation about another framework focusing on business rules called RuleGen.

RuleGen is a framework written in PL/SQL that generates  code to maintain data integrity constraints. Right now RuleGen implements table constraints, i.e. at most (more...)

Small change make difference

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a

SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise

MYSUN:oracle> ( /usr/proc/bin

$ (more...)

Make Rails database migrations faster on Oracle

When using Ruby on Rails on Oracle I noticed that my database migration tasks are much slower than on MySQL.

I just found the cause for that – rake db:schema:dump task was taking very long time on my Oracle databases (and this task is executed at the end of rake db:migrate). As a result of this task Oracle Rails adapter is executing

SELECT LOWER(table_name) FROM user_tables

statement. If your database contains a lot of other schemas with a lot of tables (as in my case I deploy Rails user schema in Oracle E-Business Suite database) then this (more...)

Java Search Engine Integration for EclipseLink JPA

The creator and primary contributor to Compass has taken the initiative to provide support for using EclipseLink ( with Compass. I hope to find the time to build an application that illustrates the use of the two projects but thought I would post some early information to the (more...)


Architecture Database Synchronization Options Setup No-Data-Divergence Setup Primary Database Setup Standby Database Start Managed Standby Recovery Protect Primary Database Cancel Managed Standby Recovery Activating A Standby Database Backup Standby Database Database Switchover Database Failover Automatic Archive Gap Detection Background Managed Recovery Delayed Redo


Syntax LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] = "null_string" | ((SERVICE=service | LOCATION=location) [AFFIRM | NOAFFIRM] [ALTERNATE=destination | NOALTERNATE] [ARCH | LGWR] [DELAY[=minutes] | NODELAY] [DEPENDENCY=destination | NODEPENDENCY] [MANDATORY | OPTIONAL] [MAX_FAILURE=count | NOMAX_FAILURE] [QUOTA_SIZE=blocks | NOQUOTA_SIZE] [QUOTA_USED=blocks | NOQUOTA_USED] [

Data Guard 9i Log Transportation on RAC

PURPOSE-------This article gives an overview about how to create a Data Guard Configuration on Real Application Clusters (RAC). The Configurationyou can find here is for a Physical Standby Database. SCOPE & APPLICATION-------------------You can see which initialisation parameters you have to use / changeand how the Log Transport is organized in a RAC-DataGuard environment. NOTE: The Data Guard

Creating a Data Guard Configuration

1) Ensure the Primary database is in ARCHIVELOG mode: SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /export/home/oracle/temp/oracle/arch Oldest online log sequence 7 Current log sequence 9 SQL> alter database close; Database altered. SQL> alter database archivelog; Database

Regular Tasks Of a DBA….

1.Regular Monitoring of The free space in Database. 2.Taking logical bakups of important table. 3.Checking the locks on the Database. 4.Checking the long running queries on ur database 5.Analysing the performance of ur Database 6.Investigate Wait Statistics 7.Tablespace Usage 8.Ensure Connectivity to Oracle 9.Lock Contention 10.Extent Failure 11.Alert Logs

Regular Taska Of a DBA

1.Regular Monitoring of The free space in Database.2.Taking logical bakups of important table.3.Checking the locks on the Database.4.Checking the long running queries on ur database5.Analysing the performance of ur Database6.Investigate Wait Statistics 7.Tablespace Usage 8.Ensure Connectivity to Oracle 9.Lock Contention 10.Extent Failure 11.Alert Logs 12.Redo Logs 13.Check if all the instances

Oracle 11g Security Enhancements Part – 1

Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

  • SEC_RETURN_SERVER_RELEASE< ?xml:namespace prefix = o />

These parameters are system wide and static.

  • Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.



----------------------------- -------------------- ---------------------
sec_return_server_release_banner boolean FALSE

When set to true the (more...)

The blog tagging thing

During the last few days lots of Oracle bloggers have been busy tagging each other and posting eight unknown things about themselves. I was also tagged by some friends and was asked to post eight things about myself. I have never forwarded any chain e-mails or messages to anyone and in parallel to that I have not written anything about myself after this either.

What I think about this blog tagging thing is very similar to what Howard Rogers thought about it. He shut his site down for some time and you can read what he thinks when you go (more...)


 Gareth Roberts tagged me. Thanks for the invitation. And here are 8 things you did not know about me.

  1. My nickname Jornica is derived from Jorrit Nijssen. However five characters is often not enough for an username. By adding some extra random chosen characters ca it is long enough...
  2. I maintain another non Oracle related blog as well: Etc.
  3. Keywords of my favorite holidays: sea, sun and hills. Did I mention volcanoes?
  4. (more...)

procedure for Calculating Database Growth and scheduling in DBMS JOBS….

1. Create a Table By the Name db_growth...with following details... Name Null? Type ----------------------------------------- -------- ---------------------------- DAY DATE DATABASE_SIZE_MB NUMBER DAILY_GROWTH_MB

Introduction to Simple Oracle Auditing

IntroductionThis article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It

10 Scripts Every DBA Should Have

I. Display the Current Archivelog Status :ARCHIVE LOG LIST;II. Creating a Control File Trace FileALTER DATABASE BACKUP CONTROLFILE TO TRACE;III. Tablespace Free Extents and Free Spacecolumn Tablespace_Name format A20column Pct_Free format 999.99select Tablespace_Name,Max_Blocks,Count_Blocks,Sum_Free_Blocks,100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free from(select Tablespace_Name, SUM(Blocks)

Fix for Rails 2.0 on Oracle with database session store

As I started to explore Rails 2.0 I tried to migrate one application to Rails 2.0 which is using Oracle as a database. Here are some initial tips for Rails 2.0 on Oracle that I found out.

Oracle adapter is no more included in Rails 2.0 so you need to install it separately. It is also not yet placed on therefore you need to install it with:

sudo gem install activerecord-oracle-adapter --source

The next issue that you will get is error message “select_rows is an abstract method”. You can find (more...)