彻底理解SQLPlus系统变量.docx
《彻底理解SQLPlus系统变量.docx》由会员分享，可在线阅读，更多相关《彻底理解SQLPlus系统变量.docx（29页珍藏版）》请在冰豆网上搜索。
彻底理解SQLPlus系统变量
彻底理解SQL*Plus系统变量
1.APPI[NFO]{ON|OFF|text}
启用或禁止通过DBMS_APPLICATION_INFO包自动注册当前会话正在执行的模块（命令文件）的名称，或者手工注册模块名称。
这可以使DBA能够监视每个模块的性能和资源使用情况。
注册的模块的名称保存在V$SESSION视图相应会话的MODULE字段中。
注册名称时实际是调用DBMS_APPLICATION_INFO.SET_MODULE存储过程。
[ON]：
允许自动注册通过@、@@或START调用的命令文件的名称。
[OFF]：
禁止自动注册通过@、@@或START调用的命令文件的名称。
[text]：
用来手工设置当前会话正在执行的模块名称。
示例：
SQL>setappinfoon
SQL>@js2008.sql---模块名将被自动注册
SQL>@jz.sql---模块名将被自动注册
当APPINFO为ON时命令文件的名称是自动被注册的。
可执行下面两条语句分别查看js2008.sql模块和jz.sql模块的执行情况：
SQL>select*fromv$sqlareawheremodulelike'%js2008.sql';
SQL>select*fromv$sqlareawheremodulelike'%jz.sql';
而当APPINFO为OFF时则不会自动注册命令文件的名称，我们可以手工注册。
SQL>setappinfooff
SQL>setappinfojs2008---手工注册模块名
SQL>@js2008.sql
SQL>setappinfojz---手工注册模块名
SQL>@jz.sql
然后执行下面两条语句分别查看js2008.sql模块和jz.sql模块的执行情况：
SQL>select*fromv$sqlareawheremodule='js2008';
SQL>select*fromv$sqlareawheremodule='jz';
2.ARRAY[SIZE]{n}
指示SQL*Plus一次从服务器获取多少行的数据。
n的默认值是15取值范围是1至5000。
当查询返回的数据较多时设置为较大的值可提高效率，但需占用更多的内存。
假设ARRAYSIZE设为5，而某条SQL查询将返回100行数据，则SQL*Plus需分20次才能从服务器获取到所有的数据行。
如下所示：
创建一个具有100行数据的表，并设置ARRAYSIZE为5：
SQL>setautotraceon---使SQL*Plus显示统计信息
SQL>createtablet_tempasselect*fromall_objectswhererownum<101;
SQL>setarraysize5
查询表中所有行
SQL>select*fromt_temp;
显示的统计信息如下：
0recursivecalls
9dbblockgets
26consistentgets
0physicalreads
0redosize
15561bytessentviaSQL*Nettoclient
2992bytesreceivedviaSQL*Netfromclient
21SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
100rowsprocessed
参看统计信息中“SQL*Netroundtripsto/fromclient”这行数据。
它说明了SQL*Plus需分20次从服务器获取所有数据行（加上发起查询的一次共21次）。
SQL>setarraysize100修改ARRAYSIZE为100
SQL>select*fromt_temp;
显示统计信息如下：
0recursivecalls
9dbblockgets
7consistentgets
0physicalreads
0redosize
13262bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
100rowsprocessed
再参看统计信息中的“SQL*Netroundtripsto/fromclient”这一行，说明SQL*Plus只需1次就可以从服务器获取到所有数据行（加上发起查询的一次共2次）。
3.AUTO[COMMIT]{OFF|ON|IMM[EDIATE]|n}
控制何时提交对数据库的修改。
[OFF]：
禁止自动提交，因此我们必须执行COMMIT来手工提交改变。
[ON]：
每执行成功一个INSERT、UPDATE、DELETE语句或PL/SQL块后自动提交。
[IMMEDIATE]：
同ON。
[n]：
每执行成功n个INSERT、UPDATE、DELETE语句或PL/SQL块后自动提交。
4.AUTOP[RINT]{OFF|ON}
控制SQL*Plus是否自动显示绑定变量的值。
默认为OFF。
SQL>variablev_datechar（10）
SQL>setautoprinton
下面PL/SQL执行成功后将自动打印出绑定变量的值。
SQL>begin
2selectto_char（sysdate,'yyyy-mm-dd'）into:
v_datefromdual;
3end;
4/
PL/SQL过程已成功完成。
V_DATE
--------------------------------
2007-05-21
5.AUTORECOVERY[ON|OFF]
ON设置RECOVER命令自动应用恢复过程中所需要的归档重做日志文件的默认文件名。
当AUTORECOVERY设为ON时，不需要与用户进行交互，这时所需要的文件必须具有所期望的名称并且位于所期望的位置。
当AUTORECOVERY设为ON时，所使用的文件名来源于初始化参数LOG_ARCHIVE_DEST和LOG_ARCHIVE_FORMAT的值。
要在iSQL*Plus中使用RECOVER命令，必须把AUTORECOVERY设为ON。
如果尝试在AUTORECOVERY为OFF时恢复数据库，将产生以下错误：
SP2-0872:
在iSQL*Plus中必须使用SETAUTORECOVERYON
OFF是默认选项，它需要手工输入文件名或者接受所建议的默认文件名。
SETAUTORECOVERYON
RECOVERDATABASE
6.AUTOT[RACE]{OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]
控制在成功执行SQLDML语句后显示一个跟踪报表。
报表可包括一些关于执行情况的统计信息和执行计划。
默认为OFF。
[OFF]：
不显示跟踪报表。
[ON]：
显示跟踪报表。
设为ON或TRACEONLY隐含包括了EXPLAIN和STATISTICS选项。
[TRACEONLY]：
显示跟踪报表但不显示查询结果。
[EXPLAIN]：
显示查询执行路径。
[STATISTICS]：
显示SQL语句统计信息。
SQL>setautotracetraceonly
SQL>select*fromscott.dept;
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'DEPT'
Statistics
----------------------------------------------------------
0recursivecalls
2dbblockgets
2consistentgets
0physicalreads
0redosize
604bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
4rowsprocessed
7.BLO[CKTERMINATOR]{OFF|ON|c}
设置用于结束输入PL/SQL块的字符。
此字符不能是数字字符。
默认结束字符为“.”。
[OFF]：
禁用结束字符。
[ON]：
启用结束字符，并重置结束字符为“.”。
[c]：
启用结束字符，并指定结束字符为所设置的字符。
修改结束字符为“！
”
SQL>setblockterminator!
SQL>begin
2insertinto;
3!
---输入！
后，块输入结束
SQL>setblockterminatoroff
SQL>begin
2insertinto;
3!
---输入！
后，块输入不会结束
4!
5
8.CMDS[EP]{c|OFF|ON}
控制是否允许在一行中输入多个SQL*Plus命令。
如果为ON则还可以设置多个SQL*Plus命令之间的分隔符，默认的分隔符为“;”。
[OFF]：
禁止在一行中输入多个SQL*Plus命令。
[ON]：
允许在一行中输入多个SQL*Plus命令，并设置分隔符为“;”。
[c]：
允许在一行中输入多个SQL*Plus命令，并指定分隔符为所设置的字符。
SQL>setcmdsepon
SQL>columne_nameheadingEmployeeName;columndept_nameheadingDepartName
SQL>setcmdsepoff
SQL>columne_nameheadingEmployeeName;columndept_nameheadingDepartName
SP2-0158:
未知的COLUMN选项"dept_name"
9.COLSEP{text}
设置SELECT语句返回的各列之间显示的分隔符。
默认是一个空格。
SQL>setcolsep||
SQL>setcolsep‘‘
10.COM[PATIBILITY]{V7|V8|NATIVE}
设置当前连接到的Oracle服务器的版本。
如果连接到Oracle7则设为V7，连接到Oracle8或以后版本则可设为V8，如果希望由数据库服务器来决定则设为NATIVE（如果连接到Oracle9i，COMPATIBILITY默认设为NATIVE）。
另，当连接到Oracle9i时可以把此变量的值设为V7或V8，这允许你执行Oracle7、Oracle8或Oracle8i的SQL语句。
11.CON[CAT]{c|OFF|ON}
设置置换变量和紧跟其后的普通字符的连接符。
这个变量不好解释还是直接看例子吧。
c默认为“.”。
举例，我们要查询SCOTT.DEPT表中的数据。
SQL>definev=scott.de
SQL>select*from&v;---这条语句肯定会出错，因为不存在表SCOTT.DE
原值1:
select*from&v
新值1:
select*fromscott.de
select*fromscott.de
*
ERROR位于第1行:
ORA-00942:
表或视图不存在
SQL>select*from&v.pt;---注意&v与pt之间有个句点
原值1:
select*from&v.pt
新值1:
select*fromscott.dept
DEPTNODNAME
------------------------
10a"ccounting
20RESEARCH
30s
40OPERATIONS
12.COPYC[OMMIT]{n}
设置SQL*Plus的COPPY命令在复制多少行数据后进行提交。
默认值为0，表示在整个复制操作完成后再提交。
n的取值范围为0至5000。
13.COPYTYPECHECK{OFF|ON}
启用或禁用在使用COPY命令插入或附加数据到表中时是否比较数据类型。
默认为ON。
14.DEF[INE]{c|OFF|ON}
设置置换变量的前缀字符。
[OFF]：
不扫描置换变量，不替换置换变量的值。
此设置会覆盖系统变量SCAN的设置。
[ON]：
扫描并替换置换变量的值。
设为ON后会自动把置换变量前缀改为“&”。
[c]：
设置置换变量的前缀字符为指定字符。
SQL>setdefine#
SQL>defineaa=scott.dept
SQL>select*from#aa;---将把此处的置换变量#aa替换为scott.dept
原值1:
select*from#aa
新值1:
select*fromscott.dept
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
SQL>setdefineoff---禁止扫描替换变量
SQL>select*from#aa;---此处的#aa不再作为置换变量，因此将会出错
select*from#aa
*
ERROR位于第1行:
ORA-00911:
无效字符
15.DESC[RIBE][DEPTH{n|ALL}][LINENUM{ON|OFF}][INDENT{ON|OFF}]
用来控制SQL*Plus的DESCRIBE命令的输出。
[DEPTH]：
设置递归描述一个对象的深度，默认为1。
n的取值范围为1至50。
当设为All时n=50。
[LINENUM]：
控制是否显示行号，默认为OFF。
[INDENT]：
控制是否自动进行缩进，默认为ON。
SQL>createtypeaddressasobject
2（streetvarchar2（20）,
3cityvarchar2（20）
4）;
5/
类型已创建。
SQL>createtypeemployeeasobject
2（last_namevarchar2（30）,
3empaddraddress,
4salarynumber（7,2）
5）;
6/
类型已创建。
注意比较下面两条descemployee命令的输出有什么不同。
SQL>descemployee
名称是否为空?
类型
-----------------------------------------------------------------------------
LAST_NAMEVARCHAR2（30）
EMPADDRADDRESS
SALARYNUMBER（7,2）
SQL>setdescdepth2
SQL>descemployee
名称是否为空?
类型
-----------------------------------------------------------------------------
LAST_NAMEVARCHAR2（30）
EMPADDRADDRESS
STREETVARCHAR2（20）
CITYVARCHAR2（20）
SALARYNUMBER（7,2）
16.MARKUPHTML[ON|OFF][HEADtext][BODYtext][TABLEtext][ENTMAP{ON|OFF}][SPOOL{ON|OFF}][PRE[FORMAT]{ON|OFF}]
控制SQL*Plus以HTML格式输出查询结果。
[HTMLON|OFF]：
允许或禁止SQL*Plus对输出进行HTML编码。
默认为OFF。
[HEADtext]：
设置在最终生成的HTML中，位于“
”标签内的文本。
[BODYtext]：
设置在最终生成的HTML中，位于“
”标签中的文本。
[TABLEtext]：
设置在最终生成的HTML中，位于“
”标签中的文本。[ENTMAPON|OFF]：
设为ON表示对查询结果中存在的一些特殊字符进行映射，比如把“>”和“<”映射为“>”和“<”，以便能在HTML中正确显示。
默认为ON。
[SPOOLON|OFF]：
ON表示生成格式完整的HTML文件，包括、
、等标签。默认为OFF。
[PREFORMATON|OFF]：
ON表示把查询结果按原格式放在HTML标签“
”中，OFF表示格式化查询结果为一个HTML中的“
”。默认为OFF。
SQL>setmarkuphtmlonhead"
Thisispagetitle."body"style='background:
red'"spoolon
SQL>spooljs2008.html
SQL>select*fromscott.dept;
SQL>spooloff
执行成功后请参见最终生成的js2008.html的源代码。
17.ECHO{OFF|ON}
控制在执行命令文件时是否列出其中的每个命令。
18.EDITF[ILE]file_name[.ext]
设置EDIT命令使用的缺省文件名。
也可以包含路径和扩展名。
默认为“afiedt.buf”。
SQL>edit---将打开afiedt.buf文件进行编辑
SQL>seteditfilejs2008.sql
SQL>edit---将打开js2008.sql文件进行编辑
19.EMB[EDDED]{OFF|ON}
[OFF]：
在一个新页的顶部开始打印报表。
[ON]：
紧跟着上一个报表的末尾开始打印报表。
20.ESC[APE]{c|OFF|ON}
定义转义字符。
[OFF]：
取消转义字符定义。
[ON]：
启用转义字符。
并把转义字符重置为“\”。
[c]：
设置转义字符为指定字符。
SQL>definev=scott.dept---定义置换变量
SQL>select*from&v;
原值1:
select*from&v
新值1:
select*fromscott.dept
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
SQL>setescapeon
SQL>select*from\&v;---&已被转义，所以这条语句出错
SP2-0552:
未说明结合变量”V”
21.FEED[BACK]{n|OFF|ON}
设置是否显示一个查询返回的记录数。
[OFF]：
不显示查询返回的记录数。
[ON]：
显示查询返回的记录数，并把n设为1。
[n]：
当一个查询至少选择了n行时显示返回的记录数。
设为0时同OFF。
22.FLAGGER{OFF|ENTRY|INTERMED[IATE]|FULL}
检查SQL语句是否符合ANSI/ISOSQL92标准。
如果不符合则无法执行并返回错误。
同SQL语句“ALTERSESSIONSETFLAGGER”等价。
ENTRY、INTERMEDIATE、FULL分别表示ANSI/ISOSQL92的三个结构层次。
SQL>beginnull;end;
2/
PL/SQL过程已成功完成。
SQL>setflaggerentry
SQL>beginnull;end;
2/
beginnull;end;
*
ERROR位于第1行:
ORA-00097:
使用OracleSQL特性不在SQL92Entry级中
因为PL/SQL块是Oracle特有的，并不包含在SQL92标准中，所以无法正确执行。
23.FLU[SH]{OFF|ON}
控制什么时候输出被发送到用户的显示设备。
默认值为ON。
[OFF]：
允许操作系统缓冲输出。
尽当执行一个非交互式的命令文件时才使用此值。
[ON]：
禁止操作系统缓冲输出。
24.HEA[DING]{OFF|ON}
设置是否显示列标题。
默认值为ON。
25.HEADS[EP]{c|OFF|ON}
定义标题分隔符。
此字符不能是数字字符或空格字符。
可以在COLUMN命令或BTITLE和TTITLE命令中使用此字符使列标题或者报表标题显示为多行。
当设为OFF时，把标题分隔符显示为普通字符。
设为ON时将重置为默认的“|”字符。
SQL>setheadsep+
SQL>columndnameheading'D+NAME'
SQL>select*fromscott.dept;
D
DEPTNONAMELOC
-------------------------------------
10a"ccountingnewYOrK
20RESEARCHDALLAS
30sCHICAGO
40OPERATIONSBOSTON
SQL>setheadsepoff
SQL>columndnameheading'D+NAME'
SQL>select*fromdept;
DEPTNOD+NAMELOC
-------------------------------------
10a"ccountingnewYOrK
20RESEARCHDALLAS
30sCHICAGO
40OPERATIONSBOSTON
26.INSTAN
展开阅读全文
相关搜索