怎样使用sqlplus连接oracle11g数据库 怎样使用sqlplus连接oracle11g数据库
\u600e\u6837\u4f7f\u7528sqlplus\u8fde\u63a5oracle11g\u6570\u636e\u5e93\u65b9\u6cd5\u4e00\uff1a\u5728\u5f00\u59cb\u83dc\u5355\u4e2d\uff0c\u627e\u5230oracle11g-\u5e94\u7528\u7a0b\u5e8f\u5f00\u53d1-SQL PLUS\u3002\u53cc\u51fbSQL PLUS\u3002
2
\u5f39\u51fa\u7684SQL Plus\u6846\u4e2d\uff0c\u8f93\u5165\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u7528\u6237\u540d\u548c\u5bc6\u7801\uff0c\u6309enter\u952e\u3002
3
\u5982\u679coracle\u670d\u52a1\u5668\u4e2d\u88c5\u6709\u591a\u4e2a\u6570\u636e\u5e93\u5b9e\u4f8b\uff0c\u5219\u5728\u7528\u6237\u540d\u5904\u8f93\u5165\uff1a\u7528\u6237\u540d/\u5bc6\u7801@\u6570\u636e\u5e93\u540d\u79f0\u3002\u5982\u679c\u6570\u636e\u5e93\u670d\u52a1\u5668\u4e0d\u5728\u672c\u673a\u4e0a\uff0c\u8fd8\u9700\u8981\u52a0\u4e0a\u6570\u636e\u5e93\u670d\u52a1\u5668\u7684\u5730\u5740\uff1a\u7528\u6237\u540d/\u5bc6\u7801@IP\u5730\u5740/\u6570\u636e\u5e93\u540d\u79f0\u3002
4
\u65b9\u6cd5\u4e8c\uff1a\u5728\u5f00\u59cb-\u8fd0\u884c\uff0c\u8f93\u5165cmd\u3002\u5f39\u51fa\u7684\u9ed1\u8272\u6846\u4e2d\u8f93\u5165\uff1asqlplus \u7528\u6237\u540d/\u5bc6\u7801@IP\u5730\u5740/\u6570\u636e\u5e93\u540d\u79f0\u3002
1\u3001win\u952e+R
2\u3001\u8f93\u5165"CMD"\u540e\u6309\u56de\u8f66
3\u3001\u5728\u547d\u4ee4\u7a97\u53e3\u4e2d\u8f93\u5165\uff1asqlplus
4\u3001\u6839\u636e\u63d0\u793a\uff0c\u8f93\u5165\u7528\u6237\uff0c\u7136\u540e\u6309\u56de\u8f66
5\u3001\u8f93\u5165\u5bc6\u7801
\u7b2c4\u548c\u7b2c5\u6b65\u662f\u5728\u8fde\u63a5\u672c\u5730\u6570\u636e\u5e93\uff0c\u7528\u4e00\u822c\u7528\u6237\u767b\u5f55\uff0c\u5982\u679c\u662fdba\u89d2\u8272\u767b\u5f55\uff0c\u9700\u8981\u5728\u7b2c4\u6b65\u7528\u683c\u5f0f:usr/pwd as sysdba
需要先在本机装一个oracle服务端
不想装客户端可以去官网上下载一个instantclient-basic-nt-11.2.0.4.0文件
然后在sqlplus界面 的工具-》首选项-》oracle-》连接里面配客户端地址
在E:\uplooking\work\Oracle\instantclient_11_2（我的oracle客户端安装目录）目录下，新建两个文件，名称分别为 sqlnet.ora，tnsnames.ora。这两个文件提供了客户端服务名到指定的oracle服务器提供的数据库服务名的映射。
sqlnet.ora内容如下：
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)
tnsnames.ora内容如下：
xndata =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldb)
)
)
其中，
客户端服务名可以任意设置，
服务器IP地址就是提供oracle服务的机器IP，
服务器端侦听器提供的服务名是根据服务器端的设置填写。
【注意】客户端服务名必须顶格写，前面不能有任何空格，否则会出错。
然后，为系统添加环境变量TNS_ADMIN，其值为oracle客户端安装目录，本例中就是TNS_ADMIN：E:\uplooking\work\Oracle\instantclient_11_2
注：使环境变量不用重启电脑也可以生效方法
cmd命令行下：set TNS_ADMIN=E:\uplooking\work\Oracle\instantclient_11_2 设置环境变量
重新打开一个cmd命令行：echo %TNS_ADMIN% --检查是否输出新的环境变量值
oracle认证2种方式:
1本机认证, windows(administrator), unix(root) 用户的前提下,
你可以直接, sqlplus /as systdba 就可以直接连了
2用户名密码验证
确保你的tnsnames.ora的配置是正确的, 配置的示例alias会被用来使用连接数据库
sqlplus username/password@db_alias_name 这样就可以连接并使用了.
看 help啊，最基本的操作命令 help都有。
1、切换到oracle用户
[root@oracle ~]# su - oracle
2、查看帮助
[oracle@oracle ~]$ sqlplus --help
SQL*Plus: Release 11.2.0.1.0 Production
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V
-H Displays the SQL*Plus version and the
usage help.
-V Displays the SQL*Plus version.
Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]
<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
-C <version> Sets the compatibility of affected commands to the
version specified by <version>. The version has
the form "x.y[.z]". For example, -C 10.2.0
-L Attempts to log on just once, instead of
reprompting on error.
-M "<options>" Sets automatic HTML markup of output. The options
have the form:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.
<logon> is: {<username>[/<password>][@<connect_identifier>] | / }
[AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]
Specifies the database account username, password and connect
identifier for the database connection. Without a connect
identifier, SQL*Plus connects to the default database.
The AS SYSDBA, AS SYSOPER and AS SYSASM options are database
administration privileges.
<connect_identifier> can be in the form of Net Service Name
or Easy Connect.
@[<net_service_name> | [//]Host[:Port]/<service_name>]
<net_service_name> is a simple name for a service that resolves
to a connect descriptor.
Example: Connect to database using Net Service Name and the
database net service name is ORCL.
sqlplus myusername/mypassword@ORCL
Host specifies the host name or IP address of the database
server computer.
Port specifies the listening port on the database server.
<service_name> specifies the service name of the database you
want to access.
Example: Connect to database using Easy Connect and the
Service name is ORCL.
sqlplus myusername/mypassword@Host/ORCL
The /NOLOG option starts SQL*Plus without connecting to a
database.
The EDITION specifies the value for Session Edition.
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]
Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.
When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQL*Plus commands.
Refer to the SQL*Plus User's Guide and Reference for more information.
3、简单的示范：
[oracle@oracle ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 16:27:54 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: user1
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from dual;
D
-
X
SQL>
打开本地的Oracle数据库：
在CMD窗口输入：sqlplus 回车后提示输入用户名和密码即可；
打开远程的Oracle数据库：
在CMD窗口中输入：sqlplus 用户名/密码@TNS
TNS：配置好的远程数据库服务实例名
绛旓細闇瑕佸厛鍦ㄦ湰鏈鸿涓涓猳racle鏈嶅姟绔 涓嶆兂瑁呭鎴风鍙互鍘诲畼缃戜笂涓嬭浇涓涓猧nstantclient-basic-nt-11.2.0.4.0鏂囦欢 鐒跺悗鍦sqlplus鐣岄潰 鐨勫伐鍏-銆嬮閫夐」-銆媜racle-銆杩炴帴閲岄潰閰嶅鎴风鍦板潃
绛旓細1锛 鎵ц鏁版嵁瀵煎叆瀵煎嚭 2锛 浣跨敤sql*loader鎻愬彇澶栭儴鏁版嵁 3锛 闇瑕佹殏鏃舵嫆缁濇櫘閫氱敤鎴疯闂暟鎹簱 4锛 杩涜鏁版嵁搴撶Щ妞嶆垨鑰呭崌绾ф搷浣 杩4绉嶆搷浣滈兘蹇呴』鍦ㄨ繖涓姸鎬佷笅杩涜 鍦ㄦ墦寮鏁版嵁搴撴椂浣跨敤startup restrict鍛戒护鍗宠繘鍏ュ彈闄愮姸鎬併or37.gif 浣跨敤alter system disable restricted session鍛戒护鍗冲彲浠ュ皢鍙楅檺鐘舵佹敼鍙...
绛旓細涓鑸湪sqlplus鎴栬呭叾浠栫涓夋柟oracle宸ュ叿涓紝鎸夌収璇硶鍙婇渶姹傚啓濂戒唬鐮侊紝鐩存帴鎵ц鍒涘缓杩囩▼鍗冲彲銆備竴鑸娉曞涓嬶細CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER }{INSERT | DELETE | UPDATE [OF column [, column 鈥]}[OR {INSERT | DELETE | UPDATE [OF column [, column 鈥]}...]ON...
绛旓細1銆佹鏌qlnet.ora锛圵INDOWS涓嬩綅浜%ORACLE_HOME%NETWORKADMIN鐩綍锛夋槸鍚﹀寘鍚繖鍙ワ細SQLNET.AUTHENTICATION_SERVICES=(NTS)锛屾病鏈夌殑璇濆姞涓 2銆佹鏌ョ櫥闄唚indows鐨勭敤鎴(administrator鎴栧畨瑁卭racle鏃跺浣跨敤鐨勭敤鎴)鏄笉鏄湪鍖呭惈鍦∣RA_DBA缁勪腑锛屽煙鐢ㄦ埛娌℃湁杩炰笂鍩熸湇鍔″櫒鏃跺氨鍙兘鍑虹幇杩欑鐜拌薄銆3. 瑕佷繚璇 remote_login...
绛旓細鐢╯qlplus绋嬪簭閫氳繃test缃戠粶鏈嶅姟鍚嶈繘琛屾祴璇,濡俿qlplus system/manager@test銆傚鏋滀笉鑳杩炴帴鍒版暟鎹簱,鍒欏湪tnsname.ora鏂囦欢涓殑test缃戠粶鏈嶅姟鍚(net service)鍚庨潰鍔犱笂sqlnet.ora鏂囦欢涓璑AMES.DEFAULT_DOMAIN鍙傛暟鐨勫,姝ゅ鎴戠殑鍙傛暟鍊间负testserver.com,灏嗗叾鍔犲埌缃戠粶鏈嶅姟鍚嶅悗闈,淇敼鍚庣殑tnsname.ora涓叧浜庤缃戠粶鏈嶅姟鍚嶇殑鍐呭涓:...
绛旓細sys as sysdba鏄搷浣滅郴缁熻璇佺殑锛屽繀椤诲湪瀹夎oracle鐨勬満鍣ㄤ笂鎵ц sqlplus sys/password@瀹炰緥鍚嶇櫥闄嗘姤閿欙紝浣犵‘瀹氫綘鍚庨潰鍐欑殑瀹炰緥鍚嶆纭箞锛熷ぇ閮ㄥ垎浜虹殑鎯呭喌鏄痵qlplus鍙互鐧诲綍锛宻ql developer涓嶈兘鐧诲綍锛屽ソ濂界‘璁や綘鐨勫疄渚嬪悕
绛旓細绗簩涓棶棰橈細鍏堢敵鏄巓ut鍙傛暟锛屽啀鎵ц杩囩▼锛岃寰楄繃绋嬩腑out鍙傛暟鍓嶇殑鍐掑彿銆傚嵆锛歋QL> VAR PROLD2 VARCHAR;SQL> EXEC PRO_selectById(proId1value,:PROLD2);鍥炶溅鏄剧ずPL/SQL procedure successfully completed鍗宠〃绀烘墽琛屾垚鍔熴傦紙娉ㄦ剰proId1value琛ㄧず鍏ュ弬鐨勫硷級...
绛旓細not be found in a naming method repository, or the repository could not be located or reached.浠ヤ笂鏄畼鏂圭殑瑙ｉ噴锛岃鏄庝綘鐩戝惉鏈夐棶棰樸備綘sqlplus / as sysdba鎴栬呮槸sqlplus scott/tiger杩炴帴鐨勬暟鎹簱鍚э紵杩欑鏂瑰紡鏄笉璧扮綉缁滅殑锛屾墍浠ユ湁娌℃湁鐩戝惉閮借銆鐢netca/netmgr閰嶇疆涓嬬洃鍚氨濂戒簡锛屼綘璇曡瘯銆
绛旓細1銆佷互sys鐧诲綍涓簊ysdbasqlplus /NOLOG鎴栬咃紙sqlplus sys/sys@localhost/orcl as sysdba锛寃indow绯荤粺涓嬮儴缃茬殑 oracle鏁版嵁搴撴绘槸鍚勭bug锛屾垜鐨勫繀椤绘坊鍔爄p鍦板潃鎸囧悜鎵嶈兘杩炴帴锛夈2銆佺櫥褰曞埌sys鐢ㄦ埛鍚庯紝鍏抽棴鏁版嵁搴擄紝杈撳叆鍛戒护锛歴hutdown锛涘洖杞︼紝缁撴灉鎻愮ず鏁版嵁搴撴湭鎵撳紑銆3銆佸惎鍔ㄦ暟鎹簱锛岃緭鍏ュ懡浠tartup mount;鍥炶溅鍚...
绛旓細搴旇鏄睛鍚殑闂銆備綘鍦ㄥ懡浠よ涓嬭緭鍏ワ細sqlplus scott/tiger 濡傛灉鍙互鐧诲綍锛岃鏄庢暟鎹簱姝ｅ父 sqlplus scott/tiger@orcl 濡傛灉涓婇潰鐨勬甯革紝鑰岃繖涓湁闂锛岃鏄庝睛鍚湁闂銆
