How to move a table to another Schema and Tablespace?

Toad for Oracle & DevOps Toolkit
1

Is there a way to move a (marked) TABLE mytable123 from Tablespace AAA and Schema BBB
to another Tablespace USERS and Schema MYSCH?

Privileges are given.

Ben

2

Toad’s dbms_redefinition wizard

3

Moving the tablespace is easy - it’s an ALTER TABLE command, and
Toad’s Alter Table GUI exposes that on the storage pages.

Changing the user however…you could run an export and import and do it at
that point.

Or use a CTAS command to build it anew, then drop the ‘old’ table?

There’s probably a dozen different ways you could do this.

There is a ‘create in a different schema’ option in the Schema
Browser if your right-click on the table name too.

Jeff

4

Lots of ways other ways.

Using Toad features,

Right click on the table in the schema browser and do "create in another
schema". Then right click again and "copy data". Then drop the original.

With SQL

create table new_schema.target_table as select * from
old_schema.source_type;

drop table old_schema.source_table;

but that will just create the table with the data, not constraints, triggers,
grants, etc.

In both, you can specify the tablespace.

5

If you care about the data, be aware that Toad’s “create in another
schema” feature doesn’t include data. But Toad does have a
“copy table data” feature in the schema browser. You could use that
for data.

6

I thought that too, but according to oracle docs, that’s not possible?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i100682
1

7

Moving the tablespace is easy - it’s an ALTER TABLE command, and
Toad’s Alter Table GUI exposes that on the storage pages.

@HillbillyToad:
Thank you for the reply but I don't find "Storage" tab after clicking "Alter Table" context menu entry for a certain, marked table.

I am using Toad v9.6.1.1
Maybe you are talking of another version?

Maybe you mean (in Alter TABLE dialog) the tab "Physical Attributes"?
There is an entry field option "Tablespace". If I change the Tablespace is the move automatically done?

Ben