Moving Tables Between Tablespaces Using Traditional Export/Import (EXP/IMP)
PURPOSE
This article 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:
A. On a Per Table Basis
B. On a Per User Basis
C. From user A to user B
A worked example can be seen later in the article for each of the scenarios.
A. On a Per Table Basis
B. On a Per User Basis
C. From user A to user B
A worked example can be seen later in the article for each of the scenarios.
SCOPE
It defines the steps required when moving tables from one tablespace to another. These steps require a SQL*Plus account with DBA privileges to set-up the appropriate user privileges
DETAILS
A. 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:
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 re-build 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).
B. 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 re-build 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 DEFAULT TABLESPACE ;
(b) remove quota on the old tablespace
ALTER USER QUOTA 0 ON ;
(c) grant quota on the new tablespace.
ALTER USER QUOTA ON ;
(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='';
REVOKE UNLIMITED TABLESPACE FROM;
REVOKE DBA from;
REVOKE UNLIMITED TABLESPACE FROM
REVOKE DBA from
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 ;
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.
C. 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 QUOTA 0 ON TABLESPACE ;
(b) If the user has the RESOURCE role, revoke UNLIMITED TABLESPACE from that user.
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '';
REVOKE UNLIMITED TABLESPACE from;
REVOKE UNLIMITED TABLESPACE from
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 ;
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 ;
If you receive ORA-01536: space quota exceeded for tablespace USER_B_TS, then:
ALTER USER QUOTA ON ;
4. Perform the import.
5. Set userB's quotas back if needed:
ALTER USER QUOTA nn ON TABLESPACE ;
Regrant the privileges that were revoked in step 2, if required.
Examples
A. 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
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
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=
#> 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" ;
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;
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
*
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
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
B. 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;
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;
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'
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;
SQL> GRANT dba, resource, unlimited tablespace TO scott;
C. 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;
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'
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
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace
4. Perform the import.
#> imp user_b/user_b fromuser=user_a touser=user_b file=user_a.dmp
5. Re-grant the privileges that were revoked in step 2, if required.
SQL> conn system/manager
SQL> ALTER USER user_b QUOTA 2m ON user_a_ts;
SQL> GRANT unlimited tablespace, dba TO user_b;
SQL> ALTER USER user_b QUOTA 2m ON user_a_ts;
SQL> GRANT unlimited tablespace, dba TO user_b;
Note
- exp/imp will fails on BLOB or CLOB table columns, because imp forces to import LOBs into original tablespace.
- will fail on 11g, unless you change "deferred_segment_creation" to false
REFERENCES
NOTE:10398.1 - Reading an Export File Using Import Without Physically Importing Data=======================================================================
Reading an Export File Using Import Without Physically Importing Data
Purpose:
========
The purpose of this article is to briefly describe how to list objects
in an export file without physically importing the data.
Scope and Application:
======================
This article helps to avoid possible import problems. The content
applies to Oracle V7.X and V10.X.
Reading an Export File Using Import Without Physically Importing Data:
======================================================================
Here are two simple ways to minimize the occurrence of import errors
to perform a dummy import. One way uses the SHOW parameter and the
other way creates objects separately from a .SQL file produced from an
import with the INDEXFILE parameter.
1. Import using the SHOW parameter:
-----------------------------------
Example:
imp file=exp.dmp userid=uname/pass rows=n show=y log=imp.log
When you use SHOW = Y, the contents of the export file are listed but data
is not imported into the database. If you use this qualifier in conjunction
with the 'log' parameter, the contents are spooled to the log file. Check
the log file to make sure that all expected objects are contained in the
export file.
2. Import using the 'INDEXFILE' and 'CONSTRAINTS' parameters:
-------------------------------------------------------------
When you use the INDEXFILE parameter, data is not imported into the
database. However, a .SQL file is produced which contains all the CREATE
object statements. The file can be used to pre-create the tables or create
the indexes after the import has completed.
Example:
1. Run the import utility with the indexfile option:
> imp file=exp.dmp userid=uname/pass indexfile=create_indexes.sql
2. Update create_indexes.sql to remove the 'REM' characters from the
'create table' statements.
3. Run the create_indexes.sql script to create the tables and indexes:
svrmgrl> @create_indexes.sql
4. In order to import the data, the following import command imports the
data into the pre-created tables:
> imp file=exp.dmp userid=uname/pass ignore=Y indexes=N log=imp.log
The indexfile parameter is primarily intended for use when importing table
data, without importing indexes. This helps make table data available more
quickly to users, as the resulting index.sql script may be run to create the
indexes after the import.
The file also contains the CREATE TABLE statements, preceeded with 'REM'.
Therefore, a second use of the file is to create all objects separately,
before you import the data. This is useful, as it provides the option of
updating storage information if needed.
In Oracle8i, importing with CONSTRAINTS = Y causes table constraints to be
written to the index file.
In step (4), import attempts to create the tables from the export dump
file, and finds the objects already exist which can cause errors. The
IGNORE parameter is used because it indicates that all object creation
errors should be ignored. Secondly, the INDEXES parameter is used since
the indexes were created in step (3).
===============================================================================
Comments
Post a Comment
Oracle DBA Information