Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SQL> select count(*) from t;
- COUNT(*)
- ----------
- 48283648
- SQL> set serverout on
- declare
- f utl_file.file_type;
- line varchar2(255);
- s timestamp;
- begin
- f := utl_file.fopen('TMP','demo.dat','w');
- s := systimestamp;
- for i in ( select * from t )
- loop
- line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
- utl_file.put_line(f,line);
- end loop;
- dbms_output.put_line(systimestamp-s);
- utl_file.fclose_all;
- end;
- /
- +000000000 00:07:48.436035000
- SQL> set serverout on
- declare
- f utl_file.file_type;
- line varchar2(255);
- big_line varchar2(32767);
- s timestamp;
- cnt pls_integer := 0;
- begin
- f := utl_file.fopen('TMP','demo2.dat','w',32767);
- s := systimestamp;
- for i in ( select * from t )
- loop
- line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
- cnt := cnt + 1;
- if cnt < 300 then
- big_line := big_line || line || chr(10);
- else
- utl_file.put_line(f,big_line||line);
- cnt := 0;
- big_line := null;
- end if;
- end loop;
- utl_file.put_line(f,big_line);
- dbms_output.put_line(systimestamp-s);
- utl_file.fclose_all;
- end;
- /
- +000000000 00:04:15.323634000
- $ cat t.sql
- set arraysize 5000 echo off feedback off pages 0 heading off termout off
- set markup csv on quote off
- spool /u01/app/oracle/demo3.dat
- select object_id, object_name, object_type, data_object_id from t;
- spool off
- exit
- $ time sqlplus -S bp/bp @t.sql
- real 1m32.637s
- user 1m12.632s
- sys 0m3.096s
- $ du -b /u01/app/oracle/demo*
- 1659592705 /u01/app/oracle/demo2.dat
- 1659592704 /u01/app/oracle/demo3.dat
- 1659592704 /u01/app/oracle/demo.dat
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement