【数据库】Oracle常用命令，DBA日常巡检维护SQL脚本

一、表和索引存储

1. 查看表占用存储及表行数

--表大小（dba权限-所有用户）
SELECT /*+ PARALLEL(8) */ a.owner "用户",A.TABLE_NAME "表名",b.TABLESPACE_NAME "表空间名",B.UNITS/1024/1024/1024 "占用多少GB",A.NUM_ROWS "行数（非实时，可通过表分析更新）",A.PARTITIONED "是否为分区表"
FROM all_TABLES A 
LEFT JOIN (SELECT  SUM(BYTES) UNITS,OWNER,SEGMENT_NAME,max(TABLESPACE_NAME) TABLESPACE_NAME 
     FROM dba_SEGMENTS GROUP BY SEGMENT_NAME,OWNER)B 
ON  (a.TABLE_NAME=b.SEGMENT_NAME  and a.owner=b.owner) 
WHERE UNITS is not null
 ORDER BY 4 DESC;

--表大小（当前用户）
SELECT /*+ PARALLEL(8) */ A.TABLE_NAME "表名",b.TABLESPACE_NAME "表空间名",B.UNITS/1024/1024/1024 "占用多少GB",A.NUM_ROWS "行数（非实时，可通过表分析更新）",A.PARTITIONED "是否为分区表"
FROM user_TABLES A 
LEFT JOIN (SELECT  SUM(BYTES) UNITS,SEGMENT_NAME,max(TABLESPACE_NAME) TABLESPACE_NAME 
     FROM USER_SEGMENTS GROUP BY SEGMENT_NAME)B ON  B.SEGMENT_NAME=A.TABLE_NAME  
WHERE UNITS is not null
 ORDER BY 3 DESC;

2. 查看索引占用存储

--索引大小（dba权限-所有用户）
select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024/1024 Gbytes,a.blocks
from dba_segments a, all_indexes b
where a.segment_name = b.index_name
and a.segment_type = 'INDEX' --索引类型
--and a.tablespace_name='TBS_TEST'  --表空间
--and b.table_name like '%TB_TEST%' --索引所在表
--AND A.segment_name='IDX_TB_TEST'  --索引名
order by a.bytes desc;

--索引大小（当前用户）
select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024/1024 Gbytes,a.blocks
from user_segments a, user_indexes b
where a.segment_name = b.index_name
and a.segment_type = 'INDEX' --索引类型
--and a.tablespace_name='TBS_TEST'  --表空间
--and b.table_name like '%TB_TEST%' --索引所在表
--AND A.segment_name='IDX_TB_TEST'  --索引名
order by a.bytes desc;

3. 查看表分区

--切换为分区表
ALTER TABLE tb_TEST MODIFY
  PARTITION BY RANGE (DEALDT) INTERVAL(NUMTODSINTERVAL(1,'DAY')) store in(TBS_TEST)
  ( PARTITION P1 VALUES LESS THAN (TO_DATE(' 2020-01-01', 'YYYY-MM-DD'))
     tablespace TBS_TEST
   ) ONLINE;

 --创建查看分区的方法（方便后续管理）
CREATE OR REPLACE TYPE TP_PART_OBJ as object
(
  partition_name varchar2(36),
  partition_value varchar2(500)
);

CREATE OR REPLACE TYPE TP_PARTRECORD as table of tp_part_obj;

create or replace function f_get_part(p_tabname varchar2) return tp_partrecord as
  v_tab_part tp_partrecord:=tp_partrecord();
  v_partition_value   varchar2(500);
begin
   for i in(select a.partition_name,a.high_value
              from user_tab_partitions a
             where a.table_name=upper(p_tabname))
   loop
     v_partition_value:=substr(to_char(i.high_value),1,50);
     v_tab_part.extend();
     v_tab_part(v_tab_part.count):=tp_part_obj(i.partition_name,v_partition_value);
    end loop;
  return v_tab_part;
