oracle数据库重启产生的问题详解

最近公司数据库服务器重启后造成索引、主键无效。

具体解决如下：

--oracle查询数据库表的所有索引
select a.index_name, b.column_name from dba_indexes a, dba_ind_columns b
where a.index_name = b.index_name and a.table_name = upper('S_ADSL_USER_201405') order by 1;

--由于索引有组合索引，主键索引，以下分两批处理。

--单个字段的索引处理

--将单个字段的索引名备份到t1表中

create table t_1 as select a.index_name from dba_indexes a, dba_ind_columns b
where a.index_name = b.index_name and a.owner='用户' group by  a.index_name having count(*)=1;

--批量生成删除索引的sql

select 'drop index '||t_1.index_name||';' from t_1;

--批量生成创建索引的sql
select 'create index '||a.index_name||' on '||a.table_name||'('||b.column_name||');' from dba_indexes a, dba_ind_columns b
where a.index_name = b.index_name and a.owner='IQS' and a.index_name in (select index_name from t_1) ;

--组合字段索引的处理

--两个字段索引的处理（多个以此类推）

--将两个字段的索引名备份到t2表中

create table t_2 as select a.index_name from dba_indexes a, dba_ind_columns b
where a.index_name = b.index_name and a.owner='用户' group by  a.index_name having count(*)=2;

--将索引名跟两个索引字段放入t_2_1表中

create table t_2_1 as select t_2.index_name,max(case when b.column_position = 1 then b.column_name else '' end) as c1,
max(case when b.column_position = 2 then b.column_name else '' end) c2 from  t_2  ,dba_ind_columns b where t_2.index_name = b.index_name
group by t_2.index_name

 --批量生成删除索引的sql

select 'drop index '||t_2.index_name||';' from t_2;

 --批量生成创建两个字段组合索引的sql

select 'reate index '||a.index_name||' on '||a.table_name||'('||b.c1||','||b.c2||');' from dba_indexes a, t_2_1 b
where a.index_name = b.index_name ;
'CREATEINDEX'||A.INDEX_NAME||'ON'||A.TABLE_NAME||'('||B.C1||','||B.C2||');'    

注意：主键索引是无法删除的，不但你不用管它，以下创建主键的时候系统会自动删除/创建主键索引

 --查询某张表的主键信息

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('表名');

--删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

--创建主键

alter table 表名 add primary key (主键字段);

--序列（重建完索引后序列为主键的表同样报错，所以走了一下弯路，怀疑是序列的问题，但是重建完序列后才发现是主键的问题，以下我列举下序列的重建。大家不必走这一步）

--查询某用户下的序列

select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='用户';

--删除序列

DROP SEQUENCE 序列名;

--重建序列sql

CREATE SEQUENCE emp_sequence 
     INCREMENT BY 1   -- 每次加几个 
     START WITH 1     -- 从1开始计数 
     NOMAXVALUE       -- 不设置最大值 
     NOCYCLE          -- 一直累加，不循环 
     CACHE 10; 

--主要注意的是：由于有些序列会被作为主键，而众所周知主键也是唯一索引，因此大家需要把开始计数的值设为目前表中还不存在的值，即，start with X 应大于目前此序列的last_number.