How to Move Tables Between Tablespaces Using Traditional Oracle Export/Import (EXP/IMP)

This post discusses three ways you can move tables from one tablespace to another tablespace using traditional Export/Import utilities (exp/imp). Alternative methods exist to achieve this see:

On a Per Table Basis
On a Per User Basis
From user A to user B

A worked example can be seen later in the article for each of the scenarios.

Details

1. Moving Tables on a Per Table Basis

The following steps will move tables on a per-table basis: 1. Check the tablespaces in use and perform the table level export.

2. If you have enough space in the database, rename the table(s) and drop the indexes. After the table has been reimported successfully then drop the renamed table. If you do not have enough space in the database to rename the table(s) drop it. It may wise to check the dump file before performing the drop to ensure that the file can be read. To perform a check of the dump file use the following syntax:


$ imp username/password file=expdat.dmp show=yes log=imp.log

This will read the dump file and give you some confidence that it can be used to rebuild the tables. No objects are imported/created at this point.

3. Run import with INDEXFILE= to get the create table and create index statements.

4. Edit the resulting file, and set the tablespace clause to indicate the new tablespace. Delete the create index statements.

5. Grant quota on the new tablespace.

6. Run the edited create script to create the table(s).

7. Run import with IGNORE=Y to populate the new table(s) and create the index(es).

2. Moving Tables on a Per User Basis

The following steps will move tables on a per-user basis: 1. Perform a user level or full database export.

2. Drop or rename the table(s) you are moving. It may wise to check the dump file before performing the drop to ensure that the file can be read. To perform a check of the dump file use the following syntax


$ imp username/password file=expdat.dmp show=yes log=imp.log

This will read the dump file and give you some confidence that it can be used to rebuild the tables. No objects are imported/created at this point.

3. For the user with the tables that are being moved, perform the following: (a) set their default tablespace to the desired tablespace:

ALTER USER [username] DEFAULT TABLESPACE [new tablespace];

(b) remove quota on the old tablespace:

ALTER USER [username] QUOTA 0 ON [old tablespace];

ALTER USER [username] QUOTA [bytes] ON [new tablespace];

(d) If the user has the RESOURCE role, revoke UNLIMITED TABLESPACE from that user. Also ensure that the user does not have DBA role:

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='[username]';
REVOKE UNLIMITED TABLESPACE FROM [username];
REVOKE DBA from [username];

This is done so that the import does not bring the objects back into the “old” tablespace.

4. Test to make sure that the user can no longer create objects in the “old” tablespace. Create a table and specify the old tablespace:

CREATE TABLE JUNK (A NUMBER) TABLESPACE [old tablespace];

If you receive an ORA-01950 “no privileges on tablespace ‘%s’”, then you have succeeded in removing the create object privilege for that user in the specified tablespace.

5. Perform a user-level import of this user.

6. Regrant the privileges that were revoked in step 3, if required.

3. Moving Tables from userA to userB

IMPORT will always import tables into a tablespace that has the same name as the original tablespace (in the original database where the EXPORT was done from), regardless of what is userB’s default tablespace. This assumes the destination database has a tablespace with the same name as the original tablespace from which userA’s tables were EXPORTed.

Consider the following:

An Export has been done in database A of userA’s tables, which are in tablespace USER_A_TS. You are attempting to import into database B into userB’s schema which is in tablespace USER_B_TS. You do the IMPORT with FROMUSER=USERA TOUSER=USERB but userA’s tables still are being put in tablespace USER_A_TS and not in userB’s default tablespace of USER_B_TS.

The following steps will move tables from userA tablespace USER_A_TS to userB tablespace USER_B_TS: 1. Perform a user level export for user_a. 2. For userB, check tablespace quotas on tablespaces USER_A_TS and USER_B_TS.

(a) Remove userB’s quotas from USER_A_TS (which is userA’s original tablespace).

First, note any original quotas if any:

SELECT * FROM DBA_TS_QUOTAS where username = '';

Now remove them:

ALTER USER [userB] QUOTA 0 ON TABLESPACE [USER_A_TS];

(b) If the user has the RESOURCE role, revoke UNLIMITED TABLESPACE from that user.

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '[userB]';
REVOKE UNLIMITED TABLESPACE from [userB];

3. Test to make sure that the user can no longer create objects in the “old” tablespace. Create a table and specify the old tablespace:

CREATE TABLE JUNK (A NUMBER) TABLESPACE [USER_A_TS];

If you receive an ORA-01950 no privileges on tablespace ‘%s’, then you have succeeded in removing the create object privilege for that user in the specified tablespace. Check to see that userB can create table(s) in the new tablespace USER_B_TS.

