Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-00902: invalid datatype

ORA-00902: invalid datatype

From: Andrew <andrewolsen_os3_at_yahoo.com>
Date: 27 Mar 2003 08:40:51 -0800
Message-ID: <f6ada335.0303270840.480943a@posting.google.com> 





With Oracle9i and the TimeStamp data type:
I received this error message when I tried to create a table with a
timestamp column in it. I traced this error to the fact that I had
ported my database creation scripts (including initORCL.ora ) from
8.1.7 and so the compatible flag was not set in the initORCL.ora
script.



The solution to the problem for me was to change the 
compatible=9.2.0 flag in the initORCL.ora script and then restart the
database.



For what it's worth, I am inlining the scripts here:

------------------- BEGIN initORCL.ora ----------------------


##############################################################################



# Copyright (c) 1991, 2001 by Oracle Corporation

##############################################################################

 


###########################################

# Cache and I/O

###########################################

db_block_size=8192


#db_cache_size=67108864

 


###########################################

# Cursors and Library Cache

###########################################

open_cursors=300
 


###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/u01/app/oracle/oradata/ORCL/bdump
core_dump_dest=/u01/app/oracle/oradata/ORCL/cdump
timed_statistics=TRUE


user_dump_dest=/u01/app/oracle/oradata/ORCL/udump
 


###########################################

# Distributed, Replication and Snapshot

###########################################

db_domain=dev.slc.innovantage.com


#db_domain=localdomain

#remote_login_passwordfile=SHARED

#remote_login_passwordfile=EXCLUSIVE

 


###########################################

# File Configuration

###########################################


control_files=("/u01/app/oracle/oradata/ORCL/control01.ctl",
"/u01/app/oracle/oradata/ORCL/control02.ctl",
"/u01/app/oracle/oradata/ORCL/control03.ctl")
 


###########################################



# MTS


###########################################

#dispatchers="(PROTOCOL=TCP)(SER=MODOSE)",

"(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
 


###########################################

# Miscellaneous

###########################################

compatible=9.2.0


db_name=ORCL
 


###########################################

# Network Registration

###########################################

instance_name=udb1
 


###########################################

# Pools

###########################################

java_pool_size=117440512


large_pool_size=1048576


shared_pool_size=117440512
 


###########################################

# Processes and Sessions

###########################################

processes=150
 


###########################################

# Redo Log and Recovery

###########################################

#fast_start_mttr_target=300

 


###########################################

# Resource Manager

###########################################

resource_manager_plan=SYSTEM_PLAN
 


###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

sort_area_size=524288
 


###########################################

# System Managed Undo and Rollback Segments

###########################################

#undo_management=AUTO

#undo_tablespace=UNDOTBS

#rollback_segments=rbs01,rbs02,rbs03,rbs04 


----------------------- END init ORCL.ora
-----------------------------


---------------------- BEGIN database creation script
-----------------




REM Sample script to create database - Page 33 - 36

REM * ***********************************************************
REM * Script to create ORCL instance with db_block_size = 8192
REM *
REM *  Created:   Andrew Olsen - 05/13/2002
REM *  From script provided by Jeffery Hunter at www.idevelopment.info
REM *  Thanks!
REM *




REM *


REM * ***********************************************************


REM * Now perform all commands necessary to create


REM * the final database after the CREATE DATABASE command has
REM * succeeded.


spool $ORACLE_HOME/dbs/ORCL/crdbORCL.log



REM * Connect as internal


REM connect internal;



REM * Shutdown database


shutdown abort;



REM * Startup the instance


startup nomount pfile=$ORACLE_HOME/dbs/ORCL/initORCL.ora 



REM * Start the instance (ORACLE_SID must be set to <ORCL>).
REM *


REM * Create the <ORCL> database.


REM *



create database "ORCL"


    maxinstances 2


    maxlogfiles  32


    maxdatafiles 1000


    character set "UTF8"


datafile '/u01/app/oracle/oradata/ORCL/system_01_01.dbf' size 10M
AUTOEXTEND ON,



         '/u01/app/oracle/oradata/ORCL/system_01_02.dbf' size 10M AUTOEXTEND
ON

