Oracle 11G with RAC on Windows Laptop - Guide

I was wake up by production call today @4:30am, but call ended in 10mins. Decided to read something from Oracle List and found message about RACATTACK event in Oracle OpenWorld OTN Lounge
http://www.blogger.com/img/blank.gifhttp://www.blogger.com/img/blank.gif
Further checking on Google found, this is Group which takes your Laptop with Windows and install Oracle RAC with VMWWARE. That allow us to play around.

They have created very nice Step-by-Step instructions with Detail Screenshots in the following link.

Oracle_RAC_At_Home from RACATTACK on Wikibook.

I haven't tried this, but Information is very nice and details.

Oracle - Shared Server

I got blog pointer from Jonathan Lewis about Oracle Shared Server

This is well written by Howard Rogers. Here is info from Jonathan Lewis, Oracle Scratchpad.

This item from Howard Rogers sums up the shared server technology (formerly multi-threaded server, or MTS) so well that I decided it was worth a special mention.

Playing Around with Star Transformations and Bitmap Indexes

Mark Rittman Mand Consulting has blog entry about

Playing Around with Star Transformations and Bitmap Indexes

It is very nice article about Oracle, Star Transformation.

Must read article.

Database App Development and Migration: Tools That Rock

Man, Mike Hichwa's team at Oracle is spinning out some incredible database tools. These are the guys and gals responsible for two of the most innovative things to come out of here in a long time, Application Express and SQL Developer. I view this group as a "skunk works" that has some pretty nice flexibility in terms of its role within the Server Technologies division here at Oracle, and the results show it.

Read More

Extract CSV file format in Oracle SQL

How to extract CSV file format data in Oracle SQL Plus.

---10G Solution. On SQL Plus session
alter session set nls_date_format='YYYY-MM-DD';

Session altered.

select regexp_replace(column_value,' *<[^>]*>[^>]*>’,';’)
from table(xmlsequence(cursor(select * from emp)));

=======================

-- Oracle 9i Solution. SQL Plus session.
-- Following sql fail to give EMPTY space for NULL values properly.
-- See PL/SQL solution to handle
SELECT XMLTRANSFORM
(COLUMN_VALUE,
XMLTYPE
('















'
)
) csv
FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *
FROM emp)))
/
=======================================
---- Oracle 9i PL/SQL solution
SQL> VAR cur refcursor

SQL> DECLARE
ctx DBMS_XMLGEN.ctxhandle;
BEGIN
ctx := DBMS_XMLGEN.newcontext ('select * from emp');
DBMS_XMLGEN.setnullhandling (ctx, DBMS_XMLGEN.empty_tag);

OPEN :cur FOR
SELECT (SELECT RTRIM(XMLAGG (XMLELEMENT (c,VALUE (t2),',').EXTRACT('//text()')),',')
FROM TABLE (XMLSEQUENCE (VALUE (t1).EXTRACT ('ROW/*'))) t2) csv
FROM TABLE(XMLSEQUENCE(DBMS_XMLGEN.getxmltype (ctx).EXTRACT('ROWSET/ROW'))) t1;

DBMS_XMLGEN.closecontext(ctx);
END;
/
PL/SQL procedure successfully completed.

SQL> PRINT cur
==================================

Build an Online Reporting Application Using Oracle XML Publisher

by Mark Rittman

Learn how to create an online reporting environment using XML Publisher technology, step by step.

Build an Online Reporting Application Using Oracle XML Publisher

by Mark Rittman

Learn how to create an online reporting environment using XML Publisher technology, step by step.

Oracle 9i - How to add missing partitions

Assume, We need to insert missing partition as new partition with Oracle Range Partition.

Here is the simple example for that.

create table karth_test (a date,data char(10))
partition by range(a)
( partition p1 values less than (to_date('2007-01-01','YYYY-MM-DD')),
partition p2 values less than (to_date('2007-02-01','YYYY-MM-DD')),
partition p4 values less than (to_date('2007-04-01','YYYY-MM-DD'))
)

select partition_name pname, high_value from user_tab_partitions
where table_name ='KARTH_TEST' order by partition_name

alter table KARTH_TEST add partition p3 values less than (TO_DATE('2007-03-01','YYYY-MM-DD'))

-- Above statement raises ORA-14074: partition bound must collate higher than that of the last partition.

-- We can execute following statement to overcome above error

Alter table KARTH_TEST split partition p4 at (TO_DATE('2007-03-01','YYYY-MM-DD'))
into (partition p3, partition p4)

Drop table KARTH_TEST