Oracle维护

发表于 更新于 分类于 阅读次数：

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

约束

约束类型 Type Code 规范命名 名称说明
主键约束 P PK_表名_列名 Primary Key
外键约束 R FK_表名_列名 Foreign Key
非空约束 NN_表名_列名 Not Null
唯一约束 U UK_表名_列名 Unique Key
检查约束 C CK_表名_列名 Check

查询约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 常用视图 (权限由大到小: dba_* > all_* > user_*)
-- dba_constraints：侧重约束具体信息
-- dba_cons_columns：侧重约束列信息

参考如下：

select *
  from dba_constraints dc
 where dc.owner = 'SCOTT'
   and dc.table_name = 'EMP';

select *
  from dba_cons_columns dcc
 where dcc.owner = 'SCOTT'
   and dcc.table_name = 'EMP';

添加约束

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 TMS_USER_SC.SYSTEM_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];

系统资源

资源

查看值得怀疑的 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
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次尝试失败后锁住用户