end f_get_part;

--获取分区值和分区名
SELECT * FROM TABLE(f_get_part('TB_TEST'));

4. 索引相关操作

--删除主键索引
alter table TB_TEST  drop  constraint PK_TB_TEST;
--获取建表语句
SELECT DBMS_METADATA.GET_DDL('TABLE','TB_TEST','OWNER_NAME') FROM DUAL;

--分区表索引
create table tmp_t0(
  c0 varchar2(100),
  c1 varchar2(100),
  c2 varchar2(100)
)
partition by hash (c0) 
partitions 4;

--本地索引
create index idx_tmp_t0_c0 on tmp_t0(c0) local;
--全局索引
create index idx_tmp_t0_c1 on tmp_t0(c1);
--全局分区索引
create index idx_tmp_t0_c2 on tmp_t0(c2) global
partition by hash (c2)
partitions 4;

--ORA-08104:该索引对象98177正在被联机建立或重建
--手动中止时执行
declare
RETVAL boolean;
OBJECT_ID NUMBER;
WAIT NUMBER;
begin
  OBJECT_ID:=98177;
  WAIT:=NULL;
  RETVAL:= SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
  COMMIT;
END;

二、表空间存储

1. 查询表空间使用情况

--（dba权限）
SELECT a.tablespace_name ,
       round(total / (1024 * 1024 * 1024), 2) "表空间大小(G)",
       round((total - free) / (1024 * 1024 * 1024), 2) "表空间使用大小(G)",
       round(free / (1024 * 1024 * 1024), 2) "表空间剩余大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
     order by 5 desc;

--增加临时表空间
alter temporary tablespace temp add tempfile  size 31g;

alter temporary tablespace temp add tempfile '/oradata/TESTINST/TESTINST/AA170605CC2D08B0E0530A0B0C021D9F/datafile/o1_mf_temp_hjjt0lpg_.dbf' size 31G ;

--system和sysaux为数据库系统表空间，不用太注意。其余使用率过高或者表空间剩余大小不足，则需要扩容
alter tablespace xxxxx add datafile size 31G autoextend on next 200m maxsize unlimited;

--最好有调度定期检查表空间是否足够，不足则自动扩容。

2. 实际使用率查看（高水位）

--高水位
SELECT owner,TABLE_NAME,ROUND((BLOCKS*8192/1024/1024),2)"理论大小M",
ROUND((NUM_ROWS*AVG_ROW_LEN/1024/1024),2)"实际大小M",
ROUND( (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024),2) "Data lower than HWM in MB" , 
to_char(round((NUM_ROWS*AVG_ROW_LEN/1024/1024)/(BLOCKS*8192/1024/1024),3)*100,'fm999990.99999')||'%' "实际使用率%" 
FROM dba_TABLES where  (NUM_ROWS*AVG_ROW_LEN/1024/1024)/(BLOCKS*8192/1024/1024)<0.6
AND OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB','EXFSYS', 'CTXSYS','WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS','OGG')
AND BLOCKS NOT IN ('0') ORDER BY 3 DESC; 

------查看某表大小
SELECT  bytes/1024/1024/1024 "单位G",a.* FROM USER_SEGMENTS a WHERE SEGMENT_NAME ='表名';
--若某些表实际使用率过低，则需要进行碎片整理。
--分区表需要单独整理每个分区，或者清除空分区。

3. 表碎片整理（清水位）

--整理非分区表碎片
alter table TB_TEST move online;

--整理分区碎片
alter table TB_TEST_PART move PARTITION SYS_P13236 online;
alter table TB_TEST_PART move PARTITION SYS_P13271 online;
alter table TB_TEST_PART move PARTITION SYS_P13278 online;

--清除空分区
set serveroutput on;
declare 
  vsql varchar2(1000);
  vcount number;
