数据量大建索引建不上数据量大索引失效

转载

mob64ca13feda16 2024-03-18 19:20:03

文章标签 数据量大建索引建不上 oracle java 表空间分区表 文章分类 数据仓库大数据

当oracle存储大数据量数据时的常用操作

一、分区表索引失效

**
原因：当表重新规划分区时可能会出现索引失效情况，此时需要重建索引
1、查看失效的索引:

select * from dba_ind_partitions where status != 'USABLE';

2、将查询结果拼接成重建索引的sql语句

select 'alter index ' || index_name || ' rebuild partition ' || partition_name || ';' from dba_ind_partitions where status != 'USABLE';

3、执行查询结果中的语句，重建索引
重建索引sql例如:

alter index JS_YSXX_DAY_IND2 rebuild partition PART_20200102;

二、月分区表转日分区表

月份区创建：

partition by range (TDRQ)
(
  partition PART_202001 values less than (TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202002 values less than (TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202003 values less than (TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202004 values less than (TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202005 values less than (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202006 values less than (TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202007 values less than (TO_DATE(' 2020-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202008 values less than (TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202009 values less than (TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202010 values less than (TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202011 values less than (TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_202012 values less than (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TJ_DATA
    pctfree 10
    initrans 1
    maxtrans 255
);

月份区转日分区

ALTER TABLE 表名 SPLIT PARTITION 月分区名 into (
	partition 日分区名 values less than (TO_DATE(时间, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition 最后一个日分区名字);

例：

ALTER TABLE gd_qyjjbxxb SPLIT PARTITION PART_202012
INTO (
	partition PART_20201201 values less than (TO_DATE('2020-12-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201202 values less than (TO_DATE('2020-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201203 values less than (TO_DATE('2020-12-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201204 values less than (TO_DATE('2020-12-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201205 values less than (TO_DATE('2020-12-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201206 values less than (TO_DATE('2020-12-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201207 values less than (TO_DATE('2020-12-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201208 values less than (TO_DATE('2020-12-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201209 values less than (TO_DATE('2020-12-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201210 values less than (TO_DATE('2020-12-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201211 values less than (TO_DATE('2020-12-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201212 values less than (TO_DATE('2020-12-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201213 values less than (TO_DATE('2020-12-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201214 values less than (TO_DATE('2020-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201215 values less than (TO_DATE('2020-12-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201216 values less than (TO_DATE('2020-12-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201217 values less than (TO_DATE('2020-12-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201218 values less than (TO_DATE('2020-12-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201219 values less than (TO_DATE('2020-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201220 values less than (TO_DATE('2020-12-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201221 values less than (TO_DATE('2020-12-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201222 values less than (TO_DATE('2020-12-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201223 values less than (TO_DATE('2020-12-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201224 values less than (TO_DATE('2020-12-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201225 values less than (TO_DATE('2020-12-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201226 values less than (TO_DATE('2020-12-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201227 values less than (TO_DATE('2020-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201228 values less than (TO_DATE('2020-12-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201229 values less than (TO_DATE('2020-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201230 values less than (TO_DATE('2020-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
	partition PART_20201231);

三、Oracle导入数据泵 .dmp文件

Imp 用户名/密码@数据库实例file=数据泵文件路径 full=y  ignore=y log=日志文件路径

例

imp snjs/snjs@hnjsdb file=/home/oracle/ysxx03.dmp full=y ignore=y log=/home/oracle/backup/ysxx03.log

四、归档错误

原因：数据频繁的增删改(dml)会引起归档日志的剧增，基础解决办法就是删除归档日志
查看归档日志占用:

select * from v$flash_recovery_area_usage;

1、执行执行命令： rman target/
2、DELETE ARCHIVELOG FROM TIME ‘SYSDATE-7’;
脚本文件

rman target/ <<EOF
DELETE ARCHIVELOG FROM TIME 'SYSDATE-7';
yes
exit;
EOF

五、存储过程插入大量数据变慢

删除表中的复合索引，复合索引会影响表的插入速度，因为每次插入都有一个索引排序的过程。如果表的数据量很大，应该对表进行分区，将索引建立在分区表上，不要全局索引。待数据插入完成之后，再把复合索引添加到分区表中。

六、分区表索引创建

先建立失效索引，然后再生效失效的索引。

CREATE INDEX 所有者.索引名 ON 所有者.表名(索引字段) TABLESPACE 表空间 LOGGING
LOCAL(PARTITION 分区名 LOGGING UNUSABLE NOCOMPRESS,
PARTITION 分区名 LOGGING UNUSABLE NOCOMPRESS,.....
)

例：

CREATE INDEX SNJS.GD_QQDXXB_IND3 ON SNJS.GD_QQDXXB (QDID)
TABLESPACE GXJS_INDEX LOGGING 
LOCAL ( 
PARTITION PART_201901
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201902
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201903
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201904
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201905
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201906
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201907
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201908
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201909
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201910
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201911
LOGGING 
unusable
NOCOMPRESS, 
PARTITION PART_201912
LOGGING 
unusable
NOCOMPRESS
)
NOPARALLEL;

生效失效的索引见：分区表索引失效

七、表空间不足（无法在表空间扩展）

增加表空间的外部存储文件
1、查询表空间的剩余量：

select dbf.tablespace_name "表空间名称",
dbf.totalspace "总量(G)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(G)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例" 
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 /1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 /1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

2、增加表空间
tj_data是表空间名 +DATA是需要挂载的磁盘组

alter tablespace tj_data add datafile '+DATA' size 30G;

八、查看锁表和解锁

1、查看被锁的表：

select p.spid,
       a.sid,
       b.session_id,
       c.object_name,
       b.oracle_username,
       b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id

2、查看锁表的sql

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
   and s.prev_sql_addr = a.address
order by sid, s.serial#;

3、解锁方法

alter system kill session 'SID，serial#';

SID和Serial#共同确定一唯一的session。

九、查看正在运行的job和杀死job；

SELECT SID,JOB FROM DBA_JOBS_RUNNING;

SELECT SID ,serial# FROM v$session  WHERE SID ='8960'

ALTER SYSTEM KILL SESSION '8960,22626';

EXEC DBMS_JOB.BROKEN('201',TRUE);

十、查看正在运行的sql

SELECT b.sid,  
       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

;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。