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"

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

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

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.