Re: Urgent: Why is system tablespace growing?

From: Paul Drake <paled_at_home.com>
Date: Fri, 11 May 2001 23:46:27 -0700
Message-ID: <F001.00301DC4.20010511232030@fatcity.com>

abdul latif wrote:
>
> Does anyone know why system tablespace is changing?
>
> I'm doing an fromuser touser import and the system
> tablespace is for some reason is being written to.
>
> Here what is being reported:
>
> IMP-00017: following statement failed with ORACLE
> error 1652:
> "CREATE UNIQUE INDEX "PK_BF" ON "PAGE" ("ID" ) P"
> "CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
> 65536) "
> " LOGGING"
> IMP-00003: ORACLE error 1652 encountered
> ORA-01652: unable to extend temp segment by 1065 in
> tablespace SYSTEM
> IMP-00017: following statement failed with ORACLE
> error 1652:
> "CREATE INDEX "I_BFPT" ON "FLOW" ("ID" ) P"
> "CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
> 65536) "
> " LOGGING"
> IMP-00003: ORACLE error 1652 encountered
> ORA-01652: unable to extend temp segment by 1065 in
> tablespace SYSTEM
> IMP-00017: following statement failed with ORACLE
> error 1652:
>
> TIA
>
> abdul latif
>

diagnose

select default_tablespace, teporary_tablespace from dba_users where username=<to_user>;

Unless you specify a default tablespace and temporary tablespace - they will both default to SYSTEM.

2. correct

   revoke resource from <to_user>;
   revoke connect from <to_user>;
   grant create table to <to_user>;
   grant create session to <to_user>;

   alter user <to_user> default tablespace <user_tablespace> temporary
tablespace <temp_tbspc>;
   alter user <to_user> quota unlimited on <user_tablespace> quota

unlimited on <temp_tbspc>;

The idea here is to restrict the user to have NO privileges to write to the SYSTEM tablespace.

hth,

Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: paled_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Sat May 12 2001 - 01:46:27 CDT