Re: Changing table ownership

forbrich_at_supernet.ab.ca (Hans Forbrich) writes: >(milapier_at_ge.hydro.qc.hq) wrote:  

>> >Hi Everyone!  

>> >Is it possible to change ownership of a table in Oracle 7?

Yes, but it ain't supported.
What you have to do is similar to way you can change column names. (There is a script to do this on ftp.bf.rmit.edu.au)

Basically:
(1) Use SYS.USER$ and find the numbers of the appropriate users
(2) Get everybody else off the database.
(3) Rummage through SYS.OBJ$ to find the appropriate object
(4) As SYS, update SYS.OBJ$ and change the OWNER# column.
(5) Shut the database down and restart it (this is because the

     SGA won't have the new information - you have been doing this
     behind Oracle's back)

OK, so it is an evil thing to do, but when you have no more space around to create fred.emp as select * from mary.emp, then you have to do something like this.

BTW, you might consider looking at related objects such as indices and changing them as well.

YOUR MILEAGE MAY VARY - TAKE AN EXPORT FIRST (hopefully you have unix, so you can use the "compress on the fly" trick documented in the FAQ is disk space as well as tablespace is a problem). If disk is REALLY tight, you can adapt the compress on the fly to throw the export out to a tape device on the fly and use NO disk
(well, about 8 disk blocks all up).

Regards
David

--
---
David T. Bath       | dtb_at_ftp.bf.rmit.edu.au (mail=dtb_at_bf.rmit.edu.au)
3/175 Wattletree Rd | Snr Tech Consultant, Global Consulting Services
MALVERN 3144 VIC    | 179 Grattan St, Carlton, Vic 3053, Australia
+61 3 9500 9337     | Ph:+61 3 93477511  Fx:+61 3 93470182 Mb: +61 15 824171
WWW pages incl. ORA FAQ: lynx http://www.bf.rmit.edu.au/~orafaq