Oracle

ORA-29341: The transportable set is not self-contained

2010-06-04T17:46:00.004-05:00

H:\>exp 'sys/****@db5 as sysdba' parfile=Z:\DATA_NORTH_TBSP_EXP.PAR

Export: Release 10.2.0.3.0 - Production on Fri Jun 4 15:14:40 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS.DBMS_PLUGTS", line 1387
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

SYS@db5> EXECUTE dbms_tts.transport_set_check('DATA_NORTH', TRUE, TRUE);

PL/SQL procedure successfully completed.

SYS@db5> SELECT * FROM transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------
Index EGIS_RW_X.SDE_RIX_113 in tablespace DATA_NORTH points to table EGIS_RW_X.DATA_SOUTH
SOUTH

SYS@db5> ALTER INDEX EGIS_RW_X.SDE_RIX_113 REBUILD TABLESPACE DATA_SOUTH;

Index altered.

SYS@db5> SELECT * FROM transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------
Index EGIS_RW_X.SDE_RIX_113 in tablespace DATA_NORTH points to table EGIS_RW_X.DATA_SOUTH
SOUTH

SYS@db5> EXECUTE dbms_tts.transport_set_check('DATA_NORTH', TRUE, TRUE);

PL/SQL procedure successfully completed.

SYS@db5> SELECT * FROM transport_set_violations;

no rows selected

H:\exp 'sys/****@db5 as sysdba' parfile=Z:\DATA_NORTH_TBSP_EXP.PAR

Export: Release 10.2.0.3.0 - Production on Fri Jun 4 15:20:11 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace DATA_NORTH ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DATA_NORTH
. . exporting table SDE_RAS_81
. . exporting table SDE_BND_81
. . exporting table SDE_AUX_81
. . exporting table SDE_BLK_81
. . exporting table F85
. . exporting table S85
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Deploying Mapviewer in Oracle 11g

2009-11-30T16:12:00.000-05:00

Install Oracle Spatial in Oracle 11g

2009-10-31T08:22:00.005-05:00

Install Oracle Spatial in Oracle 11g

Install Oracle Spatial in Oracle 11g

Create the (just the basic without any bells and whistles) database first.

To install Oracle Spatial, you need to have these below said database components/products already installed in the database:-

1) Java
2) XML database
3) Oracle Multimedia (In Pre-11g versions, its called interMedia)

Let us check if its already there:-

> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');

no rows selected

It shows that these components are not installed.

1) Installing Java:-

Requirements for Java install: Check space availability for SYSTEM UNDO (minimum 100MB) and SYSTEM (minimum 70MB) tablespace, and memory settings for SHARED POOL (minimum 96MB) and JAVA POOL (minimum 50MB).

Run "initjvm.sql" to verify these requirements.

> col COMPONENT for a15
>select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';

COMPONENT         CURRENT_MB MIN_MB     MAX_MB
---------------   ---------- ---------- ----------
shared pool       156        156        156
large pool        4          0          4
java pool         4          4          4
streams pool      0          0          0
Shared IO Pool    0          0          0

> ALTER SYSTEM SET JAVA_POOL_SIZE=50M;

System altered.

> select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
2 MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';

COMPONENT         CURRENT_MB MIN_MB     MAX_MB
---------------   ---------- ---------- ----------
shared pool       156        156        156
large pool        4          0          4
java pool         52         4          52
streams pool      0          0          0
Shared IO Pool    0          0          0

>SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME IN ('SYSTEM','UNDOTBS1');

TABLESPACE_NAME                BYTES_MB   MAX_BYTES_MB
------------------------------ ---------- ------------
SYSTEM                         325        32767.9844
UNDOTBS1                       200        32767.9844

> spool C:\full_jvminst.log;

