4.SQLPLUS登陆后的初始设置： The site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql.
5.定制自己用户登陆的设置： SQL*Plus also supports a User Profile, executed after the Site Profile. This file is generally named login.sql. SQL*Plus searches for the user profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
6.改变自己登录文件的设置：SET SQLPROMPT Followed by the connect information variable in the form: SET SQLPROMPT '&_CONNECT_IDENTIFIER > ' SET TIME Followed by ON, displays the current time before each command prompt.
7.保存当前的设置，下次启动时直接执行： store set login.sql create/replace 8. 登录：sqlplus –h获取帮助信息SQLPLUS [ [Options] [Logon] [Start] ]10.结束一个SQL块：n with a semicolon (;) n with a slash (/) on a line by itself n with a blank line
11.使用连接符”-”输入sqlplus命令：
12.设置login.sql文件:ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
SET PAGESIZE 24
SET LINESIZE 78
SET NUMFORMAT $99,99913.设置帮助：设置set oracle_home=E:/oracle/product/10.1.0/Db_1设置set system_pass=system/system运行helpins 向数据库中添加帮助信息%ORACLE_HOME%/BIN/HELPINS
14.设置自动提交：SET AUTOCOMMIT ON15.执行操作系统命令： HOST COMMAND16.获取结构的命令： Lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure.17.对于一个长的输出设置暂停：set pause on|off|18.SQLPlus注释需要注意的地方：1. Do not put comments within the first few keywords of a statement. 2. Do not put comments after statement terminators (period, semicolon or slash). 3. Do not put statement termination characters at the end of a comment line or after comments in a SQL statement or a PL/SQL block. 4. Do not use ampersand characters ’&’ in comments in a SQL statement or PL/SQL block.
22.不能使用 &&定义变量的部分情况： You cannot use substitution variables in the buffer editing commands, APPEND, CHANGE, DEL, and INPUT, nor in other commands where substitution would be meaningless, such as in SQL*Plus comments (REMARK, /*... */ or --).
23.set命令 16:14:37 SCOTT@orcl 11-12月-08> help set SET --- Sets a system variable to alter the SQL*Plus environment settings SET system_variable value where system_variable and value represent one of the following clauses: APPI[NFO]{OFF|ON|text} 等价于通过dbms_applocation_info注册信息； ARRAY[SIZE] {15|n} 设置每一次sql取回的行数； AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} 设置自动提交； AUTOP[RINT] {OFF|ON} 自动打印 AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n} AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text} [EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED] | BLO[CKTERMINATOR] {.|c|OFF|ON} EA[CH]|OFF} CMDS[EP] {;|c|OFF|ON} RECSEPCHAR {_|c} COLSEP {_|text} SERVEROUT[PUT] {OFF|ON} COM[PATIBILITY] {V7|V8|NATIVE} [SIZE n] [FOR[MAT] CON[CAT] {.|c|OFF|ON} {WRA[PPED] | COPYC[OMMIT] {0|n} WOR[D_WRAPPED] | COPYTYPECHECK {OFF|ON} TRU[NCATED]}] DEF[INE] {&|c|OFF|ON} 设置&是否成为输入变量 DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]} [LINENUM {ON|OFF}] [INDENT {ON|OFF}] *SHOW[MODE] {OFF|ON} ECHO {OFF|ON} *SQLBL[ANKLINES] {OFF|ON} *EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] | EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]} ESC[APE] {/|c|OFF|ON} *SQLCO[NTINUE] {> | text} FEED[BACK] {6|n|OFF|ON} *SQLN[UMBER] {OFF|ON} FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} SQLPLUSCOMPAT[IBILITY] {x.y[.z]} *FLU[SH] {OFF|ON} *SQLPRE[FIX] {#|c} HEA[DING] {OFF|ON} *SQLP[ROMPT] {SQL>|text} HEADS[EP] {||c|OFF|ON} SQLT[ERMINATOR] INSTANCE [instance_path|LOCAL] {;|c|OFF|ON} LIN[ESIZE] {80|n} ({150|n} iSQL*Plus) *SUF[FIX] {SQL|text} LOBOF[FSET] {n|1} *TAB {OFF|ON} LOGSOURCE [pathname] *TERM[OUT] {OFF|ON} LONG {80|n} *TI[ME] {OFF|ON} LONGC[HUNKSIZE] {80|n} TIMI[NG] {OFF|ON} MARK[UP] HTML [ON|OFF] *TRIM[OUT] {OFF|ON} [HEAD text] [BODY text] [TABLE text] *TRIMS[POOL] {ON|OFF} [ENTMAP {ON|OFF}] UND[ERLINE] {-|c|ON|OFF} [SPOOL {ON|OFF}] VER[IFY] {OFF|ON} [PRE[FORMAT] {ON|OFF}] WRA[P] {OFF|ON} An asterisk (*) indicates the SET option is not supported in iSQL*Plus. 24.能够disabled的命令：You can disable the following SQL*Plus commands: COPY HOST SET EDIT PASSWORD SPOOL EXECUTE QUIT START EXIT RUN GET SAVE You can also disable the following SQL commands: ALTER GRANT SET CONSTRAINTS ANALYZE INSERT SET ROLE AUDIT LOCK SET TRANSACTION CONNECT NOAUDIT TRUNCATE CREATE RENAME UPDATE DELETE REVOKE DROP SELECT You can also disable the following PL/SQL commands: BEGIN DECLARE 25.角色控制： Roles are created and used with the SQL CREATE, GRANT, and SET commands: n To create a role, you use the CREATE command. You can create roles with or without passwords. n To grant access to roles, you use the GRANT command. In this way, you can control who has access to the privileges associated with the role. n To access roles, you use the SET ROLE command. If you created the role with a password, the user must know the password in order to access the role. 26.不让用户在SQL*Plus中设置角色： To prevent application users from accessing application roles in SQL*Plus, you can use the PUP table to disable the SET ROLE command. You also need to disable the BEGIN and SQL*Plus EXECUTE commands to prevent application users setting application roles through a PL/SQL block. This allows a SQL*Plus user only those privileges associated with the roles enabled when they started SQL*Plus. 27.禁用用户权限： To disable a role for a given user, insert a row in the PUP table containing the user’s username in the Userid column, “ROLES” in the Attribute column, and the role name in the Char_Value column. Note: When you enter "PUBLIC" or "%" for the Userid column, you disable the role for all users. You should only use "%" or "PUBLIC" for roles which are granted to "PUBLIC". If you try to disable a role that has not been granted to a user, none of the roles for that user are disabled. The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example: PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- SQL*Plus HR ROLES ROLE1 SQL*Plus PUBLIC ROLES ROLE2 During login, these table rows are translated into the command SET ROLE ALL EXCEPT ROLE1, ROLE2 28.限制禁用： Like the Product User Profile table, the RESTRICT option allows you to disable certain commands that interact with the operating system. However, commands disabled with the -RESTRICT option are disabled even when no connection to a server exists, and remain disabled until SQL*Plus terminates. The following table shows which commands are disabled in each restriction level. Command Level 1 Level 2 Level 3 EDIT disabled disabled disabled GET disabled HOST disabled disabled disabled SAVE disabled disabled SPOOL disabled disabled START disabled STORE disabled disabled 29.连接isql*plus权限的种类： 9i： There are three modes of access to iSQL*Plus: n Connect as a normal user Requires an Oracle Database account username and password entered in the iSQL*Plus Login screen. n Connect as a SYSDBA or SYSOPER privileged user Requires an Oracle Database account username and password entered in the iSQL*Plus DBA Login screen, and an Oracle HTTP Server authentication username and password entered in a separate dialog. n Generate the iSQL*Plus Server statistics report Requires an Oracle HTTP Server authentication username and password 10g： There are two modes of access to iSQL*Plus: · Connect as a normal user. Requires an Oracle Database account username and password entered in the iSQL*Plus Login screen. · Connect as a SYSDBA or SYSOPER privileged user. Requires an Oracle Database account username and password entered in the iSQL*Plus DBA Login screen, and an Application Server authentication username and password entered in a separate dialog
The site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql.
5.定制自己用户登陆的设置：
SQL*Plus also supports a User Profile, executed after the Site Profile. This file is
generally named login.sql. SQL*Plus searches for the user profile in your current
directory, and then the directories you specify with the SQLPATH environment
variable. SQL*Plus searches this colon-separated list of directories in the order they
are listed.
6.改变自己登录文件的设置：SET SQLPROMPT Followed by the connect information variable in the form:
SET SQLPROMPT '&_CONNECT_IDENTIFIER > '
SET TIME Followed by ON, displays the current time before each
command prompt.
7.保存当前的设置，下次启动时直接执行：
store set login.sql create/replace
8. 登录：sqlplus –h获取帮助信息SQLPLUS [ [Options] [Logon] [Start] ]10.结束一个SQL块：n with a semicolon (;)
n with a slash (/) on a line by itself
n with a blank line
11.使用连接符”-”输入sqlplus命令：
12.设置login.sql文件:ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
SET PAGESIZE 24
SET LINESIZE 78
SET NUMFORMAT $99,99913.设置帮助：设置set oracle_home=E:/oracle/product/10.1.0/Db_1设置set system_pass=system/system运行helpins 向数据库中添加帮助信息%ORACLE_HOME%/BIN/HELPINS
14.设置自动提交：SET AUTOCOMMIT ON15.执行操作系统命令：
HOST COMMAND16.获取结构的命令：
Lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure.17.对于一个长的输出设置暂停：set pause on|off|18.SQLPlus注释需要注意的地方：1. Do not put comments within the first few keywords of a statement.
2. Do not put comments after statement terminators (period, semicolon or slash).
3. Do not put statement termination characters at the end of a comment line or
after comments in a SQL statement or a PL/SQL block.
4. Do not use ampersand characters ’&’ in comments in a SQL statement or
PL/SQL block.
19.使用SQLPlus执行sqlsqlplus scott/tiger@zianed @hello.sql20.执行嵌套脚本使用@@
21.退出执行：使用help w
获得帮助
22.不能使用 &&定义变量的部分情况：
You cannot use substitution variables in the buffer editing commands, APPEND,
CHANGE, DEL, and INPUT, nor in other commands where substitution would be
meaningless, such as in SQL*Plus comments (REMARK, /*... */ or --).
23.set命令
16:14:37 SCOTT@orcl 11-12月-08> help set SET
---
Sets a system variable to alter the SQL*Plus environment settings SET system_variable value
where system_variable and value represent one of the following clauses: APPI[NFO]{OFF|ON|text} 等价于通过dbms_applocation_info注册信息； ARRAY[SIZE] {15|n} 设置每一次sql取回的行数； AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} 设置自动提交； AUTOP[RINT] {OFF|ON} 自动打印 AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n} AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text} [EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED] | BLO[CKTERMINATOR] {.|c|OFF|ON} EA[CH]|OFF} CMDS[EP] {;|c|OFF|ON} RECSEPCHAR {_|c} COLSEP {_|text} SERVEROUT[PUT] {OFF|ON} COM[PATIBILITY] {V7|V8|NATIVE} [SIZE n] [FOR[MAT] CON[CAT] {.|c|OFF|ON} {WRA[PPED] | COPYC[OMMIT] {0|n} WOR[D_WRAPPED] | COPYTYPECHECK {OFF|ON} TRU[NCATED]}] DEF[INE] {&|c|OFF|ON} 设置&是否成为输入变量 DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]} [LINENUM {ON|OFF}] [INDENT {ON|OFF}] *SHOW[MODE] {OFF|ON} ECHO {OFF|ON} *SQLBL[ANKLINES] {OFF|ON} *EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] | EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]} ESC[APE] {/|c|OFF|ON} *SQLCO[NTINUE] {> | text} FEED[BACK] {6|n|OFF|ON} *SQLN[UMBER] {OFF|ON} FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} SQLPLUSCOMPAT[IBILITY] {x.y[.z]} *FLU[SH] {OFF|ON} *SQLPRE[FIX] {#|c} HEA[DING] {OFF|ON} *SQLP[ROMPT] {SQL>|text} HEADS[EP] {||c|OFF|ON} SQLT[ERMINATOR] INSTANCE [instance_path|LOCAL] {;|c|OFF|ON} LIN[ESIZE] {80|n} ({150|n} iSQL*Plus) *SUF[FIX] {SQL|text} LOBOF[FSET] {n|1} *TAB {OFF|ON} LOGSOURCE [pathname] *TERM[OUT] {OFF|ON} LONG {80|n} *TI[ME] {OFF|ON} LONGC[HUNKSIZE] {80|n} TIMI[NG] {OFF|ON} MARK[UP] HTML [ON|OFF] *TRIM[OUT] {OFF|ON} [HEAD text] [BODY text] [TABLE text] *TRIMS[POOL] {ON|OFF} [ENTMAP {ON|OFF}] UND[ERLINE] {-|c|ON|OFF} [SPOOL {ON|OFF}] VER[IFY] {OFF|ON} [PRE[FORMAT] {ON|OFF}] WRA[P] {OFF|ON} An asterisk (*) indicates the SET option is not supported in iSQL*Plus. 24.能够disabled的命令：You can disable the following SQL*Plus commands:
COPY HOST SET
EDIT PASSWORD SPOOL
EXECUTE QUIT START
EXIT RUN
GET SAVE
You can also disable the following SQL commands:
ALTER GRANT SET CONSTRAINTS
ANALYZE INSERT SET ROLE
AUDIT LOCK SET TRANSACTION
CONNECT NOAUDIT TRUNCATE
CREATE RENAME UPDATE
DELETE REVOKE
DROP SELECT
You can also disable the following PL/SQL commands:
BEGIN DECLARE
25.角色控制：
Roles are created and used with the SQL CREATE, GRANT, and SET commands:
n To create a role, you use the CREATE command. You can create roles with or
without passwords.
n To grant access to roles, you use the GRANT command. In this way, you can
control who has access to the privileges associated with the role.
n To access roles, you use the SET ROLE command. If you created the role with a
password, the user must know the password in order to access the role.
26.不让用户在SQL*Plus中设置角色：
To prevent application users from accessing application roles in SQL*Plus, you can
use the PUP table to disable the SET ROLE command. You also need to disable the
BEGIN and SQL*Plus EXECUTE commands to prevent application users setting
application roles through a PL/SQL block. This allows a SQL*Plus user only those
privileges associated with the roles enabled when they started SQL*Plus.
27.禁用用户权限：
To disable a role for a given user, insert a row in the PUP table containing the user’s
username in the Userid column, “ROLES” in the Attribute column, and the role
name in the Char_Value column.
Note: When you enter "PUBLIC" or "%" for the Userid column,
you disable the role for all users. You should only use "%" or
"PUBLIC" for roles which are granted to "PUBLIC". If you try to
disable a role that has not been granted to a user, none of the roles
for that user are disabled.
The Scope, Numeric_Value, and Date_Value columns should contain NULL. For
example:
PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE
VALUE VALUE VALUE
------- ------ --------- ----- -------- ------ -----
SQL*Plus HR ROLES ROLE1
SQL*Plus PUBLIC ROLES ROLE2
During login, these table rows are translated into the command
SET ROLE ALL EXCEPT ROLE1, ROLE2
28.限制禁用：
Like the Product User Profile table, the RESTRICT option allows you to disable
certain commands that interact with the operating system. However, commands
disabled with the -RESTRICT option are disabled even when no connection to a
server exists, and remain disabled until SQL*Plus terminates.
The following table shows which commands are disabled in each restriction level.
Command Level 1 Level 2 Level 3
EDIT disabled disabled disabled
GET disabled
HOST disabled disabled disabled
SAVE disabled disabled
SPOOL disabled disabled
START disabled
STORE disabled disabled
29.连接isql*plus权限的种类：
9i：
There are three modes of access to iSQL*Plus:
n Connect as a normal user
Requires an Oracle Database account username and password entered in the
iSQL*Plus Login screen.
n Connect as a SYSDBA or SYSOPER privileged user
Requires an Oracle Database account username and password entered in the
iSQL*Plus DBA Login screen, and an Oracle HTTP Server authentication
username and password entered in a separate dialog.
n Generate the iSQL*Plus Server statistics report
Requires an Oracle HTTP Server authentication username and password
10g：
There are two modes of access to iSQL*Plus:
· Connect as a normal user.
Requires an Oracle Database account username and password entered in the iSQL*Plus Login screen.
· Connect as a SYSDBA or SYSOPER privileged user.
Requires an Oracle Database account username and password entered in the iSQL*Plus DBA Login screen, and an Application Server authentication username and password entered in a separate dialog