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

更多用法