Guest User

Untitled

a guest
Jun 18th, 2018
111
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