Question & Answer
Question
What is the process to export an Oracle based IBM® Rational® RequisitePro® schema?
Answer
Exporting an Oracle based RequisitePro schema:
A dump file can be created using Oracle's Export utility.
Note: The default location of the export utility is the <Path to Oracle_Home>\bin directory.
Note: EXPORT parameter OWNER indicates that the export will be run in user mode.
Note: User mode makes it possible to copy data in tables and indexes owned by a particular user, as well as objects, such as procedures and triggers also owned by that user.
- From the operating system command line issue the following command to export and create an Oracle dump file:
exp system/manager@<instance alias> owner=reqpro file=c:\oracle\export\reqpro.dmp buffer=400000
- When this command completes the export process is complete.
Export utility parameter descriptions:
| Parameter | Description |
| EXP | The command used to start the Export utility. |
| SYSTEM/MANAGER | The username and password under which the export will execute.
If this is not the user who owns the schema objects this user must have been granted the exp_full_database role. Also, if you want to create the export from an Oracle instance other than the default Oracle instance you would need to modify this parameter by adding @<instance alias>. The modified parameter would be: |
| OWNER | Indicates the user whose database objects should be exported. |
| FILE | Identifies the location where the dump file will be created and the name of the dump file (*.dmp). |
| BUFFER | Specifies the size, in bytes, of the buffer used to fetch rows.
As a result, this parameter determines the maximum number of rows in an array fetched by EXPORT. If the export takes a long time try increasing this value. Use 400000 as the default. |
Importing an Oracle based RequisitePro schema:
Note: Do not import into an existing RequisitePro user database schema. Importing into an existing schema can cause problems with the following areas: referential integrity, sequences and triggers.
Note: You can experience problems if you attempt to import a dump file created with an incompatible version of the Oracle Export utility. For example, if you attempt to import a dump file created with the 8.1.7 version of the Oracle Export utility into a database using the 8.1.6 version of the Oracle Import utility the import might fail. You can only import a dump file that was created with a equal or lower version of the export utility. In this situation you should recreate the dump file using a 8.1.6 or lower version of the Export utility.
- Connect to the database through SQL*Plus as a user with administrative privileges and run the following SQL statements.
In the following statements the tablespaces are named IMPORT_DATA and IMPORT_INDEX. The tablespaces can be named and located (by the DATAFILE parameter) where you choose, and are examples only.
CREATE TABLESPACE IMPORT_DATA
DATAFILE 'C:\ORACLE\ORACLEDATA\SCHEMAS\IMPORT_DATA.DBF'
SIZE 110M REUSE AUTOEXTEND ON NEXT 110M DEFAULT STORAGE
(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE TABLESPACE IMPORT_INDEX
DATAFILE 'C:\ORACLE\ORADATA\SCHEMAS\IMPORT_INDEX.DBF'
SIZE 30M REUSE AUTOEXTEND ON NEXT 30M DEFAULT STORAGE
(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>
DEFAULT TABLESPACE IMPORT_DATA
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, IMP_FULL_DATABASE TO <USERNAME>;
GRANT CREATE ROLE TO <USERNAME> WITH ADMIN OPTION;
CREATE ROLE <USERNAME>_ROLE;
GRANT CREATE ANY VIEW TO <USERNAME>;
- Exit SQL*Plus.
- From the operating system command line issue the following command to import the Oracle dump file.
Note: When this command completes the import process is complete.
IMP
FROMUSER=<OLD_REQPRO>
TOUSER=<USERNAME>
BUFFER=400000 IGNORE=Y COMMIT=Y
Export utility parameter descriptions:
| Parameter | Description |
| IMP | The command used to execute the Import utility. |
| SYSTEM/MANAGER | The user name and password under which the import will be executed.
The user executing the import must have been granted the imp_full_database role. Also, if you wish to import to an instance other than the default Oracle instance you need to modify this parameter by adding @<instance alias>. The modified parameter would read: |
| FILE | Identifies the path to the dump file (*.dmp). |
| FROMUSER | Name of the user whose schema objects will be imported from the dump file.
This is the same user whose schema objects were exported during the export process. |
| TOUSER | Name of the user schema into which database objects from the dump file should be placed.
This user must exist in the database prior to executing the Import utility. |
| BUFFER | Specifies the size, in bytes of the buffer used to fetch rows during the import process.
Use 400000 as the default |
| IGNORE | Specifies whether to ignore errors that occur during import while creating database objects. |
| COMMIT | Specifies whether IMPORT should commit each time a buffer worth of data is written to the database |
Historical Number
20524
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21125077