Ever use the DESC command in a SQL*Plus session, then have to scroll right to find some of the fields? The DESCribe command uses the LINESIZE value to control the display of fields. Here is a handy way around it.
From the testing I've done, it turns out it is not related to the database version. Instead, it is related to the version of SQL*Plus you use to connect to the database. SQL*Plus 3.3 does not change the display of a DESC command based on the LINESIZE parameter. With SQL*Plus versions shipped with Oracle 8i and 9i, the LINESIZE parameter directly affects how the DESCribe command displays information. When we upgraded to 8i, we did not change to SQL*Plus 8.1 on the client machines, so we never noticed this problem. Only when we added 9i and the latest SQL*Plus to the mix of systems did this become an issue.
In our environment we have a login script that is the same for everyone. We set the LINESIZE = 300. This worked well until we moved to Oracle 9i and started using SQL*Plus 9.0.0. Now the normal queries still work fine, but when you need to describe any table, the linesize affects the display and causes the user to waste time scrolling the window to see all the info. We've only been using this for a week and have had positive feedback from users and developers.
In a SQL*Plus session:
edit desc.sql set linesize 80 desc &1 set linesize 300
Then save and exit. To use:
SQL> start desc user_tables
This will shrink the line, describe the object, then increase the linesize back to a larger value. For even more control you can replace the 80 and 300 with variables. For example:
set linesize &1 desc &2 set linesize &3To use:
SQL> start desc 75 user_tables 250
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in September 2002