RE: Change Schema OWNER of ALL Objects in 10gR2 ?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 16 May 2007 11:54:33 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9D97FAF@usahm208.amer.corp.eds.com>

The problem with using partitioning to move objects between users is that the feature is an additional charge item, but if you have paid for it then this does seem to be an interesting use of the feature.

Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Aponte Sent: Tuesday, May 15, 2007 4:28 PM
To: oracle-l_at_freelists.org
Subject: RE: Change Schema OWNER of ALL Objects in 10gR2 ?

I've used the partition exchange feature to quickly move between schemas in the same database. It may not fit your needs but it's a good-to-know.

Tony Aponte

/* Simplified sample just to give you an idea */

SQL> CREATE TABLE APONTET.orig
(
col1 NUMBER,
col2 NUMBER

)
TABLESPACE USERS
PARTITION BY RANGE (col1)
(
PARTITION BOGUS VALUES LESS THAN (MAXVALUE)
LOGGING
)
Table created.

SQL> CREATE TABLE wmx.new
(
col1 NUMBER,
col2 NUMBER

)
TABLESPACE USERS
Table created.

SQL> insert into apontet.orig values (1,1) 1 row created.

SQL> insert into wmx.new values (2,2)
1 row created.

SQL> select * from apontet.orig

COL1 COL2
---------- ----------
1 1

1 row selected.
SQL> select * from wmx.new

COL1 COL2
---------- ----------
2 2

1 row selected.

SQL> alter table apontet.orig exchange partition bogus with table SQL> wmx.new
Table altered.

SQL> select * from apontet.orig

COL1 COL2
---------- ----------
2 2

1 row selected.

SQL> select * from wmx.new

COL1 COL2
---------- ----------
1 1

1 row selected.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Asif Momen Sent: Tuesday, May 15, 2007 6:20 AM
To: VIVEK_SHARMA_at_infosys.com
Cc: oracle-l_at_freelists.org
Subject: Re: Change Schema OWNER of ALL Objects in 10gR2 ?

Hi Vivek,

That is the only way out !!!

Regards

VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote: Folks

How can the Schema OWNER of ALL Objects Owned by (say) OWNER1 be Changed to OWNER2 in 10gR2?

NOTE - OWNER2 does NOT exist in the Database.
NOTE - Other / multiple schemas & respective Objects should remain unaffected.
NOTE - Database is a Testing Database. Hence Downtime is NOT an issue.

Qs Is Export , Import the the only way using 1 of the following?

exp - OWNER=OWNER1 / imp - FROMUSER=OWNER1, TOUSER=OWNER2 , expdp - SCHEMAS=OWNER1 / impdp - REMAP_SCHEMA=OWNER1:OWNER2

Cheers

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 16 2007 - 10:54:33 CDT