Oracle Import and Export
发表于:2024-10-10 作者:千家信息网编辑
千家信息网最后更新 2024年10月10日,Chapter:SQL*LoaderLab1.Import text file to databaseassume text file is like this: 1: 60,CONSULTING
千家信息网最后更新 2024年10月10日Oracle Import and Export
Chapter:SQL*Loader
Lab1.Import text file to database
assume text file is like this:
1: 60,CONSULTING,TORONTO
2: 70,HR,OXFORD
3: 80,EDUCATION,
Then user can write a control file of import as following:
1: LOAD DATA2: INFILE 'depts.txt'3: BADFILE 'depts.bad'4: DISCARDFILE 'depts.dsc'5: APPEND
6: INTO TABLE DEPT7: FILEDS TERMINATED BY ','8: TRAILING NULLCOLS9: (DEPTNO INTEGER EXTERNAL(2),10: DNAME,
11: LOC)
execute OS command:
1: sqlldr control=depts.ctl log=depts.log
results of select:1: SQL> select * from iolab.dept;2:
3: DEPTNO DNAME LOC
4: ---------- -------------------- ----------5: 60 CONSULTING TORONTO
6: 70 HR OXFORD
7: 80 EDUCATION
Hints:One can use method of "Direct Path" to load data from text file.It load content from text file and write it to datafile directly,not like normal way of generating SQL sentences to insert every row to tables.
Lab2.External table
Function:It uses textfile on OS to be queried by database and it can't be modified by database.
- Create directory object
1: CREATE DIRECTORY IOLABDIR AS '/u01/app/oracle/iolab';2: GRANT READ,WRITE ON DIRECTORY IOLABDIR TO IOLAB;
Create text file
1: John,Watson
2: Roopesh,Ramklass
3: Sam,Alapati
- Edit control file of import
1: LOAD DATA2: INFILE 'names.txt'3: BADFILE 'names.bad'4: DISCARD 'names.dsc'5: TRUNCATE6: INTO TABLE NAMES7: FIELDS TERMINATED BY ','8: TRAILING NULLCOLS9: (FIRST,LAST)
Execute OS command
1: sqlldr iolab/iolab control=names.ctl log=names.log external_table=generate_only;
View the log and get the model of "CREATE EXTERNAL TABLE"
1: CREATE TABLE "SYS_SQLLDR_X_EXT_NAMES"2: (
3: "FIRST" CHAR(20),4: "LAST" CHAR(20)5: )
6: ORGANIZATION external7: (
8: TYPE oracle_loader
9: DEFAULT DIRECTORY IOLABDIR10: ACCESS PARAMETERS11: (
12: RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF813: BADFILE 'IOLABDIR':'names.bad'14: DISCARDFILE 'IOLABDIR':'names.dsc'15: LOGFILE 'names.log_xt'16: READSIZE 1048576
17: FIELDS TERMINATED BY "," LDRTRIM18: MISSING FIELD VALUES ARE NULL19: REJECT ROWS WITH ALL NULL FIELDS20: (
21: "FIRST" CHAR(255)22: TERMINATED BY ",",23: "LAST" CHAR(255)24: TERMINATED BY ","25: )
26: )
27: location
28: (
29: 'names.txt'30: )
31: )REJECT LIMIT UNLIMITED
- Edit it as you like and then create external table
- Results of query
1: SQL> select * from names;2:
3: FIRST LAST4: -------------------- --------------------5: John Watson
6: Roopesh Ramklass
7: Sam Alapati
Chapter:Data Pump(summary)
Function:Data Pump utilites can import and export data from or to oracle-exclusive file.
Export to file(The directory object should exist)
1: expdp system/manager@orcl11g full=y dumpfile=datadir:full_%U.dmp filesize=2G compression=all
- Import from file(The directory object should exist)
1: impdp system/manager@orcl11g full=y directory=samba_dir dumpfile=full_%U.dmp
Transport tablespace(The outline)
1: SQL 'ALTER TABLESPACE XXX OFFLINE/READONLY' ON SOURCE HOST2: EXPORT METADATA OF THE TABLESPACE BY DATAPUMP3: COPY DATAFILES AND METADATA FILES OF THE TABLESPACE TO DESTINATE DATABASE4: IMPORT METADATA AND DATAFILES OF THE TABLESPACE BY DATAPUMP ON DESTINATE HOST5: SQL 'ALTER TABLESPACE XXX ONLINE' ON SOURCE HOST
Hints:When it's performing transporting tablespace,operator should focus on ENDIAN_FORMAT on different platform.If the source and the destination databases' ENDIAN_FORMAT are not matched,operator would use RMAN to convert datafile.As an example:RMAN> convert datafile '/u02/ttsfiles/ts1.dbf' from platform='Linux IA (32-bit)' format '/u02/ttsfiles/ts1conv.dbf'the characters of platform is referred by query 'SELECT * FROM TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME';
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发管理分析模型
es文件管理进服务器
连云港网络技术教育
网页数据库录入软件
obs直播设置服务器在哪找
深信服网络技术服务底薪
政法单位网络安全的整体状况判断
宁夏飞跃网络技术
计算机网络技术包含两个
网络安全你我同行
实况足球手游初始化服务器
新城租房软件开发
简幻欢服务器为什么不能设管理员
软件开发专业有考研吗
成都app软件开发一般要多少钱
网络安全涉及哪六个方面
奉化财务软件开发选哪家
上海瑞庭网络技术杭州分公司
易信网络技术开发有限公司
运维和数据库管理
三级网络技术路由器配置
欧美国家掌握6g网络技术
国家对企业网络安全强制要求
网络安全与执法招生
电脑不能自动上传数据到服务器
网络安全防护管理措施
sql学生管理数据库查询
数据库密码字段如何快速查询
hana数据库事务
服务器内存条2400t什么意思