Oracle-L: Fw: lob problem

From: <bulbultyagi_at_now-india.net.in>
Date: Thu, 15 May 2003 10:02:00 -0800
Message-ID: <F001.00599E99.20030515100200@fatcity.com>

Hello
I have Oracle 9.0.1.1.1 enterprise edition with partitioning option, on win32

I have created two test databases called 'lahiri' and 'oemrep' using the same Oracle software.

I created the database 'oemrep' using dbca, on the other hand I created 'lahiri' using 'create database' followed by catalog, catproc (as sys) and pupbld (as system).

I set compatible=9.0.1.1.1 on both the databases before creating them.

I get the following error on the manually created database (lahiri) and not
on the one created using dbca.

  I am able to run
  create table test
  ( my_col clob,
    my_col2 varchar2(30) ) ;
  when I am connected as sysdba or connected as system   but I am unable to run the above when I connect as user scott. My system   tablespace is dictionary managed.

I created scott as follows :

sql create user scott identified by tiger

          default tablespace users
          temporary tablespace temp
          quota unlimited on users ;

          grant  create  session, create table, create sequence,  create
  procedure,  create
          view to scott;

          grant select_catalog_role to scott;

  The default tablespace called 'users' is locally managed with autoextend   switched on in its datafiles (maxsize unlimited) oh , and automatic segment
  space management.
  When scott tries to
  sql create table test

          ( my_col clob, my_col2 varchar2(30) ) ;   create table test
  *
  ERROR at line 1:
  ORA-03001: unimplemented feature

1..Any idea what scripts need to be run specifically to enable lob support for the user scott ?. Or does it have something to do with privileges or the nature of tablespaces.

2..What does the dbca do that I need to do ?

  3.. I had already going through the entire process of specifying the options
  in dbca and then at the last moment telling dbca to just create scripts so   that I could read thru then and fiind out what to do. But when I read thru
  those scripts I found out that :
  a.. dbca creates many scripts
  b.. each of those scripts , calls many other scripts itself.   This makes it very difficult to find out what really enables lob support.

4.. Could it be due to some error occuring while catalog and catproc run ? To check this out I set did the following : I started my database in restricted mode as sys and then re ran catalog.sql
after spooling on (but with echo off), the only errors I got were when the object name conflicted with an existing object name. Other than that all grant creations , view creations, comment creations,etc suceeded (I went thru the entire spooled file and checked all the errors). Same thing with catproc.sql.

The problem persists.
Anyway sys and system are able to create the table containing clob column. Why not scott ?

Any ideas
..........gaurav

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <bulbultyagi_at_now-india.net.in
INET: bulbultyagi_at_now-india.net.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 15 2003 - 13:02:00 CDT