>set echo on
>connect sys/*** as sysdba
>startup mount
>alter system set "_system_trig_enabled" = false scope=memory;
>alter database open;
>ALTER SYSTEM SET JAVA_POOL_SIZE=50M;

>@C:\oracle\ora11\javavm\install\initjvm.sql
>@C:\oracle\ora11\xdk\admin\initxml.sql
>@C:\oracle\ora11\xdk\admin\xmlja.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catjava.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catexf.sql

> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

COUNT(*) OBJECT_TYPE
---------- ---------------
       317 JAVA DATA
       763 JAVA RESOURCE
     19974 JAVA CLASS

>shutdown immediate
>set echo off
>spool off
>exit

2) Installing XML DB:-

Create a user and tablespace to store the XML DB components:-

> connect sys/*** as sysdba
> Shutdown Immediate;
> Startup;

> create tablespace XDB
datafile 'C:\ORACLE\ORADATA\ORA11\XDB1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;

> create user XDB identified by XDB
default tablespace XDB
quota unlimited on XDB
temporary tablespace TEMP;

> @C:\oracle\ora11\RDBMS\ADMIN\catqm.sql XDB XDB TEMP

> @C:\Babu\4Project\GTO_PROJECT\Oracle_Spatial\Oracle_11g\xdbusagecheck.sql

> SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'XDB';

TABLESPACE_NAME                BYTES_MB   MAX_BYTES_MB
------------------------------ ---------- ------------
XDB                            830        4096

@C:\oracle\ora11\RDBMS\ADMIN\utlrp.sql

> COL COMP_NAME FOR A30
> select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database';

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle XML Database            VALID       11.1.0.6.0

3) Installing Oracle Multimedia:-

Create a tablespace to store the Oracle Spatial components (or SYSAUX default tablespace can be used) :-

> select version, status from dba_registry where comp_id='JAVAVM';

VERSION                        STATUS
------------------------------ -----------
11.1.0.6.0                     VALID

> create tablespace ORA_SPATIAL
datafile 'C:\ORACLE\ORADATA\ORA11\ORA_SPATIAL1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;

> @C:\oracle\ora11\ord\admin\ordinst.sql SYSAUX ORA_SPATIAL

> COL USERNAME FOR A20
> COL ACCOUNT_STATUS FOR A20
> COL DEFAULT_TABLESPACE FOR A20
> SELECT USERNAME,TO_CHAR(CREATED,'DD-MON-YY HH24:MI:SS') CREATED,ACCOUNT_STATUS,DEFAULT_TABLESPACE
FROM DBA_USERS ORDER BY 2;

USERNAME             CREATED            ACCOUNT_STATUS       DEFAULT_TABLESPACE
-------------------- ------------------ -------------------- -------------------
SYS                  03-JAN-08 10:08:44 OPEN                 SYSTEM
SYSTEM               03-JAN-08 10:08:44 OPEN                 SYSTEM
OUTLN                03-JAN-08 10:08:46 OPEN                 SYSTEM
DIP                  03-JAN-08 10:11:02 EXPIRED & LOCKED     USERS
TSMSYS               03-JAN-08 10:11:06 EXPIRED & LOCKED     USERS
ORACLE_OCM           03-JAN-08 10:12:40 EXPIRED & LOCKED     USERS
BABU                 11-AUG-08 09:31:08 OPEN                 BABU
DBSNMP               23-JAN-08 13:30:18 EXPIRED & LOCKED     SYSAUX
EXFSYS               29-OCT-09 13:26:45 EXPIRED & LOCKED     SYSAUX
XS$NULL              29-OCT-09 13:51:16 OPEN                 USERS
ANONYMOUS            29-OCT-09 14:24:51 LOCKED               XDB
XDB                  29-OCT-09 14:40:29 OPEN                 XDB
ORDSYS               29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
ORDPLUGINS           29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
SI_INFORMTN_SCHEMA   29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
MDSYS                29-OCT-09 15:03:24 EXPIRED & LOCKED     ORA_SPATIAL

16 rows selected.

> @C:\oracle\ora11\ord\im\admin\catim.sql

> execute validate_ordim;

> select version, status from dba_registry where comp_id='ORDIM';

VERSION STATUS
------------------------------ -----------
11.1.0.6.0 VALID

1 row selected.

4) Verify Install Oracle Spatial in Oracle 11g

> connect sys/*** as sysdba

> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID    VERSION    STATUS
---------- ---------- -----------
ORDIM      11.1.0.6.0 VALID
XDB        11.1.0.6.0 VALID
JAVAVM     11.1.0.6.0 VALID

3 rows selected.

> @C:\oracle\ora11\md\admin\mdprivs.sql
> @C:\oracle\ora11\md\admin\mdinst.sql

> set serveroutput on
> execute validate_sdo;

> col COMP_ID for a10
> col COMP_NAME for a10
> col CONTROL for a10
> col SCHEMA for a10
> col VERSION for a10
> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';

COMP_ID    CONTROL    SCHEMA     VERSION    STATUS      COMP_NAME
---------- ---------- ---------- ---------- ----------- ----------
SDO        SYS        MDSYS      11.1.0.6.0 VALID       Spatial

> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;

no rows selected

> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB','SDO');

COMP_ID    VERSION    STATUS
---------- ---------- -----------
SDO        11.1.0.6.0 VALID
ORDIM      11.1.0.6.0 VALID
XDB        11.1.0.6.0 VALID
JAVAVM     11.1.0.6.0 VALID

4 rows selected

Oracle Error - "ORA-01762: vopdrv: view query block not in FROM"

2009-07-31T09:10:00.000-05:00

Came across this error "ORA-01762: vopdrv: view query block not in FROM"

Here is the version and platform details:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Here is the structure of the view:

SQL> DESC DBA_AUTOTASK_WINDOW_CLIENTS;
Name Null? Type
----------------------- -------- ----------------
WINDOW_NAME NOT NULL VARCHAR2(30)
WINDOW_NEXT_TIME TIMESTAMP(6) WITH TIME ZONE
WINDOW_ACTIVE VARCHAR2(5)
AUTOTASK_STATUS VARCHAR2(8)
OPTIMIZER_STATS VARCHAR2(8)
SEGMENT_ADVISOR VARCHAR2(8)
SQL_TUNE_ADVISOR VARCHAR2(8)
HEALTH_MONITOR VARCHAR2(8)

This query works:

SQL> SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS

WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
---------------- ---------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW 03-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
TUESDAY_WINDOW 04-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
WEDNESDAY_WINDOW 05-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
THURSDAY_WINDOW 06-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
FRIDAY_WINDOW 31-JUL-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SATURDAY_WINDOW 01-AUG-09 06.00.00.000000 AM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SUNDAY_WINDOW 02-AUG-09 06.00.00.000000 AM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED

7 rows selected.

But this one fails (or sucks! for the USA readers!):

SQL> SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS;
SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS
*
ERROR at line 1:
ORA-01762: vopdrv: view query block not in FROM

Any clue?

SECUREFILE Migration - Oracle 11g Feature

2009-07-01T14:25:00.001-05:00

Step#1) Create a partition table:-

SQL> CREATE TABLE DOC_TAB (ID NUMBER, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#2) Insert data

SQL> INSERT INTO DOC_TAB VALUES (5,'XXX');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (15,'YYY');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (25,'ZZZ');

1 row created.

SQL> commit;

Commit complete.

Step#3) Create a transient table

SQL> CREATE TABLE DOC_TAB_TEMP (ID NUMBER, COLX CLOB)
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS SECUREFILE,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS SECUREFILE,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS SECUREFILE);

Table created.

Step#4) Table Redefinition

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
DECLARE
*
ERROR at line 1:
ORA-12089: cannot online redefine table "BABU"."DOC_TAB" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 4

SQL> CREATE TABLE DOC_TAB (ID NUMBER PRIMARY KEY, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#4) Table Redefinition (again)

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
0

PL/SQL procedure successfully completed.

New features in Oracle 11g Database - Reference Partition

2009-05-28T09:14:00.002-05:00

Reference Partitioning is a new feature in Oracle 11g, it is meant for partitioning the parent and child tables using the foreign key constraint.

SQL> CREATE TABLE PARENT_TAB (
2 COL1 NUMBER(3) PRIMARY KEY,
3 COL2 DATE)
4 PARTITION BY RANGE (COL2) (
5 PARTITION Q1 VALUES LESS THAN (TO_DATE('1-4-2009','DD-MM-YYYY')),
6 PARTITION Q2 VALUES LESS THAN (TO_DATE('1-7-2009','DD-MM-YYYY')));

Table created.

SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3),
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);
PARTITION BY REFERENCE (CHILD_FK)
*
ERROR at line 4:
ORA-14652: reference partitioning foreign key is not supported

SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3) NOT NULL,
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);

Table created.

In Reference Partitioning, specifying NOT NULL for the FOREIGN KEY column in child table is mandatory. Otherwise it will throw the above error.

Oracle NUMBER Data Types

2008-12-29T15:43:00.007-05:00

Introduction: This is my study about number data types present
in Oracle database. I have used Oracle 10g (10.2.0.3) for my study:-

Data types INT, INTEGER and SMALLINT all uses NUMBER in Oracle, let us see how:-

Example # 1:

drop table tab1 purge;

create table tab2 (
I INT,
S SMALLINT,
IG INTEGER);

describe tab1

Name Null? Type
------------------ -------- ----------
I NUMBER(38)
S NUMBER(38)
IG NUMBER(38)

Example # 2: Let's see about other number data types:-

drop table tab2 purge;

create table tab2 (
X number,
NUM1 NUMBER,
FLOAT1 FLOAT,
BFLOAT BINARY_FLOAT,
BDOUBLE BINARY_DOUBLE);

describe tab2

Name Null? Type
----------------------- -------- -------------
X NUMBER
NUM1 NUMBER
FLOAT1 FLOAT(126)
BFLOAT BINARY_FLOAT
BDOUBLE BINARY_DOUBLE

declare
y number;
begin for a in 1..10 loop
y:=1/(a*100);
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for .99999999999999999999
col FLOAT1 for .99999999999999999999
col BFLOAT for .99999999999999999999
col BDOUBLE for .99999999999999999999
set lines 120

select * from tab2;

X NUM1 FLOAT1 BFLOAT BDOUBLE
-- ---------------------- ---------------------- ---------------------- ------------
1 .01000000000000000000 .01000000000000000000 .00999999978000000000 .01000000000000000000 2 .00500000000000000000 .00500000000000000000 .00499999989000000000 .00500000000000000010 3 .00333333333333333333 .00333333333333333333 .00333333341000000000 .00333333333333333350 4 .00250000000000000000 .00250000000000000000 .00249999994000000000 .00250000000000000010 5 .00200000000000000000 .00200000000000000000 .00200000009000000000 .00200000000000000000 6 .00166666666666666667 .00166666666666666667 .00166666671000000000 .00166666666666666680 7 .00142857142857142857 .00142857142857142857 .00142857141000000000 .00142857142857142860 8 .00125000000000000000 .00125000000000000000 .00124999997000000000 .00125000000000000000 9 .00111111111111111111 .00111111111111111111 .00111111114000000000 .00111111111111111110 10 .00100000000000000000 .00100000000000000000 .00100000005000000000 .00100000000000000000
10 rows selected.

In the above, the NUM1 (which is using Number data type) and FLOAT1 (which is using FLOAT data type) preserves accuracy/scale than others.

Example # 3: Now, lets us try this

delete tab2;

declare
y number;
begin for a in 1..10 loop
y:=(a+(sqrt(a))/(a*100));
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for 99.99999999999999999999999999999999999999
col FLOAT1 for 99.99999999999999999999999999999999999999

select NUM1,FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 1.01000000000000000000000000000000000000 1.01000000000000000000000000000000000000
2.00707106781186547524400844362104849039 2.00707106781186547524400844362104849040
3.00577350269189625764509148780501957456 3.00577350269189625764509148780501957460
4.00500000000000000000000000000000000000 4.00500000000000000000000000000000000000
5.00447213595499957939281834733746255247 5.00447213595499957939281834733746255250
6.00408248290463863016366214012450981899 6.00408248290463863016366214012450981900
7.00377964473009227227214516536234180061 7.00377964473009227227214516536234180060
8.00353553390593273762200422181052424520 8.00353553390593273762200422181052424520
9.00333333333333333333333333333333333333 9.00333333333333333333333333333333333330
10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

10 rows selected.

select avg(NUM1) NUM1, avg(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 5.50502099789929266650050620329386725341 5.50502099789929266650050620329386725346

select sum(NUM1) NUM1, sum(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 55.05020997899292666500506203293867253408 55.05020997899292666500506203293867253460

select max(NUM1) NUM1, max(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

Example # 4: Now, lets us compare NUMBER and FLOAT data type and try this

drop table tab3 purge;

create table tab3 (
NUM1 NUMBER,
FLOAT1 FLOAT);

delete tab3;

insert into tab3 values (
1234567891234567891234567890123456789123.45,
1234567891234567891234567890123456789123.45);

col NUM1 for 9999999999999999999999999999999999999999.99
col FLOAT1 for 9999999999999999999999999999999999999999.99

select * from tab3;

NUM1
--------------------------------------------
FLOAT1
--------------------------------------------
1234567891234567891234567890123456789123.00
1234567891234567891234567890123456789100.00

In the above when comparing the NUM1 (NUMBER data type) and FLOAT1 (FLOAT data type), it appears that NUM1 (Number data type) preserves accuracy/scale than the other.

When default is used, NUMBER datatype can preserve values upto 40 bytes and FLOAT upto 38 bytes

Bug in Oracle 10g while using Check Constraint

2008-12-17T15:43:00.003-05:00

Came across this bug in the check constraint in Oracle database 10g.
This has been tested in both 32 and 64 bit Oracle 10g (10.2.0.3) running on Windows 2003 Server.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Reproducing the bug: Create a table called STORE having filed called LOCATION, this field should accept specified values or NULL values, below is the syntax:-

SQL> CREATE TABLE STORE (LOCATION VARCHAR2(15));

Table created.

SQL> ALTER TABLE STORE
2 ADD CONSTRAINT C1 CHECK
3 (LOCATION IN ('PHILLY','HARRISBURG',NULL));

Table altered.

Bug Note: By specifying a NULL, the constraint doesn't not verifies/validates the entered value.

SQL> INSERT INTO STORE VALUES ('PHILLY');
1 row created.

SQL> INSERT INTO STORE VALUES ('philly');
1 row created.

SQL> INSERT INTO STORE VALUES ('HARRISBURG');
1 row created.

SQL> INSERT INTO STORE VALUES ('harrisburg');
1 row created.

SQL> INSERT INTO STORE VALUES (null);
1 row created.

SQL> INSERT INTO STORE VALUES ('PITTSBURGH');
1 row created.

SQL> INSERT INTO STORE VALUES ('pittsburgh');
1 row created.

SQL> commit;
Commit complete.

SQL> col SEARCH_CONDITION for a50
SQL> select
2 STATUS,
3 DEFERRABLE,
4 DEFERRED,
5 VALIDATED,
6 SEARCH_CONDITION
7 FROM ALL_CONSTRAINTS A
8 WHERE table_name='STORE';

STATUS DEFERRABLE DEFERRED VALIDATED SEARCH_CONDITION
-------- -------------- --------- ------------- ----------------------------------------
ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED LOCATION IN ('PHILLY','HARRISBURG',NULL)

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
philly
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

PROBLEM: DMLs like INSERT, UPDATE and DELETE works, but Query (SELECT) statements doesn't work in Oracle 10g (Tested in 10.2.0.3 both in 32 and 64 bit) while querying for records that actually violates the constraint rule. But works fine in Oracle 9i (tested in 9.2.0.8) and 11g (tested in 11.1.0.6)

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='philly';

COUNT(*)
----------
0

Condition that doesn't violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='PHILLY';
COUNT(*)
----------
1

SQL> select count(*) from STORE where upper(LOCATION)=upper('philly');
COUNT(*)
----------
2

Condition that violate the constraint rule, DMLs works fine:-
SQL> update STORE set LOCATION='XYX' where LOCATION='philly';
1 row updated.

SQL> commit;
Commit complete.

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='XYX';
COUNT(*)
----------
0

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
XYX
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

Condition that violate the constraint rule, DMLs works fine:-
SQL> delete from STORE where LOCATION='XYX';

1 row deleted.

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

6 rows selected.

SQL> commit;

Commit complete.

FIX for 10g

ALTER TABLE STORE drop CONSTRAINT C1 ;

Option # 1: Create a check constraint without specifying NULL. By default check constraint will accept NULL values, hence it doesn't have to be mentioned in the constraint condition

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG'));

or

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') or LOCATION IS NULL);

Option # 2: Specify function like UPPER or LOWER in the constraint

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(UPPER(LOCATION) IN ('PHILLY','HARRISBURG'));

Option # 3: If NOT NULL need to be enforced along with the above condition, then use the following:-

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') and LOCATION IS NOT NULL);

Did anyone else had similar problem?

New features in Oracle 11g Database - Virtual Columns

2007-12-21T09:52:00.001-05:00

5. Virtual Columns
Virtual Columns are either created at the time of creating or modifying a table. Virtual Columns are defined by expressions or functions, which will be included as part of table structure and hence its metadata. Virtual Columns are like Views, they don't use additional disk space for its data.

U1> CREATE TABLE student_mark(
2     stud_id NUMBER(5),
3     stud_name VARCHAR2(15),
4     subj1_mark NUMBER(3),
5     subj2_mark NUMBER(3),
6     subj3_mark NUMBER(3),
7     tot_mark AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
Name                    Null?    Type
----------------------- -------- ----------------
STUD_ID                          NUMBER(5)
STUD_NAME                        VARCHAR2(15)
SUBJ1_MARK                       NUMBER(3)
SUBJ2_MARK                       NUMBER(3)
SUBJ3_MARK                       NUMBER(3)
TOT_MARK                         NUMBER

U1> DROP TABLE student_mark;

Table dropped.

U1> CREATE TABLE student_mark(
2     stud_id NUMBER(5),
3     stud_name VARCHAR2(15),
4     subj1_mark NUMBER(3),
5     subj2_mark NUMBER(3),
6     subj3_mark NUMBER(3),
7     tot_mark NUMBER(4) AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
Name                    Null?    Type
----------------------- -------- ----------------
STUD_ID                          NUMBER(5)
STUD_NAME                        VARCHAR2(15)
SUBJ1_MARK                       NUMBER(3)
SUBJ2_MARK                       NUMBER(3)
SUBJ3_MARK                       NUMBER(3)
TOT_MARK                         NUMBER(4)

U1> INSERT
2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3     VALUES(10, 'Rose', 75, 45, 66);

1 row created.

U1> INSERT
2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3     VALUES(20, 'Bose', 45, 79, 88);

1 row created.

U1> SELECT *
2     FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK
---------- --------------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186
        20 Bose                    45         79         88        212

U1> ALTER TABLE student_mark
2     ADD avg_mark NUMBER(3) AS (((subj1_mark + subj2_mark + subj3_mark) / 3));

Table altered.

U1> DESCRIBE student_mark
Name                    Null?    Type
----------------------- -------- ----------------
STUD_ID                          NUMBER(5)
STUD_NAME                        VARCHAR2(15)
SUBJ1_MARK                       NUMBER(3)
SUBJ2_MARK                       NUMBER(3)
SUBJ3_MARK                       NUMBER(3)
TOT_MARK                         NUMBER(4)
AVG_MARK                         NUMBER(3)

U1> INSERT
2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3     VALUES(30, 'Jose', 63, 68, 72);

1 row created.

U1> SELECT *
2 FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK   AVG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

U1> ALTER TABLE student_mark DROP COLUMN avg_mark;

Table altered.

Attaching a user defined function with a virtual column:-

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
3     BEGIN
4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
5         INTO subj_avg
6         FROM student_mark
7         WHERE stud_id = stud_no;
8         RETURN(subj_avg);
9     END;
10 /

Function created.

U1> ALTER TABLE student_mark
2     ADD avrg_mark AS (get_avg(stud_id));

Table altered.

U1> DESCRIBE student_mark
Name                    Null?    Type
----------------------- -------- ----------------
STUD_ID                          NUMBER(5)
STUD_NAME                        VARCHAR2(15)
SUBJ1_MARK                       NUMBER(3)
SUBJ2_MARK                       NUMBER(3)
SUBJ3_MARK                       NUMBER(3)
TOT_MARK                         NUMBER(4)
AVRG_MARK                        NUMBER

U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Lets see what happens when we drop the function:-

U1> DROP FUNCTION get_avg;

Function dropped.

U1> SELECT *
2 FROM student_mark;
SELECT *
*
ERROR at line 1:
ORA-00904: "U1"."GET_AVG": invalid identifier

U1> DESCRIBE student_mark
Name                    Null?    Type
----------------------- -------- ----------------
STUD_ID                          NUMBER(5)
STUD_NAME                        VARCHAR2(15)
SUBJ1_MARK                       NUMBER(3)
SUBJ2_MARK                       NUMBER(3)
SUBJ3_MARK                       NUMBER(3)
TOT_MARK                         NUMBER(4)
AVRG_MARK                        NUMBER

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
3     BEGIN
4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
5         INTO subj_avg
6         FROM student_mark
7         WHERE stud_id = stud_no;
8         RETURN(subj_avg);
9     END;
10 /

Function created.

U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Partition on Virtual Columns

This enables to partition the table based on the expression or function that were used to create a virtual column. Lets take the below example:-

U1> CREATE TABLE phone_cust (
2     cust_id NUMBER(5),
3     name VARCHAR2(15),
4     street VARCHAR2(15),
5     city VARCHAR2(10),
6     zip NUMBER(5),
7     phone NUMBER(10),
8     area_code AS (SUBSTR(phone,1,3)||'-'||SUBSTR(phone,4,3)));

Table created.

U1> DESCRIBE phone_cust
Name                    Null? Type
----------------------- -------- ----------------
CUST_ID                          NUMBER(5)
NAME                             VARCHAR2(15)
STREET                           VARCHAR2(15)
CITY                             VARCHAR2(10)
ZIP                              NUMBER(5)
PHONE                            NUMBER(10)
AREA_CODE                        VARCHAR2(7)

U1> ALTER TABLE phone_cust modify area_code AS (SUBSTR(phone, 1, 3));

Table altered.

U1> DESCRIBE phone_cust
Name                    Null? Type
----------------------- -------- ----------------
CUST_ID                          NUMBER(5)
NAME                             VARCHAR2(15)
STREET                           VARCHAR2(15)
CITY                             VARCHAR2(10)
ZIP                              NUMBER(5)
PHONE                            NUMBER(10)
AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 John            660 Boas Street Harrisburg      17102 7171234567 717
        20 Raju            123 Mkt Street Lemoyne         17011 7174567891 717

U1> DROP TABLE phone_cust;

Table dropped.

U1> CREATE TABLE phone_cust(
2     cust_id NUMBER(5),
3     name VARCHAR2(15),
4     street VARCHAR2(15),
5     city VARCHAR2(10),
6     zip NUMBER(5),
7     phone NUMBER(10),
8     area_code AS (SUBSTR(phone, 1, 3)))
9   PARTITION BY range(area_code)(
10   PARTITION p1 VALUES less than(500) TABLESPACE encr_tbs_1,
11   PARTITION p2 VALUES less than(999) TABLESPACE encr_tbs_2)
12   enable ROW movement;

Table created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(10, 'Chel', '456 Walnut St', 'Philly', '19139', 2154567891);

1 row created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(20, 'John', '660 Boas Street', 'Harrisburg', '17102', 7171234567);

1 row created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(30, 'Raju', '123 Mkt Street', 'Lemoyne', '17011', 7174567891);

1 row created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(40, 'Appu', '3 Bridge Trc', 'Woodbridge', '07085', 7324567891);

1 row created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(50, 'Babu', '5 1st Ave', 'Manhattan', '10037', 2124567891);

1 row created.

U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(60, 'Rangasamy', '12 Circle Dr', 'Redlands', '92415', 9094567891);

1 row created.

U1> commit;

Commit complete.

U1> DESCRIBE phone_cust
Name                    Null? Type
----------------------- -------- ----------------
CUST_ID                          NUMBER(5)
NAME                             VARCHAR2(15)
STREET                           VARCHAR2(15)
CITY                             VARCHAR2(10)
ZIP                              NUMBER(5)
PHONE                            NUMBER(10)
AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> SELECT * FROM phone_cust PARTITION(p1);

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> UPDATE phone_cust SET phone = 2121234567 WHERE cust_id = 20;

1 row updated.

U1> commit;

Commit complete.

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212
        30 Raju            123 Mkt Street Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

6 rows selected.

U1> SELECT * FROM phone_cust PARTITION(p1);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        30 Raju            123 Mkt Street Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

New features in Oracle 11g Database

2007-12-18T14:47:00.001-05:00

What's new in an Oracle 11g Database?

1. Lets check some INIT parameters first:-

MEMORY_TARGET: Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, increasing or decreasing SGA and PGA as needed.

MEMORY_MAX_TARGET: Maximum value that MEMORY_TARGET initialization parameter can use.

DIAGNOSTIC_DEST: Diagnostics for each database instance are located in this dedicated directory

2. Password is now Case Sensitive.

SQL> create user u1 identified by U1 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u1;

Grant succeeded.

SQL> create user u2 identified by u2 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u2;

Grant succeeded.

SQL> connect u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect u1/U1
Connected.

SQL> connect u2/U2
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect u2/u2
Connected.

3. READ-ONLY Table

SQL> connect u1/U1
Connected.

SQL> create table t1 (a number) ;

Table created.

SQL> begin
   2 for x in 1..20 loop
   3 insert into t1 values (x);
   4 end loop;
   5 commit;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
NO

1 row selected.

SQL> alter table t1 READ ONLY;

Table altered.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
YES

1 row selected.

SQL> insert into t1 values (99);
insert into t1 values (99)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "U1"."T1"

SQL> alter table t1 READ WRITE;

Table altered.

SQL> insert into t1 values (99);

1 row created.

SQL> commit;

Commit complete.

4. Tablespace Encryption

As we all know, in an Oracle database all the data are finally stored in the datafiles, what if the datafiles containing sensitive data were stolen? there are always chances that these datafiles can either be disseminated or attached to a different database and the sensitive data can be exposed or revealed.

In order to overcome this, Oracle introduced Transparent Data Encryption (TDE), in Oracle 10g version, a column of a table can be encrypted based on a (master) key, and that key is not stored within the same database, but externally in an Oracle wallet.

Now in Oracle 11g, you can encrypt an entire tablespace, i.e., a whole table or all the tables in an encrypted tablespace using tablespace encryption.

Tablespace encryption is supported on all data stored in an encrypted tablespace including internal large objects (LOBs) such as BLOBs and CLOBs. But data stored externally, like BFILE data is not encrypted.

If a table is created with BFILE column in an encrypted tablespace, then this BFILE column will not be encrypted, but the rest of the table will be encrypted.

Configure wallet location by setting the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora:-

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\ora11\admin\ORA11\wallet)))

Tablespace Level
ENCRYPTION algorithms supported in Oracle11g are 3DES168, AES128, AES192 and AES256

SYS> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SYS> CREATE TABLESPACE ENCR_TBS_1
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_01.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS> CREATE TABLESPACE ENCR_TBS_2
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_02.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION USING 'AES256'
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> select * from V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT ENC
---------- ------- ---
         5 AES128 YES
         6 AES256 YES

SYS> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME     ENC
----------------    ---
SYSTEM              NO
SYSAUX              NO
UNDOTBS1            NO
TEMP                NO
USERS               NO
TEST_TS1            NO
ENCR_TBS_1          YES
ENCR_TBS_2          YES

8 rows selected.

SQL> desc DBA_ENCRYPTED_COLUMNS
Name                Null?    Type
------------------- -------- -------------
OWNER NOT           NULL     VARCHAR2(30)
TABLE_NAME          NOT NULL VARCHAR2(30)
COLUMN_NAME         NOT NULL VARCHAR2(30)
ENCRYPTION_ALG               VARCHAR2(29)
SALT                         VARCHAR2(3)

SQL> col owner for a5
SQL> col table_name for a10
SQL> col column_name for a14
SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME    ENCRYPTION_ALG                SAL
----- ---------- -------------- ----------------------------- ---
U1    CUST       CRED_CARD_NUM AES 192 bits key              NO
U1    CUST_2     CRED_CARD_NUM 3 Key Triple DES 168 bits key NO

SYS> CREATE TABLE CUST_1(
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16))
   5     TABLESPACE ENCR_TBS_1;

Table created.

Above table is created in an encrypted tablespace, hence all data in this table is stored encrypted on the disk.

SYS> insert into CUST_1 values (30,'Kate',1234567812345678);

1 row created.

SYS> insert into CUST_1 values (40,'Deep',5678123456781234);

1 row created.

SYS> commit;

Commit complete.

SYS> col CRED_CARD_NUM for 9999999999999999
SYS> select * from CUST_1;

CUST CUST_NAME CRED_CARD_NUM
---- ---------- -----------------
30   Kate       1234567812345678
40   Deep       5678123456781234

SYS> alter system set encryption wallet close;

System altered.

C:\>cd C:\oracle\ora11\admin\ORA11\wallet

C:\oracle\ora11\admin\ORA11\wallet>C:\oracle\ora11\BIN\orapki wallet export -wallet . -dn "CN=oracle" -request oracle.req -pwd "b1b1"

Table Level
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1"
*
ERROR at line 1:
ORA-28354: wallet already open

A wallet is automatically opened when you set or reset the master encryption key. Once you set the encryption, you will be see a file created in the above DIRECTORY location.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";

System altered.

Creating a table with an encrypted column using the default AES192 algorithm

SQL> conn u1/u1
Connected.
SQL> create table CUST (
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16) ENCRYPT
   5     );

Table created.

U1> desc cust
Name                Null?    Type
------------------- -------- --------------------
CUST_ID                      VARCHAR2(4)
CUST_NAME                    VARCHAR2(10)
CRED_CARD_NUM                NUMBER(16) ENCRYPT

U1> insert into CUST values (10,'Tom',1234567812345678);

1 row created.

U1> insert into CUST values (20,'Sam',5678123456781234);

1 row created.

U1> commit;

Commit complete.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);
CREATE INDEX cust_card_idx ON CUST (cred_card_num)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

U1> ALTER TABLE CUST MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);

Index created.

U1> col CRED_CARD_NUM for 9999999999999999
U1> select * from CUST;

CUST CUST_NAME CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Creating a table with an encrypted column using NO SALT parameter

U1> create table CUST_2 (
2     cust_id VARCHAR2(4),
3     cust_name VARCHAR2(10),
4     cred_card_num NUMBER(16) ENCRYPT NO SALT
5     );

Table created.

U1> insert into CUST_2 select * from CUST;

2 rows created.

U1> commit;

Commit complete.

U1> select * from CUST_2;

CUST CUST_NAME CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Adding Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT SALT);

Table altered.

Removing Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> ALTER TABLE CUST_2 REKEY;

Table altered.

U1> ALTER TABLE CUST_2 REKEY USING '3DES168';

Table altered.

Creating a table with an encrypted column using 3DES168 algorithm

create table CUST_3 (
    cust_id VARCHAR2(4),
    cust_name VARCHAR2(10),
    cred_card_num NUMBER(16) ENCRYPT USING '3DES168'
    );

Adding and modifying encrypted columns

ALTER TABLE CUST_3 ADD (cust_addr VARCHAR2(15) ENCRYPT);

ALTER TABLE CUST_3 modify (cust_name ENCRYPT);

Disable Column Encryption

ALTER TABLE CUST_3 MODIFY (cust_name DECRYPT);

Manually Creating an Oracle 11g Database

2007-12-18T14:42:00.001-05:00

Creating an Oracle 11g Database in Windows using command line.

Creating a 11g database is just the same as 10g.

INIT.ORA.Below are the contents of my init.ora:-

db_name='ORA11'
memory_target=1G
processes = 150
audit_file_dest='C:\oracle\ora11\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=C:\oracle\ora11\admin\ORA11\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=C:\oracle\ora11\admin\ORA11\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("C:\oracle\oradata\ORA11\ORA11_CONTROL1.ora", "C:\oracle\oradata\ORA11\ORA11_CONTROL2.ora")
compatible ='11.1.0'

Set Environment Variables:-

set ORACLE_SID=ORA11
set ORACLE_HOME=C:\oracle\ora11

Create Oracle service

C:\oracle\ora11\bin\oradim.exe -new -sid ORA11 -startmode m -INTPWD oracle -PFILE "C:\oracle\ora11\database\initORA11.ora"

Instance created.
Error while deleting value, OS Error = 2

Create Database

C:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 10 15:25:47 2007

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

SQL> connect sys/oracle as sysdba
Connected to an idle instance.

SQL> startup nomount pfile="C:\oracle\ora11\database\initORA11.ora"

ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes

CREATE DATABASE ORA11
USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
DATAFILE 'C:\oracle\oradata\ORA11\SYSTEM01.DBF' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE 'C:\oracle\oradata\ORA11\SYSAUX01.DAT' SIZE 120M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
DEFAULT TABLESPACE USERS DATAFILE 'C:\oracle\oradata\ORA11\USERS01.DBF' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\ORA11\TEMP01.DBF' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\oradata\ORA11\UNDOTBS01.DBF'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 'C:\oracle\oradata\ORA11\REDO01.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO02.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO03.LOG' SIZE 100MREUSE
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1;

@C:\oracle\ora11\rdbms\admin\catalog.sql
@C:\oracle\ora11\rdbms\admin\catproc.sql

SQL> connect system/ORACLE as sysdba
Connected.

@C:\oracle\ora11\sqlplus\admin\pupbld.sql

SQL> select program from v$session;

PROGRAM
-----------------------
sqlplus.exe
ORACLE.EXE (q001)
ORACLE.EXE (CJQ0)
ORACLE.EXE (q000)
ORACLE.EXE (W000)
ORACLE.EXE (QMNC)
ORACLE.EXE (FBDA)
ORACLE.EXE (SMCO)
ORACLE.EXE (MMNL)
ORACLE.EXE (MMON)
ORACLE.EXE (RECO)
ORACLE.EXE (SMON)
ORACLE.EXE (CKPT)
ORACLE.EXE (LGWR)
ORACLE.EXE (DBW0)
ORACLE.EXE (MMAN)
ORACLE.EXE (DIA0)
ORACLE.EXE (PSP0)
ORACLE.EXE (DBRM)
ORACLE.EXE (DIAG)
ORACLE.EXE (VKTM)
ORACLE.EXE (PMON)

22 rows selected.

Drop Database

SQL> connect sys/oracle as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> drop DATABASE ;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> STARTUP mount RESTRICT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.

SQL> drop DATABASE ;

Operation 203 succeeded.

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

Following messages were noted in Alert.ora

Tue Dec 11 08:36:23 2007
Warning: chk_tab_prop - table doesn't exist :SYS.KUPC$DATAPUMP_QUETAB
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_S
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_I
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_H
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_T
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_G
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_P
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_D
Tue Dec 11 08:39:11 2007
SERVER COMPONENT id=CATPROC: timestamp=2007-12-11 08:39:11

Failure to Extend Rollback Segment

2007-09-26T14:34:00.000-05:00

Environment: Oracle 9.2.0.7 EE, 32bit, Windows 2003 Enterprise
Server.

Problem: Transaction (bulk load) hangs and getting below error in the alert.log

Thread 1 advanced to log sequence 1234
Current log# 1 seq# 1234 mem# 0: D:\ORACLE\ORADATA\LOG1.ORA
Wed Sep 25 21:06:06 2007
Failure to extend rollback segment 19 because of 1000 conditionFULL status of
rollback segment 19 set.
Wed Sep 25 21:06:20 2007
SMON: FULL status of rollback segment 19 cleared.

I have seen various reasons and scenarios (like insufficient disk space for RBS/UNDO tablespace/datafiles to grow, or hitting maxsize limitation for datafiles, max extents reached, ORA-1555 the famous "snapshot too old" error, etc) causing failure of a rollback (undo) segment. But this is the first time I came across a RBS failure which is slightly different the usual.

The database is in AUTOMATIC undo management mode

NAME                    TYPE        VALUE
----------------------- ----------- ---------
undo_management         string      AUTO
undo_retention          integer     10800
undo_suppress_errors    boolean     FALSE
undo_tablespace         string      UNDOTBS1

Solution: After trying various options, attempts and looking the alert log message under the microscope, I came to know that the reason for the error is ORA-01000 (as the error in the alert.log says "because of 1000", so DBAs are expected to assume it is ORA-01000 error, go figure!). Here is what Oracle documentation say about this error:

ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of
cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

After changing the OPEN_CURSORS parameter (from 300) to 2000 and restarting the database, the bulk load went fine.

Did anyone had this problem? welcome to share your thoughts..

Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines

2007-03-01T16:32:00.000-05:00

Environment: GIS database environment, ESRI's ArcSDE 9.0 running on Oracle 9.2.0.7 database on a 32 bit Windows 2003 Server operating system.

Table of Contents

Introduction
Rollback and De-Installation of Patches
Patch Install Verification
Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server
1. Software and Utility Requirement
2. DST Patch Download from Oracle Metalink
3. Shutdown and Reboot
4. Patch Install
4.1. Patch # 5548107 [Interim Patch for Bug: 5548107]
4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]
4.2.2.1. Patch Database(s)
4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]
5. Final Testing
Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines

Introduction: This document provides steps involved in installing Oracle DST patches in Oracle Database and Client machines. Identify and list out the Oracle database server(s) and client machines.

Server	Oracle Product Installed
ORADBTEST	Database Server
ORADBPROD	Database Server
WEBTEST	Client
WEBPROD	Client
APPTEST	Client
APPPROD	Client

Rollback and De-Installation of Patches: It’s safe to backup entire database server (including ORACLE_HOME and Database files) before patches are installed for the purpose of restoring the database.

However, there is a second option available, rollback scripts are created for each patch install when applied using Oracle OPatch Utility, hence a patch (or a patch-set) can be rolled back using them. During the patch install, rollback scripts are automatically created at "%ORACLE_HOME%\.patch_storage\<patch_id>\" folder in the name "rollback_<patch_id>.cmd". Patch De-Installation instructions are available at the bottom of each respective README.txt (or README.html if available) files.

There was no requirement for patch rollback when installed, hence steps for rolling back a patch is not included in this document.

Patch Install Verification:Verify the log file "Apply_<patch_id>_<timestamp>.log" created at "%ORACLE_HOME%\.patch_storage\<patch_id>\ for any errors after every OPatch install. Also check %ORACLE_HOME%\cpu\CPUJan2007Apply_<ORACLE_SID>_<timestamp>.log after running "catcpu.sql" script for patch 5654905.

Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server

1. Software and Utility Requirement: Oracle DST Patches are applied using an Oracle Patch utility called OPatch. It requires Perl 5.005_03 or higher and JDK versions of 1.3.1 or higher.

Note: Java, OPatch and Perl that were already present in Database Server may not include all the required files for DST patches. Hence download and reinstall all the three once again to successfully install the DST patches.

1.1. JAVA: Download and install JAVA from below Sun website and set PATH accordingly:-

J2SDK - http://java.sun.com/j2se/1.4.2/download.html

C:\j2sdk1.4.2_13\bin>java -version
java version "1.4.2_13"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_13-b06)
Java HotSpot(TM) Client VM (build 1.4.2_13-b06, mixed mode

In Database Server, it is installed in "C:\j2sdk1.4.2_13", include its "bin" folder in "Path" environment variable [refer section 1.4].

1.2. Perl - Download Perl using Oracle Interim Patch # 2417994 and install it as per instructions provided in the README.txt file.

Log into "metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2417994" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.1 release, downloaded file name would be "p2417872_9201_WINNT.zip". [Or use below link to download]

http://updates.oracle.com/ARULink/Download/process_form/p2417872_9201_WINNT.zip?file_id=536569&aru=2430176

C:\perl\perl\5.00503\bin\MSWin32-x86>perl -version
This is perl, version 5.005_03 built for MSWin32-x86
Copyright 1987-1999, Larry Wall
Perl may be copied only under the terms of either the Artistic License or
the GNU General Public License, which may be found in the Perl 5.0 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'. If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.

In Database Server, Perl is installed in "C:\perl", include its "..\bin\MSWin32-x86" folder in "Path" environment variable [refer section 1.4].

1.3. OPatch - In Database Server, OPatch is installed in "D:\oracle\ora92\OPatch" folder. [If already present, move/backup the files present in the OPatch folder and reinstall new OPatch in the same folder]. Include its "D:\oracle\ora92\OPatch" folder in "Path" environment variable [refer section 1.4].

set ORACLE_HOME=d:\oracle\ora92

D:\>opatch version

Oracle Interim Patch Installer version 1.0.0.0.52
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..
We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

OPatch Version: 1.0.0.0.52

OPatch returns with error code = 0

Download Oracle OPatch using Bug # 2417872, and install it as per instructions provided in the README.txt file.

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2617419" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 10.1.0.2 release, downloaded file name would be "p2617419_10102_GENERIC.zip". [Or use below link to download].

http://updates.oracle.com/ARULink/Download/process_form/p2617419_10102_GENERIC.zip?aru=5939446&file_id=9596770&patch_file=p2617419_10102_GENERIC.zip

Reference: Oracle9i Data Server Interim Patch Installation (OPatch) Note: 189489.1

1.4. Path environment variable: Once Path is set for all the above 3, it would look as follows:-

echo %Path%
D:\oracle\ora92\jre\1.4.2\bin\client;D:\oracle\ora92\jre\1.4.2\bin;
D:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;
C:\Program Files\Oracle\jre\1.1.8\bin;C:\WINDOWS\system32;
C:\WINDOWS;C:\WINDOWS\System32\Wbem;
C:\Program Files\Microsoft SQL Server\80\Tools\BINN;
C:\esri_sde\ArcSDE\ora9iexe\bin;c:\program files\netiq\Common\Bin;
C:\j2sdk1.4.2_13\bin;D:\oracle\ora92\OPatch;
C:\perl\perl\5.00503\bin\MSWin32-x86;

2. DST Patch Download from Oracle Metalink:-
2.1. Interim Patch for Bug: 5548107

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5548107" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5548107_92070_GENERIC.zip". [Or use below link to download].

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5548107&release=8092070&plat_lang=2000P&patch_num_id=759969

2.2. Oracle 9.2.0.7 Patch 15 on Windows 32 bit # 5654905

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5654905" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". The downloaded file name would be "p5654905_92070_WINNT.zip". [Or use below link to download].

http://updates.oracle.com/ARULink/Download/process_form/p5654905_92070_WINNT.zip?file_id=18784604&aru=8927462

2.3. Oracle JVM Patch # 5047902

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" provide "5047902" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5047902_92070_GENERIC.zip". [Or use below link to download].

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5047902&release=8092070&plat_lang=2000P&patch_num_id=682328

Unzip all the above three zip files (patches) in the appropriate folders and following is the folder structure created in Database Server:

Example:
Folder called "D:\5548107" for patch 5548107
Folder called "D:\5654905" for patch 5654905
Folder called "D:\5047902" for patch 5047902
Read the README.txt present in the respective unzipped folder for further information about the patches.

3. Shutdown and Reboot

3.1. Manual Services - Make all the ArcSDE and Oracle Services "Startup Type" as "Manual" if they are "Automatic" [Note down the "Startup Type" of these services prior to the change, will need to reverse it back to the original startup type ("Automatic") after the patch install].

3.2. Shutdown ArcSDE Service(s):

C:\>set ORACLE_SID=TESTORCL
C:\>set SDEHOME=C:\esri_sde\ArcSDE\ora9iexe

C:\>sdemon -o shutdown -i esri_sde -p <password> -N

ArcSDE Instance esri_sde on TESTORCL is Shutdown!

3.3. Shutdown Oracle Databases: [From command line sqlplus.exe, not the windows sqlplusw.exe]

SQL> conn sys/@TESTORCL as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3.4. Stop all Oracle* services:

C:\>net stop OracleServiceTESTORCL
The OracleServiceTESTORCL service is stopping.
The OracleServiceTESTORCL service was stopped successfully.

C:\>net stop OracleOra92TNSListener
The OracleOra92TNSListener service is stopping
.The OracleOra92TNSListener service was stopped successfully.

3.5. Rename bin and reboot server

- Rename the "bin" folder in "D:\oracle\ora92" to some other name (Example: "bin_save") and reboot the server.
- Once the server comes back up, rename the "bin_save" folder back to "bin" again

4. Patch Install
4.1. Patch # 5548107[Interim Patch for Bug: 5548107]. Patch the Oracle Home (Run only once for an Oracle Home):

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
D:\>set ORACLE_HOME=D:\ORACLE\ORA92

D:\>cd 5548107

D:\5548107>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-02-35.log"

Result:

Installed Patch List:
=====================
1) Patch 5548107 applied on Wed Feb 28 15:28:38 EST 2007
[ Base Bug(s): 5548107 ]

OPatch succeeded.
OPatch returns with error code = 0

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

D:\5548107>opatch apply

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\5548107\Apply_5548107_02-28-2007_16-03-22.log"

Subset patches: 5548107,

The fixes for Patch 5548107, are included in the patch currently
being installed (5548107). OPatch will roll back the subset patch(es) and install the new patch (5548107).

Backing up comps.xml ...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = d:\oracle\ora92)
Is this system ready for updating?
Please respond Y|N >
Y
Rolling back patch 5548107...

Creating log file quot;D:\oracle\ora92\.patch_storage\5548107\RollBack_5548107_02-28
-2007_16-03-39.log"

Rolling back with all-node mode.
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Oracle instances have been shut down, proceeding with auto-rollback.

Removing patch 5548107...

Restoring copied files...

Warning: Cannot restore the file
"d:\oracle\ora92\rdbms\admin\utltzuv2.sql"
as OPatch can't find backup file, so it is backed up as
quot;d:\oracle\ora92\rdbms\admin\utltzuv2.sql_removed_5548107"

Updating inventory...

Back to applying patch 5548107...

Backing up 2nd copy of comps.xml ...

Applying patch 5548107...

Patching jar files...

Patching copy files...

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...
Backing up comps.xml ...

OPatch succeeded.
OPatch returns with error code = 0
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Optional: Patch 5548107 will create a file called "utltzuv2.sql" in the "ORACLE_HOME\rdbms\admin" folder, this script is to check if TIMESTAMP WITH TIME ZONE data is stored in the database. Refer to Metalink document "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note: 396670.1" for further details.

4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]

4.2.1. Patch the Oracle Home (Run once for each Oracle Home):

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
D:\>cd D:\5654905

set ORACLE_HOME=D:\ORACLE\ORA92

D:\5654905>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-19-
55.log"

Result:

Installed Patch List:
=====================
1) Patch 5548107 applied on Wed Feb 28 16:04:19 EST 2007
[ Base Bug(s): 5548107 ]

OPatch succeeded.
OPatch returns with error code = 0

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

D:\5654905>opatch apply

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\5654905\Apply_5654905_02-28-20
07_16-21-12.log"

Backing up comps.xml ...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = d:\oracle\ora92)
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\5654905\custom\scripts\pre.bat)...
Applying patch 5654905...

Patching jar files...

Patching copy files...

Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\30"
Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\90"
Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\101"
Creating file to hold list of directories that were mkdir'ed: "D:\oracle\ora92\.patch_storage\5654905\opatch_dirs_created.lst"

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...
Backing up comps.xml ...

*****************************************************************

** ATTENTION **

** **

** Please note that the Security Patch Installation (Patch Deinstallation) is **

** not complete until all the Post Installation (Post Deinstallation) **

** instructions noted in the Readme accompanying this patch, have been **

** successfully completed. **

** **

*****************************************************************

*****************************************************************

Executing the Apply post-patch script (D:\5654905\custom\scripts\post.bat)...

OPatch succeeded.
OPatch returns with error code = 0
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
D:\5654905>cscript //nologo remove_demo.js
ORACLE_HOME is set to D:\ORACLE\ORA92
This script will remove the Vulnerable OHS Demos %ORACLE_HOME%/Apache/Apache/fcgi-bin/echo*
Please note that you will NOT be able to restore these demos after removing,
if you may want to restore these demos, please manually backup all the files under
%ORACLE_HOME%/Apache/Apache/fcgi-bin/ directory, and then rerun remove_demo.js
Continue to remove the Vulnerable OHS Demos? Please respond Y|[N] =>
Y
Removing the Vulnerable OHS Demos...
Patch Installation Script Completed
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

4.2.2. Patch the Databases (run for each database if multiple instances running)

4.2.2.1. Database: TESTORCL

C:\>net start OracleOra92TNSListener
The OracleOra92TNSListener service is starting.
The OracleOra92TNSListener service was started successfully.

C:\>net start OracleServiceTESTORCL
The OracleServiceTESTORCL service is starting.
The OracleServiceTESTORCL service was started successfully.

C:\>D:

D:\>cd D:\oracle\ora92\cpu\cpujan2007

D:\oracle\ora92\cpu\cpujan2007>sqlplus "sys/<password>@TESTORCL as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 16:35:05 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 630268612 bytes
Fixed Size 455364 bytes
Variable Size 285212672 bytes
Database Buffers 343932928 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

@catcpu.sql
...
...
No. of Invalid Objects is :50
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_SDE_28Feb2007_16_36_40.log

not spooling currently

SQL> select status,count(*) from dba_objects group by status order by 1;

STATUS COUNT(*)
------- ----------
INVALID 60
VALID 33031

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

D:\oracle\ora92\cpu\cpujan2007>cd ../..

D:\oracle\ora92>cd rdbms/admin

D:\oracle\ora92\rdbms\admin> sqlplus "sys/<password>@TESTORCL as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 16:43:07 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

SQL> @utlrp.sql

PL/SQL procedure successfully completed.

Table created.
...
...
PL/SQL procedure successfully completed.

SQL> select status,count(*) from dba_objects group by status order by 1;

STATUS COUNT(*)
------- ----------
INVALID 2
VALID 33079

SQL> column owner format a15
SQL> column object_name format a25
SQL> column object_type format a25
SQL> select owner,object_name, object_type from dba_objects where status <>
'VALID' order by 1,2,3;

OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------- -------------------------
TEST_ATT P_UPD_SALE_PRICE PROCEDURE
TEST_BTT P_UPD_SALE_PRICE PROCEDURE

Shutdown and Restart TESTORCL database, and start ArcSDE Service esri_sde

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 630268612 bytes
Fixed Size 455364 bytes
Variable Size 285212672 bytes
Database Buffers 343932928 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

D:\oracle\ora92\rdbms\admin>set ORACLE_SID=TESTORCL

D:\oracle\ora92\rdbms\admin>set SDEHOME=C:\esri_sde\ArcSDE\ora9iexe

D:\oracle\ora92\rdbms\admin>sdemon -o start -i esri_sde -p <password>
ArcSDE Instance esri_sde started Wed Feb 28 16:53:16 2007

Testing: Using ArcCatalog, connect to Vector (esri_sde) and see if the connection succeeds and able to view the layers. Tested and it worked.

4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]

4.3.1. Shutdown all ArcSDE Services and Oracle Databases [as described in section 3.2 through 3.4].

4.3.2. Move file "orajox9java_util.dll" from "D:\oracle\ora92\javavm\admin" to some other (backup) folder and ensure that the file is backed up and not present in "ORACLE_HOME\javavm\admin" folder.

4.3.3. Patch the Oracle Home (Run only once for an Oracle Home):

D:\5654905>cd ../5047902

D:\5047902>opatch apply

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\5047902\Apply_5047902_02-28-20
07_17-53-12.log"

Backing up comps.xml ...

OPatch detected non-cluster Oracle Home from the inventory and will patch the lo
cal system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = d:\oracle\ora92)
Is this system ready for updating?
Please respond Y|N >
Y
Applying patch 5047902...

Patching jar files...

Patching copy files...

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...
Backing up comps.xml ...

OPatch succeeded.
OPatch returns with error code = 0

D:\5047902>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : D:\ORACLE\ORA92
Oracle Home Inventory : D:\ORACLE\ORA92\inventory
Central Inventory : C:\Program Files\oracle\inventory
from : N/A
OUI location : D:\ORACLE\ORA92\oui
OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll
Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe"
Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log

Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_17-55-
09.log"

Result:

Installed Patch List:
=====================
1) Patch 5047902 applied on Wed Feb 28 17:54:56 EST 2007
[ Base Bug(s): 5047902 ]
2) Patch 5654905 applied on Wed Feb 28 16:24:22 EST 2007
[ Base Bug(s): 4605400 4315431 3801082 3721136 4741071 4572340 5211863 444
8018 4192148 4049345 4047167 4544805 4908162 5411847 2838218 4928723 5250979 404
7969 5059488 3975758 4589659 4872999 3344331 4523125 3350337 4162272 4925103 412
4059 5242644 5064363 5500873 4708037 5250980 5064365 4379241 4632780 4547641 475
1923 4751528 5225794 4499035 5654905 4547566 5188596 4748597 5689875 3125250 462
5102 2701372 5021708 5049060 3904125 4523371 4083461 5049062 4656033 5212539 324
3584 3667025 4721492 3807408 3396162 4947132 4950942 4964703 4754842 4684373 456
7733 2595474 4519477 4567971 3967276 4715022 2816302 4312390 4107544 4329304 501
5557 4427475 4607458 4007599 4134994 4536817 4248629 4573980 4923667 4434689 457
0598 4917666 4287891 4409977 3799129 4294948 4627859 4718976 4969880 4869694 400
8013 4689959 5025840 3101559 4900129 3098032 5249142 4285404 4308824 4369235 463
2494 4618715 3429960 4592382 4335559 3984145 3684772 3857781 4687386 4288876 461
0323 5116414 3281270 4036921 4253914 4121749 5290940 5490841 4257473 4446528 454
8339 3328894 3937981 4449595 4398475 4727589 3879892 4402255 3206617 4369410 518
8321 4486132 4207529 3351594 5490931 4727517 4523577 5694714 4593537 3816595 493
9157 5382965 4567854 4579590 4659781 4939797 4529007 4275234 2709343 5045474 432
2477 4627335 3383661 4695511 2467239 4483951 4188472 4658188 3889156 4599763 469
7938 ]
3) Patch 5548107 applied on Wed Feb 28 16:04:19 EST 2007
[ Base Bug(s): 5548107 ]

OPatch succeeded.
OPatch returns with error code = 0

4.3.4. Startup Oracle Databases and respective ArcSDE Services [use section 4.2.2.1. for startup command usage] and below Create JAVA in each databases.

D:\oracle\ora92\rdbms\admin>sqlplus "sys/<password>@TESTORCL as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 18:01:33 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> CREATE OR REPLACE JAVA SYSTEM
2 /

Java created.

4.3.5. Make ArcSDE and Oracle Services "Startup Type" to "Automatic" if they were originally "Automatic" (refer section 3.1).

5. Final Testing: Shutdown ArcSDE Services and Oracle Databases, reboot the machine, once the machine comes up, startup Oracle Database and ArcSDE Services and connect it from ArcCatalog and other front end applications.

Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines

1. Backup/move (create a folder, example "old_28Feb2007") the existing "timezlrg.dat" and "timezone.dat" files present in "ORACLE_HOME\oracore\zoneinfo" to the new folder ("old_28Feb2007").

2. Locate the new "timezlrg.dat", "timezone.dat" and "readme.txt" in the "...5654905\files\oracore\zoneinfo" folder where Patch 5654905 is unzipped (or copy it from the DB server where the patch has already been installed) and copy it to "ORACLE_HOME\oracore\zoneinfo".

Author:	Babu Rangasamy
Title:	GIS and Database Consultant
Created Date:	March 1, 2007
Contact:	Babu.Rangasamy[at]gmail.com

Impact of DST 2007 (Day Light Saving) on Oracle Databases

2007-02-08T16:21:00.000-05:00

Daylight Saving Time (DST) - USA - 2007

Introduction: As per the Energy Policy Act of 2005, DST will start from 2 a.m. on the 2nd Sunday in March and end at 2 a.m. on First Sunday in November [Earlier it used to start at 2 a.m. 1st Sunday in April and end at 2 a.m. Last Sunday in October].

Spring Forward: On 2nd Sunday in March, clock will leap ahead from 1:59 a.m. to 3:00 a.m.

Fall Back: On 1st Sunday in November, clock will move back from 1:59 a.m. to 1:00 a.m.

Countries Affected: Canada and Bermuda are making similar changes to be consistent with USA time changes. Computers and applications running in other countries (who don't follow DST) having associated with computers and applications running in the DST implemented counties could also be affected.

Operating System: Once the required patch is applied, the system clock will automatically adjust its system time at the beginning and end of DST. Any dependent applications running on the OS will obtain the system time from the system clock.

Microsoft Daylight Saving Time: Help and Support Center

How it affects the Oracle Database?: Time zone and DST are used in Oracle database at the below two areas. Hence 2 patches are available, 1 for Time zone files and other for OJVM.

[1] Time Zone: Applicable only for Oracle 9.x and 10.x. There are two datatypes TIMESTAMP WITH LOCAL TIME ZONE [TSLTZ] and TIMESTAMP WITH TIME ZONE [TSTZ] and a function TZ_OFFSET which obtains their time-zone information from Oracle's time-zone file which comes as a part of Oracle database installation.

[1a] Database Server Side:

If your database uses any of the above (datatypes or/and functions) and uses the affected time zones, then patch is required, otherwise patch is not necessary. A script is available to assess the use of time zone in the database.

[1b] Client Side: If all the below conditions are met, then time zone file install is required:-
- connects to the affected database
- time zone data type used by client
- client uses the affected time zone

Refer Below Metalink documents:-
- Effects on client and middle-tier of applying time zone patches on the Oracle Database Note:396426.1
- Workarounds when Database time zone patches are not available for your patchset Note:396387.1

[2] Oracle Java Virtual Machine (OJVM) uses time-zone information which is stored in its database. Need to install the patch only on the database side if OJVM is installed, applicable for Oracle database versions from 8.1.7.4 to 10.2.0.3.

Here is a typical (9.2) query to check whether OJVM is installed in your database:-

column owner format a10
SELECT object_type,
      owner,
      status,
      COUNT(*)
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
GROUP BY object_type,owner,status;
OBJECT_TYPE        OWNER     STATUS COUNT(*)
------------------ ---------- ------- ----------
JAVA DATA          SYS        VALID   288
JAVA CLASS         SYS        VALID   9080
JAVA CLASS         SYS        INVALID 1
JAVA CLASS         ORDSYS     VALID   870
JAVA CLASS         ORDSYS     INVALID 12
JAVA SOURCE        SYS        VALID   8
JAVA SOURCE        ORDSYS     VALID   8
JAVA RESOURCE      SYS        VALID   180
JAVA RESOURCE      ORDSYS     VALID   16

9 rows selected

Refer to Metalink Note:397770.1 [How to Correctly Check if the Oracle JVM is Installed in the Database].

[3] Here are the things that you got to do:-

[3a] Backup your client and server.

[3b] Run re_tz_views.sql [Refer to Metalink Note:412971.1 - SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a
Database]

@cre_tz_views.sql

spool cre_tz_views_output.txt

select * from TZ$TSLTZ_TAB_COLS;
select * from TZ$TSLTZ_VW_COLS;
select * from TZ$NAMED_TSTZ_TAB_COLS;
select * from TZ$NAMED_TSTZ_VW_COLS;
select * from TZ$ARGUMENTS;
select * from TZ$DBTIMEZONE;
select * from TZ$OVERVIEW;
select * from TZ$SOURCE;

spool off

drop function TZ$NAMED_TSTZ_VW_COLS_FN ;
drop view TZ$TSLTZ_TAB_COLS ;
drop view TZ$TSLTZ_VW_COLS ;
drop view TZ$ARGUMENTS ;
drop view TZ$SOURCE ;
drop view TZ$DBTIMEZONE;
drop function TZ$NAMED_TSTZ_TAB_COLS_FN;
drop type TSTZ_NAMED_TABLE;
drop view TZ$NAMED_TSTZ_TAB_COLS;
drop view TZ$NAMED_TSTZ_VW_COLS ;
drop view TZ$OVERVIEW ;

DBTIMEZONE
----------
-04:00

USAGE_TYPE                  VALUE      DESCRIPTION
--------------------------- ---------- ------------------------------------------------------------------------
DBTIMEZONE                  -04:00     Database Time Zone, if this is an 'offset' then not affected by DST
NAMED_TSTZ_TABLE_USE_NONSYS 0          Number of TSTZ columns using named time zones in tables not owned by SYS
NAMED_TSTZ_TABLE_USE_SYS    0          Number of TSTZ columns using named time zones in tables owned by SYS
NAMED_TSTZ_VIEW_USE_NONSYS 0          Number of TSTZ columns using named time zones in views not owned by SYS
NAMED_TSTZ_VIEW_USE_SYS     0          Number of TSTZ columns using named time zones in views owned by SYS
TSLTZ_TABLE_USE             0          Number of TSLTZ columns used in tables
TSLTZ_VIEW_USE              0          Number of TSLTZ columns used in views
TZ_ARGUMENTS                0          Number of PL/SQL objects with Time Zone arguments

OWNER           NAME                           TYPE         LINE       TEXT
--------------- ------------------------------ ------------ ---------- -------------------------------------------------------------------------------------------
BABU            TESTXSP_STORE_DDL              PROCEDURE    25         updatedate=systimestamp,RESOURCE_TYPE=IN_RESOURCE_TYPE
BABU            TESTXSP_STORE_DML              PROCEDURE    39         IN_BABU_SERVICE,IN_SERVICE_TYPE,IN_ONLINK,IN_CONTENT_TYPE,systimestamp, IN_RESOURCE_TYPE );
BABU            CREATE_ONLINE_DOCUMENT         PROCEDURE    13         insert into TEST_TAB (ACTTIME) values (systimestamp);
BABU_RW         B_UPDATE_APPL_ALERT_UPDATE     TRIGGER      5          SELECT SYSTIMESTAMP INTO :NEW.LAST_UPDATE_STARTED FROM dual;
BABU_ADMIN      B_INSERT_SERVICE_TXT           TRIGGER      13         SELECT SYSTIMESTAMP INTO :NEW.TIME_STAMP FROM dual;
BABU_ADMIN      B_UPDT_WEB_SERVICES_TXT        TRIGGER      2          select sysdate into :new.timestamp from dual;
BABU_ADMIN      B_INSRT_WEB_SERVICES_TXT       TRIGGER      2          select sysdate into :new.timestamp from dual;
BABU_ADMIN      PKG_SERVICE                    PACKAGE BODY 18         || 'TO_TIMESTAMP(''' || start_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' || ' AND '
BABU_ADMIN      PKG_SERVICE                    PACKAGE BODY 19         || 'TO_TIMESTAMP(''' || end_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')'

[3c] Identify the correct "utltzuv2.sql" [this script only checks stored TIMESTAMP WITH TIME ZONE data in the database, but NOT the TIMESTAMP WITH LOCAL TIME ZONE data], download it [Refer Table 1 in Metalink Note:359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database] and run the "utltzuv2.sql" to check the if TSTZ datatype is used in any tables in the database.

For 9.2.0.7, download Patch and install patch 5548107 (also to get the utltzuv2.sql script).

Refer to "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note:396670.1"

SQL> @utltzuv2.sql

You current timezone version is 1!
Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by version 3 transition rules

PL/SQL procedure successfully completed.

[3d] Save TSTZ data as varchar2.

[3e] Apply patch [Refer Table 2 and 3 in Metalink Note:359145.1
- Impact of 2007 USA daylight saving changes on the Oracle database]
[For 9.2.0.7 DB TimeZone, use Patch bundle 15 (bug 5654905) or later]
[For 9.2.0.7 OJVM, use JVM Timezone Patch 5047902]

[3f] Restore back to TSTZ datatype

[3g] Apply fixes to the clients and middle-tiers.

[3h] Work on already existing TSLTZ data.

[3i] Apply patch for OJVM.

References:
1. Impact of 2007 USA daylight saving changes on the Oracle database - Note:359145.1
2. How to Determine Whether Time Zone Changes Will Affect Your Database - Note:406410.1
3. SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database - Note:412971.1
4. USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products Note:397281.1
5. Timestamps & time zones - Frequently Asked Questions Note:340512.1

Click here for a step-by-step documentation on "Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines"

Author:	Babu Rangasamy
Title:	GIS and Database Consultant
Created Date:	February 8, 2007
Contact:	Babu.Rangasamy[at]gmail.com

Oracle Shared Server (Erstwhile Multi-Threaded Server) and ORA-4031 Error on Large Pool

2006-12-07T16:30:00.000-05:00

Environemnt: Oracle 9.2, Windows 2000 Server.
Basic MTS Configuration:- Include the following 2 parameters in the init.ora:-
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)"
SHARED_SERVERS = 20

Note:
DISPATCHERS (Required to set): Oracle recommends a ratio of 1 DISPATCHER for every 1000 connections,
here I have allocated 2 DISPATCHERS to be on the safer side and it can handle up to 2000 connections.
SHARED_SERVERS (Optional): Database server uses 1 shared server for every 20 connections, so 20 will support 200 sessions, which is fair enough for this database requirement.

Troubleshooting: ORA-4031 Errors

Received the below ORA-4031 Error at the client side:-

ORA-4031: unable to allocate %s bytes of
shared memory ("%s","%s","%s","%s")

Noticed following error in the Alert.log of a dedicated server (non
multi-threaded server), where a job runs and used a database link, which is
connect to a multi-threaded server:-

ORA-04031: unable to allocate 64 bytes of
shared memory ("large pool","unknown object","session heap","trigger condition
node")

No errors noted in the Alert.log of MTS server, database functioning
normal, accepting user connections, but following were noted in the trace files
(SID_s001_2076.trc) of user_dump_dest:-

Dump file f:\oracle\admin\<sid>\udump\<sid>_s000_2056.trc
Thu Nov 09 19:35:43 2006
ORACLE V9.2.0.6.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: <sid>

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 2056, image: ORACLE.EXE

*** 2006-11-09 19:35:43.048
*** SESSION ID:(16.4) 2006-11-09 19:35:43.001
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=11/09/2006 19:35:41
Allocation request for: sort key
Heap: 58B34B04, size: 8192
******************************************************
HEAP DUMP heap name="large pool" desc=02170098
extent sz=0xfc4 alt=100 het=32767 rec=9 flg=-126 opc=0
parent=00000000 owner=00000000 nex=00000000 xsz=0x0
******************************************************
HEAP DUMP heap name="sort subheap" desc=58B34B04
extent sz=0x2024 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=55F9F3AC owner=00000000 nex=00000000 xsz=0x1
Subheap has 1871424 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: 5A59A1A4, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=10, calls cur/top: 5A705014/5A705014, flag: (40) MS SERVER
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 139
last post received-location: kmcmbf: not KMCVCFTOS
last process to post me: 5a59f358 1 128
last post sent: 0 0 146
last post sent-location: kmcpdp
last process posted by me: 5a59f358 1 128
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 5A5F8F10
O/S info: user: SYSTEM, term: <server_host_name>, ospid: 2056
OSD pid info: Windows thread id: 2056, image: ORACLE.EXE
=========================
User Session State Object
=========================
----------------------------------------
SO: 5A6068B4, type: 4, owner: 5BAE7D58, flag: INIT/-/-/0x00
(session) trans: 00000000, creator: 5BAE7D58, flag: (1000e5) USR/- BSY/-/-/-/-/-
DID: 0001-000B-00000008, short-term DID: 0000-0000-00000000
txn branch: 00000000
oct: 3, prv: 0, sql: 67908120, psql: 67908120, user: 50/RTT
O/S info: user: <OS User>, term: <Client_Hostname>, ospid: 2892:2560, machine:
<domain>\<hostname>
program: <program_name>
application name: <app name>, hash value=0
last wait for 'SQL*Net message to client' blocking sess=0x0 seq=24275 wait_time=6
driver id=4d545300, #bytes=1, =0
temporary object counter: 0
...No current library cache object being loaded
===========================
Current Instatiation Object
===========================
-------------------------------------
INSTANTIATION OBJECT: object=576F3410
type="cursor"[2] lock=659E4EF0 handle=67908120 body=00000000 level=0
flags=FST[60] executions=0
cursor name:
SQL Query...............
child pin: 5bd992b0, child lock: 5bdd363c, parent lock: 659e4ef0
xscflg: 80110476, parent handle: 67908120, xscfl2: 7a00009
Dumping Literal Information
Bind Pos: 8, Bind Len: 21, Bind Val:
nxt: 4.0x00000660 nxt: 3.0x00000470 nxt: 2.0x000007d0 nxt: 1.0x00000304
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bhp size: 172/560
whp size: 1887880/1895664
Dump of CURRENT WORK HEAP:

Since database is functioning normal and accepting user connections, its not
so critical to find an immediate solution, but the above heap in the trace file
causes some concern. Based on the Oracle's METALINK document

ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
(ID: 223153.1), increased LARGE_POOL_SIZE from 70M to 300M, decreased
SHARED_POOL_SIZE & SHARED_POOL_RESERVED_SIZE from 200M & 20M to 150M & 15M
respectively. After the changes, init.ora will look like below:-

SGA_MAX_SIZE=1400M
PGA_AGGREGATE_TARGET=600M
DB_16K_CACHE_SIZE=400M
DB_CACHE_SIZE=400M
LARGE_POOL_SIZE=300M
SHARED_POOL_SIZE=150M
SHARED_POOL_RESERVED_SIZE=15M
JAVA_POOL_SIZE=40M
HASH_AREA_SIZE=4M
SORT_AREA_RETAINED_SIZE=2M
SORT_AREA_SIZE=2M
DB_BLOCK_SIZE=8192
DB_FILE_MULTIBLOCK_READ_COUNT=16
DISPATCHERS='(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)'
LOG_BUFFER=1M
OPEN_CURSORS=300
QUERY_REWRITE_ENABLED='FALSE'
SESSION_CACHED_CURSORS=50
SESSIONS=340
PROCESSES=400
SHARED_SERVERS=20

About LARGE_POOL: It is a part of SGA, doesn't use LRU list, not a part of SHARED_POOL. Used by
Oracle Shared Server (MTS) for UGA and RMAN if used. If LARGE_POOL is not set, then MTS will use memory from SHARED_POOL.

Use V$SGASTAT, V$SGA_DYNAMIC_COMPONENTS and V$SGA to find info about SGA and run the below query to check the summary of SGA:-

SELECT * FROM v$sgastat
WHERE pool ='large pool';
SELECT NVL(POOL,NAME) POOL_NAME,
ROUND(SUM(BYTES)/1024/1024,2) MBYTES
FROM V$SGASTAT GROUP BY NVL(POOL,NAME) ORDER BY 2 DESC;

COLUMN component FORMAT A15
SELECT component,
current_size/1024/1024 CURRENT_SIZE_MB,
granule_size/1024/1024 GRANULE_SIZE_MB
FROM v$sga_dynamic_components;

SELECT name,
ROUND((value)/1024/1024,2) MBYTES
FROM v$sga ORDER BY 2 DESC;

Note: Requires logon to access Oracle Metalink documents.Reference: Configuration
1)
Oracle9i Database Administrator's Guide - Configuring Oracle for the Shared Server - Chapter 5

2)
Net Services Administrator's Guide - Chapter 14 - Configuring Shared Server

3) Oracle Metalink Subject:
Multi-Threaded Server (MTS) Diagnostics, Doc ID: 1005259.6

Reference: Troubleshooting and Tuning

4) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use - Tuning the Large Pool and Shared
Pool for the Shared Server Architecture

5) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool

6) Oracle Metalink Subject:

ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
Doc ID: 223153.1

7) Oracle Metalink Subject:
Diagnosing and Resolving Error ORA-04031, Doc ID:146599.1

8) Oracle Metalink Subject:
Fundamentals of the Large Pool, Doc ID:62140.1

9) Oracle Metalink Subject:
ORA-4031 AND ORA-600 [1113] USING MTS AND LARGE_POOL_SIZE, Doc ID:1058003.6

Caching Objects in Shared Pool

2006-12-05T15:20:00.000-05:00

Introduction:
This document is about KEEPing frequently used database objects like PROCEDURES, PACKAGES, SEQUENCES, TRIGGERS and CURSORS in the SHARED_POOL of SGA. Caching/KEEPing frequently
used objects in SHARED POOL will help improve the database performance.

Tested Environment: Oracle 9.2, Windows 2000 Server.

Connect to the database as sys:-
SQL> connect sys/**** as sysdba

Step#1: Create a table sp_obj_tab to hold the objects that need to be cached:-

SQL> CREATE TABLE sys.sp_obj_tab (
      objownr VARCHAR2(64),
      objname VARCHAR2(1000),
      objtype CHAR(1),
      listdte DATE,
      keepdte DATE,
      remarks CHAR(30));

Step#2: Take a look at script shared_pool_mgmt.SQL and run it, which will create a package called shared_pool_mgmt:-

SQL> @shared_pool_mgmt.SQL

Step#3: Create the below triggers which runs during database startup and shutdown:-

SQL> CREATE OR REPLACE TRIGGER db_shutdown_sp_obj_list
      BEFORE SHUTDOWN ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.list_obj_table;
      END;
      /

SQL> CREATE OR REPLACE TRIGGER db_startup_sp_obj_keep
      AFTER STARTUP ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.keep_obj_startup;
      END;
      /

Step#4: Schedule a dbms job that runs at regular intervals, the below once runs at 5 pm every day:-

SQL> VARIABLE JOBNO NUMBER;

SQL> BEGIN
      DBMS_JOB.SUBMIT(JOB=>:JOBNO,
      WHAT=>'shared_pool_mgmt.keep_obj_periodic;',
      NEXT_DATE=>TRUNC(SYSDATE)+1+17/24,
      INTERVAL=>'TRUNC(SYSDATE)+1+17/24');
      COMMIT;
     END;
     /

SQL> PRINT JOBNO;

Below is the script, mentioned in Step#2, copy and paste it in a file called shared_pool_mgmt.SQL :-

CREATE OR REPLACE PACKAGE shared_pool_mgmt AS
---------------------------------------------------
---- Name: SHARED_POOL_MGMT
---- Schema: SYS
---- Created by: BabuMani
---- Created on: Dec-05-2006
-- Description: This package helps database
-- administrators to KEEP frequently used database
-- objects like PROCEDURES, PACKAGES, SEQUENCES,
-- TRIGGERS and CURSORS in the SHARED_POOL of SGA.
---------------------------------------------------
PROCEDURE list_obj_table;
-- PROCEDURE LIST_OBJ_TABLE: Identifies the top 10
-- objects based on various system resource
-- consuming factors and writes them in a table
-- called SP_OBJ_TAB. This table is populated
-- during database shutdown. This procedure is run
-- using database shutdown trigger (BEFORE
-- SHUTDOWN ON DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_startup;
-- PROCEDURE KEEP_OBJ_STARTUP: Reads the objects
-- listed in table SP_OBJ_TAB during database
-- startup and KEEP them (except CURSORS)in
-- SHARED_POOL. This procedure is run using
-- database startup trigger (AFTER STARTUP ON
-- DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_periodic;
-- PROCEDURE KEEP_OBJ_PERIODIC: It refreshes the
-- list of objects in table SP_OBJ_TAB and KEEP
-- them (including CURSORS) in SHARED_POOL.
-- Suggested to run this procedure at scheduled
-- intervals.
---------------------------------------------------
END shared_pool_mgmt;
/

CREATE OR REPLACE PACKAGE BODY shared_pool_mgmt AS

PROCEDURE list_obj_table IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE FROM sys.sp_obj_tab;
  COMMIT;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
  SELECT DISTINCT owner,name,DECODE(type,'TRIGGER','R','P'),SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type IN ('PACKAGE','PROCEDURE','PACKAGE BODY','TRIGGER')
     AND loads >= 1 AND executions > 0
  UNION
  SELECT DISTINCT owner,name,'Q',SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type = 'SEQUENCE'
     AND loads >= 1 ;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY executions DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY loads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY buffer_gets DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY disk_reads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY sharable_mem DESC)
         WHERE ROWNUM<=10;
  COMMIT;
END list_obj_table;

PROCEDURE keep_obj_startup IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur1 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab WHERE objtype<>'C';
BEGIN
  OPEN cur1;
   LOOP
    FETCH cur1 into own, nam, typ;
    EXIT WHEN cur1%NOTFOUND;
    SYS.DBMS_SHARED_POOL.KEEP(''|| own || '.' || nam || '',''||typ||'');
    UPDATE sys.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur1;
END keep_obj_startup;

PROCEDURE keep_obj_periodic IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur2 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab;
BEGIN
  shared_pool_mgmt.list_obj_table;
  OPEN cur2;
   LOOP
    FETCH cur2 INTO own, nam, typ;
    EXIT WHEN cur2%NOTFOUND;
    IF typ='C' THEN
     SYS.dbms_shared_pool.keep(''|| own ||'',''||typ||'');
    ELSE
     SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '',''||typ||'');
    END IF;
    UPDATE SYS.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur2;
END keep_obj_periodic;

END shared_pool_mgmt;
/

Below are some Handy-Dandy queries:-

COLUMN pool FORMAT A25

SELECT pool||(' -- Total') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool
UNION
SELECT pool||(' --- Free') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE name='free memory' AND pool IS NOT NULL GROUP BY pool;

SELECT type,COUNT(*) COUNT FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

SELECT DECODE(objtype,'R','Trigger','P','Package','C','Cursor','Q','Sequence',objtype) OBJTYPE, COUNT(*) COUNT FROM sp_obj_tab GROUP BY objtype ORDER BY 2 ;

COMPUTE SUM LABEL 'TOTAL ' OF COUNT ON REPORT
COMPUTE SUM LABEL 'TOTAL ' OF SHARABLE_MEM_K ON REPORT
BREAK ON REPORT

SELECT type,COUNT(*) COUNT, round(sum(SHARABLE_MEM)/1024) SHARABLE_MEM_K FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

Library Cache Hit Ratio:

COMPUTE SUM LABEL 'TOTAL' OF PINHITS ON REPORT
COMPUTE SUM LABEL 'TOTAL' OF PINS ON REPORT
BREAK ON REPORT
  SELECT namespace
       , pins
       , pinhits
       , reloads
       , invalidations
    FROM v$librarycache
ORDER BY namespace;

CLEAR COMPUTE
CLEAR BREAK

  SELECT sum(pinhits) pinhits,
         sum(pins) pins,
         round((sum(pinhits)/sum(pins))*100,2) LIBRARY_CACHE_HIT_RATIO
    FROM v$librarycache
ORDER BY namespace;

Dictionary Cache (Row Cache) Hit Ratio:

column parameter format a25
column RATIO format 999.9
column UPDATES format 999,999,999

  SELECT parameter
       , SUM(gets) gets
       , SUM(getmisses) getmisses
       , 100*SUM(gets - getmisses) / SUM(gets) RATIO
       , SUM(modifications) UPDATES
    FROM v$rowcache
   WHERE gets > 0
GROUP BY parameter;

SELECT ROUND((SUM(gets - getmisses - fixed)) / SUM(gets)*100,2) "ROW CACHE" FROM v$rowcache;

Reference:
Oracle9i Database Performance Tuning Guide and Reference - Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool