Partitioned Table.... 11g Features

Interval-Numeric Range Partitioned Table

Also possible are Interval-Hash and Interval-List………  11g Features

create table interval_part (
person_id   number(5) not null,
first_name  varchar2(30),
last_name   varchar2(30))
partition by range (person_id)
interval (100) store in (users) (
partition p1 values less than (101))
tablespace tbs1;

desc interval_part

SELECT table_name, tablespace_name, partitioned FROM user_tables;
col high_value format a20
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;
INSERT INTO interval_part (person_id, first_name, last_name) VALUES (100, 'Dan', 'Morgan');
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;
INSERT INTO interval_part (person_id, first_name, last_name) VALUES (101, 'Heli', 'Helskyaho');

SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

INSERT INTO interval_part (person_id, first_name, last_name) VALUES (567, 'Tara', 'Havemeyer');

SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

Interval-Date Range Partitioned Table ………  11g Features

CREATE TABLE interval_date (
person_id NUMBER(5) NOT NULL,
last_name VARCHAR2(30),
dob DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN (users, example, tbs1) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2008-03-15','YYYY-MM-DD')));

INSERT INTO interval_date (person_id, last_name, dob) VALUES (1, 'Morgan', SYSDATE-365);
INSERT INTO interval_date (person_id, last_name, dob)VALUES (2, 'Lofstrom', SYSDATE-365);
INSERT INTO interval_date (person_id, last_name, dob) VALUES (3, 'Haveyer', SYSDATE-200);
INSERT INTO interval_date (person_id, last_name, dob)VALUES (4, 'Small', SYSDATE-60);
INSERT INTO interval_date (person_id, last_name, dob) VALUES (5, 'Ellison', SYSDATE+60);

col partition_name format a14
col tablespace_name format a15
col high_value format a85
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

conn / as sysdba

SELECT * FROM insert_tsn_list$;

Interval-Interval Range Partitioned Table with new partitions created every six months ………  11g Features

CREATE TABLE interval_interval (
prod_id NUMBER, time_id DATE, amount_sold NUMBER) PARTITION BY RANGE (time_id)
INTERVAL (NUMTOYMINTERVAL(6,'MONTH'))
STORE IN (tbs1) ( PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-1999', 'DD-MON-YYYY'))) ENABLE ROW MOVEMENT;

INSERT INTO interval_interval SELECT promo_id, time_id, amount_sold FROM sh.sales;
SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'INTERVAL_INTERVAL';
INSERT INTO interval_interval SELECT promo_id, time_id, amount_sold FROM sh.sales;

conn / as sysdba

SELECT * FROM insert_tsn_list$;

Interval-Interval Range Partitioned Table with new partitions created every hour using OLTP compression ----……… 11g Features

CREATE TABLE hourly_interval (
datetime  DATE,
some_data NUMBER)
PARTITION BY RANGE (datetime)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
STORE IN (part1, part2, part3) (
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2010 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))) COMPRESS FOR OLTP;

INSERT INTO hourly_interval (datetime, some_data)VALUES (SYSDATE, 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(1/24), 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(1/24), 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(3/24), 1);

exec dbms_stats.gather_table_stats(USER,'HOURLY_INTERVAL', CASCADE=>TRUE);

SELECT table_name, partition_name, high_value, num_rows, compression, compress_for FROM user_tab_partitions;

Environment variable: "PATH"

Environment variable: "PATH" - This test checks whether the length of the environment variable "PATH" does not exceed the recommended length.
info:Verification result for node:
info:expected value :1024
info:actual value :3

This test checks whether the length of the environment variable “PATH” does not exceed the recommended length
Ok the above problem causes problems when installing an Oracle 11g

 database on a machine which already has a lot of different software installed, at Seer Computing our Consultant’s machines have Oracle 9i, 10g as well as 11g installed.

So what is the solution to the above problem … the way we get round it is as follows (instructions for Windows XP machine).

