oracle日常运维11g

==================–当前用户定时任务selectcount()fromuser_jobs;–有权限访问的定时任务selectcount()fromall_jobs;–整个数据库所有定时任务selectcount(*)fromdba_jobs;solarisprstat-a查看进程视图lsnrctlstatus--数据库监听状态======================启动关闭检...

==================
–当前用户定时任务
select count() from user_jobs;
–有权限访问的定时任务
select count() from all_jobs;
–整个数据库所有定时任务
select count(*) from dba_jobs;solaris

prstat -a 查看进程视图
lsnrctl status --数据库监听状态
======================启动关闭检查
Oracle Net Listener的进程:
ps -ef | grep tnslsnr

Oracle Background Process进程:
ps -ef | grep -i “ora_”

Oracle Management Agent
ps -ef | grep emagent

OACELE 12C
sqlplus / nolog
conn sys/passwd as sysdba;

=====================查询机器数据源当前连接数
select b.MACHINE, b.PROGRAM , count()
from vprocessa,vprocess a, vprocessa,vsession b
where a.ADDR = b.PADDR and b.USERNAME is not null
group by b.MACHINE , b.PROGRAM order by count() desc;
============================= 查询账号连接消耗内存
SELECT server “连接类型”,
s.username,
OSUSER,
NAME,
VALUE/1024/1024 “占用内存MB”,
s.SID “会话ID”,
s.serial#,
spid “操作系统进程ID”,
p.PGA_USED_MEM,
p.PGA_ALLOC_MEM,
p.PGA_FREEABLE_MEM,
p.PGA_MAX_MEM
FROM vsessions,vsession s, vsessions,vsesstat st, vstatnamesn,vstatname sn, vstatnamesn,vprocess p
WHERE st.SID = s.SID
AND st.statistic# = sn.statistic#
AND sn.NAME LIKE ‘session pga memory’
AND p.addr = s.paddr
ORDER BY VALUE DESC
===========================查询磁盘读过多的SQL
select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000 order by a.disk_reads desc;

-----------锁表,kill
SELECT sid, serial#FROM vsessionWHEREsid=(SELECTsidFROMvsession WHERE sid = (SELECT sid FROM vsessionWHEREsid=(SELECTsidFROMvlock
WHERE id1 = (SELECT object_id FROM user_objects
WHERE object_name = upper(‘aaa’)));

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

select b.username,b.sid,b.serial#,logon_time from vlockedobjecta,vlocked_object a,vlockedo​bjecta,vsession b where a.session_id = b.sid order by b.logon_time;

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM vsessions,vsession s, vsessions,vlock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

–alter system kill session ‘sid,serial#’;

oracle查询账号密码周期
SELECT * FROM dba_profiles s WHERE s.profile=‘DEFAULT’ AND resource_name=‘PASSWORD_LIFE_TIME’;

-----------------------***********************************************************************
linux下获取占用CPU资源最多的10个进程,可以使用如下命令组合:
ps aux|head -1;ps aux|grep -v PID|sort -rn -k 3|head
linux下获取占用内存资源最多的10个进程,可以使用如下命令组合:
ps aux|head -1;ps aux|grep -v PID|sort -rn -k 4|head
命令组合解析(针对CPU的,MEN也同样道理):
ps aux|head -1;ps aux|grep -v PID|sort -rn -k 3|head
该命令组合实际上是下面两句命令:
ps aux|head -1
ps aux|grep -v PID|sort -rn -k 3|head

ps -ef|grep oraclecedata |sort -r -k 5,7 |head -20

查看当前50个进程
ps -ef|grep oraclecedata |sort -r -k 5,7 |head -50

------------监听日志 http://www.cnblogs.com/kerrycode/p/4227579.html
可以移走监听日志文件(listener.log),数据库实例会自动创建一个listener.log文件
% lsnrctl set log_status off

% mv listener.log listener.log.yyyymmdd

% lsnrctl set log_status on

–linux head------------
查看监听日志
head -n 10 listener.log//查询日志文件中的头10行日志;
head -n -10 listener.log//查询日志文件除了最后10行的其他所有日志;

–linux sed-------------
//按照行号
sed -n ‘5,10p’ listener.log //这样你就可以只查看文件的第5行到第10行。
//按照时间段
sed -n ‘/2019-01-01 16:17:20/,/2019-02-01 11:17:36/p’ listener.log

–linux tail -----------
-f 循环读取
-q 不显示处理信息
-v 显示详细的处理信息
-c<数目> 显示的字节数
-n<行数> 显示行数
-q, --quiet, --silent 从不输出给出文件名的首部
-s, --sleep-interval=S 与-f合用,表示在每次反复的间隔休眠S秒

tail -n 10test.log查询日志尾部最后10行的日志;
tail -n 10test.log查询10行之后的所有日志;
tail -fn 10test.log循环实时查看最后1000行记录(最常用的)

//一般还会配合着grep用, 例如 : tail -fn 1000 test.log | grep ‘关键字’
如果一次性查询的数据量太大,可以进行翻页查看,
例如:tail -n 4700 aa.log |more -1000 可以进行多屏显示(ctrlf 或者 空格键可以快捷键)

–linux cat -----------
一次显示整个文件 : $ cat filename
从键盘创建一个文件 : $ cat > filename
将几个文件合并为一个文件: $cat file1 file2 > file //只能创建新文件,不能编辑已有文件.
将一个日志文件的内容追加到另外一个 : $cat -n textfile1 > textfile2
清空一个日志文件 $cat : >textfile2

history | more // 分页查看记录

/var/log/message 系统启动后的信息和错误日志,是Red Hat Linux中最常用的日志之一
/var/log/secure 与安全相关的日志信息
/var/log/maillog 与邮件相关的日志信息
/var/log/cron 与定时任务相关的日志信息
/var/log/spooler 与UUCP和news设备相关的日志信息
/var/log/boot.log 守护进程启动和停止相关的日志消息
/var/log/wtmp 该日志文件永久记录每个用户登录、注销及系统的启动、停机的事件

===================================================
create user wsv_read identified by pass;
grant connect to wsv_read;
grant resource to wsv_read;
select ‘GRANT SELECT ON WSV_MACHINE.’||object_name||’ to wsv_read;’ from dba_objects where owner=‘WSV_MACHINE’ and object_type=‘TABLE’;

1.GRANT 赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

常用的数据对象权限有以下五个:
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;

索引=================================================
组合索引的两列 当有一列是等值查询,一列是范围查询,等值查询列在前,范围查询列在后,索引会高效

如果单列查询列和联合索引的前置列一样,单列可以不建索引,直接利用联合索引来检索数据
索引的监控跟踪
alter index index_name monitoring usage;
查询是否使用
select * from v$object_usage;
user_indexes

取消索引的监控跟踪
alter index index_name nomonitoring usage;

alter index index_name unusable;命令使索引失效
alter session set skip_unusable_indexes=true;就可以在session级别跳过无效索引作查询。
alter index index_name rebuild (online);

