SQL*Plus连接语句

Oraclede的 SQL*PLus提供了一个命令行方式的解释器，同时支持SQL和PL/SQL。SQL*Plus可以接受用户输入的语句，然后把语句发送给Oracle服务器，并显示处理结果。

 

1.启动SQL*Plus

为了启动SQL*Plus需要跟随以下步骤：

1.1执行以下的其中一项

• 确认PATH环境变量包含了ORACLE_HOME/bin。
• 改变目录为ORACLE_HOME/bin。

1.2输入命令

sqlplus /nolg

（注意：LINUX和UNIX操作系统对大小写敏感）

 

2.SQL*Plus连接语句

提交SQL*Plus连接语句开始连接到Oracle实例，或在任何时刻重新连接到一个不同的用户。连接语句的语法如下：

CONN[ECT] [username | /][@connect_identifier] [AS {SYSOPER | SYSDBA}]

当你输入用户名后，SQL*Plus会提示输入密码，你输入的密码是不会被显示的。

下表描述了在CONNECT语句的语法成分。

语法成分	描述
/	调用连接请求的外部验证，这种验证类型不需要数据库密码。最常见的外部验证结构是操作系统验证，数据库用户身份验证通过使用主机账户登录到主机操作系统。也可以通过Oracle钱包或由网络服务进行外部认证
AS {SYSOPER | SYSDBA	指明数据库用户正在以SYSOPER或SYSDBA系统权限连接，只有某些预定义的用户或者已经添加到密码文件的用户才能使用这些权限。
username	一个有效的数据库用户名。数据库通过在数据字典中查找匹配的用户名验证请求，并提示数据用户密码。
connect_identifier(1)	一个Oracle Net连接标识符，用来远程连接，确切语法取决于Oracle Net配置。如果省略，SQL*Plus尝试连接到本地实例。  一个常见的​​连接标识符是OracleNet连接描述符（网络地址和数据库服务名）的别名，网络服务名称。网络服务名称通常是定义在本地tnsname.ora文件中，但也可以使用其他方法定义。
connect_identifier(2)	作为另外一个连接标识符，它可以使用简单的连接语法。简易连接提供了即装即用TCP/IP连接到远程数据库，而无需在本地电脑上配置Oracle网络服务。 连接的连接标识符的语法如下： host[:port][/service_name] 其中： host:远程数据库计算机的主机名或IP地址。 port:主机上Oracle网络监听器监听数据库连接的TCP端口。如果省略，默认是1521。 service_name：数据库服务名，如果远程主机上的Oracle网络监听器配置默认服务，则可以省略。如果没有配置值默认服务，则必须要提供。每个数据库通常提供的服务名称等于全局数据库名

 

3.EXAMPLES

3.1使用SYSDBA系统权限以操作系统验证方式连接到数据库

connect / as sysdba

 

3.2使用简易连接标识符连接到远程数据库

connect salesadmin@db1.mycompany.com:1522/sales.mycompany.com

4.SQL*PLUS命令汇总

Command	Description
@ (at sign)	Runs SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server.
@@ (double at sign)	Runs a script. This command is similar to the @ (at sign) command It is useful for running nested scripts because it looks for the specified script in the same path as the calling script.
/ (slash)	Executes the SQL command or PL/SQL block.
ACCEPT	Reads a line of input and stores it in a given substitution variable.
APPEND	Adds specified text to the end of the current line in the buffer.
ARCHIVE LOG	Starts or stops the automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files.
ATTRIBUTE	Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes.
BREAK	Specifies where and how formatting will change in a report, or lists the current break definition.
BTITLE	Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition.
CHANGE	Changes text on the current line in the buffer.
CLEAR	Resets or erases the current clause or setting for the specified option, such as BREAKS or COLUMNS.
COLUMN	Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns.
COMPUTE	Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions.
CONNECT	Connects a given user to Oracle Database.
COPY	Copies results from a query to a table in the same or another database.
DEFINE	Specifies a substitution variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
DEL	Deletes one more lines of the buffer.
DESCRIBE	Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function procedure.
DISCONNECT	Commits pending changes to the database and logs the current user off Oracle Database, but does not exit SQL*Plus.
EDIT	Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer.
EXECUTE	Executes a single PL/SQL statement.
EXIT	Terminates SQL*Plus and returns control to the operating system.
GET	Loads an operating system file into the buffer.
HELP	Accesses the SQL*Plus command-line help system.
HOST	Executes an operating system command without leaving SQL*Plus.
INPUT	Adds one or more new lines after the current line in the buffer.
LIST	Lists one or more lines of the buffer.
PASSWORD	Enables a password to be changed without echoing the password on an input device.
PAUSE	Displays the specified text, then waits for the user to press Return.
PRINT	Displays the current value of a bind variable.
PROMPT	Sends the specified message to the user's screen.
EXIT	Terminates SQL*Plus and returns control to the operating system QUIT is identical to EXIT.
RECOVER	Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.
REMARK	Begins a comment in a script.
REPFOOTER	Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
REPHEADER	Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
RUN	Lists and runs the SQL command or PL/SQL block currently stored in the SQL buffer.
SAVE	Saves the contents of the buffer in an operating system file (a script).
SET	Sets a system variable to alter the SQL*Plus environment for your current session.
SHOW	Shows the value of a SQL*Plus system variable or the current SQL*Plus environment.
SHUTDOWN	Shuts down a currently running Oracle Database instance.
SPOOL	Stores query results in an operating system file and, optionally, sends the file to a printer.
START	Runs the SQL statements in the specified script. The script can be called from a local file system or a web server in SQL*Plus command-line.
STARTUP	Starts an Oracle Database instance and optionally mounts and opens a database.
STORE	Saves attributes of the current SQL*Plus environment in an operating system script.
TIMING	Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers.
TTITLE	Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.
UNDEFINE	Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
VARIABLE	Declares a bind variable that can be referenced in PL/SQL.
WHENEVER OSERROR	Exits SQL*Plus if an operating system command generates an error.
WHENEVER SQLERROR	Exits SQL*Plus if a SQL command or PL/SQL block generates an error.
XQUERY	Runs an XQuery 1.0 statement.