Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools


Interview Questions for Oracle DBA

Go Back to the list of questions.

Question: Posted on: Jan 15, 2007
by: dbapool Editor
Views:40195
How can we change the owner of a table?
Editorial Answer:
Owner of a table is the schema name which holds the table. To change the owner we can just recreate the table in the new schema and drop the previous table.
Answer by users:
 

 Jayanta
 INDI

Posted on: Feb 28, 2007

but does this really mean the change of ownership?
it is like making a duplicate table in another schema there by destroying the original one. and it is only possible when the user copying the table in another schema has proper privileges.


 Masri
 JOR

Posted on: Nov 06, 2007

There is no direct way, but you can create the table in the new owner schema by:
create table xx as select * from old_owner.xx;


 bhallar
 USA

Posted on: Nov 20, 2007

CTAS will not bring all the privileges associated with that table, the trigger, the indexes, best thing is to export and import back into different schema.


 Jha
 INDI

Posted on: Nov 26, 2007

The owner of the table can not be changed directly. To change the ownership , first take the export of the table from the given schema and then import the table in other schema using remap_schema parameter in the export syntax.

Or in 10g we can use the datapump an use the parameter fromuser , touser during importing the table into other schema..


 Nikki
 USA

Posted on: Feb 18, 2008

The export and import or SQL Copy command are easy options if table is small.


 Vish
 INDI

Posted on: May 25, 2008

You can drop a table in existing schema and create in new schema


 bobby
 INDI

Posted on: Jun 17, 2008

THEY IS NO DIRECT WAY...
CREATE TABLE BY USING AS SELECT * FROM
EX: CREATE TABLE ABC_NEW AS SELECT * FROM OLDOWNER.ABC_OLD


 Amit_P
 INDI

Posted on: Jun 14, 2010

To change the ownership, do export and import with with system user with fromuser= and touser= parameter.


 Vigyan
 USA

Posted on: Jun 27, 2010

"create table as" is the simplest way and works in most cases.

 








  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor