jump to navigation

Missing information in SQL*Plus May 19, 2011

Posted by mwidlake in development.
Tags:
trackback

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
About these ads

Comments»

1. Neil Chandler - May 19, 2011

Martin,

That bugged me for years, to the point where I wrote a html generator that can run the current sql code and display it in a html table in Firefox.

Don’t forget that from 11gR2 Client, sqlplusw is no longer available. It’s SQL Developer for all of us (or the DOS version of SQL*Plus if you can’t cope with modernity)

regards

Neil

2. Gary - May 19, 2011

The sqlplusw version got deprecated in 11g
http://download.oracle.com/docs/cd/B28359_01/install.111/b32006/whatsnew.htm#BGGBABGJ

Guess it’s time to switch to straight sqlplus.exe

3. Kyle Hailey - May 19, 2011

Interesting
I’ve seen similar issues with SQL*PLus on windows but I always use sqlplus.exe. I’ve never really looked into the issues, but the issues seem to start around 150 characters wide. My reaction has just to try and keep things under 150. (these numbers aren’t exact just what I recall)

Sqlplus.exe has several benefits such as being able to take advantage of rlrwap.
Tanel Poder has added an impressive tool kit with script code name completion. I haven’t used it myself but it’s pretty cool to watch him use it.
Cut and paste can be set up on the windows option for sqlplus.exe as well.
It’s easy to change the starting path such that sqlplus.exe starts in the directory that has all my sql scripts.
There are other advantages that I’ve forgotten.

- Kyle

- Kyle

4. William Robertson - May 20, 2011

Even without rlwrap, sqlplus.exe has command line history, which sqlplusw.exe lacks. You can copy text with Enter, and paste with right-click. You can configure the font and colours (e.g. to have a different colour for production). Also sqlplusw.exe doesn’t use your font smoothing settings so it remains blocky. I’ve never understood would anyone would use it.

5. Graham - May 20, 2011

sqlplus.exe was always my preference over sqlplusw

Will Robertson used to have a great page on how to set up sqlplus.exe to be as useful as possible, I’m sure it’s still there.

Graham

6. William Robertson - May 20, 2011
Graham - May 20, 2011

Ha! When I started writing my comment yours hadn’t appeared Will. Glad to see your page is still there for all to use.

mwidlake - May 21, 2011

That’s a really nice page of information William, thanks for that (and to Graham for suggesting you put up a link to it)

7. Flado - May 21, 2011

sqlplusw should have been shot dead years ago… I tried it once in 1997 and avoided it ever since, with good reason, judging from this post.
sqlplus[.exe] FTW!

Cheers!
Flado

mwidlake - May 21, 2011

Hi Flado,

It isn’t perfect (and it is shot dead in 11.2 :-) ) – but it does the job most of the time. As others have pointed out, sqlplus.exe is a little more configurable but when you instal the windows client, the sqplusw is there in front of you and works. Usually :-)

Martin

8. Log Buffer #221, A Carnival of the Vanities for DBAs | The Pythian Blog - May 21, 2011

[...] Martin Widlake is playing with the missing information in the SQL*Plus as he prefers to work against the database using SQL*Plus. [...]

9. Bernard Polarski - May 25, 2011

When you see the word ‘MS Windows’ on a PC, it is not a trademark, but a warning.

Neil Chandler - May 25, 2011

Bernard,

Later versions of Windows, properly built and administered, are as solid and reliable an operating system as any Linux or Unix I have worked on. The problem tends to revolve around the initial build not being good enough, and the administration being less rigorous than you tend to find on Unix servers.

SQL Server 2005 onwards is very much as good as Oracle too, for most people. Again, it tends to be the administration that lets it down. Try hiring a SQL Server admin that can successfully configure and run an 800GiB mirrored environment. You’ll struggle to find one.

Neil


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 156 other followers

%d bloggers like this: