RE: Change Schema OWNER of ALL Objects in 10gR2 ?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 May 2007 11:54:33 -0400
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
