Oracle维护

不忘初心收录于类别 DB

2021-08-02 2023-09-25 约 3325 字预计阅读 7 分钟次阅读条评论

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

1 约束

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

1.1 查询约束

 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 创建约束

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.3 删除约束

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

1.4 重命名约束

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

1.5 禁用启用约束

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

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

2 索引

2.1 索引类型

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

2.2 查询索引

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;

2.3 创建索引

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

2.4 删除索引

1
drop index 索引名;

2.5 修改索引

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;

3 系统资源

3.1 资源

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

 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;

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

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

3.3 会话

查询正在执行的 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

更多用法

3.4 连接数

3.4.1 查询连接数

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

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;

3.4.2 修改连接数

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

4 根据 PID 查询执行的 sql

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

5 查杀会话进程

通过top命令获取进程PID

通过sql查询SID及SERIAL#

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

查询锁死的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维护

6 用户

6.1 解锁用户

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

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次尝试失败后锁住用户

7 AWR报告

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

7.1 创建AWR报告

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

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

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

1
select * from dba_hist_wr_control;

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

修改采集间隔30分钟

1
2
3
4
5
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30,
retention => 8*24*60);
END;

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

7.3 解读AWR

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

目录

Oracle维护

1 约束

1.1 查询约束

1.2 创建约束

1.3 删除约束

1.4 重命名约束

1.5 禁用启用约束

2 索引

2.1 索引类型

2.2 查询索引

2.3 创建索引

2.4 删除索引

2.5 修改索引

3 系统资源

3.1 资源

3.2 shrink sapce

3.3 会话

3.4 连接数

3.4.1 查询连接数

3.4.2 修改连接数

4 根据 PID 查询执行的 sql

5 查杀会话进程

6 用户

6.1 解锁用户

7 AWR报告

7.1 创建AWR报告

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

7.3 解读AWR

相关内容

目录

Oracle维护

1 约束

1.1 查询约束

1.2 创建约束

1.3 删除约束

1.4 重命名约束

1.5 禁用启用约束

2 索引

2.1 索引类型

2.2 查询索引

2.3 创建索引

2.4 删除索引

2.5 修改索引

3 系统资源

3.1 资源

3.2 shrink sapce

3.3 会话

3.4 连接数

3.4.1 查询连接数

3.4.2 修改连接数

4 根据 PID 查询执行的 sql

5 查杀会话进程

6 用户

6.1 解锁用户

7 AWR报告

7.1 创建AWR报告

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

7.3 解读AWR

相关内容

Oracle数据块损坏

Oracle迁移审计表AUD$

Oracle init.ora常用参数

Oracle rename数据文件

Oracle dblink MySQL