需求场景:data.txt源数据:[INFO]2012-12-0100:01:171610FHR行号=24.查看指定计划的钢卷数据.计划号=121200102.[INFO]2012-12-0100:03:131610FHR行号=24.查看指
需求场景:
data.txt源数据:
[INFO] 2012-12-01 00:01:17 1610 FHR "行号=24. 查看指定计划的钢卷数据. 计划号=121200102." [INFO] 2012-12-01 00:03:13 1610 FHR "行号=24. 查看指定计划的钢卷数据. 计划号=121200103." [INFO] 2012-12-01 00:20:21 7362 RICC "IntervalTime=0,RollingTime=0" [INFO] 2012-12-01 00:20:21 7363 RICC "WRTFMOff: 24.5, 24.8, 24.9, 25.1, 25.3, 25.3, 25.4, 26.5[C]." [INFO] 2012-12-01 00:20:21 7362 RICC "IntervalTime=0, RollingTime=0, WRTFMOff:fT=24.632685"
拿到这一份源数据后,第一眼我们看到像这种类型的数据就是应该使用sql*loader工具。把该源文件的数据导入到oracle数据库。
那么,我们先来熟悉一下这个工具:
一、sql*loader的概述
很多情况之下,我们的数据需要批量处理传输到库,或者在库之间批量传输数据。其中,常见
的情形是用从事务处理系统提取的数据填充数据仓库,或将数据从实时系统复制到测试开发环境。对
大规模操作而言,使用标准的INSERT语句插入数据并不总是最佳方式,而oracle本身附带了SQL*Loader和Data Pump功能来支持批量操作。通过使用外部表,还可以在不将数据插入数据库的情况下读取数据。
从体系结构上讲,SQL*Loader进程与其他用户进程类似:它通过服务器进程连接到数据库。
要插入行,可以采用两种技术:常规方式或直接路径读取。常规方式是使用INSERT。SQL*Loader用户进程构造values子句中包含绑定变量的insert语句,然后读取源数据文件,为每一个要插入的行执行
一次insert。此方法使用数据库缓冲区缓存,并生成撤销(undo)和重做数据(redo log),这些insert
语句与其他同类语句相似,通过普通的提交处理实现数据永久化。
直接路径是避开了database buffer,sql*loader读取源数据文件,并将内容发送到服务器进程。
此后,美国服务器,服务器进程在其PGA中组装表数据中的块,并将它们直接写入数据文件。写操作在表的高水位
线上完成,称为数据保存(data save)。高水位线是表段中的一个标记,其上未写入任何数据:高水
位线上的空间是分配给尚未使用的表的空间。加载完成后,服务器空间,sql*loader移动高水位线,免备案空间,从而包含最新
写入的数据块,并且其他用户都可以立即看见这些数据块内的行。上述操作相当于一个commit命令。
此时不会生成撤销,也可以主动的取消重做日志的生成。所以说,直接路径加载是很快捷的。
二、SQL*Loader直接路径缺点
直接路径也存在以下缺点:
1、执行操作期间,必须删除或禁用引用完整性约束(只能实施unique、primary key、not null约束)。
2、会将针对其他会话的DML锁定表
3、不会激活insert触发器
4、无法为群集表使用
sql*loader体系结构:
三、实际操作演示
好了,这里没办法说得太细节化,相关的知识大家自己补充。
新建一个测试表TEST(我们要把源数据导入这个表):
SQL>conn hr/oracle@testdb
SQL> create table test(
2 name varchar2(10),
3 indate varchar2(20),
4 intime varchar2(20),
5 num int,
6 source varchar2(10),
7 description varchar2(128)
8 );
准备好data.txt、data_test.ctl(加载所要使用的控制文件,定义数据规则的)
data_test.ctl:
sqlldr这个命令就是sql*loader工具,是在$ORACLE_HOME/bin目录下
C:\Documents and Settings\Administrator>sqlldr hr/oracle@testdb control=E:\oracle\exercises\data_test.ctl log=E:\oracle\exercises\data_test.log
3 小时前 上传
下载附件(39.43 KB)
查询下结果:
3 小时前 上传
下载附件(39.34 KB)
到此为止,提出的需求已经完成。关于控制文件里面的命令是什么意思,有时间的话下次解释一下或者留点给大家自己去学习吧。希望大家可以相互参与到讨论当中,相互提高自己!
本文出自 “海斌的技术博客” 博客,转载请与作者联系!

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download
The most popular open source editor

WebStorm Mac version
Useful JavaScript development tools