1. When the failure occurs during installation of Oracle 11g, click on cancel, you will need to restart the installation from scratch
2. Go to Start -> Control Panel -> System -> Advanced -> Environment Variables (at bottom of the option)
3. Find the Path environment in the System Variables window and edit it, click on it and select all the values within it, paste these into a word document or something and save it. You might wish to see whats needed and what isnt but thats a different problem !
4. Clear the Path environment and simply add a single directory such as c:\app\
5. Start the installation of Oracle 11g again and sit there and wait while it chunders through the processing.
6. Return to the Environment Variables and paste the originals back after the new ones put in by the installation

Recovering of a dropped tablespace

Recovering of a dropped tablespace

 Tablespace Point In Time Recovery (TSPITR) method cannot be used to recover from a case of a dropped tablespace.

When u drop a tablespace including the contents and datafiles. The controlfile will  no  longer have any records  of the dropped tablespace. If all u attempt to recover the dropped tablespace using the RMAN RECOVER u will hit a error massage like

RMAN-06019: could not translate tablespace name "COLA"

There are some work around step to get rid of the above massage.

23:30:36 SQL> drop tablespace cola including contents and datafiles;

C:\>rman target backup/backup

RMAN> recover tablespace cola;

Starting recover at 08-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:06

Finished recover at 08-FEB-12

RMAN> restore datafile 6;

Starting restore at 08-FEB-12

using channel ORA_DISK_1

RMAN-00571: ===============================================

RMAN-00569: ======= ERROR MESSAGE STACK FOLLOWS =========

RMAN-00571: ===============================================

RMAN-03002: failure of restore command at 02/08/2012 23:31:00

RMAN-20201: datafile not found in the recovery catalog

RMAN-06010: could not translate tablespace name "COLA"

So to recover from a dropped tablespace,

1)      Do a point in time recovery of the whole database until the time the tablespace was dropped.

Let us examine the first option using the example shown below:

In this example, CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for the database.

With Flashback enabled, the db_recovery_file_dest will have a sub-directory ‘autobackup’ as shown below for each day

Directory of E:\Food_database\backup\FOOD\AUTOBACKUP\2012_02_08

2/08/2012  11:30 PM    <DIR>          .

2/08/2012  11:30 PM    <DIR>          ..

2/08/2012  03:40 PM         7,995,392 O1_MF_S_774718807_7M52B8SD_.BKP

2/08/2012  03:55 PM         7,995,392 O1_MF_S_774719720_7M536S4D_.BKP

2/08/2012  11:09 PM         7,995,392 O1_MF_S_774745763_7M5WNNPG_.BKP

2/08/2012  11:23 PM         7,995,392 O1_MF_S_774746627_7M5XHN7T_.BKP

2/08/2012  11:24 PM         7,995,392 O1_MF_S_774746653_7M5XJG00_.BKP

2/08/2012  11:28 PM         7,995,392 O1_MF_S_774746936_7M5XS9QG_.BKP

2/08/2012  11:29 PM         7,995,392 O1_MF_S_774746943_7M5XSJN0_.BKP

2/08/2012  11:30 PM         7,995,392 O1_MF_S_774747042_7M5XWMCQ_.BKP

              8 File(s)     63,963,136 bytes

              2 Dir(s)  24,172,269,568 bytes free

When we drop the tablespace we are changing the structure of the database and since controlfile autobackup has been turned on, we see another backup file has been created in the autobackup location in the flash recovery area on disk.

23:30:36 SQL> drop tablespace cola including contents and datafiles;

Tablespace dropped.

We then shutdown the database, startup in nomount mode and attempt to restore the controlfile from autobackup.

The most recent controlfile autobackup has been restored, but since this has been taken after the tablespace was dropped, the tablespace which has been dropped (COLA) is not referenced in the control file that we just restored. If we try to restore and recover the database, the dropped tablespace will not be restored.

23:47:29 SQL> startup nomount

ORACLE instance started.

Total System Global Area  452984832 bytes

Fixed Size                  1249536 bytes

Variable Size             117444352 bytes

Database Buffers          331350016 bytes

Redo Buffers                2940928 bytes

