Install Oracle Spatial in Oracle 11g

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