sqlplus

 sqlplus  -----登录数据库  inctact

 sqlplus -s 

#!/bin/sh
DIR=/home/omcbo/UNIWARE_DC/bin/dbdata
sqlplus -s dgwy_user/W5#dgwy001@UWNMS2 << EOFa
set echo off
set space 0
set trimspool on
set newp none
set numwidth 20
set termout off
set pagesize 0 linesize 5000 feedback off tab off
spool $DIR/ssss.data
select REGION_NAME||'|'||NE_TYPE||'|'||NE_NAME from V_DG_BSC;
spool off
quit;
EOFa

以上实clear screen　　 清空当前屏幕显示

SQL*Plus系统环境变量设置及修改：

SQL> show all --查看所有系统变量值

SQL> show user --显示当前连接用户

SQL> show error　　　　　　　　　　　　　　　 --显示错误

SQL> set heading off --禁止输出列标题，默认值为ON

SQL> set feedback off --禁止显示最后一行的计数反馈信息，默认值为"对6个或更多的记录，回送ON"

SQL> set timing on --默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能

SQL> set sqlprompt "SQL> " --设置默认提示符，默认值就是"SQL> "

SQL> set linesize 1000 --设置屏幕显示行宽，默认100

SQL> set autocommit ON --设置是否自动提交，默认为OFF

SQL> set pause on --默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页

SQL> set arraysize 1 --默认为15

SQL> set long 1000 --默认为80

说明：
long值默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型
如：

SQL> desc user_views

SQL> desc user_views
Name Null? Type
----------------------------------------------------- -------- ---------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)现数据采集的过程，其中||‘|’||为去除段与段之间的空格

#!/bin/sh
DIR=/home/omcbo/UNIWARE_DC/bin/dbdata
sqlplus -s dgwy_user/W5#dgwy001@UWNMS2 << EOFa
set echo off
set space 0
set trimspool on
set newp none
set verify off
set timing off
set long 1000
set arraysize 1
set termout off
set pagesize 0 linesize 5000 feedback off tab off
select replace(replace(REGION_NAME,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(NE_TYPE,chr(13),NULL),chr(10),NULL)||'|'||replace(rep
lace(NE_NAME,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(CONFIRMED,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(VENDOR_NAME,chr(
13),NULL),chr(10),NULL)||'|'||replace(replace(VERSION,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(SOFTWARE_VERSION,chr(13),NULL),chr(
10),NULL)||'|'||replace(replace(SPC,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(CP_MODEL,chr(13),NULL),chr(10),NULL)||'|'||replace(re
place(IOG_MODEL,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(XZ_TYPE,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(RELATED_POOL,ch
r(13),NULL),chr(10),NULL)||'|'||replace(replace(RELATED_MSC,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(RELATED_MGW,chr(13),NULL),chr
(10),NULL)||'|'||replace(replace(RELATED_SGSN,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(RELATED_PCU,chr(13),NULL),chr(10),NULL)||'|
'||replace(replace(RELATED_OMC,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(LOCATIONNAME,chr(13),NULL),chr(10),NULL)||'|'||replace(rep
lace(NSEI,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(DR_STATUS,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(DR_MODE,chr(13),NUL
L),chr(10),NULL)||'|'||replace(replace(DR_BOARD,chr(13),NULL),chr(10),NULL)||'|'||replace(replace(CIC,chr(13),NULL),chr(10),NULL)||'|'||rep
lace(replace(COVER_DIS,chr(13),NULL),chr(10),NULL) from V_DG_BSC;
quit;
EOFa

replace(replace(REGION_NAME,chr(13),NULL)---替换字段中的换行+回车