Re: Move DB to Another Schema

Ken Janusz wrote:
>
> I can't find any details of this in any of my documentation. I originally
> created my DB as SYS. I have setup a new user and need to move my DB /
> Tables, etc. to the new schema. How do I go about this? Can I do it via
> SQL Plus or do I need to do it via svrmgr? Or, would it just be easier to
> create a new DB (DDL SQL script) as the new user?
>
> Thanks,
> Ken Janusz
> --

Ooops. Simplest thing is to create a parameter file starting with TABLES= and listing all the tables you created at the wrong place, export, and then import using FROMUSER=SYS TOUSER=whomever. Then cleanup the SYS account but VERY VERY CAREFULLY. When you create the other account, first do not give it the RESOURCE privilege (or revoke UNLIMITED TABLESPACE immediately afterwards), but give it unlimited quota on two tablespaces, one for tables and one for indexes, and set the tablespace for tables as the default tablespace. You will have to ALTER INDEX ... REBUILD TABLESPACE <tablespace for indexes> on all indexes afterwards. Sequences, views, synonyms etc. will have to bre recreated manually.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).