RE: Change Schema OWNER of ALL Objects in 10gR2 ?
- From: "Tony Aponte" <Tony_Aponte@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 May 2007 16:27:36 -0400
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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