begin
  for i in(select * from user_tab_partitions a where a.table_name='TB_TEST_PART' and a.partition_name<>'P1')
  loop
    vsql:='select /*+ parallel(8)*/ count(1) from TB_TEST_PART partition('||i.partition_name||')';
    execute immediate vsql into vcount;
    if vcount=0 then
       vsql:='alter table TB_TEST_PART drop partition '||i.partition_name||' update global index;';
       dbms_output.put_line(vsql);  --执行该行的打印结果
    end if;
  end loop;
end;

--整理完碎片后，若该表有索引则必须重建索引
select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online Parallel 8 ;
alter index '||OWNER||'.'||INDEX_NAME||' noparallel ;' 
from all_indexes where table_name in ('TB_TEST');

alter index IDX_TB_TEST1 rebuild online;
alter index IDX_TB_TEST2 rebuild online;

--以上步骤结束，必须做表分析，避免数据库出现异常。
begin
dbms_stats.gather_table_stats(
	ownname=>'OWNER_NAME',      --该表用户
	tabname=>'TB_TEST',   		--表名
	degree => 8,
	cascade => true,
	force=>true);
end;

4. 创建表空间和用户

--表空间
CREATE TABLESPACE TBS_TEST DATAFILE '\opt\oracle_data\tablespace\TBS_TEST' size 800M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 

--索引表空间
CREATE TABLESPACE TBS_TEST_INDEX DATAFILE '\opt\oracle_data\tablespace\TBS_TEST_INDEX' size 512M  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;    
 
--2.建用户
create user TESTUSER identified by "1qaz@WSX3edc" default tablespace TBS_TEST temporary tablespace temp profile default;
 
--3.赋权
grant connect,resource to TESTUSER;
grant create any sequence to TESTUSER;
grant create any table to TESTUSER;
grant delete any table to TESTUSER;
grant insert any table to TESTUSER;
grant select any table to TESTUSER;
grant unlimited tablespace to TESTUSER;
grant execute any procedure to TESTUSER;
grant update any table to TESTUSER;
grant create any view to TESTUSER;

三、表压缩、锁表定位及处理

1. 获取未压缩表及其大小

--1.获取未压缩表及其大小（DBA账户） 
--一般压缩业务系统不再操作的历史数据
with sql1 as (
select a.segment_name,a.tablespace_name,sum(a.bytes)/1024/1024/1024 gb
  from dba_segments a,dba_objects b
 where a.owner='OWNER_NAME'				--用户名
   and a.segment_name=b.object_name
   and b.object_type='TABLE'
   --and a.tablespace_name='TBS_DATA_TEST'
 group by a.segment_name,a.tablespace_name)
select x1.*,x2.num_rows,X2.Compression,case when x3.table_name is null then 0 else 1 end is_part
  from sql1 x1,all_tables x2,all_part_tables x3
 where x1.segment_name=x2.table_name
   and x2.table_name=x3.table_name(+)
   and x2.table_name like '%2020%' 		--表名
   and x2.compression='DISABLED' 		--未压缩的
 order by 3 desc;

2. 压缩表操作

--2.对表压缩（业务账户）
declare 
  vsql varchar2(1000);
  vtablename varchar2(200):='ST_TEST'; --非分区表名
begin
  for i in (select * from user_tables a where a.table_name = vtablename   and a.compression='DISABLED' order by 1)
  loop
    vsql:='alter table '||i.table_name||' move  online  row store compress advanced';
    execute immediate vsql;
    for j in(select * from user_indexes b where b.table_name=i.table_name  and b.index_type<>'LOB')
    loop
      vsql:='alter index '||j.index_name||' rebuild online   parallel 8 nologging compress advanced high';
      execute immediate vsql;
      vsql:='alter index '||j.index_name||' noparallel logging';
      execute immediate vsql;
    end loop;
  end loop;
end; 

