所有文章 标签 分类 关于 友链 视频 阅读 工具

Oracle维护

不忘初心  收录于  类别 DB
     约 2444 字   预计阅读 5 分钟    条评论  

记录维护Oracle用到的一些语句

约束类型Type Code规范命名名称说明
主键约束PPK_表名_列名Primary Key
外键约束RFK_表名_列名Foreign Key
非空约束NN_表名_列名Not Null
唯一约束UUK_表名_列名Unique Key
检查约束CCK_表名_列名Check
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 常用视图 (权限由大到小: dba_* > all_* > user_*)
-- dba_constraints：侧重约束具体信息
-- dba_cons_columns：侧重约束列信息
-- user_constraints：当前用户模式下创建的对象
-- all_constraints：当前用户模式下创建的对象加上当前用户能访问的其他用户创建的对象

-- 参考如下：

select * from dba_constraints;
select * from user_constraints;
select * from all_constraints;
select * from dba_cons_columns;
select * from user_cons_columns;
select * from all_cons_columns;

1
2
3
4
5
6
7
8
9

-- 1.唯一性约束
alter table 表名 add constraint uk_* unique(列名) [not null];

-- 2.检查约束
alter table 表名 add constraint ck_* check(列名 between 1 and 100); 
alter table 表名 add constraint ck_* check(列名 in ('值1', '值n')); 

-- 3.非空约束(多个约束中，not null 位于末尾)
alter table 表名 modify(列名 constraint nk_* not null);

1
2
3

alter table 表名 drop constraint 约束名;
参考：
alter table TEST_USER.TABLE_USER drop constraint SYS_C0014437;

1

alter table 表名 rename constraint 约束名 to new_约束名;

1
2
3
4
5

-- 1.禁用 disable
alter table 表名 disable constraint 约束名 [cascade];

-- 2.启用 enable
alter table 表名 enable constraint 约束名 [cascade];

B-tree B树索引

Bitmap 位图索引

REVERSE 反向索引

HASHHASH索引

Function-based基于函数的索引

Partitioned/NonPartitioned 分区索引/非分区索引

Domain 域索引

1
2
3
4
5
6
7
8
9

-- dba_ind_columns：索引对应哪些列
-- dba_indexes：所有索引
-- 与约束类似
-- 参考命令：

select * from dba_indexes;
select * from all_indexes;
select * from user_indexes;
select * from dba_ind_columns;

1
2
3
4
5
6
7
8

-- 普通索引
create index 索引名 on 表名(列名);
-- 唯一索引
create unique index <index_name> on <table_name>(<coiumn_name>);
-- 位图索引
create bitmap index <index_name> on <table_name>(<column_name>);
-- 组合索引
create index 索引名 on 表名(列名1,,列名2);

1

drop index 索引名;

1
2
3
4
5
6

-- 修改索引名称
alter index <index_name> rename to <index_new_name>;
-- 修改索引为无效
alter index <index_name> unusable;
-- 重建索引
alter index <index_name> rebuild online;

查看值得怀疑的 SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

select substr(to_char(s.pct,'99.00'),2)||'%'load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where s.ranking<=5
  and p.address=s.address
order by 1, s.address, p.piece;

查看消耗内存多的 SQL

1
2
3
4
5
6
7
8
9

select b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by disk_reads desc;

查看逻辑读多的 SQL

1
2
3
4
5
6

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where rownum<=30;

查看执行次数多的 SQL

1
2
3

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)
 where rownum < 81;

查看读硬盘多的 SQL

1
2
3

select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

查看排序多的 SQL

1
2
3

select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;

查询正在执行的 SQL 语句及执行该语句的用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT b.sid oracleID,
    b.username 登录Oracle用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
    b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
  AND b.sql_hash_value = c.hash_value
-- 或
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
 from v$session a, v$sqlarea b 
where a.sql_address = b.address

查询执行过的 SQL 语句及执行该语句的用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select a.USERNAME        登录Oracle用户名,
       a.MACHINE         计算机名,
       SQL_TEXT,
       b.FIRST_LOAD_TIME,
       b.SQL_FULLTEXT
  from v$sqlarea b, v$session a
 where a.sql_hash_value = b.hash_value
   and b.FIRST_LOAD_TIME between '2021-07-01/09:00:00' and
       '2021-07-01/10:00:00'
 order by b.FIRST_LOAD_TIME desc;

