結合linesize,pagesize,col format很好的設定顯示寬度

freshairpeng發表於2009-03-13
ORM

我們在使用sql*plus的時候，經常會遇到顯示不下需要列的情況：

針對以上情況，我個人經常使用的方法如下：

（1）減少查詢的列。------最笨的方法。

（2）設定顯示的linesize,pagesize,結合col format 設定比較長的列的顯示寬度。

比如

SQL>select file_name,tablespace_name,status,bytes from dba_data_files;

SQL> select file_name,tablespace_name,bytes,status from dba_data_files;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES STATUS
------------------------------ ---------- ---------
/ora10gBase/oradata/pskDB/users01.dbf
USERS                             5242880 AVAILABLE

/ora10gBase/oradata/pskDB/sysaux01.dbf
SYSAUX                          262144000 AVAILABLE

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES STATUS
------------------------------ ---------- ---------

/ora10gBase/oradata/pskDB/undotbs01.dbf
UNDOTBS1                         26214400 AVAILABLE

/ora10gBase/oradata/pskDB/system01.dbf

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES STATUS
------------------------------ ---------- ---------
SYSTEM                          503316480 AVAILABLE

/ora10gBase/oradata/pskDB/pskBigTbl.dbf
PSKBIGTBL                       104857600 AVAILABLE


FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES STATUS
------------------------------ ---------- ---------
/ora10gBase/oradata/pskDB/rman_ts.dbf
RMAN_TS                          20971520 AVAILABLE


6 rows selected.

就查詢四列，但是卻無法在一行中把一條記錄顯示出來，原因就是因為系統預設顯示的寬度太短跟file_name varchar2(513)定義的長度有點長

SQL> desc dba_data_files;
 Name   Null?    Type
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------
 FILE_NAME    VARCHAR2(513)
 FILE_ID    NUMBER
 TABLESPACE_NAME    VARCHAR2(30)
 BYTES    NUMBER
 BLOCKS     NUMBER
 STATUS     VARCHAR2(9)
 RELATIVE_FNO    NUMBER
 AUTOEXTENSIBLE     VARCHAR2(3)
 MAXBYTES    NUMBER
 MAXBLOCKS    NUMBER
 INCREMENT_BY    NUMBER
 USER_BYTES    NUMBER
 USER_BLOCKS    NUMBER
 ONLINE_STATUS    VARCHAR2(7)

我們只要設定一下，以上問題就能解決:

SQL>set linesize 400;

SQL>col file_name format 50;

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME                STATUS
-------------------------------------------------- ------------------------------ ---------
/ora10gBase/oradata/pskDB/users01.dbf              USERS                          AVAILABLE
/ora10gBase/oradata/pskDB/sysaux01.dbf             SYSAUX                         AVAILABLE
/ora10gBase/oradata/pskDB/undotbs01.dbf            UNDOTBS1                       AVAILABLE
/ora10gBase/oradata/pskDB/system01.dbf             SYSTEM                         AVAILABLE
/ora10gBase/oradata/pskDB/pskBigTbl.dbf            PSKBIGTBL                      AVAILABLE
/ora10gBase/oradata/pskDB/rman_ts.dbf              RMAN_TS                        AVAILABLE

6 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14766526/viewspace-567188/，如需轉載，請註明出處，否則將追究法律責任。

相關文章