 |
|
How to change an Oracle table owner
Oracle Tips by Burleson Consulting
October 24, 2007
|
Question: I want to
move a table from one schema owner to another. Is there an
alternative to dropping and recreating the table under the other
user ID?
Answer: The
traditional approach for changing a table owner is to use CTAS
or export/import.
create table bill.emp as select * from scott.emp;
Other approaches are to create a
private synonym for the table such that the table owner is not
required, and to create a public synonym without the table name:
create public synonym emp for
scott.emp;
There are also unsupported "hacks"
where you can update the data dictionary directly, but these can
be very dangerous and will make your database unsupported.
