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

原创 2012年03月30日 13:47:16

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’

相关文章推荐

【DB.Oracle】PL/SQL Developer export/import excel 数据

导入/导出 表数据 from/into excel(csv)文件

Oracle数据库PL/SQL学习笔记二——基础控制语句

--简单的if判断begin if &var > 10 and &var 50 then dbms_output.put_line('输入值大于50'); else ...

Oracle11g基础知识【SQL学习笔记】

下面的命令都是在Oracle11g下操作的。 SQLplus:Oracle的客户端 Oracle进入方式: ①cmd命令行 ②SQLplus ③浏览器 1. 查询语句(select语句)【重要...

Java学习笔记07——Oracle数据库SQL开发基础

一、Oracle数据库系统搭建 (1)下载安装:将下载的两个压缩文件解压到同一文件夹下,仅安装数据库软件,单实例数据库安装,企业版,Oracle基目录/主目录(软件位置)。 (2)数据库创建:Da...
  • okaeri
  • okaeri
  • 2017年07月04日 14:04
  • 115

Oracle PL/SQL语句基础学习笔记(下)

oracle游标学习笔记

Oracle学习笔记(二十一)——pl/sql基础

一、pl/sql基础 1、pl/sql(procedural language/sql):是oracle在标准的sql语言上的扩展。 不仅允许嵌入sql语言,还可以定义变量和常量。允许使用条件语句和...

Oracle学习笔记——PL/SQL编程基础

PL/SQL编程基础: 1.PL/SQL块基本结构: declare    (可选项,用于声明变量) …… begin …… exception (异常处理) …… end / 2...

[oracle学习笔记]之一:sql/plus基础操作

oracle学习笔记1、表空间分类:永久表空间,临时表空间,UNDO表空间。查看用户的表空间,这有两个数据字典: dba_tablespaces 管理员用户级别查看;...

Oracle11g基础知识2【SQL学习笔记】

表连接: SQL> select ename, sal from emp 2 join (select max(sal) max_sal, deptno from emp group by de...

SQL_import_export_tool.rar

  • 2015年12月21日 10:38
  • 2.03MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle基础学习笔记(三)(Import, Export, SQL Loader)
举报原因:
原因补充:

(最多只允许输入30个字)