Changing a table's owner ?

[countrypaul]

Is it possible to change the owner of a table in Oracle ?
For example:
If table statistics was created by Paul, but I now want it to be accessed as part of an application schema rather than 'personal' schema. That is to reference it as ABCD.statistics rather than paul.statistics from other users. I could copy the table into ABCD (if there was enough storage), but I wondered if it was possible to change the owner?

I've been looking at the documentation but cannot find anything appropriate. As is often the case, it may be there but I can't find it as I'm not searching for the right thing !

Thanks

Paul

 

[Turkbear]

Hi,
No..Once created by a user it is forever owned by that user..You could, of course, create a synonym and eliminate any need to reference the schema name in any query.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[countrypaul]

Thanks Turkbear

I would prefer to use synonyms, however my customer does not want them used in the particular application, so I don't have that choice. Looks like I'll have to do it the hard way.

Paul

 

[SantaMufasa]

Paul,

As an addendum to the existing comments: Perhaps the most straightforward method to achieve your results is to do an export of the table, then do an "imp" with "...fromuser=Paul touser=ABCD..." as options. This way, ABCD owns the table, the table has all the rows, and all the indexes come across, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[countrypaul]

Thanks Dave,

Since it looks as though there is not enough space in oracle for to copies of the table, I figured export/import and deleting the original table between was the way to go. Thanks for confirming it.

Paul

 

[Turkbear]

Hi,
However, since you will need to drop the table from the original schema BEFORE importing to the new one ( due to your space limitations), I would do at least 2 exports to different file system locations to be sure that I have a good one that can be imported.


Better safe than sorry....






To Paraphrase:"The Help you get is proportional to the Help you give.."