Oracle基础学习笔记（三）(Import, Export, SQL Loader)

EXPORT, IMPORT are used for following tasks :

Backup Oracle data in operating system files.

Restore tables that were dropped

Save space or reduce fragmentation in the database

Move data from one owner to another

 

EXPORT used in three ways :

a)      Interactive Dialogue Mode

1.       Type EXP in CMD

2.       Enter username and connection string like : username@SID

3.       Enter password

4.       Either change the default buffer size or skip by pressing ENTER

5.       The prompt will show the name of the dump file, either change the name or skip by pressing ENTER

6.       The prompt will ask user to decide whether to export all the users data or the selective tables.

Enter U or 2 to export all the user data. Or Enter T or 3 to export the selected tables.

7.       If enter T or 3, then prompt will ask for either to export the structure alone or the table with all the rows.

8.       The prompt will ask whether to compress the EXTENT.

 

b)      Controlled through by passing parameters

1.       Type command :

EXP username/password@sid file=filename.expdat tables=(table name)

c)       Parameter File Controlled

1.       Create a parameter file ends with .TXT extension. The file will contains below code :

File=filename.expdat tables=(table name)

2.       In the command prompt, type below command and press ENTER

Exp username/password@sid parfile=filename with storage path

Ex :

Exp Scott/tiger@orcl parfile=’c:\parafile.txt’

 

Import used in three ways :

a)      Interactive Dialogue Mode

1.       Type IMP in CMD

2.       Enter username and connection string like : username@SID

3.       Enter password

4.       The prompt will show the name of the dump file, either change the name or skip by pressing ENTER

5.       The prompt will ask for the username of the schema from where you want to import data.

6.       The prompt will ask for the table that you wanted to import.

 

b)      Controlled through by passing parameters

1.       Type command :

IMP username/password@sid file=filename.expdat tables=(table name)

c)       Parameter File Controlled

1.       Create a parameter file ends with .TXT extension. The file will contains below code :

File=filename.expdat tables=(table name)

2.       In the command prompt, type below command and press ENTER

Imp username/password@sid parfile=filename with storage path

Ex :

Imp Scott/tiger@orcl parfile=’c:\parafile.txt’

 

SQL * Loader

1)      Allow one to load bulk data from a flat file into one or more database tables

2)      It takes two input files – a control file and a data file

3)      The control file contains info about the data – where to load it, what to do if something goes wrong, etc.

Step 1 : create the data file ends with .CSV

Step 2 : create control file ends with .CTL, it contains below code :

LOAD DATA

INFILE ‘C:\data.csv’

REPLACE

INTO TABLE marks

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY “”

TRAILING NULLCOLS

(

Empid INTEGER EXTERNAL, --- mention the data type of the column as NUMERIC

Quiz1 INTEGER EXTERNAL,

Grade --- the data type is string and no need to mention that

)

 

Step 3 : type below command in the CMD

Sqlldr username/password@sid CONTROL=’C:\Marks.ctl’