方法一：登錄時會顯示版本號
[oracle@ogg1 ~]$ sqlplus / as sysdba       #有時直接輸入 #sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on 駘涓 7?20 17:51:14 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
方法二：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


用sysdba身份登錄 比如 conn /as  sysdba 匿名管理員登陸
  
查看SID
1.用sysdba身份登錄 比如 conn /as  sysdba
2.select instance_name from v$instance;
 
查看用戶名
select * from dba_users; --查看數(shù)據(jù)庫里面所有用戶，前提是你是有dba權(quán)限的帳號，如sys,system
select * from all_users;  --查看你能管理的所有用戶！
select * from user_users; --查看當(dāng)前用戶信息 ！

-------------------------------------------------------------------------------------------
[oracle@redhat4 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:05
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                06-OCT-2015 19:45:16
Uptime                    0 days 1 hr. 24 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4.7)(PORT=1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "orcl.7" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.7" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@redhat4 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:14
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@redhat4 ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:26
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4.7)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                06-OCT-2015 21:10:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4.7)(PORT=1521)))
The listener supports no services
The command completed successfully

-------------------------------------------------------------------------

創(chuàng)新互聯(lián)建站是專業(yè)的江門網(wǎng)站建設(shè)公司，江門接單;提供網(wǎng)站設(shè)計制作、成都網(wǎng)站設(shè)計,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進行江門網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!

（總結(jié)）Oracle監(jiān)聽服務(wù)lsnrctl參數(shù)及查詢狀態(tài)詳解

lsnrctl命令常用參數(shù)詳解：

lsnrctl
start
啟動指定的監(jiān)聽器

stop
關(guān)閉指定的監(jiān)聽器

status
顯示監(jiān)聽器的狀態(tài)。status命令顯示監(jiān)聽器是不是活動的，日志與跟蹤文件的位置，監(jiān)聽器已經(jīng)持續(xù)運行了多長時間，以及監(jiān)聽器所監(jiān)聽的任務(wù)。如下圖所示：

services
列舉監(jiān)聽器的服務(wù)信息，比如這些服務(wù)是否有任何專用的預(yù)生成服務(wù)器進程或與之相關(guān)的調(diào)度進程，以及每個服務(wù)已有多少連接被接受或拒絕。這種方法用來檢查一個監(jiān)聽器是否在監(jiān)聽一個指定服務(wù)。
列出服務(wù)的一個匯總表及為每個協(xié)議服務(wù)處理程序所建立和拒絕的連接信息個數(shù)。

Version
顯示oracle net軟件與協(xié)議適配器的版本。

reload
重新裝入監(jiān)聽器，重新讀取listener.ora文件，但不關(guān)閉監(jiān)聽器。如果該文件發(fā)生了變化，重新刷新監(jiān)聽器。

save_config
當(dāng)從lsnrctl工具中對listener.ora文件進行了修改時，復(fù)制一個叫做listener.bak的listener.ora的文件。

trace
打開監(jiān)聽器的跟蹤特性。

change_password
允許用戶修改關(guān)閉監(jiān)聽器所需要的密碼。

quit
退出lsnrctl工具。

exit
退出lsnrctl工具。

set*
password
指定在lsnrctl命令行工具中執(zhí)行管理任務(wù)所需要的密碼。

rawmode
displaymode
trc_file
指定監(jiān)聽器跟蹤信息的位置。默認設(shè)置是$ORACLE_HOME\network\trace\listener.trc

trc_directory
trc_level
跟蹤級別
OFF – 未啟用跟蹤功能。OFF 為默認設(shè)置。
USER – 將跟蹤設(shè)置為用戶的相應(yīng)級別。跟蹤以標(biāo)識用戶導(dǎo)致的錯誤條件。
ADMIN – 將跟蹤設(shè)置為數(shù)據(jù)庫管理員的相應(yīng)級別。跟蹤以標(biāo)識特定的安裝問題。
SUPPORT – 將跟蹤設(shè)置為客戶支持人員的相應(yīng)級別。 跟蹤文件可能會變得很大。
Oracle Corporation 建議在不進行網(wǎng)絡(luò)問題診斷時關(guān)閉跟蹤功能。

