Solved

Export/Import

Posted on 2002-04-24
5
2,793 Views
Last Modified: 2008-03-03

Hi Oracle Experts,

Is it possible to use EXPORT/IMPORT to move all the objects of schema A in Database/Tablespace DB1/TSA to schema B in Database/Tablespace DB2/TSB ?

Or, is there any else simple way to do this ?

Thanks a lot.

Albert

0
Comment
Question by:AlbertYou
5 Comments
 
LVL 1

Expert Comment

by:belem
ID: 6967832
Hi AlbertYou

Yes it is possible

exp system/manager@your1db owner=scott file=... log=...
imp system/manager@your2db fromuser=scott touser=yourschema file=... log=...

Belem
0
 

Expert Comment

by:myexpert2002
ID: 6967835



   Albert,

          You can Export/Import all database objects from one database to other. Export/Import utitility has the options. Refer to Oracle Documentation:

Oracle8i Server and SQL*Plus -->Oracle8i Utilities -->Export/Import

or u can refer to otn site.

Good Luck.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 6967854
Hi Albert,

1) This here is a simpler method:

exp schemaA/pwdA@serviceA file=c:\schemaA.dmp log=c:\schemaA.log

imp schemaB/pwdB@serviceB file=c:\schemaB.dmp
log=c:\schemaB.log

You can give any of the other key/value pairs available for exp and imp tools.

2) As for tablespaces, you can generate scripts from toadfree, a freeware from quest.com. If yolu selecte tablespace option as well you can come to know which tablespace each table belongs to.

3) You can run this ddl on destination schema in databaseB with due fine tuning into tablespaces of you choice that you will create like what you created for schemaA in databaseA.

--- k_murli_krishna
0
 
LVL 3

Accepted Solution

by:
p_yaroslav earned 100 total points
ID: 6967877
Hi!

Here few decisions of this problem (not mine).

 
   
Doc ID:  Note:1012307.6
Subject:  Moving Tables Between Tablespaces Using EXPORT/IMPORT
Type:  BULLETIN
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  06-SEP-1995
Last Revision Date:  14-MAR-2002
 

PURPOSE
-------

This article discusses three ways you can move tables from one tablespace to
another tablespace using Export/Import. Alternative methods exist to achieve
this see [NOTE:110246.1]

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 & APPLICATION
-------------------

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.


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

   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=<filename> 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 <username> DEFAULT TABLESPACE <new tablespace>;

   (b) remove quota on the old tablespace

       ALTER USER <username> QUOTA 0 ON <old tablespace>;

   (c) grant quota on the new 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.


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 = '<userB>';

   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

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                
                                                                               
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 (like vi) 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 (NEW_USERS)

   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                          
                                                                               

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;                                                
                                                                               

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;    
                                                                                                                 


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;
                                                                               
                                                                   
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 <USER_B_TS>;
        *
        ERROR at line 1:
        ORA-01536: space quota exceeded for tablespace USER_B_TS


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

References
----------
[NOTE:110246.1]   How to reorganize tables in Oracle8i
[NOTE:10398.1]    Reading an Export File Using Import Without Physically Importing Data
[NOTE:2072618.6]  Information on Export Files in ORACLE7
.

 

--------------------------------------------------------------------------------
 
 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.  

__________________________________________________________



Also :


Can you export just a single tablespace using the Export Utility?
    Enhancement Request 192876.

    Common workaround(s):

    a - Generate a list of users in that tablespace, and do user-level
        exports.   Typically databases are designed such that a few users
        have created objects in particular tablespaces, and all of those
        users' objects are in those tablespaces.

        To generate such a list, use the following select statement:

        select owner from dba_segments where
        tablespace_name='<TABLESPACE TO BE EXPORTED>';

    b - Generate a list of all objects that tablespace, and do table-level
        exports.

        To generate such a list, use the following select statement:

        select owner, object_name, object_type from dba_segments
        where tablespace_name='<TABLESPACE TO BE EXPORTED>';

__________________________________________________--


Hope it helps!
Yaroslav.
0
 
LVL 2

Author Comment

by:AlbertYou
ID: 6970201
Hi Yaroslav,

Your answer is perfect.
Thanks !!

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now