Oracle维护

记录维护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;

MEMORY_TARGETMEMORY_MAX_TARGET 的设置值不超过 /dev/shm 的大小。

如果您的物理内存是 16GB,/dev/shm 被配置为使用最多 8GB,那么您设置的 MEMORY_TARGETMEMORY_MAX_TARGET 的总和就不应超过 8GB。

查询MEMORY_TARGETMEMORY_MAX_TARGET

1
2
3
SELECT name, value
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target');

如果 MEMORY_TARGETMEMORY_MAX_TARGET 的值是 0,这意味着该特定参数没有被激活或配置。在这种情况下,数据库可能正在使用传统的 SGA 和 PGA 参数(如 SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET 等)来管理内存。

查询 SGA_TARGET, SGA_MAX_SIZE, 和 PGA_AGGREGATE_TARGET

1
2
3
4
5
6
7
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');

查询 DB_CACHE_SIZE

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

SGAINFO 视图

1
2
3
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 使用字节作为内存参数的基本单位。

修改 SGA

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

修改 PGA

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 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

 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;

在Oracle数据库中,shrink space是一种用于减少表或索引占用空间的操作。它可以帮助回收未使用的空间,从而减少数据库文件的大小。

Shrink space 操作可以应用于表或索引,有两种常见的方法:

  1. Shrink Table:执行Shrink Table操作会收缩表所占用的空间。它将重新组织表中的数据,从而消除数据碎片并回收未使用的空间。这种操作可以通过执行ALTER TABLE语句并使用SHRINK SPACE子句来实现。例如,ALTER TABLE table_name SHRINK SPACE;

  2. 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 不需要)

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

查询正在执行的 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
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 的发起者的发放程序

 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;

根据 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;

合并sql

1
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块

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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 语句

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

通过top命令获取进程PID

image-20201014144435691

通过sql查询SIDSERIAL#

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

image-20201014144610705

查询锁死的session

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

杀死会话进程

1
alter system kill session '223,10057';

更多用法: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
cd $ORACLE_BASE/diag/tnslsnr/oracle/listener/trace
# 根据被锁时间对日志进行过滤
grep 16-DEC-2021 listener.log | 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
6
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30,
retention => 8*24*60);
END;
/

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

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

相关内容