Moving tables from one schema to another

[ehspacl]

Dear All,

Can anyone tell me how to move a table from one schema to another. Can't seems to find the command anywhere.

Many thanks in advance

Paul

 

[sem]

Ther's no such command. BTW why do you need it? You may grant rights on a table to anybody else.

Regards, Dima

http://seminihin.narod.ru

 

[ehspacl]

It's just that all the tables in the db except 3 are in the same schema. Just wanted them all in the same schema.
Thanks anyway

Paul

 

[sem]

You may export/import them.

Regards, Dima

http://seminihin.narod.ru

 

[ehspacl]

True, could do this as well.
Thanks
Paul

 

[SantaMufasa]

Ehspacl (and others),

Hold on...there certainly is a command to move a table from one tablespace to another (and to do it very rapidly (i.e., using parallel processors and avoid writing redo). You will be amazed at how quickly this command processes tables:

alter table <table_name> move parallel nologging tablespace <new tablespace name>;

I have a SQL script that writes a SQL script to move entire schemas or tablespaces to another tablespace. The added advantage of such a move it that it eliminates the "Swiss cheese holes" that result over time from deleting/updating rows.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:58 (21Apr04) UTC (aka "GMT" and "Zulu"), 08:58 (21Apr04) Mountain Time)

 

[sem]

Dave, glad to see you!
The original question was about changing table owner, not location.

Oh, I know, you're more than busy now

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Duh !!! I must be so busy that I cannot even read the thread properly. Sorry for the "unrelated response". And yes, "paying customers" have been keeping me away from my beloved Tek-Tips for days (but I am sneaking a visit now for a few minutes). Thanks, Dima, for your watchful "quality-assurance" eye.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:50 (21Apr04) UTC (aka "GMT" and "Zulu"), 09:50 (21Apr04) Mountain Time)