linux文件拆分split说明与案例

一、问题描述：

今天接到个活，要将一个很大的sql脚本导入数据库，谁可想这个脚本最大的10G多，小的也要4，5G，好吧，由于脚本中的SQL语句又太长，所以还要对文件里进行修改，在其中增加回车。方法使用sed或perl，这两个命令就不在这里介绍了，需要的话去看我的另一篇博客。可是就是在用到sed这样的命令时报错：sed: couldn't write 4700 items to ./sed2nIvq6: No space left on device ，上网找了些文档，有的说是inode问题，当然我也不是很清楚啦，网上有人认为是cache问题，重启动机器还是一样报错。我认为有可能是文件过大造成的吧，于是我就想到了拆分文件的方式来测试一下。果不其然被我说中了，拆分后的文档可以顺利使用sed命令完成我的需求。

二、split说明

1.功能：

将文件分割成多个

2.语法：

split [OPTION]... [INPUT [PREFIX]]

3.描述：

将输入文件'INPUT'分割，输出固定大小的文件片段"PREFIXaa,PREFIXab,..."，默认大小为1000行，并且PREFIX的默认值为'x'，如果没有指定输入文件(INPUT文件)，或者输入为-，那么会从标准输入读取。

4.常用参数

    -a, --suffix-length=N

    使用的后缀的长度，默认长度为2，例如'aa','ab'等后缀，其长度就是2。

    -b, --bytes=SIZE

    指定每个输出文件片段的大小。

    -C, --line-bytes=SIZE

    指定每个输出文件片段的最大行字节(???)。

    -d, --numeric-suffixes

    指定输出文件片段后缀为数字而不是默认的字母形式。

    -l, --lines=NUMBER

    指定每个输出文件片段的行数。

以上，指定的大小"SIZE"可以用如下的方式指定单位：后缀为b表示512字节，后缀为k表示1K，后缀为m表示1M。

三、实验

1.检查文件，些文件约5.5G大小，注意将要分割的文件放在一个空的目录中

[oracle@hcrdb test]$ pwd

/home/oracle/test

[oracle@hcrdb test]$ ls -l

总用量 5730164

-rw-r--r-- 1 oracle oinstall 5867680117 2月   2 16:31 BC_M_SELLER.sql

[oracle@hcrdb test]$ du -h

5.5G    .

2.分割文件并验证

[oracle@hcrdb test]$ split -b 1024m BC_M_SELLER.sql 1

[oracle@hcrdb test]$ ls -l 1*

-rw-r--r-- 1 oracle oinstall 1073741824 2月   5 16:45 1aa

-rw-r--r-- 1 oracle oinstall 1073741824 2月   5 16:45 1ab

-rw-r--r-- 1 oracle oinstall 1073741824 2月   5 16:46 1ac

-rw-r--r-- 1 oracle oinstall 1073741824 2月   5 16:47 1ad

-rw-r--r-- 1 oracle oinstall 1073741824 2月   5 16:47 1ae

-rw-r--r-- 1 oracle oinstall  498970997 2月   5 16:47 1af

3.注意事宜

因为会产生多个文件，所以需要在空目录里来生成新的文件。由于此次是个SQL脚本，按照大小来分割还要注意其中两个文件的连接处，不会按照SQL语句来分割，所以还需要人工处理一下，我的操作是看一下1aa文件的结尾部分，找到SQL语句的开始，再看一下1ab文件的开始部分找到SQL语句的后半部分，使这个语句完成后再数据库中执行并COMMIT。下面看一下我的操作：

4.合并SQL语句操作

（1）查看1aa文件尾部最后一条SQL语句

[oracle@hcrdb test]$ tail -1 1aa

