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. 

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.