1

I have a 25 GB oracle table and I connecting from a server which is not DB server. I was using spool, but even after 8 hours, I am not getting any output and process was still running. I was using bewlo code-

sqlplus -s ${user}/${pass}@"(DESCRIPTION=(ADDRESS=(PROTOCOL=${protocol})(HOST=${host})(PORT=${port}))(CONNECT_DATA=(SERVER=${server})(SERVICE_NAME=${service_name})))" <
<EOF > /dev/null
set NEWPAGE NONE;
set feed off;
spool ${V_File_Name_Tmp};
set ECHO OFF;
set FEEDBACK OFF;
set VERIFY OFF;
set TERMOUT OFF;
set TRIMOUT ON;
set TRIMSPOOL ON;
set PAGESIZE 0 embedded on;
set LINESIZE 32767;
set SPACE 4;
set heading off;
set arraysize 5000;

SELECT /*+PARALLE(A,8)*/ PROFILE_ID||'|'||CO_ID||'|'||SNCODE||'|'||SPCODE_HISTNO||'|'||STATUS_HISTNO||'|'||TO_CHAR(ENTRY_DATE,'YYYY-MM-DD')||'|'||CHANNEL_NUM||'|'||OVW_
ACC_FIRST||'|'||TO_CHAR(DATE_BILLED,'YYYY-MM-DD')||'|'||SN_CLASS||'|'||OVW_SUBSCR||'|'||SUBSCRIPT||'|'||OVW_ACCESS||'|'||OVW_ACC_PRD||'|'||ACCESSFEE||'|'||CHANNEL_EXCL|
|'|'||DIS_SUBSCR||'|'||TO_CHAR(INSTALL_DATE,'YYYY-MM-DD')||'|'||TO_CHAR(TRIAL_END_DATE,'YYYY-MM-DD')||'|'||PRM_VALUE_ID||'|'||CURRENCY||'|'||SRV_TYPE||'|'||SRV_SUBTYPE|
|'|'||OVW_ADV_CHARGE||'|'||ADV_CHARGE||'|'||ADV_CHARGE_PRD||'|'||DELETE_FLAG||'|'||REC_VERSION||'|'||ATTRIB_HISTNO FROM ${v_schema}.${v_table} A;


spool off;
exit;
EOF

Can someone please help me what to do to resolve the issue? Is there other method to extract data in file other than spool?

Mohammud Ayaz Khan
  • 11
  • 2
  • 4
    The fastest way to export such a large amount of data would be to use export datapump, like with the command `expdp`. (Although that export would be in a binary format, and the file would be created on the database server and need to be copied over.) For troubleshooting the script, maybe try running the code interactively with SQL\*Plus first, and with a predicate like `where rownum <= 100`. That change will help you determine if the problem is with the SQL statement or with the script. (Also, you can remove the parallel hint. It won't help improve spooling speed, and it's spelled wrong.) – Jon Heller Aug 07 '20 at 01:19
  • 1
    You need to be careful using "sqlplus -s" and redirecting things to /dev/null, because if you have an error in your user/pass then its most likely just sitting there waiting for you to correct it ...It be better to use "sqlplus -L" which will exit immediately if you have a connection failure – Connor McDonald Aug 10 '20 at 03:17

0 Answers0