Index Internals.

Tables can grow large, and when they do, it becomes difficult for users to quickly find the data they need. Oracle offers indexes as a method of speeding database performance when accessing tables with a lot of data. The b-tree index is the traditional and most useful indexing mechanism. It stores the data in a tree like structure.

The b-tree index structure is made up of root block, branch blocks and leaf blocks. The root block is an entry point where search for data in index starts. Any index contains only and only one root block. The root block is (more...)

Oracle 11g NF Database Replay

Oracle 11g New Feature Database Replay

“Simulating production load is not possible” , you might have heard these word.

In one project, where last 2 year management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.

After using these feature of 11g , they will gain confidence and will able to migrate to Linux with (more...)

Oracle 11g Database Replay

If your database currently running on 10g R2 , and want upgrade database to 11g then you can take advantage of Database Replay , As per Datasheet given on OTN workload capture on can run/replay on 11g.

So , it simply mean , before you going to upgrade from 10g R2 to 11g , you can take advantage of database Replay feature i.e. capture work load on Production 10g R2 database , then copy workload to test system , upgrade test system to 11g , run workload captured on production and (more...)

Add / Delete a row from a SQL based Tabular Form (Static ID)

The current application I am working on involves a re-write to a 12 screen wizard that was written 18 months ago. Several of the screens make use of manually built tabular forms (SQL report regions) and collections to hold the values entered. Some of the screens in the wizard have (more...)

Creating a physical standby Database in Oracle10g

STEPS for creating 10g dataguardprerequisite : 9i dataguard setup knowledgestep1 :Prepare initSID.ora file for primary and standby databases as follow.** STANDBY setup parameters are given in BOLDpart A)**** Production database primary file ****prod.__db_cache_size=125829120prod.__java_pool_size=4194304prod.__large_pool_size=4194304prod.__shared_pool_size=79691776prod.__streams_pool_size=0*.

Migrating Dictionar managed tablespace to locally managed tablespace

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;*ERROR at line 1:ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrateORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0ORA-06512: at line 1SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')PL/SQL procedure successfully

Installation of oracle9i/10g on Linux & Solaris

9/10g install on Linux/Solaris Install Oracle 9i Database on Linux RHEL AS 3the following lines can be added to the /etc/sysctl.conf file:kernel.shmmax = 2147483648kernel.shmmni = 128kernel.shmall = 2097152kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000In addition the following lines can be added to the /etc/security/limits.conf file:oracle soft nofile

Sql Scripts for knowing the transaction status

select * from v$transaction;--All tranasactions + sid and usernameselect s.sid,s.username, t.start_time, t.used_ublk, t.used_urecfrom v$transaction t, v$session swhere t.ses_addr=s.saddr;--All tranasactions + sid and username + first 64 bytes of SQLselect s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_textfrom v$transaction t, v$session s, v$sql sqlwhere t.ses_addr=s.saddrand


Give more knowledge to cost based optimizer about object statisticsand make it intelligent.. U remember Knowledge is Power - Aristotle-- Gather stats for a table. Auto sampling and auto number of histogram -- buckets, might not work in previous Oracle version.begindbms_stats.gather_table_stats('&OWNER','&TABLE_NAME',null,DBMS_STATS.AUTO_SAMPLE_SIZE -- put null or percentage for older oracle,false

Query in DR….

What is Right What is Wrong Found a DBA giving following commands at standby site to switchover while standby database was mounted.SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY*ERROR at line 1:ORA-16139: media recovery requiredSQL> recover managed standby database disconnect;Media recovery complete.SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO

How to create Databas in 2 node RAC ..?

Database creation in RAC environment( 2 instances). can not rely on dbca always CREATE PARAMETER FILE AND PLACE IT IN SHARED LOCATION (O:\TEST\INITTEST.ORA shared location here ) AS FOLLOWING SO THAT BOTH RAC INSTANCES CAN SHARE IT. point to this pfile/spfile from individual instance home/dbs. # START OF INITTEST.ORA *.aq_tm_processes=1*.test1.background_dump_dest='D:\Oracle\admin\test1\bdump'*.

Way to get Query execution plan in Oracle10g

traditional method before 10gselect * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;10g select * from v$sql where sql_id='&SQL_ID';select * from v$sqlstats where sql_id='&SQL_ID';select * from dba_hist_sqlstat where sql_id='&SQL_ID';select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));


--1 Get DDL of the object:declareclb CLOB; pos INTEGER:=1; amt INTEGER; len INTEGER; txt VARCHAR2(4000);beginclb := dbms_metadata.get_ddl ('$OBJ_TYPE','$OBJ_NAME','$OWNER');len := LENGTH(clb);LOOPamt := nvl(INSTR (clb, chr(10), pos),len) - pos;IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;pos := pos + amt + 1;DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));EXIT WHEN pos>=len


--1 Move index from one tablespace to anotheralter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME;--2 Moving index partition from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME;--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild subpartition &IND_SUBPART_NAME tablespace &


--Long operationsselect sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds elapsed,last_update_time last_timefrom v&session_longops --where sid=73order by last_update_time desc;--All active sessionsselect * from v&session where status='ACTIVE'--and sid in (37,43)order by sid;--Find session's sid or process id by it's sid or process


-1 Move table from one tablespace to another-- (check for unusable indexes after that).alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME;--2 Move table partition from one tablespace to another-- (check for unusable indexes and partitoned indexes after that).alter table $OWNER.$TABLE_NAMEmove partition $TAB_PART_NAME tablespace $NEW_TS_NAME;--3 Move table subpartition from one tablespace

OraSRP with Google Charts

OraSRP now is able to generate charts using Google Chart API. Actually, OraSRP is just generates a hyperlink and Google generates *.png picture.

This feature is disabled by default and you can enable it by specifying --google-charts flag.

Regular Expressions: REGEXP_COUNT (New SQL Snippets Tutorial)

| Dec 16, 2007
Introduced in Oracle 11g the REGEXP_COUNT function greatly simplifies counting the number of times a pattern appears inside a string. The following examples demonstrate how to use REGEXP_COUNT with some simple patterns. For versions prior to 11g alternative logic that provides similar functionality is also presented.

Mac OSX, Bootcamp and a Missing Hash Key

I bought a Mac Book Pro about 8 months ago as my main business Laptop. Coupled with a copy of Parallels, I built my Oracle Server (Database and Apps Server) on a Windows VM environment which left Mac OSX free for Development using SQL Developer, Dreamweaver etc

A couple of (more...)

Integrating the Google Chart API in APEX

A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance,5,6,0&chs=200x100&chl=10|20|30|40

will give the image shown left. At the right the Flash (more...)