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

 
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Tony Aponte
Sent: Tuesday, May 15, 2007 4:28 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Asif Momen
Sent: Tuesday, May 15, 2007 6:20 AM
To: VIVEK_SHARMA@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Change Schema OWNER of ALL Objects in 10gR2 ?

Hi Vivek,
 
That is the only way out !!!
 
Regards

VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx> 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

Other related posts: