Oracle data files (4)

1. DMP file (imp/EXP)

1. The DMP file is backward compatible, indicating that the new version can read the old version of DMP.
2. For DMP file rules: the Export version of the DMP file must be smaller than or equal to the Import version of the DMP file.
3. DMP files (create/alter Statement)
4. Pay attention to DMP file transmission

• There is a warning for FTP transmission of WINDOWS and files. By default, windows regards the DMP file as a "text" file and linefeeds (unix marks the last line) convert to carriage return/line feed, which will completely damage the DMP file.
• When transferring DMP files through FTP in windows, make sure that binary transmission is performed.

Ii. Data Pump files

1. At least two tools in Oracle10g use the data pump file format.

• External tables can load and unload data in pump format
• New Import and Export tools IMPDP/EXPDP

N Note: The data pump format can only be available in oracle10 release1 and later versions, and oracle release cannot be used

1. Data of the Data Pump File

• They are all cross-platform (portable) binary files that contain metadata (XML storage) and may also contain data. Complex filtering/conversion functions can be implemented. To some extent, this is due to the use of XML

1. External tables were first introduced in oracle9i release 1

• Using External tables, we can read unformatted text files in the operating system just like reading database tables.
• SQL is used to process external tables. External tables are read-only and are designed to provide external data to oracle.

1. External tables in Oracle 10grelease1 and later versions can also be used

• Used to retrieve data from the database in the Data Pump format
• Example:

N create directory: create or replace directory gull as 'd: \ dir'

N export External table:

Create table all_objects_unload

Organization external

(Type oracle_datapump

Default directory gll

Location ('allobjects. dat '))

As select * from all_objects

N create a table pointing to external data

Create table gulltt

(Owner varchar2 (30 ),

Object_name varchar2 (30)

)

Organization external

(Type oracle_datapump

Default directory gll

Location ('allobjects. dat '))

N if the field or field type is incorrect, the corresponding