说明:

  • alter session set skip_unusable_indexes=true;就可以在session级别跳过无效索引作查询。
  • 分区索引应适用user_ind_partitions。
  • 状态分4种:
    N/A说明这个是分区索引需要查user_ind_partitions或者user_ind_subpartitions来确定每个分区是否可用;
    VAILD说明这个索引可用;
    UNUSABLE说明这个索引不可用;
    USABLE 说明这个索引的分区是可用的。
  • 查询当前索引的状态:select distinct status from user_indexes;
  • 查询那个索引无效:select index_name from user_indexes where status <> ‘VALID’;
  • 详细讲解可参考:http://www.sudu.cn/info/html/edu/20071225/20526.html。
  • 批量rebuild下:select ‘alter index ‘||index_name||’ rebuild online;’ from user_indexes where status <> ‘VALID’ and index_name not like’%$$’;
  • --------------------------oracle 用户被锁
    在登陆时被告知test用户被锁
    1、用dba角色的用户登陆,进行解锁,先设置具体时间格式,以便查看具体时间
    SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
    Session altered.

    2、查看具体的被锁时间

    SQL> select username,lock_date from dba_users where username=’TEST’;

    USERNAME LOCK_DATE
    TEST 2009-03-10 08:51:03

    3、解锁
    SQL> alter user test account unlock;
    User altered.
    4、查看是那个ip造成的test用户被锁

    查看$ORACLE_HOME/network/admin/log/listener.log日志

    10-MAR-2009 08:51:03 * (CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.69.1.11)(PORT=49434)) * establish * lhoms * 0

    10-MAR-2009 08:51:03 * (CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.69.1.11)(PORT=49435)) * establish * lhoms * 0www.Examda.CoM考试就到考试大

    这样可知是上面10.69.1.11的ip尝试多次失败登陆造成的被锁

    注:

    一般数据库默认是10次尝试失败后锁住用户

    1、查看FAILED_LOGIN_ATTEMPTS的值

    select * from dba_profiles

    2、修改为30次

    alter profile default limit FAILED_LOGIN_ATTEMPTS 30;

    3、修改为无限次(为安全起见,不建议使用)

    alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

    oracle查看被锁的表和解锁
    –以下几个为相关表
    SELECT * FROM vlock;SELECT∗FROMvlock;SELECT * FROM vlock;SELECT∗FROMvsqlarea;
    SELECT * FROM vsession;SELECT∗FROMvsession;SELECT * FROM vsession;SELECT∗FROMvprocess ;
    SELECT * FROM vlockedobject;SELECT∗FROMallobjects;SELECT∗FROMvlocked_object;SELECT * FROM all_objects;SELECT * FROM vlockedo​bject;SELECT∗FROMallo​bjects;SELECT∗FROMvsession_wait;

    –查看被锁的表
    select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

    –查看用户进程照成死锁
    select b.username,b.sid,b.serial#,logon_time from vlockedobjecta,vlocked_object a,vlockedo​bjecta,vsession b where a.session_id = b.sid order by b.logon_time;

    –查看连接的进程
    SELECT sid, serial#, username, osuser FROM v$session;

    –3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode
    SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
    s.terminal, s.logon_time, l.type
    FROM vsessions,vsession s, vsessions,vlock l
    WHERE s.sid = l.sid
    AND s.username IS NOT NULL
    ORDER BY sid;

    这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,
    任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。

    –杀掉进程 sid,serial#
    alter system kill session’210,11562’;

    oracle 查询表的主外键
    1>根据所有主外键关联的表
    select (select a.table_name
    from user_constraints a
    where a.constraint_name = c.r_constraint_name) 主表表名,
    c.r_constraint_name 主表主键键约束名,
    c.table_name 从表表名,
    c.constraint_name 从表外键约束名,c.*
    from user_constraints c
    where c.constraint_type = ‘R’;

    2>查询表的约束
    select * from user_cons_columns

    3>查询表的字段信息
    select * from user_tab_columns

    主外键删除数据(如果没有级联删除)
    先删除子表,在删除主表 delete
    插入数据时,先插入主表,后插入子表
    (以下慎用)
    1alter table table_name disable primary key cascade; (级联删除)
    2、启用外键约束的命令为:alter table 表名 enable constraint 约束名
    3、禁用外键约束的命令为:alter table 表名 disable constraint 约束名

    ===12c
    col pdb_name for a30

    select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    切换容器
    alter session set container=PDBORACL12C;
    alter session set container=CDB$ROOT;

    col username for a20
    col con_id for a20
    col common for a20
    select username,con_id,common from cdb_users;

    源文地址:https://www.guoxiongfei.cn/csdn/4639.html
    0