log_file
指定一個監(jiān)聽器將把日志信息寫到哪里。這個參數(shù)在默認的情況下是ON,并默認為%oracle_home%\network\log\listener.log

log_directory
log_status
current_listener
inbound_connect_timeout
定義監(jiān)聽器在一個會話得到啟動時將等待的有效響應(yīng)時間。默認設(shè)置為10秒。

startup_waittime
定義監(jiān)聽器在響應(yīng)lsnrctl命令行工具中的一條status命令之前將等待多長時間。

save_config_on_stop
指定在一個lsnrctl會話期內(nèi)所發(fā)生的修改在退出時是否應(yīng)該被保存起來。

show*
rawmode
顯示關(guān)于status和service的較詳細信息（當(dāng)他們設(shè)置成on時）值為ON或OFF。

displaymode
把lsnrctl工具的顯示模式設(shè)置成raw、compact、normal或verbose
服務(wù)顯示模式為NORMAL

rules
trc_file
指定監(jiān)聽器跟蹤信息的位置。默認設(shè)置是$ORACLE_HOME\network\trace\listener.trc

current_listener
目前的監(jiān)聽器為 listener2
inbound_connect_timeout
定義監(jiān)聽器在一個會話得到啟動時將等待的有效響應(yīng)時間。默認設(shè)置為10秒。

startup_waittime
定義監(jiān)聽器在響應(yīng)lsnrctl命令行工具中的一條status命令之前將等待多長時間。

snmp_visible
save_config_on_stop
指定在一個lsnrctl會話期內(nèi)所發(fā)生的修改在退出時是否應(yīng)該被保存起來。

lsnrctl部分查詢狀態(tài)詳解：

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm1.oracle.domain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                17-MAY-2011 21:03:40
Uptime                    0 days 0 hr. 2 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.1/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.1/db/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1600)))
Services Summary…
Service “POD” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…
Service “POD1” has 1 instance(s).
Instance “POD”, status UNKNOWN, has 1 handler(s) for this service…
Service “PODS” has 1 instance(s).
Instance “POD”, status READY, has 2 handler(s) for this service…
Service “pod_XPT” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…
Service “repos” has 2 instance(s).
Instance “repos”, status UNKNOWN, has 1 handler(s) for this service…
Instance “repos”, status READY, has 1 handler(s) for this service…
Service “repos_XPT” has 1 instance(s).
Instance “repos”, status READY, has 1 handler(s) for this service…
The command completed successfully

 

 status詳解

Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=1600)))

#此處說明該主機地址為ocm1.oracle.domain，開啟的監(jiān)聽端口有1521,1522,1600

Services Summary…
Service “POD” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…

#此處說明有一個服務(wù)名POD的服務(wù)，數(shù)據(jù)庫實例名為POD，狀態(tài)READY說明其目前處于動態(tài)注冊
Service “POD1” has 1 instance(s).
Instance “POD”, status UNKNOWN, has 1 handler(s) for this service…

#此處說明有一個服務(wù)名為POD1的服務(wù)，數(shù)據(jù)庫的實例名為POD，狀態(tài)UNKNOWN說明其目前是靜態(tài)注冊
Service “PODS” has 1 instance(s).
Instance “POD”, status READY, has 2 handler(s) for this service…

#此處說明有一個服務(wù)名為PODS的服務(wù)，數(shù)據(jù)庫實例名為POD，狀態(tài)READY說明其是動態(tài)注冊

 

Service “repos” has 2 instance(s).
Instance “repos”, status UNKNOWN, has 1 handler(s) for this service…
Instance “repos”, status READY, has 1 handler(s) for this service…
The command completed successfully