CREATE TABLE JUNK (A NUMBER) TABLESPACE [USER_B_TS];

If you receive ORA-01536: space quota exceeded for tablespace USER_B_TS, then:

ALTER USER [userB] QUOTA [bytes] ON [USER_B_TS];

4. Perform the import.

5. Set userB’s quotas back if needed:

ALTER USER [userB] QUOTA nn ON TABLESPACE [USER_A_TS];

Regrant the privileges that were revoked in step 2, if required.

Examples

1. On a Per Table Basis

1. Check the tablespaces in use and perform the table level export:

SQL> CONN scott/tiger
SQL> SELECT table_name, tablespace_name FROM user_tables WHERE table_name = 'EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS

SQL> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'EMP';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
PK_EMP                         USERS

$ exp scott/tiger file=emp.dmp rows=yes tables=emp

2. Drop or rename the table you wish to move:

SQL> CONN scott/tiger
SQL> RENAME emp to old_emp;
SQL> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'EMP';

no rows selected

SQL> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'OLD_EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
OLD_EMP                        USERS

3. Run import with INDEXFILE=[filename] to get a file with the create table and index statements.

$ imp scott/tiger file=emp.dmp indexfile=emp.sql

4. Using an editor to make the following changes: - Remove “REM " from the CREATE and ALTER TABLE statements - Remove the CONNECT and CREATE INDEX statements - Replace the tablespace names with the new name ()

After the edit, the file should look similar to:

CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
"SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL
131072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "NEW_USERS" ;

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY
("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 131072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "NEW_USERS" ENABLE ;

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY
("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ;

ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "PK_EMP" ;

ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;

5. Grant quota on the new tablespace

SQL> CONN system/manager
SQL> ALTER USER scott QUOTA 2m ON new_users;

If the user has no quota, then the create will fail with:

CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'NEW_USERS

6. Run the script to create the tables

SQL> CONN scott/tiger
SQL> @emp.sql
SQL> SELECT table_name, tablespace_name FROM user_tables WHERE table_name = 'EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            NEW_USERS

7. Run the import with IGNORE=Y to populate the new table(s) and create the index(es).

#] imp scott/tiger file=emp.dmp ignore=yes

2. On a Per User Basis

1. Perform a user level or full database export:

$ exp scott/tiger file=scott.dmp log=scott.log

2. Drop or rename the table(s) you are moving:

SQL> CONN scott/tiger
SQL> RENAME emp TO old_emp;
SQL> RENAME dept TO old_dept;

3. Grant quota on the new tablespace:

SQL> CONN system/manager
SQL> ALTER USER scott DEFAULT TABLESPACE new_users;
SQL> ALTER USER scott QUOTA 0 ON users;
SQL> ALTER USER scott QUOTA 2m ON new_users;
SQL> REVOKE unlimited tablespace FROM scott;
SQL> REVOKE dba FROM scott;

4. Test to make sure that the user can no longer create objects in the “old” tablespace. Create a table and specify the old tablespace.

SQL> CONN scott/tiger
SQL> CREATE TABLE test (a varchar2(10)) tablespace users;
     *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

5. Perform the import with IGNORE=YES:

$ imp scott/tiger file=scott.dmp log=imp_scott.log ignore=yes

6. Re-grant the privileges that were revoked in step 3, if required.

SQL> CONN system/manager
SQL> GRANT dba, resource, unlimited tablespace TO scott;

3. From user A to user B

The following steps will move tables from userA tablespace USER_A_TS to userB tablespace USER_B_TS:

1. Perform a user level export for user_a

$ exp user_a/user_a file=user_a.dmp

2. For userB check tablespace quotas on tablespaces USER_A_TS and USER_B_TS and then amend accordingly:

SQL> SELECT tablespace_name, max_blocks FROM dba_ts_quotas WHERE username = 'USER_B';

TABLESPACE_NAME                MAX_BLOCKS
------------------------------ ----------
USER_B_TS                             256
USER_A_TS                             256

SQL> ALTER USER user_b QUOTA 0 on user_a_ts;
SQL> REVOKE unlimited tablespace FROM user_b;
SQL> REVOKE dba FROM user_b;
SQL> ALTER USER user_b QUOTA 2m ON user_b_ts;

3. Test to make sure that the user can no longer create objects in the “old” tablespace. Create a table and specify the old tablespace.

SQL> CONN user_b/user_b
SQL> CREATE TABLE test (a varchar2(10)) TABLESPACE user_a_ts;
create table test (a varchar2(10)) tablespace user_a_ts
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USER_A_TS'

Check to see that userB can create table(s) in the new tablespace, USER_B_TS.

SQL> CREATE TABLE JUNK (A NUMBER) TABLESPACE ;
     *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace