Oracle维护
记录维护Oracle用到的一些语句
1 修改数据库端口
关闭监听
lsnrctl stop修改 listener.ora 配置文件
vi $ORACLE_HOME/network/admin/listener.ora修改 tnsnames.ora 配置文件
vi $ORACLE_HOME/network/admin/tnsnames.ora查看 local_listener 参数
SQL> show parameter local_listener;修改 local_listener 参数
SQL> alter system set local_listener="(address = (protocol = tcp)(host = localhost)(port = 1521))";启动监听
lsnrctl start查看监听状态
lsnrctl status2 数据库解锁
2.1 解锁用户
设置具体时间格式，以便查看具体时间
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';查看具体的被锁时间
select username,lock_date from dba_users where username='TEST';解锁
alter user test account unlock;查看是哪个 IP 造成的 test 用户被锁。
cd $ORACLE_BASE/diag/tnslsnr/oracle/listener/trace
# 根据被锁时间对日志进行过滤
grep 16-DEC-2021 listener.log | more注意：一般数据库默认是10次尝试失败后锁住用户
创建触发器记录日志-数据库中创建触发器（只记录失败）
CREATE OR REPLACE TRIGGER sys.logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
message VARCHAR2(168);
ip VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module VARCHAR2(50);
v_action VARCHAR2(50);
v_pid VARCHAR2(10);
v_sid NUMBER;
v_program VARCHAR2(48);
v_username VARCHAR2(32);
BEGIN
IF (ora_is_servererror(1017)) THEN
-- get ip FOR remote connections :
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
ip := sys_context('userenv', 'ip_address');
END IF;
SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
SELECT p.spid, v.program
INTO v_pid, v_program
FROM v$process p, v$session v
WHERE p.addr = v.paddr
AND v.sid = v_sid;
v_os_user := sys_context('userenv', 'os_user');
v_username := sys_context('userenv','authenticated_identity');
dbms_application_info.read_module(v_module, v_action);
message := to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') ||
' Password Erro: logon denied from ' || nvl(ip, 'localhost') || ' ' ||
v_pid || ' User:' || v_os_user || ' with ' || v_program || ' – ' ||
v_module || ' ' || v_action||' dbuser:' || v_username;
sys.dbms_system.ksdwrt(2, message);
END IF;
END;
/这个触发器不写入数据库表，会在告警日志中记录
查看数据库的告警日志
cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
tail -200f alert_orcl.log2.2 解锁表
查询当前被锁对象
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#;杀死当前被锁对象
alter system kill session 'sid, s.serial#';3 会话管理
3.1 查询正在执行的 SQL 语句及执行该语句的用户
SELECT
b.OSUSER,
b.machine,
b.PROGRAM,
b.username,
b.sid,
b.serial#,
spid,
paddr,
b.LOGON_TIME,
b.last_call_et,
sql_text,
SQL_FULLTEXT
FROM v$process a,
v$session b,
v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value- 显示 session ↔ OS 进程 ↔ SQL
- 可用于 DBA 定位问题会话和对应的 OS 进程
或者：
select a.username,
a.sid,
a.serial#,
b.SQL_TEXT,
b.SQL_FULLTEXT
from v$session a,
v$sqlarea b
where a.sql_address = b.address- 更简洁，主要看 会话 & SQL 文本
3.2 查询正在执行的 SQL 和执行时间
select v.last_call_et,
v.username,
v.machine,
v.program,
v.module,
v.sid,
v.serial#,
sql.sql_text,
sql.sql_fulltext,
sql.sql_id,
sql.disk_reads,
v.event
from v$session v,
v$sql sql
where v.sql_address = sql.address
and v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username is not null;- 显示 SQL 执行时长（
last_call_et） - 帮助排查 慢 SQL 或 锁等待
3.3 查询 SQL 第一次加载时间和 SQL 全文
select a.USERNAME,
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
order by b.FIRST_LOAD_TIME desc;- 关注 SQL 首次加载到 shared pool 的时间
- 可用来分析缓存中最新的 SQL
3.4 查询 SQL 执行的发起程序及 CPU 时间
SELECT OSUSER,
PROGRAM,
USERNAME,
sid,
serial#,
SCHEMANAME,
a.LOGON_TIME,
B.Cpu_Time,
a.last_call_et,
STATUS,
B.SQL_TEXT,
b.SQL_FULLTEXT
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- 显示 谁在哪个程序里执行 SQL
- 并且按照 CPU 消耗排序，方便找高消耗 SQL
3.5 查看占 IO 较大的正在运行的 session
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 DESC4 内存管理
4.1 自动内存管理
4.1.1 MEMORY_TARGET
MEMORY_TARGET 和 MEMORY_MAX_TARGET 的设置值不超过 /dev/shm 的大小。
如果您的物理内存是 16GB，/dev/shm 被配置为使用最多 8GB，那么您设置的 MEMORY_TARGET 和 MEMORY_MAX_TARGET 的总和就不应超过 8GB。
查询MEMORY_TARGET 和 MEMORY_MAX_TARGET值
SELECT name, value
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target');如果 MEMORY_TARGET 或 MEMORY_MAX_TARGET 的值是 0，这意味着该特定参数没有被激活或配置。在这种情况下，数据库可能正在使用传统的 SGA 和 PGA 参数即手动内存管理（如 SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET 等）来管理内存。
4.1.2 SGA PGA
查询 SGA_TARGET, SGA_MAX_SIZE, 和 PGA_AGGREGATE_TARGET
SELECT name, value
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'pga_aggregate_target', 'pga_aggregate_limit');
-- 以 mb 查询
SELECT name, value/1024/1024 AS value_in_mb
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'pga_aggregate_target', 'pga_aggregate_limit');4.1.3 Buffer Cache
查询 DB_CACHE_SIZE
SELECT name, value
FROM v$parameter
WHERE name in ('db_cache_size','shared_pool_size','large_pool_size','java_pool_size');
-- 以 mb 查询
SELECT name, value/1024/1024 AS value_in_mb
FROM v$parameter
WHERE name in ('db_cache_size','shared_pool_size','large_pool_size','java_pool_size');SGAINFO 视图
SELECT name, bytes
FROM V$SGAINFO
WHERE name IN ('Buffer Cache Size', 'Shared Pool Size', 'Large Pool Size', 'Java Pool Size');
-- 以 mb 查询
SELECT name, bytes/1024/1024 AS bytes_in_mb
FROM V$SGAINFO
WHERE name IN ('Buffer Cache Size', 'Shared Pool Size', 'Large Pool Size', 'Java Pool Size');如果 DB_CACHE_SIZE 的值为 0，这通常意味着数据库正在使用自动内存管理（Automatic Memory Management, AMM）功能，或者使用了自动 SGA 管理。
如果 DB_CACHE_SIZE 的值为 0，但未启用自动内存管理（AMM）功能，这种情况通常意味着 Oracle 数据库正在使用自动共享内存管理（Automatic Shared Memory Management, ASMM）来自动管理系统全局区（System Global Area, SGA）的大小。
V$PARAMETER视图返回的参数的值通常是以字节（Bytes）为单位的。Oracle 使用字节作为内存参数的基本单位。
4.2 修改 SGA
-- 修改 sga_max
ALTER SYSTEM SET sga_max_size = 16G SCOPE=SPFILE;
-- 修改 SGA
ALTER SYSTEM SET sga_target = 16G SCOPE=SPFILE;4.3 修改 PGA
-- 设置 PGA
alter system set pga_aggregate_limit = 0 SCOPE=SPFILE;
alter system set pga_aggregate_target = 4G SCOPE=SPFILE;总内存分配：SGA + PGA ≤ 物理内存的 80%
OLTP系统
SGA_MAX_SIZE = ( 总内存 × 80% )× 80%
SGA_TARGET 建议和 SGA_MAX_SIZE 保持一致
PGA_AGGREGATE_TARGET = ( 总内存 × 80% ) × 20 %
PGA_AGGREGATE_LIMIT 建议设置 PGA_AGGREGATE_TARGET 的 1.5~2 倍
PGA_AGGREGATE_LIMIT 当达到限制时，数据库引擎会终止调用甚至是杀掉会话。
5 系统资源
查看归档日志生成速率（按天统计）
SELECT TO_CHAR(completion_time, 'YYYY-MM-DD') AS day,
COUNT(*) AS archives,
SUM(blocks * block_size)/1024/1024 AS total_size_mb
FROM v$archived_log
GROUP BY TO_CHAR(completion_time, 'YYYY-MM-DD')
ORDER BY day DESC;查看值得怀疑的 SQL
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
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
select*
from(select buffer_gets, sql_text
from v$sqlarea
where buffer_gets>500000
order by buffer_gets desc)
where rownum<=30;查看执行次数多的 SQL
select sql_text, executions
from (select sql_text, executions from v$sqlarea order by executions desc)
where rownum < 81;查看读硬盘多的 SQL
select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;查看排序多的 SQL
select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;5.1 shrink sapce
在Oracle数据库中，shrink space是一种用于减少表或索引占用空间的操作。它可以帮助回收未使用的空间，从而减少数据库文件的大小。
Shrink space 操作可以应用于表或索引，有两种常见的方法：
Shrink Table：执行Shrink Table操作会收缩表所占用的空间。它将重新组织表中的数据，从而消除数据碎片并回收未使用的空间。这种操作可以通过执行ALTER TABLE语句并使用SHRINK SPACE子句来实现。例如，ALTER TABLE table_name SHRINK SPACE;。Shrink Index：执行Shrink Index操作会收缩索引所占用的空间。它会重新组织索引结构并消除索引的碎片，从而回收未使用的空间。这种操作可以通过执行ALTER INDEX语句并使用SHRINK SPACE子句来实现。例如，ALTER INDEX index_name SHRINK SPACE;。
在执行shrink space操作时，Oracle会为其分配适当的资源并尽力减小占用空间。但请注意，这种操作可能会导致一些性能开销，并且可能需要一定的时间才能完成。此外，对于使用跨度较大的分区表或索引，shrink space操作可能不会立即释放磁盘空间，而是将其转换为可重用的空间。
在执行shrink space操作之前，建议您详细了解相关文档、备份数据库，并在适当情况下进行测试，以确保操作的安全性和有效性。
shrink必须开启对象的row movement功能（shrink index 不需要）
alter table table_name enable row movement;
alter table table_name disable row movement;5.2 连接数
5.2.1 查询连接数
查询数据库当前进程的连接数：
select count(*) from v$process;查看数据库当前会话的连接数：
select count(*) from v$session;查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
-- 或者
show parameter processes;查询所有数据库的连接数
select schemaname,count(*) from v$session group by schemaname;查询终端用户使用数据库的连接情况。
select osuser,schemaname,count(*) from v$session group by schemaname,osuser;查看当前不为空的连接
select * from v$session where username is not null;查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;连接数
select count(*) from v$session;并发连接数
select count(*) from v$session where status='ACTIVE';最大连接
show parameter processes;5.2.2 修改连接数
alter system set processes = 300 scope = spfile;
shutdown immediate;
startup;5.3 ADR（Automatic Diagnostic Repository）保留策略设置
SHOW CONTROL 会显示 ADR 的清理策略；
SHORTP_POLICY 默认是 720 小时（30 天），LONGP_POLICY 默认是 8760 小时（365 天）。
短生命周期内容包括 trace、core dump、打包信息；
长生命周期内容包括 incident 信息、incident dump、alert log。
adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)6 根据 OS PID 查询当前执行 SQL
根据操作系统 PID 查询对应的 Oracle Session 以及当前执行的 SQL。
SELECT p.spid AS os_pid,
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.sql_id,
q.sql_text,
q.sql_fulltext
FROM v$process p
JOIN v$session s
ON s.paddr = p.addr
LEFT JOIN v$sql q
ON q.sql_id = s.sql_id
WHERE p.spid = '23040';查不到用这个
SELECT LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS full_sql
FROM v$sqltext
WHERE (hash_value, 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
WHERE paddr = (SELECT addr FROM v$process WHERE spid = 30185));太多的时候可以获取 kill 语句
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';'
FROM v$session
WHERE paddr IN (SELECT addr FROM v$process WHERE spid IN ('15552', '13678'));7 查杀会话进程
通过top命令获取进程PID
通过sql查询SID及SERIAL#
select SID,SERIAL# from v$session where paddr in (select addr from v$process where spid in ('15552'));
查询锁死的session
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;杀死会话进程
alter system kill session '223,10057';更多用法：Oracle维护
8 AWR报告
Oracle中的AWR，全称为Automatic Workload Repository，自动负载信息库。它收集关于特定数据库的操作统计信息和其他统计信息，Oracle以固定的时间间隔(默认为1个小时)为其所有重要的统计信息和负载信息执行一次快照，并将快照存放入AWR中。这些信息在AWR中保留指定的时间(默认为1周)，然后执行删除。执行快照的频率和保持时间都是可以自定义的。
8.1 创建AWR报告
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sqlEnter 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: # 报告名称
8.2 修改AWR采集间隔和保留时间
查看当前awr采集时间间隔和保留时间
select * from dba_hist_wr_control;Oracle 11g中，AWR默认保留8天
修改采集间隔30分钟
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30,
retention => 8*24*60);
END;
/interval 快照间隔，单位是分钟 retention 快照保留周期，单位是分钟
8.3 解读AWR
9 SYSAUX表空间剩余不足处理
AWR创建快照失败
查询表空间使用率
select TABLESPACE_NAME,
(TABLESPACE_SIZE - USED_SPACE) * 8 / 1024 / 1024 free_space,
USED_SPACE * 8 / 1024 / 1024 USED_SPACE,
TABLESPACE_SIZE * 8 / 1024 / 1024 TABLESPACE_SIZE,
USED_PERCENT
from DBA_TABLESPACE_USAGE_METRICS
order by 5;查询表并 truncate
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;不推荐 dbms_workload_repository 包中 drop_snapshot_range 存储过程来删除快照，因为里面都是 delete 操作。耗时非常久，归档日志切换频繁。
参考链接：
案例：AWR手工创建快照失败，SYSAUX表空间剩余不足处理
Oracle处理关于sysaux表空间爆满的问题—更新最新方法！！
10 UNDOTBS表空间迁移
创建一个新的 undo 表空间
create undo tablespace undotbs2 datafile '/data/oracle/oradata/undotbs02.dbf' size 1G autoextend on next 256M;设置新的表空间为系统 undo_tablespace
alter system set undo_tablespace=undotbs2 scope=both;查询旧的还原表空间数据状态
select t.segment_name, t.tablespace_name, t.segment_id, t.status from dba_rollback_segs t;旧的还原表空间数据状态均为 offline 即可删除之前的 undo 表空间
drop tablespace undotbs1 including contents and datafiles;