千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

Oracle一些比较常用的命令(持续更新)

发表于：2024-10-24 作者：千家信息网编辑
千家信息网最后更新 2024年10月24日，方法一：登录时会显示版本号[oracle@ogg1 ~]$ sqlplus / as sysdba #有时直接输入 #sqlplusSQL*Plus: Release 11.2.0.3.0
千家信息网最后更新 2024年10月24日Oracle一些比较常用的命令(持续更新)
方法一：登录时会显示版本号[oracle@ogg1 ~]$ sqlplus / as sysdba       #有时直接输入 #sqlplusSQL*Plus: Release 11.2.0.3.0 Production on 骀涓 7?20 17:51:14 2015Copyright (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 ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production用sysdba身份登录 比如 conn /as  sysdba 匿名管理员登陆  查看SID1.用sysdba身份登录 比如 conn /as  sysdba2.select instance_name from v$instance; 查看用户名select * from dba_users; --查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,systemselect * from all_users;  --查看你能管理的所有用户！select * from user_users; --查看当前用户信息 ！-------------------------------------------------------------------------------------------[oracle@redhat4 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:05Copyright (c) 1991, 2009, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date                06-OCT-2015 19:45:16Uptime                    0 days 1 hr. 24 min. 49 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xmlListening 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 stopLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:14Copyright (c) 1991, 2009, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))The command completed successfully[oracle@redhat4 ~]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-OCT-2015 21:10:26Copyright (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 - ProductionSystem parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xmlListening 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                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date                06-OCT-2015 21:10:26Uptime                    0 days 0 hr. 0 min. 0 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/redhat4/listener/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4.7)(PORT=1521)))The listener supports no servicesThe 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
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37290))
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(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
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.domain)(PORT=37290))
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(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









很赞哦！
监听 监听器 服务 跟踪 状态 数据 数据库 实例 动态 命令 信息 工具 用户 文件 时间 级别 静态 管理 位置 参数 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 农产品库存数据库 浅谈网络安全风险评估 阿里云服务器经常丢失 网络安全法是什么时候实施 2020国家网络安全答案 大智慧每天下载哪些数据库 建设智慧园区软件开发 电力系统网络安全全域防御体系 服务器负载均衡软件 socket 保存数据库 公司管理信息系统数据库课设 怀旧服各个服务器介绍 用户直接访问数据库安全吗 网络安全法 运营者的义务 杭州称林网络技术有限公司 数据库游标过大 数据库列排列 sql数据库跟踪工具 视频流媒体服务器搭建 电子邮件服务器警报 贵阳龙芯服务器如何挑选 陆夫人玩的mc服务器 服务器对ip绑定 网络安全管理专业的大学 第四届世界互联网大会新科技 ygopro2服务器 osee网络安全 苹果软件开发培训学校 河北回收二手服务器平台 九江毅涛网络技术有限公司

相关文章

0