spool myfile
select * from emp;
spool off

SET SERVEROUTPUT ON

DECLARE
	v_file UTL_FILE.FILE_TYPE;
	v_buffer  VARCHAR2(32767);
	v_name    VARCHAR2(128) := 'utl_file_buffered.txt';
	v_lines   PLS_INTEGER := 0;
	c_eol     CONSTANT VARCHAR2(1) := CHR(10);
	c_eollen  CONSTANT PLS_INTEGER := LENGTH(c_eol);
	c_maxline CONSTANT PLS_INTEGER := 32767;
BEGIN
	v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);

	FOR r IN (SELECT u || ',' || v || ',' || w || ',' || x || ',' || y || ',' || z as csv FROM tbl1)
	LOOP
		IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
			v_buffer := v_buffer || c_eol || r.csv;
		ELSE
			IF v_buffer IS NOT NULL THEN
				UTL_FILE.PUT_LINE(v_file, v_buffer);
			END IF;
			v_buffer := r.csv;
		END IF;
 
 		v_lines := v_lines + 1;

	END LOOP;

	UTL_FILE.PUT_LINE(v_file, v_buffer);
	UTL_FILE.FCLOSE(v_file);

	DBMS_OUTPUT.PUT_LINE('File='||v_name||'; Lines='||v_lines);

END;
/

set colsep ','
spool unload.csv
 select * from tbl_1;
spool off
exit
/

SQL> set autotrace traceonly statistics
SQL> select * from dual;

1 row selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

[xyz@xyz-training ~]$ cat unload.sql
set colsep ','
spool unload.csv
 select * from tbl_1;
spool off
exit
/[xyz@xyz-training ~]$

[xyz@xyz-training ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 10 09:11:27 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn username@xe
Enter password:
Connected.
SQL> @unload.sql

3000000 rows selected.


Statistics
----------------------------------------------------------
        198  recursive calls
          0  db block gets
     237715  consistent gets
      40253  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$

[xyz@xyz-training ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 10 10:13:54 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn username@xe
Enter password:
Connected.
SQL> @unload.sql
SQL> set autotrace traceonly statistics
SQL>
SQL> set colsep ','
SQL> spool unload.csv
SQL>  select * from tbl_1;

3000000 rows selected.


Statistics
----------------------------------------------------------
        534  recursive calls
          0  db block gets
     237766  consistent gets
      40261  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$

[xyz@xyz-training ~]$ cat unload.csv
SQL>  select * from tbl_1;

3000000 rows selected.


Statistics
----------------------------------------------------------
        534  recursive calls
          0  db block gets
     237766  consistent gets
      40261  physical reads
          0  redo size
  217141868  bytes sent via SQL*Net to client
    2200374  bytes received via SQL*Net from client
     200001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    3000000  rows processed

SQL> spool off
[xyz@xyz-training ~]$

3000000 rows selected.

SP2-0308: cannot close spool file
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[xyz@xyz-training ~]$

[oracle@localhost bin]$ oerr sp2 308
00308,0, "cannot close spool file\n"
// *Cause:  The file is currently being used.
// *Action: Release the file from the other process.