Oracle基础学习笔记(三)(Import, Export, SQL Loader)
来源:互联网 发布:人工智能的发展方向 编辑:程序博客网 时间:2024/05/29 06:49
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’
- Oracle基础学习笔记(三)(Import, Export, SQL Loader)
- Oracle工具使用(export,import,sql…
- Oracle Export,Import
- Oracle export,import
- Oracle import/export
- Oracle import and export
- oracle import/export 命令
- Oracle学习笔记三:SQL语言基础(上)
- Oracle基础学习笔记(三)
- Oracle PL/SQL 学习笔记(三)
- Oracle学习笔记(三)PL/SQL
- 【DB.Oracle】PL/SQL Developer export/import excel 数据
- Oracle SQL*Loader 学习实例
- Oracle SQL*Loader学习例子
- Difference between SQL*Loader & IMPORT utilities in Oracle
- ES6基础--import和export
- oracle Export and Import 简介
- oracle Export and Import 简介
- 分享通用基类库-转载微软的数据操作类SQLHELP
- 系统保存客户操作历史记录
- c# socket流转对象
- 国内代理软件CCproxy软件使用方法(图解)
- 利用隐藏参数_asm_allow_only_raw_disks创建ASM环境
- Oracle基础学习笔记(三)(Import, Export, SQL Loader)
- 分享通用基类库-C#重写微软的SQLHELP的数据库操作基类
- Linux下进程的“终结者”
- 冒泡排序和日期代码
- 9.PNG 图片
- php SQL 注入
- Fedora 16上源码建立pydev + eclipse的OpenStack开发环境笔记草稿 ( by quqi99 )
- 李开复--自定人生
- 怎样在SSRS中根据固定行数分页 pagebreaker