Re: moving tables from the system tablespace to a new tablespace

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/08/08
Message-ID: <965764306.16840.1.pluto.d4ee154e@news.demon.nl>#1/1

I have a feeling those tables are under the SYS user also?: Is that correct? Then you have hit a BIG nono.

You can't export the SYS user. (as the SYS user owns metadata only, which is created during creating your objects)

What you could do is

- create a new user
- grant that new user privilege on the affected tables
- connect as the new user
- create table <tablename>

  tablespace <new tablespace>
  as select * from sys.<oldtable>
Of course you will loose your constraints on that table.

Hth,

Sybrand Bakker, Oracle DBA

<cferring_at_my-deja.com> wrote in message news:8moa6l$dof$1_at_nnrp1.deja.com...
> Hi everyone,
>
> due to poor design, some of our Production tables are in the System
> tablespace.
>
> I want to create a new tablespace or two, and move those Production
> tables off the System tablespace.
> What are my options?
>
> I think about Exporting the System tablespace, create one or two new
> tablespace, import the System one into one of those.
> Would that work well (since it's the System Tablespace)?
>
> Otherwise, should I rebuild the whole database (please don't say so)?
> Any good advise?
>
> thanks in advance,
> C.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Aug 08 2000 - 00:00:00 CDT