Oracle一些比較常用的命令(持續更新)

方法一：登錄時會顯示版本號
[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; --查看數據庫里面所有用戶，前提是你是有dba權限的帳號，如sys,system
select * from all_users;  --查看你能管理的所有用戶！
select * from user_users; --查看當前用戶信息 ！

-------------------------------------------------------------------------------------------
[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

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

（總結）Oracle監聽服務lsnrctl參數及查詢狀態詳解

lsnrctl命令常用參數詳解：

lsnrctl
start
啟動指定的監聽器

stop
關閉指定的監聽器

status
顯示監聽器的狀態。status命令顯示監聽器是不是活動的，日志與跟蹤文件的位置，監聽器已經持續運行了多長時間，以及監聽器所監聽的任務。如下圖所示：

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

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

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

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

trace
打開監聽器的跟蹤特性。

change_password
允許用戶修改關閉監聽器所需要的密碼。

quit
退出lsnrctl工具。

exit
退出lsnrctl工具。

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

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

trc_directory
trc_level
跟蹤級別
OFF – 未啟用跟蹤功能。OFF 為默認設置。
USER – 將跟蹤設置為用戶的相應級別。跟蹤以標識用戶導致的錯誤條件。
ADMIN – 將跟蹤設置為數據庫管理員的相應級別。跟蹤以標識特定的安裝問題。
SUPPORT – 將跟蹤設置為客戶支持人員的相應級別。 跟蹤文件可能會變得很大。
Oracle Corporation 建議在不進行網絡問題診斷時關閉跟蹤功能。

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

log_directory
log_status
current_listener
inbound_connect_timeout
定義監聽器在一個會話得到啟動時將等待的有效響應時間。默認設置為10秒。

startup_waittime
定義監聽器在響應lsnrctl命令行工具中的一條status命令之前將等待多長時間。

save_config_on_stop
指定在一個lsnrctl會話期內所發生的修改在退出時是否應該被保存起來。

show*
rawmode
顯示關于status和service的較詳細信息（當他們設置成on時）值為ON或OFF。

displaymode
把lsnrctl工具的顯示模式設置成raw、compact、normal或verbose
服務顯示模式為NORMAL

rules
trc_file
指定監聽器跟蹤信息的位置。默認設置是$ORACLE_HOME\network\trace\listener.trc

current_listener
目前的監聽器為 listener2
inbound_connect_timeout
定義監聽器在一個會話得到啟動時將等待的有效響應時間。默認設置為10秒。

startup_waittime
定義監聽器在響應lsnrctl命令行工具中的一條status命令之前將等待多長時間。

snmp_visible
save_config_on_stop
指定在一個lsnrctl會話期內所發生的修改在退出時是否應該被保存起來。

lsnrctl部分查詢狀態詳解：

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，開啟的監聽端口有1521,1522,1600

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

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

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

#此處說明有一個服務名為PODS的服務，數據庫實例名為POD，狀態READY說明其是動態注冊

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

#此處說明有一個服務名為repos的服務，對應數據庫實例為repos，UNKNOWN,READY狀態說明其目前有一個靜態注冊和一個動態注冊

 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的服務，對應數據庫實例為POD，狀態READY說明其處于動態注冊

DEDICATED” established:0 refused:0 state:ready說明是專有連接，連接數為0拒絕數為0，狀態：動態
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的服務，對應數據庫實例為POD，狀態UNKNOWN說明其處于靜態注冊

DEDICATED” established:2 refused:0說明是專有連接，連接數為2拒絕數為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的服務，對應數據庫實例名為POD的數據庫，READY狀態說明其為動態注冊

“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，最大數為1022，狀態是動態注冊

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

對應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服務對應的實例有2個，但此處都為實例repos，一個為靜態UNKNOWN,一個為動態READY，目前連接數都為0，拒絕也為0
The command completed successfully