sool导出

平时需要导出手机号或短信数据到本地txt或excel，使用pl/ sql的csv特别慢，可以使用spool脚本去执行，步骤如下：
1、oracle用户登录192.168.1.172服务器
2、sqlplus /nolog
3、conn username/ password
4、执行脚本：
/*set colsep ','
set heading off
spool /home/oracle/shuyun_desmobile.txt; 
select desmobile from qxtbak_10 t 
where t.user_id='10100267' 
and substr(log_id,1,6)>='20161020'
spool off;*/
5、数据全部导入到/home/oracle/目录下的temp_desmobile.txt中








----------------------------------------------------------
set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile02.txt; 
select desmobile from  (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=6000000) where rn >3000000;
spool off;


-------------------------------------------------------


set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile03.txt; 
select desmobile from  (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=9000000 ) where rn >6000000;
spool off;
-----------------------------------------------------
set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile04.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=12000000 ) where rn >9000000;
spool off 
-----------------------------------------------------






set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile05.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=15000000 ) where rn >12000000;
spool off 
-----------------------------------------------------


set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile06.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=18000000 ) where rn >15000000;
spool off 
-----------------------------------------------------


set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile07.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=21000000 ) where rn >18000000;
spool off 
-----------------------------------------------------


set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile08.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=24000000 ) where rn >21000000;
spool off 
-----------------------------------------------------


set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile09.txt; 
select desmobile from (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
and rownum<=27000000 ) where rn >24000000;
spool off 
-----------------------------------------------------
set colsep ''
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool /home/oracle/m5_mryx_desmobile10.txt; 
select desmobile from  (select desmobile,rownum rn from logdata_1705_bak t where t.user_id =120032315 and rcode ='0'
) where rn >27000000;
spool off;