| Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2) Part Number B25107-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2) Part Number B25107-01 |
|
|
View PDF |
This section describes how to import and export data and load and unload data with Oracle Database Express Edition (Oracle Database XE). It contains the following topics:
Oracle Database XE can copy data between itself and other Oracle databases, and between itself and external files. Data copying is accomplished by exporting and importing data, and by unloading and loading data. The following table defines these terms.
| Term | Definition |
|---|---|
| Exporting | Copying database data to external files for import into another Oracle database only. The files are in a proprietary binary format. |
| Importing | Copying data into the database from external files that were created by exporting from another Oracle database. |
| Unloading | Copying database data to external text files for consumption by another Oracle database or another application (such as a spreadsheet application). The text files are in an industry-standard format such as tab-delimited or comma-delimited (CSV). |
| Loading | Copying data into the database from external text files that are in either a standard delimited format or in any of the formats that are supported by the Oracle SQL*Loader utility. |
Data exported from any Oracle Database edition (Express Edition, Standard Edition, and Enterprise Edition), can be imported into any other edition.
Oracle Database Express Edition (Oracle Database XE) provides a number of powerful options for importing, exporting, loading, and unloading data. Table 10-1 provides a summary of these options.
Table 10-1 Summary of Oracle Database XE Import/Export Options
| Feature or Utility | Description |
|---|---|
|
Data Load/Unload wizards in the Oracle Database XE graphical user interface |
|
|
SQL*Loader utility |
|
|
Data Pump Export and Data Pump Import utilities |
|
|
Export and Import utilities |
|
Table 10-2 provides a number of load/unload/import/export scenarios and suggests the appropriate option to use for each.
Table 10-2 Import/Export Scenarios and Recommended Options
| Import/Export Scenario | Recommended Option |
|---|---|
|
You have fewer than 10 tables to load, the data is in spreadsheets or tab- or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields). |
Data Load/Unload wizards in the Oracle Database XE graphical user interface |
|
You have to load data that is not delimited. The records are fixed length, and field definitions depend on column positions. |
SQL*Loader |
|
You have tab-delimited text data to load, and there are more than 10 tables. |
SQL*Loader |
|
You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001). |
SQL*Loader |
|
You want to import or export an entire schema from or to another Oracle database. There is no XMLType data in any of the data. |
Data Pump Export and Data Pump Import |
|
You want to import or export data from or to another Oracle database. The data contains XMLType data and contains no |
Import ( |
See Also:
Oracle Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*LoaderYou can unload and load data in the following ways:
Unload and load data with the Data Unload/Load wizards of the Oracle Database XE graphical user interface
The wizards read and write delimited text files only.
Load data with the Oracle SQL*Loader utility
SQL*Loader supports many text file formats and includes advanced features.
This section includes the following topics:
See Also:
Oracle Database Utilities for more information on SQL*LoaderThe Data Load/Unload wizards of the Oracle Database XE graphical user interface enable you to easily load and unload delimited text data to and from the database. The step-by-step wizards have the following features:
You can load or unload XML files or delimited-field text files (such as comma-delimited (.csv) or tab-delimited files).
You can load by copying and pasting from a spreadsheet.
You can omit (skip) columns when loading or unloading.
You can load into an existing table or create a new table from the loaded data.
When loading into a new table, the primary key can be taken from the data or generated from a new or existing Oracle sequence.
When loading into a new table, column names can be taken from the loaded data.
Each time that you load from a file, file details are saved in a Text Data Load Repository. You can access these files from within the repository at any time.
Limitations include the following:
The wizards load and unload table data only. They do not load or unload other kinds of schema objects.
You can load and unload to and from your own schema only. This is also true for users with administrator privileges.
You can load or unload only a single table at a time.
There are no data type limitations for unloading to text or XML files, or for loading from XML files. However, when loading from spreadsheets (through copy and paste) or from text files, only the following data types are supported: NUMBER, DATE, VARCHAR2, CLOB, BINARY_FLOAT, and BINARY_DOUBLE.
This section contains the following examples of unloading and loading data with wizards:
Suppose you want to unload the REGIONS table, which is part of the HR sample schema, so that it can be used in another application. Suppose also that you want to create a tab-delimited text file, and you want to save the data in a file called regions.txt.
To unload the REGIONS table:
Log in to the Oracle Database XE graphical user interface as the HR user.
See "Accessing the Database Home Page" for information on getting logged in.
Note:
An administrator must first unlock the
HR account and assign a password. See "Logging In as an Administrator" and "Locking and Unlocking User Accounts" for instructions.On the Database Home Page, click the Utilities icon, and then click the Data Load/Unload icon.
On the Data Load/Unload page, click the Unload icon, and then click the Unload to Text icon.
The Unload to Text page appears, showing the Schema wizard step. This wizard step displays a Schema list, in which HR is selected. Because you can unload from your own schema only, you cannot change this selection.
Click Next.
The Table Name wizard step appears.
From the Table list, select REGIONS, and then click Next.
The Columns wizard step appears.
Select all columns by clicking and dragging or by clicking and shift-clicking, and then click Next. (You can also select a subset of columns. Deselected columns are excluded from the unload operation.)
The Options wizard step appears.
Complete the following steps:
In the Separator field, remove the comma if present, and enter a backslash and a lowercase T (\t) to indicate that you want the tab character to be the field delimiter. (You can use any character as the delimiter.)
Select the Include Column Names check box.
This causes the first row unloaded to be the column names, rather than the first row of data. You can use this first row to set column names when you load.
In the File Character Set list, select Unicode UTF-8.
Click Unload Data.
A Save As window appears, with the file name regions.txt filled in. Depending on your browser, another window may precede the Save As window, asking you if you want to save or open the file. If so, take the option to save the file to disk.
Save the file regions.txt to the Desktop or to a directory of your choice.
(Optional) Open the regions.txt file with a text editor or spreadsheet application to verify that the REGIONS table was unloaded properly.
Suppose your application calls for a REGIONS table, where each row contains a region number and a region name. Suppose also that you previously unloaded region data from a desktop database system into a tab-delimited text file named regions.txt.
You want to use the region number field in each record as a business key but not as the primary key, and you therefore decide to have the Load wizard generate a numeric primary key for each loaded record.
Note:
You can complete the following steps with theregions.txt file that you create in "Example: Unloading Data with the Unload Wizard".To load the REGIONS table:
Log in to the Oracle Database XE graphical user interface as any user other than SYSTEM or HR.
To log out first, click the Logout button at the upper right-hand corner of the page. See "Accessing the Database Home Page" for information on getting logged in. If no database user other than SYSTEM or HR exists, create one. See "Creating Users" for instructions.
On the Database Home Page, click the Utilities icon, and then click the Data Load/Unload icon.
On the Data Load/Unload page, click the Load icon, and then click the Load Text Data icon.
The Load Data page appears, showing the Target and Method wizard step.
Under the Load To heading, select New table, and under the Load From heading, select Upload file (comma separated or tab delimited).
Click Next.
The File Details wizard step appears.
Complete the following steps:
Click Browse, select the regions.txt file, and then click Open.
In the Separator field, replace the comma with a backslash and a lowercase T (\t) to indicate that the field delimiter is a tab character.
In the File Character Set list, select Unicode UTF-8.
Click Next.
The Table Properties wizard step appears.
Complete the following steps:
In the Table Name field, enter REGIONS.
Accept the default (Yes) in all Upload lists.
Setting Upload to No excludes the column from the load operation.
Click Next.
The Primary Key wizard step appears.
Complete the following steps:
Next to the Primary Key From label, select Create new column.
Next to the Primary Key Population label, select Generated from a new sequence.
These selections cause Oracle Database XE to:
Create an additional table column called ID, which is used as the primary key for REGIONS.
Create a new sequence called REGIONS_SEQ.
Use the values from the sequence to populate the ID field as each new row is added.
If you did not want to create a new primary key, and wanted to instead use the existing REGION_ID field as the primary key, you would do the following:
Select Use an existing column.
In the Primary Key list, select REGION_ID(NUMBER).
Select Not generated.
Click the Load Data button.
The load proceeds, and when it is complete, the Text Data Load Repository page appears, showing the regions.txt file at the top of the list of loaded files.
Check the load status by looking under the Succeeded and Failed columns for the regions.txt file.
The numbers in these columns indicate the number of rows that were successfully loaded or that caused an error.
SQL*Loader loads data from external datafiles into tables of an Oracle database. A particular datafile can be in fixed record format, variable record format, or stream record format (the default).
The input for a typical SQL*Loader session is a control file, which controls the behavior of SQL*Loader, and some data, located either at the end of the control file itself, or in a separate datafile.
The output of a SQL*Loader session is an Oracle database (where the data is loaded), a log file, a "bad" file, and potentially, a discard file. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load. The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.
SQL*Loader uses three different methods to load data, depending on the situation: conventional path, direct path, and external tables.
A conventional path load is the default loading method. It executes SQL INSERT statements to populate tables in an Oracle database. This method can sometimes be slower than other methods because extra overhead is added as SQL statements are generated, passed to Oracle, and executed. It can also be slower because when SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources.
A direct path load does not compete with other users for database resources. It eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing them directly to the database files, bypassing much of the data processing that normally takes place. Therefore, a direct path load can usually load data faster than conventional path. However, there are several restrictions on direct path loads that may require you to use a conventional path load. For example, direct path load cannot be used on clustered tables or on tables for which there are transactions pending.
See Oracle Database Utilities for a complete discussion of situations in which direct path load should and should not be used.
An external table load creates an external table for data that is contained in a datafile. The load executes INSERT statements to insert the data from the datafile into the target table. An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.
See Oracle Database Administrator's Guide for more information on external tables.
You can use SQL*Loader to do the following:
Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
Load data from multiple data files during the same load session.
Load data into multiple tables during the same load session.
Specify the character set of the data.
Selectively load data (you can load records based on the records' values).
Manipulate the data before loading it, using SQL functions.
Generate unique sequential key values in specified columns.
Use the operating system's file system to access the datafiles.
Load data from disk, tape, or named pipe.
Generate sophisticated error reports, which greatly aid troubleshooting.
Load arbitrarily complex object-relational data.
Use secondary datafiles for loading LOBs and collections.
In the following example, a new table named dependents will be created in the HR sample schema. It will contain information about dependents of employees listed in the employees table of the HR schema. After the table is created, SQL*Loader will be used to load data about the dependents from a flat data file into the dependents table.
This example requires a data file and a SQL*Loader control file, which you will create in the first two steps.
Create the data file, dependents.dat, in your current working directory. You can create this file using a variety of methods, such as a spreadsheet application or by simply typing it into a text editor. It should have the following content:
100,"Susan, Susie",Kochhar,17-JUN-1997,daughter,101,NULL, 102,David,Kochhar,02-APR-1999,son,101,NULL, 104,Jill,Colmenares,10-FEB-1992,daughter,119,NULL, 106,"Victoria, Vicki",Chen,17-JUN-1997,daughter,110,NULL, 108,"Donald, Donnie",Weiss,24-OCT-1989,son,120,NULL,
This file is a CSV (comma-separated values) file in which the commas act as delimiters between the fields. The field containing the first name is enclosed in double quotation marks in cases where a variant of the official name is also provided—that is, where the first name field contains a comma.
Create the SQL*Loader control file, dependents.ctl, in your current working directory. You can create this file with any text editor. It should have the following content:
LOAD DATA INFILE dependents.dat INTO TABLE dependents REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( dep_id, first_name, last_name, birthdate, relation, relative_id, benefits )
Do one of the following:
On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
Start SQL Command Line (SQL*Plus) and connect as user hr by entering the following at the command prompt:
sqlplus hr/hr
At the SQL prompt, create the dependents table, as follows:
CREATE TABLE dependents (
dep_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25) CONSTRAINT dep_last_name_nn NOT NULL,
birthdate DATE,
relation VARCHAR2(25),
relative_id NUMBER(6) CONSTRAINT emp_dep_rel_id_fk REFERENCES employees
(employee_id),
benefits CLOB
)
/
The constraint on the last_name column indicates that a value must be provided. The constraint on the relative_id column indicates that it must match a value in the employee_id column of the employees table. The benefits column has a datatype of CLOB so that it can hold large blocks of character data. (In this example, there is not yet any benefits information available so the column is shown as NULL in the data file, dependents.dat.)
After you receive the Table created message, enter exit to exit SQL Command Line.
From within your current working directory (where you created the control and data files), issue the following SQL*Loader command at the system prompt:
sqlldr hr/hr DATA=dependents.dat CONTROL=dependents.ctl LOG=dependents.log
The data in the dependents.dat file is loaded into the dependents table and the following message is displayed:
Commit point reached - logical record count 5
Information about the load is written to the log file, dependents.log. The content of the log file looks similar to the following:
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: dependents.ctl
Data File: dependents.dat
Bad File: dependents.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DEPENDENTS, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEP_ID FIRST * , O(") CHARACTER
FIRST_NAME NEXT * , O(") CHARACTER
LAST_NAME NEXT * , O(") CHARACTER
BIRTHDATE NEXT * , O(") CHARACTER
RELATION NEXT * , O(") CHARACTER
RELATIVE_ID NEXT * , O(") CHARACTER
BENEFITS NEXT * , O(") CHARACTER
Table DEPENDENTS:
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 115584 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Mon Dec 05 16:16:29 2005
Run ended on Mon Dec 05 16:16:42 2005
Elapsed time was: 00:00:12.22
CPU time was: 00:00:00.09
You can now work with the dependents table, as you would any other table.
Oracle Database XE provides the following command-line utilities for exporting and importing data:
Data Pump Export and Data Pump Import
Export and Import
The following sections provide an overview of each utility. For a summary of when you might want to use each utility, see Table 10-2.
Exporting and Importing with Data Pump Export and Data Pump Import
Exporting and Importing Data with the Export and Import Utilities
See Also:
Oracle Database Utilities for detail information on these utilities, including command line parameter descriptions and additional examples.The Data Pump Export utility exports data and metadata into a set of operating system files called a dump file set. The Data Pump Import utility imports an export dump file set into a target Oracle database.
A dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format, which means that the dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported to the same database or it can be moved to another system and loaded into the Oracle database there.
Because the dump files are written by the database, rather than by the Data Pump client application, you must create directory objects for the directories to which files will be written. A directory object is a database object that is an alias for a directory in the host operating system's file system.
Data Pump Export and Import enable you to move a subset of the data and metadata. This is done by using Data Pump parameters to specify export and import modes, as well as various filtering criteria.
You can also perform exports and imports over a network. In a network export, the data from the source database instance is written to a dump file set on the connected database instance. In a network import, a target database is loaded directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time.
Data Pump Export and Import also provide a set of interactive commands so that you can monitor and modify ongoing export and import jobs.
Note:
Data Pump Export and Data Pump Import do not support XMLType data. If you need to export and import XMLType data, use the Export and Import options described in "Exporting and Importing Data with the Export and Import Utilities".In this example, suppose that you want to make some changes to the HR sample schema and then test those changes without affecting the current HR schema. You could export the HR schema and then import it into a new HRDEV schema, where you could perform development work and conduct testing. To do this, take the following steps:
Do one of the following:
On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
At the command prompt, issue the command appropriate to your operating system, to create the directory where the exported files will be placed:
On Windows:
MKDIR c:\oraclexe\app\tmp
On Linux:
mkdir /usr/lib/oracle/xe/tmp
Start SQL Command Line (SQL*Plus) and connect as user SYSTEM by entering the following at the command prompt:
sqlplus SYSTEM/password
where password is the password that you specified for the SYS and SYSTEM user accounts upon installation (Windows) or configuration (Linux) of Oracle Database XE.
At the SQL prompt, enter the following commands to create a directory object named dmpdir for the tmp directory that you just created, and to grant read and write access to it for user HR.
On Windows:
CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp'; GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;
On Linux:
CREATE OR REPLACE DIRECTORY dmpdir AS '/usr/lib/oracle/xe/tmp'; GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;
Export the HR schema to a dump file named schema.dmp by issuing the following command at the system command prompt:
expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log
where password is the password for the SYSTEM user.
As the export operation takes place, messages similar to the following are displayed:
Export: Release 10.2.0.1.0 - Production on Tuesday, 13 December, 2005 11:48:01 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": SYSTEM/******** SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 448 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."COUNTRIES" 6.093 KB 25 rows . . exported "HR"."DEPARTMENTS" 6.640 KB 27 rows . . exported "HR"."EMPLOYEES" 15.77 KB 107 rows . . exported "HR"."JOBS" 6.609 KB 19 rows . . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows . . exported "HR"."LOCATIONS" 7.710 KB 23 rows . . exported "HR"."REGIONS" 5.296 KB 4 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: C:\ORACLEXE\APP\TMP\SCHEMA.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:48:46
The schema.dmp file and the expschema.log file are written to the dmpdir directory.
Import the dump file, schema.dmp, into another schema, in this case, HRDEV. You use the REMAP_SCHEMA command parameter to indicate that objects are to be imported into a schema other than their original schema. Because the HRDEV user account does not already exist, the import process automatically creates it. In this example, you will import everything except constraints, ref_constraints, and indexes. If a table already exists, it is replaced with the table in the export file.
At the operating system command prompt, issue the following command:
impdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp
REMAP_SCHEMA=hr:hrdev EXCLUDE=constraint, ref_constraint, index
TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log
where password is the password for the SYSTEM user.
As the import operation takes place, messages similar to the following are displayed (this output is also written to the impschema.log file in the dmpdir directory):
Import: Release 10.2.0.1.0 - Production on Tuesday, 13 December, 2005 11:49:29 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": SYSTEM/******** SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp REMAP_SCHEMA=hr:hrdev EXCLUDE=constraint, ref_constraint, index TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "HRDEV"."COUNTRIES" 6.093 KB 25 rows . . imported "HRDEV"."DEPARTMENTS" 6.640 KB 27 rows . . imported "HRDEV"."EMPLOYEES" 15.77 KB 107 rows . . imported "HRDEV"."JOBS" 6.609 KB 19 rows . . imported "HRDEV"."JOB_HISTORY" 6.585 KB 10 rows . . imported "HRDEV"."LOCATIONS" 7.710 KB 23 rows . . imported "HRDEV"."REGIONS" 5.296 KB 4 rows Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:49:49
The HRDEV schema is now populated with data from the HR schema.
Assign a password to the newly created HRDEV user account. To do so, start SQL Command Line and connect as user SYSTEM (as you did in step 4), and then at the SQL prompt, enter the following ALTER USER statement:
ALTER USER hrdev IDENTIFIED BY hrdev;
This statement assigns the password hrdev.
You can now work in the HRDEV schema without affecting your production data in the HR schema.
The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases. They are invoked with the exp and imp commands, respectively. These utilities provide support for XMLType data, whereas the Data Pump Export and Import utilities do not.
Note:
The Export and Import utilities do not support theFLOAT and DOUBLE data types. If your data contains these types and does not contain XMLType data, you must use Data Pump Export and Import, described in "Exporting and Importing with Data Pump Export and Data Pump Import".When you run the Export utility against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The dump file is an Oracle binary-format dump file that can be read only by the Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
Note:
Dump files generated by the Export (exp) utility can only be imported by the Import (imp) utility; they cannot be imported with the Data Pump Import (impdp) utility.Like Data Pump Import and Export, data exported with the Export utility can be imported with the Import utility into the same or a different Oracle database.
See Oracle Database Utilities for further information about the Export and Import utilities and for examples of how to use them.
