SQLPLUS Column Widths – Default Settings in Oracle 11g R2

oracleoracle-11g-r2sqlplus

For almost all my databases, when I run a SELECT statement, the length of the column displayed is sized to the width of the data being queried. However, I have two databases (Dev and Prod for the same application) that seem to default all column sizes to either 32 characters wide. If the data is wider than 32 characters or a column alias is used, it defaults the column to 128 characters wide.

Here are some examples from one of many good systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIF
----------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------
12345678901234567890

Here are some examples from one of the bad systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
12345678901234567890

I've checked the sqlplus settings and they are all the same. I didn't see any database parameters that looked out of the ordinary and they both have identical NLS parameters. Both databases use the same character set.

I realize I could define a format for every column being queried, but this should not be necessary as it is not on any other DB.

The database having this problem is 11.2.0.1, while all our others are 11.2.0.4, so perhaps there is a bug that I haven't been able to find. The sqlplus client I am using is 12.1.0.1 in both cases.

Update:
The symptoms don't match exactly, but Doc ID 330717.1 talks about column widths in sqlplus and basically says you can't guarantee anything without setting the format. I can accept this, but since the behavior is consistent, it seems like a cause can be determined.

Best Answer

I found the issue. It is documented an oracle note (Doc ID 1086519.1). I used the first workaround listed which is to set CURSOR_SHARING to exact.

ALTER SESSION SET CURSOR_SHARING=EXACT;

Related Solutions

Arabic characters not displaying in oracle database

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL; 

STARTUP;

Oracle’s UTL_FILE when connected locally or through listener has different behaviour

This is the expected behaviour on a unix system. either a process' effective user id allows to read/write a file or itseffective primary or its effective secondary group ids allow to read/write the files

If you log on to a unix system the effective user id , the effective group id and the effective secondary group ids are the ids defined for your login user on this system (in /etc/passwd or anywhere). if a unix process creates a new process the new process inherits this ids from the creating process.

The Oracle shadow process is the unix process that corresponds to an oracle session. This shadow process is created by the listener process if you log into the database using a network connection. If you add a new secondary group id to the oracle unix user this does not change the effective ids of the running listener process and therefore all newly created processs have the same effective user/group ids as the processes created before the adding of the new secondary group.

But if you log into the system after you added the new secondary group id to the oracle user, your login session will have this additional effective secondary group id and if you stop and start the listener process it will have the additional effective secondary group id too. So will have the shadow processes created by the new listener process.

If the listener process ist stared an stopped by another process after adding the new secondary group id but this other process was not restarted after adding the new secondary group id, the listener will not have the new secondary group id as effective secondary group id even if he is restarted because the process starting the listener process did not have the new secondary id as effective secondary id. This can happen if you restart the listener process by some cluster process. In this case this cluster process must be restarted before it restarts the listener process for the newly added secondary group id to take in effect.

If you make a local connection using sqlplus then the shadow process is spawned by sqlplus and not by a listener process. but the sqlplus process has the effective user/group ids inherited by your login session. so is you logged in after you added the new secondary group then the shadow process will have this secondary group id. If your login was already established before the secondary group was added to the oracle user then also all new local sqlplus connection (= shadow processes) of your unix session will not have this additional secondary group id as effective secondary group id.

Related Question