logfile  '/u01/app/oracle/oradata/ORCL/log_01_01.log' size 5M,
         '/u01/app/oracle/oradata/ORCL/log_01_02.log' size 5M,
         '/u01/app/oracle/oradata/ORCL/log_01_03.log' size 5M;






REM * install data dictionary:


@$ORACLE_HOME/rdbms/admin/catalog.sql



REM * install procedural components:


@$ORACLE_HOME/rdbms/admin/catproc.sql



REM * Create additional rollback segment in SYSTEM since
REM * at least one non-system rollback segment is required
REM * before creating a tablespace.
REM *




create rollback segment SYSROLL tablespace system
storage (initial 100K next 100K minextents 2 maxextents UNLIMITED);


REM * Put SYSROLL online without shutting
REM * down and restarting the database.
REM *




alter rollback segment SYSROLL online;



REM * Create a tablespace for rollback segments.
REM *



create tablespace ROLLBACK


 datafile '/u01/app/oracle/oradata/ORCL/rbs_01_01.dbf' size 5M
AUTOEXTEND ON,



           '/u01/app/oracle/oradata/ORCL/rbs_01_02.dbf' size 5M AUTOEXTEND
ON,


           '/u01/app/oracle/oradata/ORCL/rbs_01_03.dbf' size 5M AUTOEXTEND ON
 default storage (

  initial      100K
  next         100K




  pctincrease  0


  minextents   1


  maxextents   UNLIMITED


);



REM * Create the "real" rollback segments.
REM *



create rollback segment RBS01 tablespace ROLLBACK
storage (initial 100K next 100K minextents 1 maxextents UNLIMITED);
create rollback segment RBS02 tablespace ROLLBACK
storage (initial 100K next 100K minextents 1 maxextents UNLIMITED);
create rollback segment RBS03 tablespace ROLLBACK
storage (initial 100K next 100K minextents 1 maxextents UNLIMITED);
create rollback segment RBS04 tablespace ROLLBACK
storage (initial 100K next 100K minextents 1 maxextents UNLIMITED);



REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments
online


REM * without shutting down and restarting the database.
REM *


alter rollback segment RBS01 online;
alter rollback segment RBS02 online;
alter rollback segment RBS03 online;
alter rollback segment RBS04 online;






REM * Create a tablespace for temporary segments.
create tablespace TEMP


 datafile '/u01/app/oracle/oradata/ORCL/temp01.dbf' size 25M
AUTOEXTEND ON



 default storage (

 initial     100K
 next        100K




 maxextents  UNLIMITED


 pctincrease 0


);



REM * Create a tablespace for database tools.
REM *



create tablespace TOOLS


 datafile '/u01/app/oracle/oradata/ORCL/tools01.dbf' size 25M
AUTOEXTEND ON



 default storage (

 initial     50K
 next        50K




 maxextents  UNLIMITED


 pctincrease 0


);



REM * Create tablespaces for user activity.
REM *



create tablespace DATA


 datafile '/u01/app/oracle/oradata/ORCL/data01.dbf' size 25M
AUTOEXTEND ON



 default storage (

   initial     100K
   next        1000K




   maxextents  UNLIMITED


   pctincrease 0


);



REM * Create tablespaces for indexes.


REM *



create tablespace INDEX


 datafile '/u01/app/oracle/oradata/ORCL/index01.dbf' size 25M
AUTOEXTEND ON



 default storage (

   initial     100K
   next        100K




   maxextents  UNLIMITED


   pctincrease 0


);


REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don''t
REM * want that.
REM *




alter user sys temporary tablespace TEMP;
alter user system default tablespace TOOLS temporary tablespace TEMP;



REM * Now run the Oracle-supplied scripts we need for this DB
REM *


@$ORACLE_HOME/rdbms/admin/catexp.sql
@$ORACLE_HOME/rdbms/admin/dbmspool.sql
@$ORACLE_HOME/rdbms/admin/prvtpool.plb

REM * Now run the Oracle-supplied script to create the DBA views


REM * for the SYSTEM account.  Change to SYSTEM first.
REM *



connect system/manager


@$ORACLE_HOME/rdbms/admin/catdbsyn.sql



REM * All done, so close the log file and exit.
REM *



spool off;


exit

----------------------------- END database creation script




Received on Thu Mar 27 2003 - 10:40:51 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US