Missing information in SQL*Plus May 19, 2011
Posted by mwidlake in development.Tags: sql*plus
13 comments
By preference I like to work against the databse using SQL*Plus. GUIs are fine and they are much better for developing code than using sql*plus and notepad (or vi or whatever ascii text editor floats your boat). However, for finding things out quickly and also storing what I find (via spool files) then sql*plus is much better. As screens have got larger, I have used a larger – and wider – sql*plus session to show more information at one time.
But I have been having an issue with SQL*Plus not showing some information. I lose information on the right hand side of the screen.
This is on the V10 client on Windows by the way, I’ve seen it on windows XP to Vista.
Look at the below. It is quite a wide output and I have had to increase the standard 80 character linesize from 80 up to 120:
{BTW if you are looking at this in a small browser winder, you might have to click on the screen shot to open it up in it’s own window, to see what I mean – it is quite wide, that is the point of the post – it should just fit in my over-wide blog layout :-)}
set linesize 120
AUDIT_PROGRAM entry_dt ENTRY_TS AUDIT_TEXT aud_val1 aud_val2 ------------------------ -------- ------------ ----------------------------------------------------- MM_STATS_GATHER 19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP 0 1 MM_STATS_GATHER 19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07 4,962 MM_STATS_GATHER 19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07 437,500 MM_STATS_GATHER 19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07 91, MM_STATS_GATHER 19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07 441,2 MM_STATS_GATHER 19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07 927,950 MM_STATS_GATHER 19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04 6,324 MM_STATS_GATHER 19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04 771,840 MM_STATS_GATHER 19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04 168, MM_STATS_GATHER 19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04 848,0 MM_STATS_GATHER 19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04 1,625,100 MM_STATS_GATHER 19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01 461 MM_STATS_GATHER 19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01 800,020 MM_STATS_GATHER 19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01 158,
Note that for line 8 onwards there is no value for aud_val2. But then, some of the numbers in aud_val1 also look odd. But I’m getting output for some of the lines…
I am in fact getting some of the lines truncated. It just seems to be a bug in the V10 sql*plus client and it might pass you by as the first few lines usually come out “full length”. This truncation only happens if you increase the linesize.
To fix it, you need tell the “windows” sql*plus window how big your output now is. Click on the options and then environment menu items at the top left of the screen:
Change the SQL*Plus environment
Now set the screen buffer width to a bit more than it was, say by one character (and whilst you are at it, why not increasre the buffer length to 2000, the memory required is peanuts on today’s machines). I actually tend to set it to 140 to anticipate some of my more verbose scripts.
Alter the screen buffer width to one or two characters more
And confirm that you are happy to “truncate the buffer” – whoever coded this did not bother to check if you alter the values up or down – you can ignore this message if you are simply increasing the buffers.
Accept the prompt about truncation
Now when I run my sql statement I see the full output {Again, click on the image to see it’s full length to fully see how the problem is now fixed}:
AUDIT_PROGRAM entry_dt ENTRY_TS AUDIT_TEXT aud_val1 aud_val2 ------------------------ -------- ------------ ------------------------------------------------------- -------------- ---------- MM_STATS_GATHER 19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP 0 100 MM_STATS_GATHER 19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07 4,962 100 MM_STATS_GATHER 19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07 437,500 5 MM_STATS_GATHER 19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07 91,395 20 MM_STATS_GATHER 19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07 441,240 5 MM_STATS_GATHER 19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07 927,950 2 MM_STATS_GATHER 19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04 6,324 100 MM_STATS_GATHER 19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04 771,840 5 MM_STATS_GATHER 19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04 168,840 5 MM_STATS_GATHER 19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04 848,060 5 MM_STATS_GATHER 19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04 1,625,100 1 MM_STATS_GATHER 19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01 461 100 MM_STATS_GATHER 19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01 800,020 5 MM_STATS_GATHER 19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01 158,600 5
Martin Widlake's Yet Another Oracle Blog 