#此處說明有一個服務(wù)名為repos的服務(wù)，對應(yīng)數(shù)據(jù)庫實例為repos，UNKNOWN,READY狀態(tài)說明其目前有一個靜態(tài)注冊和一個動態(tài)注冊

 

 LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm1.oracle.domain)(PORT=1521)))
Services Summary…
Service “POD” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “POD1” has 1 instance(s).
Instance “POD”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “PODS” has 1 instance(s).
Instance “POD”, status READY, has 2 handler(s) for this service…
Handler(s):
“D001” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ocm1.oracle.domain, pid: 28400>
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37290))
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ocm1.oracle.domain, pid: 28398>
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37288))
Service “pod_XPT” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “repos” has 2 instance(s).
Instance “repos”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Instance “repos”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “repos_XPT” has 1 instance(s).
Instance “repos”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

 

service詳解

Service “POD” has 1 instance(s).
Instance “POD”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER

#名為POD的服務(wù)，對應(yīng)數(shù)據(jù)庫實例為POD，狀態(tài)READY說明其處于動態(tài)注冊

DEDICATED” established:0 refused:0 state:ready說明是專有連接，連接數(shù)為0拒絕數(shù)為0，狀態(tài)：動態(tài)
Service “POD1” has 1 instance(s).
Instance “POD”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER

#名為POD1的服務(wù)，對應(yīng)數(shù)據(jù)庫實例為POD，狀態(tài)UNKNOWN說明其處于靜態(tài)注冊

DEDICATED” established:2 refused:0說明是專有連接，連接數(shù)為2拒絕數(shù)為0
Service “PODS” has 1 instance(s).
Instance “POD”, status READY, has 2 handler(s) for this service…
Handler(s):
“D001” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ocm1.oracle.domain, pid: 28400>
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37290))
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ocm1.oracle.domain, pid: 28398>
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37288))

#名為PODS的服務(wù)，對應(yīng)數(shù)據(jù)庫實例名為POD的數(shù)據(jù)庫，READY狀態(tài)說明其為動態(tài)注冊

“D001” established:0 refused:0 current:0 max:1022 state:ready

 “D000” established:0 refused:0 current:0 max:1022 state:ready

這里說明其為share模式，目前，兩個dispatcher目前都連接為0，拒絕為0，最大數(shù)為1022，狀態(tài)是動態(tài)注冊

——————————————————————————————————————————————————————

對應(yīng)sql查詢

SQL> show parameter dispatchers

NAME                                 TYPE        VALUE
———————————— ———– ——————————
dispatchers                          string
max_dispatchers                      integer
SQL> alter system set dispatchers='(protocol=tcp) (dispatchers=2)’;System altered.SQL> show parameter dispatchersNAME                                 TYPE        VALUE
———————————— ———– ——————————
dispatchers                          string      (protocol=tcp) (dispatchers=2)
max_dispatchers                      integerSQL> select name,network,paddr,status from v$dispatcher;NAME NETWORK                                                                          PADDR    STATUS
—- ——————————————————————————– ——– —————-
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=seagull)(PORT=37288))                               2A220390 WAIT
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=seagull)(PORT=37290))                               2A220948 WAIT

——————————————————————————————————————————————————————

Service “repos” has 2 instance(s).
Instance “repos”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Instance “repos”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER

#repos服務(wù)對應(yīng)的實例有2個，但此處都為實例repos，一個為靜態(tài)UNKNOWN,一個為動態(tài)READY，目前連接數(shù)都為0，拒絕也為0
The command completed successfully

本文題目：Oracle一些比較常用的命令(持續(xù)更新)
文章分享：http://vcdvsql.cn/article8/gjdiop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、營銷型網(wǎng)站建設(shè)、域名注冊、商城網(wǎng)站、靜態(tài)網(wǎng)站、服務(wù)器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)