查询正在执行 SQL 的发起者的发放程序

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT OSUSER 电脑登录身份,
    PROGRAM 发起请求的程序,
    USERNAME 登录系统的用户名,
    SCHEMANAME,
    B.Cpu_Time 花费cpu的时间,
    STATUS,
    B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
          AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC

查询当前被锁对象

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
ORDER BY sid, s.serial#;

杀死当前被锁对象

1

alter system kill session 'sid, s.serial#';

查看占 IO 较大的正在运行的 session

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st. p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
 WHERE st.sid = se.sid
   AND st. sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st. wait_time = 0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC

更多用法

查询数据库当前进程的连接数：

1

select count(*) from v$process;

查看数据库当前会话的连接数：

1

select count(*) from v$session;

查看数据库的并发连接数：

1

select count(*) from v$session where status='ACTIVE';

查看当前数据库建立的会话情况：

1

select sid,serial#,username,program,machine,status from v$session;

查询数据库允许的最大连接数：

1

select value from v$parameter where name = 'processes'; 或者 show parameter processes;

查询所有数据库的连接数

1

select schemaname,count(*)fromv$session groupby schemaname;

查询终端用户使用数据库的连接情况。

1

select osuser,schemaname,count(*)fromv$session groupby schemaname,osuser;

查看当前不为空的连接

1

select * from v$session where username is not null;

查看不同用户的连接数

1

select username,count(username) from v$session where username is not null group by username;

连接数

1

select count(*) from v$session;

并发连接数

1

select count(*) from v$session where status='ACTIVE';

最大连接

1

show parameter processes;

1
2
3

alter system set processes = 300 scope = spfile;
shutdown immediate;
startup;

通过top命令获取进程PID

https://img.bwcxtech.com/img/20201014144505.png

通过sql查询SID及SERIAL#

1

select SID,SERIAL# from v$session where paddr in (select addr from v$process where spid in ('15552'));

https://img.bwcxtech.com/img/20201014144612.png

查询锁死的session

1

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

杀死会话进程

1

alter system kill session '223,10057';

根据 PID 查询执行的 sql

1

SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address) FROM v$session b WHERE b.paddr=( SELECT addr FROM v$process c WHERE c.spid=15552)) ORDER BY piece ASC;

太多的时候可以获取 kill 语句

1

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where paddr in (select addr from v$process where spid in ('15552'));

更多用法：Oracle维护

设置具体时间格式，以便查看具体时间

1

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

查看具体的被锁时间

1

select username,lock_date from dba_users where username='TEST';

解锁

1

alter user test account unlock;

查看是哪个IP造成的test用户被锁。

1
2
3

$ORACLE_BASE/diag/tnslsnr/localhost/listener/trace/listener.log
# 根据被锁时间对日志进行过滤
cat /data/oracle/diag/tnslsnr/localhost/listener/trace/listener.log |grep 16-DEC-2021 |more

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

Oracle中的AWR，全称为Automatic Workload Repository，自动负载信息库。它收集关于特定数据库的操作统计信息和其他统计信息，Oracle以固定的时间间隔(默认为1个小时)为其所有重要的统计信息和负载信息执行一次快照，并将快照存放入AWR中。这些信息在AWR中保留指定的时间(默认为1周)，然后执行删除。执行快照的频率和保持时间都是可以自定义的。

1

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Enter value for report_type: # 报告类型

Enter value for num_days: # 快照天数

Enter value for begin_snap: # 快照开始ID

Enter value for end_snap: # 快照结束ID

Enter value for report_name: # 报告名称

查看当前awr采集时间间隔和保留时间

1

select * from dba_hist_wr_control;

Oracle 11g中，AWR默认保留8天

修改采集间隔30分钟

1
2
3
4
5

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30,
retention => 8*24*60);
END;

interval 快照间隔，单位是分钟 retention 快照保留周期，单位是分钟

循序渐进解读Oracle AWR性能分析报告

更新于 2022-11-30  eb15337
 Oracle
返回 | 主页