Insert into RZ2Q_BUS.BC_M_SELLER (SELLER_TAXNO,YEARMONTH,YEAR,MONTH,STAT_TIME,ORDER_A,ORDER_C,UORDER_A,UORDER_C,DELIVER_A,DELIVER_C,RECEIVE_A,RECEIVE_C,RETURN_A,RETURN_C,INVOICE_A,INVOICE_C,INVOICE_NOTAX_A,PAY_A,PAY_C,URECEIVE_A,URECEIVE_C,URETURN_A,URETURN_C,SETTLE_A,SETTLE_C,USETTLE_A,USETTLE_C,OINVOICE_A,OINVOICE_C,OINVOICE_NOTAX_A,ORDER_YA,ORDER_YC,UORDER_YA,UORDER_YC,DELIVER_YA,DELIVER_YC,RECEIVE_YA,RECEIVE_YC,RETURN_YA,RETURN_YC,INVOICE_YA,INVOICE_YC,INVOICE_NOTAX_YA,PAY_YA,PAY_YC,URECEIVE_YA,URECEIVE_YC,URETURN_YA,URETURN_YC,SETTLE_YA,SETTLE_YC,USETTLE_YA,USETTLE_YC,OINVOICE_YA,OINVOICE_YC,OINVOICE_NOTAX_YA,ORDER_A_YOY,ORDER_C_YOY,UORDER_A_YOY,UORDER_C_YOY,DELIVER_A_YOY,DELIVER_C_YOY,RECEIVE_A_YOY,RECEIVE_C_YOY,RETURN_A_YOY,RETURN_C_YOY,INVOICE_A_YOY,INVOICE_C_YOY,INVOICE_NOTAX_A_YOY,PAY_A_YOY,PAY_C_YOY,URECEIVE_A_YOY,URECEIVE_C_YOY,URETURN_A_YOY,URETURN_C_YOY,SETTLE_A_YOY,SETTLE_C_YOY,USETTLE_A_YOY,USETTLE_C_YOY,OINVOICE_A_YOY,OINVOICE_C_YOY,OINVOICE_NOTAX_A_YOY,ORDER_YA_YOY,ORDER_YC_YOY,UORDER_YA_YOY,UORDER_YC_YOY,DELIVER_YA_YOY,DELIVER_YC_YOY,RECEIVE_YA_YOY,RECEIVE_YC_YOY,RETURN_YA_YOY,RETURN_YC_YOY,INVOICE_YA_YOY,INVOICE_YC_YOY,INVOICE_NOTAX_YA_YOY,PAY_YA_YOY,PAY_YC_YOY,URECEIVE_YA_YOY,URECEIVE_YC_YOY,URETURN_YA_YOY,URETURN_YC_YOY,SETTLE_YA_YOY,SETTLE_YC_YOY,USETTLE_YA_YOY,USETTLE_YC_YOY,OINVOICE_YA_YOY,OINVOICE_YC_YOY,OINVOICE_NOTAX_YA_YOY,SKINVOICE_A,SKINVOICE_C,ALLINVOICE_A,ALLINVOICE_C,ALLINVOICE_YA,ALLINVOICE_YC,ALLINVOICE_A_YOY,ALLINVOICE_C_YOY,ALLINVOICE_YA_YOY,ALLINVOICE_YC_YOY,SKINVOICE_NOTAX_A,SKINVOICE_NOTAX_C,ALLINVOICE_NOTAX_A,ALLINVOICE_NOTAX_C,ALLINVOICE_NOTAX_YA,ALLINVOICE_NOTAX_YC,ALLINVOICE_NOTAX_A_YOY,ALLINVOICE_NOTAX_C_YOY,ALLINVOICE_NOTAX_YA_YOY,ALLINVOICE_NOTAX_YC_YOY,SKOINVOICE_A,SKOINVOICE_C,ALLOINVOICE_A,ALLOINVOICE_C,ALLOINVOICE_YA,ALLOINVOICE_YC,ALLOINVOICE_A_YOY,ALLOINVOICE_C_YOY,ALLOINVOICE_YA_YOY,ALLOINVOICE_YC_YOY,SKOINVOICE_NOTAX_A,ALLOINVOICE_NOTAX_A,ALLOINVOICE_NOTAX_YA,ALLOINVOICE_NOTAX_A_YOY,ALLOINVOICE_NOTAX_YA_YOY,ORDER_A_CGR,ORDER_C_CGR,UORDER_A_CGR,UORDER_C_CGR,DELIVER_A_CGR,DELIVER_C_CGR,RECEIVE_A_CGR,RECEIVE_C_CGR,RETURN_A_CGR,RETURN_C_CGR,INVOICE_A_CGR,INVOICE_C_CGR,PAY_A_CGR,PAY_C_CGR,URECEIVE_A_CGR,URECEIVE_C_CGR,URETURN_A_CGR,URETURN_C_CGR,SETTLE_A_CGR,SETTLE_C_CGR,USETTLE_A_CGR,USETTLE_C_CGR,OINVOICE_A_CGR,OINVOICE_C_CGR,MC_ORDER,MC_UORDER,MC_DELIVER,MC_RECEIVE,MC_RETURN,MC_INVOICE,MC_PAY,MC_URECEIVE,MC_URETURN,MC_SETTLE,MC_USETTLE,MC_OINVOICE,MS_ORDER,MS_UORDER,MS_DELIVER,MS_RECEIVE,MS_RETURN,MS_INVOICE,MS_PAY,MS_URECEIVE,MS_URETURN,MS_SETTLE,MS_USETTLE,MS_OINVOICE) values ('12010700DK10729','2014-06',2014,6,to_date('04-11-15','DD-MON-RR'),0,0,0,0,0,0,0,0,0,0,10713,2,10400.98,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,22814,4,nu[oracle@hcrdb test]$

（2）查看1ab文件头部SQL语句

[oracle@hcrdb test]$ head -1 1ab

ll,0,0,0,0,0,0,0,0,0,0,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,10713,2,22814,4,null,null,null,null,null,null,10400.98,null,22149.52,null,null,null,null,null,null,null,0,0,0,0,null,null,null,null,null,0,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

（3）将两部分黄色语句合成的完整SQL语句

Insert into RZ2Q_BUS.BC_M_SELLER (SELLER_TAXNO,YEARMONTH,YEAR,MONTH,STAT_TIME,ORDER_A,ORDER_C,UORDER_A,UORDER_C,DELIVER_A,DELIVER_C,RECEIVE_A,RECEIVE_C,RETURN_A,RETURN_C,INVOICE_A,INVOICE_C,INVOICE_NOTAX_A,PAY_A,PAY_C,URECEIVE_A,URECEIVE_C,URETURN_A,URETURN_C,SETTLE_A,SETTLE_C,USETTLE_A,USETTLE_C,OINVOICE_A,OINVOICE_C,OINVOICE_NOTAX_A,ORDER_YA,ORDER_YC,UORDER_YA,UORDER_YC,DELIVER_YA,DELIVER_YC,RECEIVE_YA,RECEIVE_YC,RETURN_YA,RETURN_YC,INVOICE_YA,INVOICE_YC,INVOICE_NOTAX_YA,PAY_YA,PAY_YC,URECEIVE_YA,URECEIVE_YC,URETURN_YA,URETURN_YC,SETTLE_YA,SETTLE_YC,USETTLE_YA,USETTLE_YC,OINVOICE_YA,OINVOICE_YC,OINVOICE_NOTAX_YA,ORDER_A_YOY,ORDER_C_YOY,UORDER_A_YOY,UORDER_C_YOY,DELIVER_A_YOY,DELIVER_C_YOY,RECEIVE_A_YOY,RECEIVE_C_YOY,RETURN_A_YOY,RETURN_C_YOY,INVOICE_A_YOY,INVOICE_C_YOY,INVOICE_NOTAX_A_YOY,PAY_A_YOY,PAY_C_YOY,URECEIVE_A_YOY,URECEIVE_C_YOY,URETURN_A_YOY,URETURN_C_YOY,SETTLE_A_YOY,SETTLE_C_YOY,USETTLE_A_YOY,USETTLE_C_YOY,OINVOICE_A_YOY,OINVOICE_C_YOY,OINVOICE_NOTAX_A_YOY,ORDER_YA_YOY,ORDER_YC_YOY,UORDER_YA_YOY,UORDER_YC_YOY,DELIVER_YA_YOY,DELIVER_YC_YOY,RECEIVE_YA_YOY,RECEIVE_YC_YOY,RETURN_YA_YOY,RETURN_YC_YOY,INVOICE_YA_YOY,INVOICE_YC_YOY,INVOICE_NOTAX_YA_YOY,PAY_YA_YOY,PAY_YC_YOY,URECEIVE_YA_YOY,URECEIVE_YC_YOY,URETURN_YA_YOY,URETURN_YC_YOY,SETTLE_YA_YOY,SETTLE_YC_YOY,USETTLE_YA_YOY,USETTLE_YC_YOY,OINVOICE_YA_YOY,OINVOICE_YC_YOY,OINVOICE_NOTAX_YA_YOY,SKINVOICE_A,SKINVOICE_C,ALLINVOICE_A,ALLINVOICE_C,ALLINVOICE_YA,ALLINVOICE_YC,ALLINVOICE_A_YOY,ALLINVOICE_C_YOY,ALLINVOICE_YA_YOY,ALLINVOICE_YC_YOY,SKINVOICE_NOTAX_A,SKINVOICE_NOTAX_C,ALLINVOICE_NOTAX_A,ALLINVOICE_NOTAX_C,ALLINVOICE_NOTAX_YA,ALLINVOICE_NOTAX_YC,ALLINVOICE_NOTAX_A_YOY,ALLINVOICE_NOTAX_C_YOY,ALLINVOICE_NOTAX_YA_YOY,ALLINVOICE_NOTAX_YC_YOY,SKOINVOICE_A,SKOINVOICE_C,ALLOINVOICE_A,ALLOINVOICE_C,ALLOINVOICE_YA,ALLOINVOICE_YC,ALLOINVOICE_A_YOY,ALLOINVOICE_C_YOY,ALLOINVOICE_YA_YOY,ALLOINVOICE_YC_YOY,SKOINVOICE_NOTAX_A,ALLOINVOICE_NOTAX_A,ALLOINVOICE_NOTAX_YA,ALLOINVOICE_NOTAX_A_YOY,ALLOINVOICE_NOTAX_YA_YOY,ORDER_A_CGR,ORDER_C_CGR,UORDER_A_CGR,UORDER_C_CGR,DELIVER_A_CGR,DELIVER_C_CGR,RECEIVE_A_CGR,RECEIVE_C_CGR,RETURN_A_CGR,RETURN_C_CGR,INVOICE_A_CGR,INVOICE_C_CGR,PAY_A_CGR,PAY_C_CGR,URECEIVE_A_CGR,URECEIVE_C_CGR,URETURN_A_CGR,URETURN_C_CGR,SETTLE_A_CGR,SETTLE_C_CGR,USETTLE_A_CGR,USETTLE_C_CGR,OINVOICE_A_CGR,OINVOICE_C_CGR,MC_ORDER,MC_UORDER,MC_DELIVER,MC_RECEIVE,MC_RETURN,MC_INVOICE,MC_PAY,MC_URECEIVE,MC_URETURN,MC_SETTLE,MC_USETTLE,MC_OINVOICE,MS_ORDER,MS_UORDER,MS_DELIVER,MS_RECEIVE,MS_RETURN,MS_INVOICE,MS_PAY,MS_URECEIVE,MS_URETURN,MS_SETTLE,MS_USETTLE,MS_OINVOICE) values ('12010700DK10729','2014-06',2014,6,to_date('04-11-15','DD-MON-RR'),0,0,0,0,0,0,0,0,0,0,10713,2,10400.98,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,22814,4,null,0,0,0,0,0,0,0,0,0,0,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,10713,2,22814,4,null,null,null,null,null,null,10400.98,null,22149.52,null,null,null,null,null,null,null,0,0,0,0,null,null,null,null,null,0,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

四、总结

此问题是要将大的SQL脚本分割成小块，再进行执行，如果是按照SIZE来划分，一定要注意文件最后与下一个文件开头处SQL语句被拆分问题，当然要是为了避免该问题也可以采取以行为划分条件。此处就不再对以行为划分条件进行测试了。再有就是拆分文件一定要在新的文件夹中进行，以免造成与其他文件的混淆。当然对自己操作也方便些。此文中我对这一点强调了又强调，重要的事情说三遍！！！呵呵！！！

where there’s a will, there’s a way.

五、备注

官方说明

NAME

       split - split a file into pieces

SYNOPSIS

       split [OPTION]... [INPUT [PREFIX]]

DESCRIPTION

       Output  fixed-size  pieces  of INPUT to PREFIXaa, PREFIXab, ...; default size is 1000 lines,

       and default PREFIX is ‘x’.  With no INPUT, or when INPUT is -, read standard input.

       Mandatory arguments to long options are mandatory for short options too.

       -a, --suffix-length=N

              use suffixes of length N (default 2)

       -b, --bytes=SIZE

              put SIZE bytes per output file

       -C, --line-bytes=SIZE

              put at most SIZE bytes of lines per output file

       -d, --numeric-suffixes

              use numeric suffixes instead of alphabetic

       -l, --lines=NUMBER

              put NUMBER lines per output file

       --verbose

              print a diagnostic just before each output file is opened

       --help display this help and exit

       --version

              output version information and exit

       SIZE may be (or may be an integer optionally followed by) one of following: KB 1000, K 1024,

       MB 1000*1000, M 1024*1024, and so on for G, T, P, E, Z, Y.

AUTHOR

       Written by Torbjorn Granlund and Richard M. Stallman.

REPORTING BUGS

       Report split bugs to bug-coreutils@gnu.org

       GNU coreutils home page: <http://www.gnu.org/software/coreutils/>

       General help using GNU software: <http://www.gnu.org/gethelp/>

       Report split translation bugs to <http://translationproject.org/team/>

COPYRIGHT

       Copyright  © 2010 Free Software Foundation, Inc.  License GPLv3+: GNU GPL version 3 or later

       <http://gnu.org/licenses/gpl.html>.

       This is free software: you are free to change and redistribute it.  There is NO WARRANTY, to

       the extent permitted by law.

SEE ALSO

       The  full  documentation for split is maintained as a Texinfo manual.  If the info and split

       programs are properly installed at your site, the command

              info coreutils 'split invocation'

       should give you access to the complete manual.

GNU coreutils 8.4                November 2013                        SPLIT(1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26148431/viewspace-1986765/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26148431/viewspace-1986765/