How To Change Owner Of A Table In Oracle

Oracle provides no direct method to change the owner of a table. But we often face such situations to change the schema owner of the table. We have some work around to satisfy this requirement. Lets see them.

1. Export the table and import it into the new schema using FROMUSER, TOUSER clause in imp statement.

exp username/password tables=”S1.testtab” log=expDEMO.log

imp username/password tables=”testtab” fromuser=S1 touser=S1 file=expDEMO.log log=impDEMO.log

This command successfully imports the data into new schema, S1. Thus we have changed the owner of the table.

2. One other simple way is to recreate the table in the new schema as shown below.

CREATE TABLE S2.testab as select * from S1.testtab;

After S2.testab is created, S1.testtab can be truncated.

Here S2.testab will have no privileges as S1.testtab. All the grants, indexes, sequences ,constraints and any other dependencies needs to be recreated.

3. Above method of using select * may be a little cumbersome for large tables. So after creating the new table, data can be loaded from old table using sqlldr.

4. One other way, but strictly NOT recommended for other than test systems is to hack the data dictionay tables. This is to update the data dictionay table directly. But may cause potential harm to the database or even corrupt the entire database.

Article by Divya