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

From: Tony Aponte <Tony_Aponte_at_Jabil.com>
Date: Tue, 15 May 2007 16:27:36 -0400
Message-ID: <C6594C4B5C99CF4E9F536C038021F82B0478F055@alfarsmsg07.corp.jabil.org>

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 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

Received on Tue May 15 2007 - 15:27:36 CDT