Re: Export objects to another tablespace.

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Mon, 26 Apr 1999 09:19:46 -0700
Message-ID: <37249222.6447AC6F@us.oracle.com>

One extra thing to check. Given the approach they used to take, it's almost certain that the vendors took the easy appraoch and granted the users the RESOURCE role. The RESOURCE role has the UNLIMITED TABLESPACE privilege Andrew mentioned, so either revoke the RESOURCE role from the users (most of the CREATE privileges are for some unknown reason given to the CONNECT role anyway), or the UNLIMITED TABLESPACE privilege from the role.

HTH. Pete

Andrew Babb wrote:

> Alan,
>
> After you create the user on the new tablespace, make sure that the users
> default tablespace is (presumably) data, and the temporary tablespace
> (presumably) temp. Then allocate unlimited QUOTAS on the tablespace data (your
> default tablespace), and possibly quotas on the temporary tablespaces.
>
> Now make sure that the System Privilege UNLIMITED TABLESPACE does NOT belong to
> the user.
>
> When you perform an import of the user, Import will determine that it cannot
> restore the objects into the SYSTEM tablespace, and will then create the objects
> in the default tablespace.
>
> Hope this helps,
> Andrew
>
> Alan Russell wrote:
>
> > We have a vendor supplied application running on AIX that uses a runtime
> > version of Oracle. Older revisions of the app installed Oracle and placed
> > everything in the system tablespace. They have "wised up" and now new
> > installations have 3 tablespaces SYS, data and temp. My question is how can
> > I export the tables and indexes from a existing install with all in the
> > system tablespace to a new install to the data tablespace on another box? Is
> > this a simple export - import?
> >
> > AR.

Regards

Pete

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Mon Apr 26 1999 - 11:19:46 CDT