Oracle维护

不忘初心收录于类别 DB

2021-08-02 2024-09-29 约 4266 字预计阅读 9 分钟次阅读条评论

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

1 修改数据库端口

关闭监听

bash

lsnrctl stop

修改 listener.ora 配置文件

bash

vi $ORACLE_HOME/network/admin/listener.ora

修改 tnsnames.ora 配置文件

bash

vi $ORACLE_HOME/network/admin/tnsnames.ora

查看 local_listener 参数

sql

SQL> show parameter local_listener;

修改 local_listener 参数

sql

SQL> alter system set local_listener="(address = (protocol = tcp)(host = localhost)(port = 1521))";

启动监听

bash

lsnrctl start

查看监听状态

bash

lsnrctl status

2 约束

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

2.1 查询约束

sql

-- 常用视图 (权限由大到小: 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;

2.2 创建约束

sql

-- 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);

2.3 删除约束

sql

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

2.4 重命名约束

sql

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

2.5 禁用启用约束

sql

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

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

3 索引

3.1 索引类型

B-tree B树索引
Bitmap 位图索引
REVERSE 反向索引
HASHHASH索引
Function-based基于函数的索引
Partitioned/NonPartitioned 分区索引/非分区索引
Domain 域索引

3.2 查询索引

sql

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

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

3.3 创建索引

sql

-- 普通索引
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);

3.4 删除索引

sql

drop index 索引名;

3.5 修改索引

sql

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

4 自动内存管理

4.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值

sql

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.2 SGA PGA

查询 SGA_TARGET, SGA_MAX_SIZE, 和 PGA_AGGREGATE_TARGET

sql

SELECT name, value
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'pga_aggregate_target');
-- 以 mb 查询
SELECT name, value/1024/1024 AS value_in_mb
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'pga_aggregate_target');

4.3 Buffer Cache

查询 DB_CACHE_SIZE

sql

SELECT name, value
FROM v$parameter
WHERE name in ('db_cache_size','shared_pool_size','large_pool_size','java_pool_size');

SGAINFO 视图

sql

SELECT name, bytes
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 使用字节作为内存参数的基本单位。

5 系统资源

5.1 内存

修改 SGA

sql

-- 修改 sga_max
ALTER SYSTEM SET sga_max_size = 16G SCOPE=SPFILE;
-- 修改 SGA
ALTER SYSTEM SET sga_target = 16G SCOPE=SPFILE;

修改 PGA

sql

-- 设置 PGA
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=SPFILE;

5.2 资源

查看具体表的占用空间大小

sql

# dba级
select * from (
  select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc;

# 用户级
select * from (
  select t.tablespace_name, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from user_segments t
  where t.segment_type='TABLE'
  group by t.tablespace_name, t.segment_name, t.segment_type
) t
order by t.mb desc;

查看值得怀疑的 SQL

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

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

sql

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

查看执行次数多的 SQL

sql

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

查看读硬盘多的 SQL

sql

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

查看排序多的 SQL

sql

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

5.3 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 不需要）

sql

alter table table_name enable row movement;
alter table table_name disable row movement;

5.4 会话

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

sql

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 语句及执行该语句的用户

sql

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
order by b.FIRST_LOAD_TIME desc;

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

sql

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

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

sql

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

更多用法

5.5 连接数

5.5.1 查询连接数

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

sql

select count(*) from v$process;

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

sql

select count(*) from v$session;

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

sql

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

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

sql

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

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

sql

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

查询所有数据库的连接数

sql

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

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

sql

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

查看当前不为空的连接

sql

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

查看不同用户的连接数

sql

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

连接数

sql

select count(*) from v$session;

并发连接数

sql

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

最大连接

sql

show parameter processes;

5.5.2 修改连接数

sql

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

5.6 通过 adrci 清理日志

执行故障诊断命令

bash

adrci

查询ADR目录

text

show home

切换到对应目录

text

set home diag/rdbms/orcl/orcl

执行日志清理命令

text

purge -age 14400 -type trace #14400的单位是分钟
purge -age 14400 -type alert

参数修改

LONGP_POLICY：默认保留365天的事件与警告。
SHORTP_POLICY：默认保留30的trace文件或core dump file

text

set control (SHORTP_POLICY=360) #360的单位是小时

6 根据 PID 查询执行的 sql

根据 PID 查询执行的 sql

sql

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;

合并sql

sql

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 = 15552));

如果字符串连接的结果过长，可以使用PL/SQL块

sql

DECLARE
    long_string VARCHAR2(32767); -- 或适当大小的字符串
BEGIN
    SELECT LISTAGG(sql_text, '')
        WITHIN GROUP (ORDER BY piece) INTO long_string
    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 = 9204)
    );
    -- 使用DBMS_OUTPUT输出查询结果
    DBMS_OUTPUT.PUT_LINE(long_string);
    -- 在此处可以进行其他操作或输出
END;
/

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

sql

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

7 查杀会话进程

通过top命令获取进程PID

通过sql查询SID及SERIAL#

sql

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

查询锁死的session

sql

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

杀死会话进程

sql

alter system kill session '223,10057';

更多用法：Oracle维护

8 用户

8.1 解锁用户

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

sql

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

查看具体的被锁时间

sql

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

解锁

sql

alter user test account unlock;

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

bash

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

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

8.1.1 创建触发器

数据库中创建触发器（只记录失败）

sql

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;
/

这个触发器不写入数据库表，会在告警日志中记录

查看数据库的告警日志

bash

cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
tail -200f alert_orcl.log

8.2 解锁表

查询当前被锁对象

sql

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#;

杀死当前被锁对象

sql

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

9 AWR报告

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

9.1 创建AWR报告

sql

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: # 报告名称

9.2 修改AWR采集间隔和保留时间

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

sql

select * from dba_hist_wr_control;

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

修改采集间隔30分钟

sql

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

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

9.3 解读AWR

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

10 SYSAUX表空间剩余不足处理

AWR创建快照失败

查询表空间使用率

sql

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

sql

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表空间爆满的问题—更新最新方法！！

目录

Oracle维护

1 修改数据库端口

2 约束

2.1 查询约束

2.2 创建约束

2.3 删除约束

2.4 重命名约束

2.5 禁用启用约束

3 索引

3.1 索引类型

3.2 查询索引

3.3 创建索引

3.4 删除索引

3.5 修改索引

4 自动内存管理

4.1 MEMORY_TARGET

4.2 SGA PGA

4.3 Buffer Cache

5 系统资源

5.1 内存

5.2 资源

5.3 shrink sapce

5.4 会话

5.5 连接数

5.5.1 查询连接数

5.5.2 修改连接数

5.6 通过 adrci 清理日志

6 根据 PID 查询执行的 sql

7 查杀会话进程

8 用户

8.1 解锁用户

8.1.1 创建触发器

8.2 解锁表

9 AWR报告

9.1 创建AWR报告

9.2 修改AWR采集间隔和保留时间

9.3 解读AWR

10 SYSAUX表空间剩余不足处理

相关内容

Oracle数据块损坏

Oracle迁移审计表AUD$

Oracle init.ora常用参数

Oracle rename数据文件

Oracle dblink MySQL