OraFAQ Forum: SQL & PL/SQL » Missing Lines In A Spool File...

Home » SQL & PL/SQL » SQL & PL/SQL » Missing Lines In A Spool File...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Missing Lines In A Spool File... [message #252101]

Tue, 17 July 2007 19:55

aimy
Messages: 225
Registered: June 2006

Senior Member

Hi all I use this script to get a flat file..

set linesize 369;
SET NEWPAGE 0
SET SPACE 0
set pagesize 0;
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
set echo off;
set wrap off
set trimspool on
set TRIMOUT on
set TAB ON
set autocommit on;

spool /tmw/oradata18/POC/flat_20070607_v2.data

SELECT YEAR_MONTH_DATE||'|'|| BILL_PERIOD||'|'|| PARTITION_VALUES||'|'|| ACCOUNT_NBR||'|'|| SERVICE_NBR||'|'||
SERVICE_TYPE||'|'|| BILLING_CLASS_CODE||'|'|| MTX_AREA_CODE||'|'|| CALL_START_DATE||'|'|| CALL_START_TIME_HH||'|'||
CALL_START_TIME_MM||'|'|| CALL_START_TIME_SS||'|'|| CALL_DURATION_HH||'|'|| CALL_DURATION_MM||'|'||
CALL_DURATION_SS||'|'|| TERMINATING_NBR||'|'|| CALLED_PLACE||'|'|| CALL_TYPE||'|'|| CALL_CLASS_CODE||'|'||
TOLL_CHARGE_CODE||'|'|| TOLL_GROSS_CHARGE||'|'|| TOLL_DISCOUNT_AMT||'|'|| TOLL_NET_CHARGE||'|'||
TOLL_SURCHARGES||'|'|| CS_FULL_CHG_AMT||'|'|| CS_CHG_AMT||'|'|| DISCOUNT_CODE||'|'|| RATE_BAND_CODE||'|'||SOURCE 
/*+ parallel(tel_calldtl partition(bl_period_0607),4) */ 
from tel_calldtl partition(bl_period_0607) t
where year_month_date= '200706' and bill_period='07';

The total no of records should be 28800065

SELECT count(*)
/*+ parallel(tel_calldtl partition(bl_period_0607),4) */ 
from tel_calldtl partition(bl_period_0607) t
where year_month_date= '200706' and bill_period='07';

But when I count the total no. of lines from the spool file.. I only get 28799889 Sad

$ wc -l flat_20070607_v2.data
28799889 flat_20070607_v2.data

Meaning that, 176 lines are missing. So, where do I gone wrong??

Is it because I forgot to spool off the file? Or is it has anything to do with the sqlplus setting that eliminates the blank lines?

Please please..

Thank you so much.

[Updated on: Tue, 17 July 2007 19:57]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252105 is a reply to message #252101]

Tue, 17 July 2007 20:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Or is it has anything to do with the sqlplus setting that eliminates the blank lines?
WHAT blank lines?

What happens when you remove the misplaced "hint"?

CUT & PASTE doing:
wc -l /tmw/oradata18/POC/flat_20070607_v2.data
grep 200706|07 /tmw/oradata18/POC/flat_20070607_v2.data | wc -l

[Updated on: Tue, 17 July 2007 20:51] by Moderator

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252109 is a reply to message #252105]

Tue, 17 July 2007 21:08

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 09:45

>Or is it has anything to do with the sqlplus setting that eliminates the blank lines?
WHAT blank lines?

What happens when you remove the misplaced "hint"?

CUT & PASTE doing:
wc -l /tmw/oradata18/POC/flat_20070607_v2.data
grep 200706|07 /tmw/oradata18/POC/flat_20070607_v2.data | wc -l

What I mean is that.. if by chance, there are blank records --> empty rows in the result.. Will SQL exclude that in the result file?

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252112 is a reply to message #252105]

Tue, 17 July 2007 21:10

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 09:45

>Or is it has anything to do with the sqlplus setting that eliminates the blank lines?
WHAT blank lines?

What happens when you remove the misplaced "hint"?

CUT & PASTE doing:
wc -l /tmw/oradata18/POC/flat_20070607_v2.data
grep 200706|07 /tmw/oradata18/POC/flat_20070607_v2.data | wc -l

Sorry I didn't get that..

Can you please clarify what do you mean by saying that exactly?

Thank you.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252114 is a reply to message #252101]

Tue, 17 July 2007 21:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Do you know what is HINT is?

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252117 is a reply to message #252114]

Tue, 17 July 2007 21:25

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 10:14

Do you know what is HINT is?

No..

I just don't get it.. Please explain to me..

And what about this part..

Quote:

CUT & PASTE doing:
wc -l /tmw/oradata18/POC/flat_20070607_v2.data
grep 200706|07 /tmw/oradata18/POC/flat_20070607_v2.data | wc -l

Is that related to what you mean by "HINT"? Sad

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252118 is a reply to message #252101]

Tue, 17 July 2007 21:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Who wrote the actual SQL that you posted at the top of this thread?

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252119 is a reply to message #252118]

Tue, 17 July 2007 21:36

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 10:29

Who wrote the actual SQL that you posted at the top of this thread?

Of course it's me, why??

And I get those set command by searching the net. Coz I want the flat file to be as clean as it should be i.e. no trailing blanks, single-line record etc.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252120 is a reply to message #252101]

Tue, 17 July 2007 21:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What is
>/*+ parallel(tel_calldtl partition(bl_period_0607),4) */
& what is it supposed to do?

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252121 is a reply to message #252120]

Tue, 17 July 2007 21:49

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 10:37

What is
>/*+ parallel(tel_calldtl partition(bl_period_0607),4) */
& what is it supposed to do?

It's part of the Oracle Tuning I guess..

Actually, I just inherit the script from my previous colleague coz he said that it will improve the result performance. Cool

Maybe this link could help..

http://www.singlequery.com/?p=49

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252137 is a reply to message #252121]

Tue, 17 July 2007 23:07

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

How big is the spool file. Some applications on some operating systems have a 2Gb file size limit. Yours may have been truncated. I am not sure whether SQL*Plus suffers from this.

Look at the last line in the file and see whether it is truncated.

Ross Leishman

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252142 is a reply to message #252137]

Tue, 17 July 2007 23:18

aimy
Messages: 225
Registered: June 2006

Senior Member

rleishman wrote on Wed, 18 July 2007 12:07

Ross Leishman

It is 3357MB (3519546684 bytes).

I have tail the file and it seems to be OK..

$ tail -5 flat_20070607_v2.data

200706|07|0607|S453264280201|08800261225|TEL|1||14-MAY-07|10|59|58|0|0|44|08900777421|TAWAU|F|||.65|0|.65|0|0|0|01|C|SBH
200706|07|0607|S453264280201|08800261225|TEL|1||14-MAY-07|11|0|47|0|0|32|00322827222|BANGSAR|F|||.5|0|.5|0|0|0|01|D|SBH
200706|07|0607|S453264280201|08800261225|TEL|1||14-MAY-07|15|55|23|0|0|29|08600338432|BINTULU|F|||.45|0|.45|0|0|0|01|C|SBH
200706|07|0607|S453264280201|08800261225|TEL|1||14-MAY-07|15|56|17|0|1|4|08600338432|BINTULU|F|||.95|0|.95|0|0|0|01|C|SBH
200706|07|0607|S453264280201|08800261225|TEL|1||14-MAY-07|15|57|57|0|0|29|08600338432|BINTULU|F|||.45|0|.45|0|0|0|01|C|SBH

Actually I've managed to spool the same query with more than 70 million rows (around 6GB filesize) before this (due to double lines and empty spaces).

But there's one thing which is weird..

Yesterday, when I notice the file has stop growing, I query this..

$ ps -ef|grep flat_20070607.sql
 oracle9 25684 23288  0 08:47:44 pts/tf    0:00 sh ./flat_20070607.sql
 oracle9  8578  1807  1 13:40:36 pts/th    0:00 grep flat_20070607.sql

So, means that the query is still running right?

Today it is not there anymore, but the filesize and row count is still the same as yesterday (the moment it stops growing).

Disk space is still available.. adequately.

So, I wonder whether it has something to do with the SPOOL OFF (I forgot to include this) command or BLANK RECORDS removed?

[Updated on: Tue, 17 July 2007 23:20]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252181 is a reply to message #252101]

Wed, 18 July 2007 01:38

aimy
Messages: 225
Registered: June 2006

Senior Member

Oh God..

I've modified the script to include the SPOOL OFF and ran it again against another set of data which the total records should be 22873354. But when I count the lines in the spool file, again it is less than expected..22873209 Sad

$ wc -l  flat_20070313_v2.data

22873209 flat_20070313_v2.data

why why why?? ;(

[Updated on: Wed, 18 July 2007 03:17]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252203 is a reply to message #252101]

Wed, 18 July 2007 02:34

aimy
Messages: 225
Registered: June 2006

Senior Member

Well, I've done some small testing..

If there are empty records in the table, select count(*) won't take them into account.

In fact, the lines in the spool file which is the one should be greater since all empty records will become |||||||||...

Ermmmmmmmmmmmmm........ Sad

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252209 is a reply to message #252203]

Wed, 18 July 2007 02:46

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Your number of what the record count SHOULD be has changed, if I read correctly.

So I assume the data is still changing? Then the count(*) will almost certainly never quite match the actual export, even more so when the export seems to be running at least a couple of hours.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252217 is a reply to message #252209]

Wed, 18 July 2007 02:56

aimy
Messages: 225
Registered: June 2006

Senior Member

ThomasG wrote on Wed, 18 July 2007 15:46

No, the records are fixed.. It will be never changed. It is loaded monthly.

The table is extremely huge.

The query would complete about 4 to 5 hours.

It's really weird, isn't it?

Which of these settings you guys think could be relevant to this effect?

set linesize 369;
SET NEWPAGE 0
SET SPACE 0
set pagesize 0;
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
set echo off;
set wrap off
set trimspool on
set TRIMOUT on
set TAB ON
set autocommit on;

Is is TAB, WRAP? AUTOCOMMIT? Sad

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252219 is a reply to message #252209]

Wed, 18 July 2007 03:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Thomas's point still stands. If the data is static, you need to adequately explain why your COUNT(*) has changed.

Ross Leishman

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252222 is a reply to message #252219]

Wed, 18 July 2007 03:08

aimy
Messages: 225
Registered: June 2006

Senior Member

rleishman wrote on Wed, 18 July 2007 16:00

Thomas's point still stands. If the data is static, you need to adequately explain why your COUNT(*) has changed.

Ross Leishman

Oh sorry for the MISUNDERSTANDING..

Actually, the second time I ran it is for another set of data for another month.. I take the smallest set.

28800065 --> 28799889 is for June 2007 Cycle 07

whereas

22873354 --> 22873209 is for March 2007 Cycle 13

So, the conclusion is both situations got the records been reduced in the spool file.

Hope it is clear now. I'm sorry again for the misunderstanding caused.

[Updated on: Wed, 18 July 2007 03:09]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252227 is a reply to message #252222]

Wed, 18 July 2007 03:56

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Can you try with an "spool off" and an "exit" in the sql script added?

What I could imagine is that, that sqlplus maybe just hangs at the end of the script and never writes the last few lines.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252230 is a reply to message #252227]

Wed, 18 July 2007 04:08

aimy
Messages: 225
Registered: June 2006

Senior Member

Alright.. I'll try that..

Thanks..

Btw, this is the full UNIX script..

# nohup ./flat_20070607.sql &

sqlplus -s crispadm/admcrisp <<EOF

set linesize 369
SET NEWPAGE 0
SET SPACE 0
set pagesize 0
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
set echo off
set wrap off
set trimspool on
set TRIMOUT on
set TAB ON
set autocommit on


-- spool /tmw/oradata18/POC/flat_20070607_v2.data
-- 
-- SELECT YEAR_MONTH_DATE||'|'|| BILL_PERIOD||'|'|| PARTITION_VALUES||'|'|| ACCOUNT_NBR||'|'|| SERVICE_NBR||'|'||
-- SERVICE_TYPE||'|'|| BILLING_CLASS_CODE||'|'|| MTX_AREA_CODE||'|'|| CALL_START_DATE||'|'|| CALL_START_TIME_HH||-- -- '|'||
-- CALL_START_TIME_MM||'|'|| CALL_START_TIME_SS||'|'|| CALL_DURATION_HH||'|'|| CALL_DURATION_MM||'|'||
-- CALL_DURATION_SS||'|'|| TERMINATING_NBR||'|'|| CALLED_PLACE||'|'|| CALL_TYPE||'|'|| CALL_CLASS_CODE||'|'||
-- TOLL_CHARGE_CODE||'|'|| TOLL_GROSS_CHARGE||'|'|| TOLL_DISCOUNT_AMT||'|'|| TOLL_NET_CHARGE||'|'||
-- TOLL_SURCHARGES||'|'|| CS_FULL_CHG_AMT||'|'|| CS_CHG_AMT||'|'|| DISCOUNT_CODE||'|'|| RATE_BAND_CODE||'|'||SOURCE 
-- /*+ parallel(tel_calldtl partition(bl_period_0607),4) */ 
-- from tel_calldtl partition(bl_period_0607) t
-- where year_month_date= '200706' and bill_period='07';
-- 
-- spool off

spool /tmw/oradata18/POC/flat_20070316_v2.data

SELECT YEAR_MONTH_DATE||'|'|| BILL_PERIOD||'|'|| PARTITION_VALUES||'|'|| ACCOUNT_NBR||'|'|| SERVICE_NBR||'|'||
SERVICE_TYPE||'|'|| BILLING_CLASS_CODE||'|'|| MTX_AREA_CODE||'|'|| CALL_START_DATE||'|'|| CALL_START_TIME_HH||'|'||
CALL_START_TIME_MM||'|'|| CALL_START_TIME_SS||'|'|| CALL_DURATION_HH||'|'|| CALL_DURATION_MM||'|'||
CALL_DURATION_SS||'|'|| TERMINATING_NBR||'|'|| CALLED_PLACE||'|'|| CALL_TYPE||'|'|| CALL_CLASS_CODE||'|'||
TOLL_CHARGE_CODE||'|'|| TOLL_GROSS_CHARGE||'|'|| TOLL_DISCOUNT_AMT||'|'|| TOLL_NET_CHARGE||'|'||
TOLL_SURCHARGES||'|'|| CS_FULL_CHG_AMT||'|'|| CS_CHG_AMT||'|'|| DISCOUNT_CODE||'|'|| RATE_BAND_CODE||'|'||SOURCE 
/*+ parallel(tel_calldtl partition(bl_period_0316),4) */ 
from tel_calldtl partition(bl_period_0316) t
where year_month_date= '200703' and bill_period='16';

spool off

exit
  
EOF

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252429 is a reply to message #252101]

Wed, 18 July 2007 15:49

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Try the following

select count(*)
from tel_calldtl partition(bl_period_0316) t
where year_month_date= '200703' and bill_period='16';

What is the count that it returns.

also do the following commands and post the result

head 10 flat_20070607_v2.data

tail -10 tail flat_20070607_v2.data

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252451 is a reply to message #252429]

Wed, 18 July 2007 20:25

aimy
Messages: 225
Registered: June 2006

Senior Member

Bill B wrote on Thu, 19 July 2007 04:49

Thanks..

So, here's the result

SQL> select count(*)
from tel_calldtl partition(bl_period_0313) t
where year_month_date= '200703' and bill_period='13';
  
  COUNT(*)
----------
  22873354

$ head 10 flat_20070313_v2.data
10: No such file or directory
==> flat_20070313_v2.data <==
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|15|46|46|0|1|19|0172736254|MAXIS TN|F|||.4|0|.4|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|12|39|0|0|1|48|0192618309|ART GSM TN|F|||.6|0|.6|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|15|48|32|0|1|43|0146674587|DG 1800 TN|F|||.6|0|.6|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|16|52|27|0|1|56|0193020497|ART GSM TN|F|||.6|0|.6|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|15|9|43|0|2|20|0192733728|ART GSM TN|F|||.7|0|.7|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|12|10|10|0|2|29|0173693737|ADAM TN|F|||.8|0|.8|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||12-MAR-07|15|50|58|0|3|6|0193498223|ART GSM TN|F|||1|0|1|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||13-FEB-07|9|33|37|0|0|13|0129755463|MAXIS TN|F|||.1|0|.1|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||13-FEB-07|14|0|31|0|0|14|0192835779|ART GSM TN|F|||.1|0|.1|0|0|0|1|L|NBS
200703|13|0313|Y902556815103|00331687886|TEL|1||13-FEB-07|16|24|55|0|0|35|00377112211|TMN TDI|F|||.1|0|.1|0|0|0|1|A|NBS

$ tail -10 flat_20070313_v2.data
200703|13|0313|F351315780101|08200616320|TEL|1||24-FEB-07|10|25|32|0|0|1|0198576165|GSMS'WAK W|F|||.1|0|.1|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||24-FEB-07|10|26|5|0|0|11|0198576165|GSMS'WAK W|F|||.1|0|.1|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||24-FEB-07|16|47|51|0|0|41|0128551784|MAXISSWAKW|F|||.3|0|.3|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||24-FEB-07|18|25|23|0|0|25|0128551784|MAXISSWAKW|F|||.2|0|.2|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||25-FEB-07|11|2|3|0|2|54|0128551784|MAXISSWAKW|F|||.9|0|.9|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||25-FEB-07|12|3|59|0|0|36|0198576165|GSMS'WAK W|F|||.2|0|.2|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||25-FEB-07|12|7|32|0|0|23|0198576165|GSMS'WAK W|F|||.2|0|.2|0|0|0|03|L|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||25-FEB-07|20|58|25|0|1|21|08300309152|SRI AMAN|F|||.45|.2|.25|0|0|0|04|B|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||25-FEB-07|21|0|3|0|1|23|08300309152|SRI AMAN|F|||.45|.2|.25|0|0|0|04|B|SWK
200703|13|0313|F351315780101|08200616320|TEL|1||26-FEB-07|19|29|45|0|1|9|08300309152|SRI AMAN|F|||.35|.15|.2|0|0|0|02|B|SWK

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252452 is a reply to message #252227]

Wed, 18 July 2007 20:31

aimy
Messages: 225
Registered: June 2006

Senior Member

ThomasG wrote on Wed, 18 July 2007 16:56

Can you try with an "spool off" and an "exit" in the sql script added?

What I could imagine is that, that sqlplus maybe just hangs at the end of the script and never writes the last few lines.

The test is complete.

Unfortunately, the result is even worst.. Sad

357 lines are missing.

$ wc -l flat_20070316_v2.data

23327579 flat_20070316_v2.data

Total records for March 2007 Cycle 16 should be 23327936.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252454 is a reply to message #252452]

Wed, 18 July 2007 21:49

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Run it again with SET FEEDBACK ON. See how many SQL*Plus thinks it is returning.

Ross Leishman

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252461 is a reply to message #252101]

Wed, 18 July 2007 22:11

aimy
Messages: 225
Registered: June 2006

Senior Member

I've ran the same settings for another table with 598060 records and the result is completely OK!!

SQL> select count(*) /*+ parallel(CUSTOMER_ACCOUNT_CHARGES partition(BL4_PERIOD_04),4) */
from CUSTOMER_ACCOUNT_CHARGES partition(BL4_PERIOD_04)
where bill_date like '%MAY-07'; 

COUNT(*)/*+PARALLEL(CUSTOMER_ACCOUNT_CHARGESPARTITION(BL4_PERIOD_04),4)*/
-------------------------------------------------------------------------
598060

$ wc -l flat_CUSTOMER_ACCOUNT_CHARGES_MAY2007.data
598060 flat_CUSTOMER_ACCOUNT_CHARGES_MAY2007.data

So, any idea anyone?

[Updated on: Wed, 18 July 2007 22:12]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #252473 is a reply to message #252101]

Wed, 18 July 2007 23:35

aimy
Messages: 225
Registered: June 2006

Senior Member

Spooling for 5984318 records also works fine.

Ermm...

Report message to a moderator

Re: Missing Lines In A Spool File... [message #258046 is a reply to message #252101]

Thu, 09 August 2007 23:15

aimy
Messages: 225
Registered: June 2006

Senior Member

Can anyone please suggest me how could I manipulate the script so that it will commit the spool file after every 1000 rows for example?

Report message to a moderator

Re: Missing Lines In A Spool File... [message #258047 is a reply to message #258046]

Thu, 09 August 2007 23:23

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you mean "commit the spool file"?

Regards
Michel

[Updated on: Thu, 09 August 2007 23:23]

Report message to a moderator

Re: Missing Lines In A Spool File... [message #258167 is a reply to message #252101]

Fri, 10 August 2007 07:38

Bill B
Messages: 1971
Registered: December 2004

Senior Member

There is no such thing as committing a spool file. It is a flat file that is being appended to.

Report message to a moderator

Re: Missing Lines In A Spool File... [message #258501 is a reply to message #252454]

Sun, 12 August 2007 17:56

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

rleishman wrote on Thu, 19 July 2007 12:49

Run it again with SET FEEDBACK ON. See how many SQL*Plus thinks it is returning.

Ross Leishman

Report message to a moderator

Re: Missing Lines In A Spool File... [message #259255 is a reply to message #252120]

Tue, 14 August 2007 20:46

aimy
Messages: 225
Registered: June 2006

Senior Member

anacedent wrote on Wed, 18 July 2007 10:37

What is
>/*+ parallel(tel_calldtl partition(bl_period_0607),4) */
& what is it supposed to do?

Hello guys..

I'm glad to inform all of you that my peculiar problem has been resolved..

Actually that particular part of SQL as mentioned by the anacedent as above is the REAL CULPRIT to this!! Mad

Actually I'm having another peculiar problem recently... This is the script:

# nohup ./flat_CDR_0307.sql &

sqlplus -s crispadm/admcrisp <<EOF

set linesize 369
SET NEWPAGE 0
SET SPACE 0
set pagesize 0
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
set echo off
set wrap off
set trimspool on
set TRIMOUT on
set TAB ON
set autocommit on

spool /bistari/sasprod/POC/flat_CDR_20070310.data
 
SELECT YEAR_MONTH_DATE||'|'|| BILL_PERIOD||'|'|| PARTITION_VALUES||'|'|| ACCOUNT_NBR||'|'|| SERVICE_NBR||'|'||
SERVICE_TYPE||'|'|| BILLING_CLASS_CODE||'|'|| MTX_AREA_CODE||'|'|| CALL_START_DATE||'|'|| CALL_START_TIME_HH||'|'-- ||
CALL_START_TIME_MM||'|'|| CALL_START_TIME_SS||'|'|| CALL_DURATION_HH||'|'|| CALL_DURATION_MM||'|'||
CALL_DURATION_SS||'|'|| TERMINATING_NBR||'|'|| CALLED_PLACE||'|'|| CALL_TYPE||'|'|| CALL_CLASS_CODE||'|'||
TOLL_CHARGE_CODE||'|'|| TOLL_GROSS_CHARGE||'|'|| TOLL_DISCOUNT_AMT||'|'|| TOLL_NET_CHARGE||'|'||
TOLL_SURCHARGES||'|'|| CS_FULL_CHG_AMT||'|'|| CS_CHG_AMT||'|'|| DISCOUNT_CODE||'|'|| RATE_BAND_CODE||'|'||SOURCE 
/*+ parallel(tel_calldtl partition(bl_period_0310),4) */ 
from tel_calldtl partition(bl_period_0310) t
where year_month_date= '200703' and bill_period='10';

spool off

spool /bistari/sasprod/POC/flat_CDR_20070313.data
 
SELECT YEAR_MONTH_DATE||'|'|| BILL_PERIOD||'|'|| PARTITION_VALUES||'|'|| ACCOUNT_NBR||'|'|| SERVICE_NBR||'|'||
SERVICE_TYPE||'|'|| BILLING_CLASS_CODE||'|'|| MTX_AREA_CODE||'|'|| CALL_START_DATE||'|'|| CALL_START_TIME_HH||'|'-- ||
CALL_START_TIME_MM||'|'|| CALL_START_TIME_SS||'|'|| CALL_DURATION_HH||'|'|| CALL_DURATION_MM||'|'||
CALL_DURATION_SS||'|'|| TERMINATING_NBR||'|'|| CALLED_PLACE||'|'|| CALL_TYPE||'|'|| CALL_CLASS_CODE||'|'||
TOLL_CHARGE_CODE||'|'|| TOLL_GROSS_CHARGE||'|'|| TOLL_DISCOUNT_AMT||'|'|| TOLL_NET_CHARGE||'|'||
TOLL_SURCHARGES||'|'|| CS_FULL_CHG_AMT||'|'|| CS_CHG_AMT||'|'|| DISCOUNT_CODE||'|'|| RATE_BAND_CODE||'|'||SOURCE 
/*+ parallel(tel_calldtl partition(bl_period_0313),4) */ 
from tel_calldtl partition(bl_period_0313) t
where year_month_date= '200703' and bill_period='13';

spool off

exit
  
EOF

The problem is that.. When I run the script i.e. nohup ./flat_CDR_0307.sql &, only the first part of the spool file is being executed.. All subsequent spools are being IGNORED!

So, I was thinkin to remove the /*+ parallel(tel_calldtl partition(bl_period_0313),4) */ part since it somehow resembles a comment right..

So, there you go.. I've got the exact number of lines in the spool file and all spools are being treated as the order they appear in the script.

What's more.. The script is even run FASTER!!

What a **************.. Mad

Thanks a lot anyway for all your supports..

Really appreciate. Smile

Report message to a moderator

Previous Topic:	Creating primary key on loaded table
Next Topic:	SUM, DISTINCT and GROUP BY CUBE problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Oct 25 12:13:19 CDT 2025