C:\>rman target backup/backup

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 8 23:48:12 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: food (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 08-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=147 devtype=DISK

recovery area destination: E:\Food_database\backup

database name (or database unique name) used for search: FOOD

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP

channel ORA_DISK_1: control file restore from autobackup complete

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL01.CTL

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL02.CTL

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL03.CTL

Finished restore at 08-FEB-12

RMAN> alter database mount;

RMAN> report schema;

Starting implicit crosscheck backup at 08-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 08-FEB-12

Starting implicit crosscheck copy at 08-FEB-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-FEB-12

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    490      SYSTEM               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF

2    2048     UNDOTBS1             ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF

3    270      SYSAUX               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF

4    3072     USERS                ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF

5    100      EXAMPLE              ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF

7    2048     BEVERAGES            ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF

8    2048     BEVERAGES            ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF

9    165      UNDOTBS1             ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    65       TEMP                 32767       E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\TEMP01.DBF

We will need to restore a backup of the controlfile which contains records for the tablespace COLA. We use the RESTORE CONTROLFILE FROM command to restore a specific controlfile autobackup.

RMAN> restore controlfile from 'E:\Food_database\backup\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774719720_7M536S4D_.BKP';

Starting restore at 08-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=147 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL01.CTL

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL02.CTL

output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL03.CTL

Finished restore at 08-FEB-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> report schema;

Starting implicit crosscheck backup at 08-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 08-FEB-12

Starting implicit crosscheck copy at 08-FEB-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-FEB-12

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774719720_7M536S4D_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774745763_7M5WNNPG_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746627_7M5XHN7T_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746653_7M5XJG00_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746936_7M5XS9QG_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746943_7M5XSJN0_.BKP

File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    490      SYSTEM               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF

2    2048     UNDOTBS1             ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF

3    270      SYSAUX               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF

4    3072     USERS                ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF

5    100      EXAMPLE              ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF

6    0        COLA                 ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\COLA1.DBF

7    2048     BEVERAGES            ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF

8    2048     BEVERAGES            ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF

9    165      UNDOTBS1             ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    65       TEMP                 32767       E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\TEMP01.DBF

The alert log will also show the time when the tablespace was dropped. We can also see that a controlfile autobackup has taken place after the tablespace was dropped.

Wed Feb 08 23:30:39 2012

drop tablespace cola including contents and datafiles

Now that we know the time the tablespace was dropped, we can do a point in time recovery of the DATABASE in order to recover the tablespace which has been dropped.

RMAN> run

2> {

3> set until time "to_date('08-feb-2012 23:30:39','DD-MON-YYYY HH24:Mi:SS')";

4> restore database;

5> recover database;

6> }

executing command: SET until clause

Starting restore at 09-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF

restoring datafile 00002 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF

restoring datafile 00003 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF

restoring datafile 00004 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF

restoring datafile 00005 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF

restoring datafile 00006 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\COLA1.DBF

restoring datafile 00007 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF

restoring datafile 00008 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF

restoring datafile 00009 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF

channel ORA_DISK_1: reading from backup piece E:\FOOD_DATABASE\BACKUP\FOOD\BACKUPSET\2012_02_08\O1_MF_NNNDF_TAG20120208T152908_7M51OPDP_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=E:\FOOD_DATABASE\BACKUP\FOOD\BACKUPSET\2012_02_08\O1_MF_NNNDF_TAG20120208T152908_7M51OPDP_.BKP tag=TAG20120208T152908

channel ORA_DISK_1: restore complete, elapsed time: 00:09:15

Finished restore at 09-FEB-12

Starting recover at 09-FEB-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 652 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO03.LOG

archive log thread 1 sequence 653 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO01.LOG

archive log filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO03.LOG thread=1 sequence=652

archive log filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO01.LOG thread=1 sequence=653

media recovery complete, elapsed time: 00:00:24

Finished recover at 09-FEB-12

RMAN> alter database open resetlogs;

database opened

We can now see that the tablespace which has been dropped has been recovered

00:14:12 SQL> set line 150

set pagesize 150

ttitle dba_tablespace

select tablespace_name,block_size,status,contents,logging,extent_management,segment_space_management,retention from dba_tablespaces;

set pagesize 150

col file_name for a30;

ttitle dba_data_files

select file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB,BLOCKS,STATUS,AUTOEXTENSIBLE,INCREMENT_BY,ONLINE_STATUS from dba_data_files order by file_id;

Thu Feb 09                                                                                                                           page    1

                                                                    dba_tablespace

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS  LOGGING   EXTENT_MAN SEGMEN RETENTION

------------------------------ ---------- --------- --------- --------- ---------- ------ -----------

SYSTEM                               8192 ONLINE    PERMANENT LOGGING   LOCAL      MANUAL NOT APPLY

UNDOTBS1                             8192 ONLINE    UNDO      LOGGING   LOCAL      MANUAL NOGUARANTEE

SYSAUX                               8192 ONLINE    PERMANENT LOGGING   LOCAL      AUTO   NOT APPLY

TEMP                                 8192 ONLINE    TEMPORARY NOLOGGING LOCAL      MANUAL NOT APPLY

USERS                                8192 ONLINE    PERMANENT LOGGING   LOCAL      AUTO   NOT APPLY

EXAMPLE                              8192 ONLINE    PERMANENT NOLOGGING LOCAL      AUTO   NOT APPLY

COLA                                 8192 ONLINE    PERMANENT LOGGING   LOCAL      AUTO   NOT APPLY

BEVERAGES                            8192 ONLINE    PERMANENT LOGGING   LOCAL      AUTO   NOT APPLY

8 rows selected.

Elapsed: 00:00:00.06

Thu Feb 09                                                                                                                           page    1

                                                                    dba_data_files

   FILE_ID FILE_NAME                      TABLESPACE_NAME                        MB     BLOCKS STATUS    AUT INCREMENT_BY ONLINE_

---------- ------------------------------ ------------------------------ ---------- ---------- --------- --- ------------ -------

         1 E:\ORACLE\PRODUCT\10.2.0\ORADA SYSTEM                                490      62720 AVAILABLE YES         1280 SYSTEM

           TA\FOOD\SYSTEM01.DBF

         2 E:\ORACLE\PRODUCT\10.2.0\ORADA UNDOTBS1                             2048     262144 AVAILABLE YES          640 ONLINE

           TA\FOOD\UNDOTBS01.DBF

         3 E:\ORACLE\PRODUCT\10.2.0\ORADA SYSAUX                                270      34560 AVAILABLE YES         1280 ONLINE

           TA\FOOD\SYSAUX01.DBF

         4 E:\ORACLE\PRODUCT\10.2.0\ORADA USERS                                3072     393216 AVAILABLE YES          160 ONLINE

           TA\FOOD\USERS01.DBF

         5 E:\ORACLE\PRODUCT\10.2.0\ORADA EXAMPLE                               100      12800 AVAILABLE YES           80 ONLINE

           TA\FOOD\EXAMPLE01.DBF

         6 E:\ORACLE\PRODUCT\10.2.0\ORADA COLA                                   10       1280 AVAILABLE NO     0 ONLINE

           TA\FOOD\COLA1.DBF

         7 E:\ORACLE\PRODUCT\10.2.0\ORADA BEVERAGES                            2048     262144 AVAILABLE NO     0 ONLINE

           TA\FOOD\BEVERAGES01.DBF

         8 E:\ORACLE\PRODUCT\10.2.0\ORADA BEVERAGES                            2048     262144 AVAILABLE NO     0 ONLINE

           TA\FOOD\BEVERAGES02.DBF

         9 E:\ORACLE\PRODUCT\10.2.0\ORADA UNDOTBS1                              165      21120 AVAILABLE YES          640 ONLINE

           TA\FOOD\UNDOTBS02.DBF

9 rows selected.

Thus u recover the dropped tablespace.

Uninstalling Oracle 10g Manually from Windows 

platform 

List of additional steps to do for cleaning the system completely after uninstall using universal installer.
# Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with ‘Oracle’ and stop them.
# Run regedit and delete the following keys (some may have slightly different names in your registry):

HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OraclService
Note that the services control panel will still show the old services until you reboot.

# Delete the Oracle home directory
C:\Oracle

# Delete the Oracle Program Files directory:
C:\Program Files\Oracle

# Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*

Where * indicates the name of your install. Look for and remove all Oracle directories from that location.

# Remove Oracle refereces from the path. To edit your path go to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.

# Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\