SHARE
TWEET

Untitled

a guest Jun 18th, 2018 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SQL> select count(*) from t;
  2.  
  3.   COUNT(*)
  4. ----------
  5.   48283648
  6.  
  7. SQL> set serverout on
  8. declare
  9.   f utl_file.file_type;
  10.   line varchar2(255);
  11.   s timestamp;
  12. begin
  13.   f := utl_file.fopen('TMP','demo.dat','w');
  14.   s := systimestamp;
  15.   for i in ( select * from t )
  16.   loop
  17.     line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
  18.     utl_file.put_line(f,line);
  19.   end loop;
  20.   dbms_output.put_line(systimestamp-s);
  21.   utl_file.fclose_all;
  22. end;
  23. /
  24.  
  25. +000000000 00:07:48.436035000
  26.  
  27. SQL> set serverout on
  28. declare
  29.   f utl_file.file_type;
  30.   line varchar2(255);
  31.   big_line varchar2(32767);
  32.   s timestamp;
  33.   cnt pls_integer := 0;
  34. begin
  35.   f := utl_file.fopen('TMP','demo2.dat','w',32767);
  36.   s := systimestamp;
  37.   for i in ( select * from t )
  38.   loop
  39.     line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
  40.     cnt := cnt + 1;
  41.     if cnt < 300 then
  42.        big_line := big_line || line || chr(10);
  43.     else
  44.        utl_file.put_line(f,big_line||line);
  45.        cnt := 0;
  46.        big_line := null;
  47.     end if;
  48.   end loop;
  49.   utl_file.put_line(f,big_line);
  50.   dbms_output.put_line(systimestamp-s);
  51.   utl_file.fclose_all;
  52. end;
  53. /
  54.  
  55. +000000000 00:04:15.323634000
  56.  
  57. $ cat t.sql
  58. set arraysize 5000 echo off feedback off pages 0 heading off termout off
  59. set markup csv on quote off
  60. spool /u01/app/oracle/demo3.dat
  61. select object_id, object_name, object_type, data_object_id from t;
  62. spool off
  63. exit
  64. $ time sqlplus -S bp/bp @t.sql
  65.  
  66. real    1m32.637s
  67. user    1m12.632s
  68. sys     0m3.096s
  69.  
  70. $ du -b /u01/app/oracle/demo*
  71. 1659592705      /u01/app/oracle/demo2.dat
  72. 1659592704      /u01/app/oracle/demo3.dat
  73. 1659592704      /u01/app/oracle/demo.dat
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top