Move table (Prior 8i Versions)

Let's discuss the various methods available to move a table from one tablespace to another or from one schema to another in Oracle versions prior to 8i.

Let's have the table EMPLOYEE on PAYROLL_DATA tablespace owned by user PAY for discussion. Here are some scenarios, you may also combine the different methods discussed here.

1. Change owner of the table

Task: Move the table EMPLOYEE owned by PAY to the schema PAYROLL.

Steps:

1. Take export of the table

exp file=employee.dmp log=employee.exp.log tables=pay.employee

2. It does not matter, if you drop or rename the old table or leave it there. Better to drop it to avoid confusion.

drop table pay.employee;

3. Import the table to PAYROLL.

imp file=employee.dmp log=employee.imp.log fromuser=pay touser=payroll

4. Make sure the public synonym, if any exists, point to the right table.

2. Change the tablespace of the table

Task: Move the table EMP from tablespace PAYROLL_DATA to PAY_DATA.

Method 1:

1. Take export of the table

exp file=employee.dmp log=employee.exp.log tables=pay.employee

2. Drop the table.

drop table pay.employee;

3. Revoke unlimited tablespace from user PAY.

revoke unlimited tablespace from pay;

4. Change tablespace quota assignments for user PAY.

alter user PAY quota unlimited on PAY_DATA quota 0 on PAYROLL_DATA;

5. Import the table to PAY.

imp file=employee.dmp log=employee.imp.log fromuser=pay touser=pay

6. Change back the default tablespace and quota assignments for PAY.

Method 2:

1. Take export of the table

exp file=employee.dmp log=employee.exp.log tables=pay.employee

2. Drop the table.

drop table pay.employee;

3. Import the table with indexfile option to create the table and index create script. 

imp file=employee.dmp log=employee.imp.log fromuser=pay touser=pay indexfile=employee.sql

4. Edit the employee.sql file to change the tablespace name and, if required, the storage parameters.

5. Do the import

imp file=employee.dmp log=employee.imp.log fromuser=pay touser=pay ignore=y

Method 3:

1. Rename the original table.

rename employee to employee_old;

2. Create the new table from a subquery of the old table. This method will not work if any of the column data type is LONG.

create table employee 
tablespace pay_data
storage (initial 1m next 1m pctincrease 0)
as
select * from employee_old;

3. Apply the grants, constraints, triggers on employee_old table to the new employee table.