Shulou(Shulou.com)06/01 Report--

1. The result of SQLPlus query can be adjusted according to your screen:

We know that in sqlplus mode, select queries often encounter row breaks of returned records, and we tend to set row widths, column widths and page records.

Set line width: set linesize 200 indicates a character whose line width is set to 200

Set column width: column name format A40 sets name column 40 characters, a for alphanumeric, meaning character

Set up page records: set pagesize 20 sets one column for every 20 behaviors

The sqlplus command is session-level and then disappears after exit. After set linesize 5000 exit, the effect disappeared.

For example:

1. View pagesizedefault values

Show pagesize; / / first check the current pagesize. The default is 14 lines per page.

Set the number of rows displayed on a page:

Set pagesize 10; / / if the pagesize is set to 10, you can display 10 rows of records at a time.

two。 Set the width of the row

Show linesize; / / View the current linesize. Default is 80.

Set linesize 150; / / set to 150 or wider

Note: the above methods are temporary and effective.

Example:

Set linesize 20 page width is 20 characters, if it exceeds the command line window width of sqlplus, it will not work.

In the query results, the width of each column is displayed by default according to the width defined by the column, for example, if the column defines 20 characters, then

If you do not use col xxx format axxx to limit the width of the column, the column has a defined width of 20 bytes.

However, because the width of the sqlplus command window is limited, some columns are defined as 5000 characters, and the widest width can only be displayed according to the width of the sqlplus command line window.

If the values of the following fields are small, the width of the row will be defined by the size of the field.

The DISPLAY_VALUE field has a large value of 4000 characters, and its maximum width can only be displayed according to the width of the sqlplus command line window.

The field values of name and value are 80 characters and 4000 characters respectively. Check the display of these two fields

Select name,value from v$parameter

The width is displayed according to the width of the sqlplus command line window

Set the width of the value and name fields to 20:

SQL > col value format A20

SQL > col name format A20

SQL > select name,value from v$parameter

Second, permanently set the environment variable of sql*plus (glogin.sql setting)

The $ORACLE_HOME/sqlplus/admin/glogin.sql file is called every time the user logs in to sql*plus. We can modify this file to set some sql*plus environment variables each time we log in, and change the default display of SQL > to show the login user name and the login database instance name

Add the following lines at the end of the glogin.sql file

Vi / opt/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sqlset linesize 150set pagesize 30set sqlprompt "_ user'@'_connect_identifier >"

Parameter description:

Set linesize 150 / / set the number of characters a line can hold

Set pagesize 30 / / sets the number of rows displayed on a page

Set sqlprompt "_ user'@'_connect_identifier >" / / modify the sqlplus prompt to remind you of your user mode and reduce misoperation

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.