--3.对分区表压缩
declare 
  vsql varchar2(1000);
  vtablename varchar2(200):='ST_TEST_PART'; --分区表名
begin
  for i in (
select a.* from user_tab_partitions a where table_name = vtablename  
and COMPRESSION='DISABLED' and PARTITION_NAME <>'P1' order by TABLE_NAME,PARTITION_NAME)
  loop
    vsql:='ALTER TABLE '||i.TABLE_NAME||' MOVE PARTITION '||i.PARTITION_NAME||' ONLINE COMPRESS update global indexes'; 
    execute immediate vsql;
  end loop;
  
    for j in(select * from user_indexes b where b.table_name = vtablename  and b.index_type<>'LOB')
    loop
      vsql:='alter index '||j.index_name||' rebuild online   parallel 8 nologging compress advanced high';
      execute immediate vsql;
      vsql:='alter index '||j.index_name||' noparallel logging';
      execute immediate vsql;
    end loop;
end; 

3. 查询锁表及解锁语句

--锁表
select DISTINCT b.object_name,to_char(nvl(c.sql_exec_start,c.prev_exec_start),'yyyymmdd hh24:mi:ss') exec_time,C.STATUS,OSUSER,MACHINE,PROGRAM,NVL(SQL_ID,PREV_SQL_ID) SQL_ID,STATE,BLOCKING_SESSION_STATUS,EVENT,WAIT_CLASS_ID
,'alter system disconnect session '''||c.SID||','||SERIAL#||',@'||A.INST_ID||''' immediate;' disconnectSQL --执行解锁语句
  from GV$LOCKED_OBJECT a,DBA_OBJECTS b,gv$session c
 where a.object_id=b.object_id and a.inst_id=c.inst_id and a.session_id=c.sid
   --and OBJECT_NAME ='TB_TEST' --指定表
--and nvl(c.sql_exec_start,c.prev_exec_start)<sysdate-1/24 --超过1小时
   order by 2;

--锁存储过程
select DISTINCT d.object,to_char(nvl(c.sql_exec_start,c.prev_exec_start),'yyyymmdd hh24:mi:ss') exec_time,STATUS,OSUSER,MACHINE,PROGRAM,NVL(SQL_ID,PREV_SQL_ID) SQL_ID,STATE,BLOCKING_SESSION_STATUS
,'alter system disconnect session '''||c.SID||','||SERIAL#||',@'||A.INST_ID||''' immediate;' --执行解锁语句
  from GV$LOCKED_OBJECT a,gv$session c,  gv$access  d
 where a.inst_id=c.inst_id 
 and a.session_id=c.sid
   and d.sid=c.sid
   and d.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB','EXFSYS', 'CTXSYS','WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS','OGG') 
and d.type='PROCEDURE'
   --and d.OBJECT ='PROC_XXX' --指定存储过程
--and nvl(c.sql_exec_start,c.prev_exec_start)<sysdate-1/24 --超过1小时
   order by 2 ;

--缓存，查询具体sql语句，取上面查到的sql_id
select *  from gv$sql where sql_id= 'djwh686j017zy';

--如果已经kill过，则会报ora-00031的错误；看看session标记是否为killed状态，等待一段时间处理完了，上面的语句就查不出来了
--如果等不及，可以在linux上直接杀。
ps -ef | grep ’ora’ (找到所有ora开头的进程)
kill -9 (pid)

四、其余常用SQL

1. 删表回退（闪回）

--开启行移动
alter table TS_TEST enable row movement;
--关闭行移动
alter table TS_TEST disable row movement;

flashback table TS_TEST to before drop;
flashback table TS_TEST to before delete;

--删数据回退到某个时间点
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select * from TS_TEST as of timestamp to_timestamp('2020-09-20 10:40:00','yyyy-mm-dd hh24:mi:ss');
flashback table TS_TEST to timestamp to_timestamp('2020-09-20 10:40:00','yyyy-mm-dd hh24:mi:ss');

