Home » SQL & PL/SQL » SQL & PL/SQL » Missing Lines In A Spool File...
icon9.gif  Missing Lines In A Spool File... [message #252101] Tue, 17 July 2007 19:55 Go to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 Go to previous messageGo to next message
aimy
Messages: 209
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?
Re: Missing Lines In A Spool File... [message #252112 is a reply to message #252105] Tue, 17 July 2007 21:10 Go to previous messageGo to next message
aimy
Messages: 209
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.
Re: Missing Lines In A Spool File... [message #252114 is a reply to message #252101] Tue, 17 July 2007 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Do you know what is HINT is?
Re: Missing Lines In A Spool File... [message #252117 is a reply to message #252114] Tue, 17 July 2007 21:25 Go to previous messageGo to next message
aimy
Messages: 209
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
Re: Missing Lines In A Spool File... [message #252118 is a reply to message #252101] Tue, 17 July 2007 21:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Who wrote the actual SQL that you posted at the top of this thread?
Re: Missing Lines In A Spool File... [message #252119 is a reply to message #252118] Tue, 17 July 2007 21:36 Go to previous messageGo to next message
aimy
Messages: 209
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.
Re: Missing Lines In A Spool File... [message #252120 is a reply to message #252101] Tue, 17 July 2007 21:37 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What is
>/*+ parallel(tel_calldtl partition(bl_period_0607),4) */
& what is it supposed to do?
Re: Missing Lines In A Spool File... [message #252121 is a reply to message #252120] Tue, 17 July 2007 21:49 Go to previous messageGo to next message
aimy
Messages: 209
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

Re: Missing Lines In A Spool File... [message #252137 is a reply to message #252121] Tue, 17 July 2007 23:07 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Missing Lines In A Spool File... [message #252142 is a reply to message #252137] Tue, 17 July 2007 23:18 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
rleishman wrote on Wed, 18 July 2007 12:07
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

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

icon9.gif  Re: Missing Lines In A Spool File... [message #252181 is a reply to message #252101] Wed, 18 July 2007 01:38 Go to previous messageGo to next message
aimy
Messages: 209
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

icon9.gif  Re: Missing Lines In A Spool File... [message #252203 is a reply to message #252101] Wed, 18 July 2007 02:34 Go to previous messageGo to next message
aimy
Messages: 209
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
Re: Missing Lines In A Spool File... [message #252209 is a reply to message #252203] Wed, 18 July 2007 02:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.

Re: Missing Lines In A Spool File... [message #252217 is a reply to message #252209] Wed, 18 July 2007 02:56 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
ThomasG wrote on Wed, 18 July 2007 15:46
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.



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
Re: Missing Lines In A Spool File... [message #252219 is a reply to message #252209] Wed, 18 July 2007 03:00 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Missing Lines In A Spool File... [message #252222 is a reply to message #252219] Wed, 18 July 2007 03:08 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: Missing Lines In A Spool File... [message #252230 is a reply to message #252227] Wed, 18 July 2007 04:08 Go to previous messageGo to next message
aimy
Messages: 209
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
Re: Missing Lines In A Spool File... [message #252429 is a reply to message #252101] Wed, 18 July 2007 15:49 Go to previous messageGo to next message
Bill B
Messages: 1482
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

Re: Missing Lines In A Spool File... [message #252451 is a reply to message #252429] Wed, 18 July 2007 20:25 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Bill B wrote on Thu, 19 July 2007 04:49
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_20070313_v2.data

tail -10 flat_20070313_v2.data



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


Re: Missing Lines In A Spool File... [message #252452 is a reply to message #252227] Wed, 18 July 2007 20:31 Go to previous messageGo to next message
aimy
Messages: 209
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.
Re: Missing Lines In A Spool File... [message #252454 is a reply to message #252452] Wed, 18 July 2007 21:49 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Missing Lines In A Spool File... [message #252461 is a reply to message #252101] Wed, 18 July 2007 22:11 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Spooling for 5984318 records also works fine.

Ermm... http://skycrewz.net/smiley/40.gif
icon14.gif  Re: Missing Lines In A Spool File... [message #258046 is a reply to message #252101] Thu, 09 August 2007 23:15 Go to previous messageGo to next message
aimy
Messages: 209
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?
Re: Missing Lines In A Spool File... [message #258047 is a reply to message #258046] Thu, 09 August 2007 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Bill B
Messages: 1482
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.
Re: Missing Lines In A Spool File... [message #258501 is a reply to message #252454] Sun, 12 August 2007 17:56 Go to previous messageGo to next message
rleishman
Messages: 3724
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

icon9.gif  Re: Missing Lines In A Spool File... [message #259255 is a reply to message #252120] Tue, 14 August 2007 20:46 Go to previous message
aimy
Messages: 209
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 Cool
Previous Topic: Creating primary key on loaded table
Next Topic: SUM, DISTINCT and GROUP BY CUBE problem
Goto Forum:
  


Current Time: Sun Dec 04 08:24:04 CST 2016

Total time taken to generate the page: 0.04870 seconds