2. 数据库作业调度

--执行存储过程
begin
  sys.dbms_scheduler.create_job(job_name            => 'JOB_PROC_CLEAR_TABHWM', --作业名
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'PROC_CLEAR_TABHWM', --过程名
                                start_date          => sysdate,
                                repeat_interval     => 'Freq=DAILY;Interval=1;ByHour=4;ByMinute=30;BySecond=0', --执行频率
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => true,
                                comments            => '清理表碎片');
end;

--执行语句
 begin
  sys.dbms_scheduler.create_job(job_name            => 'JOB_TEST',
                                job_type            => 'PLSQL_BLOCK',
                                job_action          => 'update tb_test set uploadtime=sysdate where uploadtime is null;',
                                start_date          => sysdate,
                                repeat_interval     => 'Freq=MINUTELY;Interval=60',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => true,
                                comments            => '20240122 FIXPNG 测试job');
end;

3. 其他

--创建 DB link(加public，所有用户都可见，不见就仅当前用户)
create public database link DBL_TO_TEST 
 connect to OWNER_NAME identified by "Test@2024"
  using '10.244.244.144:1521/test';
--删除DBLINK
drop database link DBL_TO_RECKON_BINGMIN;

--关闭DBLINK
begin
    DBMS_SESSION.CLOSE_DATABASE_LINK('DBL_TO_TEST');
END;

--查看主键
select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b
where a.constraint_name=b.constraint_name and b.constraint_type = 'P' --and a.table_name=upper('');

--查表列值
select listagg(column_name,',') columns from user_tab_columns where table_name = 'TB_TEST';

--查看存储过程是否失效，失效的需要重新编译
select OBJECT_NAME,STATUS from dba_objects where object_type LIKE '%PROC%' AND OWNER ='OWNER_NAME' and status = 'INVALID';

----增加序列值
create sequence TEST_ID_1009
minvalue 1
maxvalue 999999
start with 41
increment by 1
cache 20;

select TEST_ID_1009.nextval from dual;
alter sequence TEST_ID_1009 increment by 100000;
select TEST_ID_1009.nextval from dual;
alter sequence TEST_ID_1009 increment by 1;
select TEST_ID_1009.nextval from dual;

select * from user_sequences where max_value <1000000;

--查看语句CPU 磁盘等消耗
SELECT  * FROM 
(select  v.sql_id,
v.child_number,
v.elapsed_time/1000, --秒
v.cpu_time/1000,
v.disk_reads/1000,
rank() over(order by v.elapsed_time desc) elapsed_rank,  --查看总消耗时间
--rank() over(order by v.cpu_time desc) elapsed_rank,  --查看CPU消耗时间
--rank() over(order by v.disk_reads desc) elapsed_rank,  --查看消耗磁盘读取
A.MODULE,A.OSUSER,A.INST_ID,A.SID,A.SERIAL#,
v.sql_text,V.SQL_FULLTEXT
from(SELECT DISTINCT * FROM  gv$session) A LEFT JOIN gv$sql v  ON v.sql_id=nvl(A.SQL_ID,A.PREV_SQL_ID) WHERE A.SQL_ID IS NOT NULL OR A.PREV_SQL_ID IS NOT NULL) 
WHERE  SQL_ID IS NOT NULL;


--总IOps和IO吞吐量（oracle 11g）
select sum(decode(name,'physical read IO requests',value,'physical write IO requests',value,0)) as iops,
sum(decode(name,'physical read bytes',value,'physical write bytes',value,0)) / 1024 / 1024 as mbps from v$sysstat
where name in ('physical read IO requests','physical write IO requests','physical read bytes','physical read total bytes',
'physical write bytes','physical write total bytes','physical read total IO requests','physical write total IO requests');

--磁盘
select name,free_mb/1024 FREE_GB,total_mb/1024 TOTAL_GB from v$asm_diskgroup