ORA-01537 ORA-00600 [ktssdrp1] ORA-00600 [2814] ORA-00600 [25013]

测试环境重建表空间报错
drop tablespace TS1 including contents and datafiles;
create tablespace TS1 datafile ‘/data/mydb/dbf/ts1.dbf’ size 3M;

– 测试环境测试重建表空间时报错ORA-600 [ktssdrp1]
– drop 表空间的时候，数据字典不一致，报错提示ORA-600 [ktssdrp1]

SYS@: > drop tablespace ts1 including contents and datafiles;
ORA-00600: internal error code, arguments: [ktssdrp1], [0], [1], [94720], [], [], [], [], [], [], [], []

SYS@: > alter tablespace ts1 online;
ORA-00600: internal error code, arguments: [ktssdrp1], [0], [1], [94720], [], [], [], [], [], [], [], []

-- 创建表空间
SYS@: > create tablespace TS1 datafile '/data/mydb/dbf/ts1.dbf' size 3M;
ORA-01537: cannot add file '/data/mydb/dbf/ts1.dbf' - file already part of database
ORA-00600: internal error code, arguments: [2814], [6], [/data/mydb/dbf/ts1.dbf], [], [], [], [], [], [], [], [], []

-- 查看ts$中表状态状态是2
select ts#,name,online$ from ts$ where name='TS1';
 TS# NAME               ONLINE$
---- --------------- ----------
   5 TS1                      2


# 把ts#状态手动修改为drop 再创建表空间还是提示ORA-01537
update ts$ set online$=3 where ts#=5;  
commit;


-- 查询file$中是否有表空间文件，文件存在，需要把ts#号置空
select FILE#,STATUS$,TS#,RELFILE# from file$ where ts#=5;
FILE#    STATUS$  TS#   RELFILE#
----- ---------- ---- ----------
    6          2    5          6


-- 修改file$ 再执行创建表空间
update file$ set STATUS$=1,TS#=null,RELFILE#=null where ts#=5;
commit;
create tablespace TS1 datafile '/data/mydb/dbf/ts1.dbf' size 3M;
ORA-00600: internal error code, arguments: [25013], [0], [5], [TS1], [TS1], [0], [7], [], [], [], [], []
-- 25013 数据字典不一致
select * from ts$ where name in('TS1','TS11');
-- 没发现什么差异，执行flush pool之后再执行，已经成功重建了表空间
create tablespace TS1 datafile '/data/mydb/dbf/ts1.dbf' size 3M;
复制

– 通过创建表空间，删除表空间，观察ts和file的记录变化过程

-- 正常drop 表空间 file$中的记录会删掉 ts$的记录会保留，只是修改状态
create tablespace TS11 datafile '/data/mydb/dbf/ts11.dbf' size 3M;
create tablespace TS12 datafile '/data/mydb/dbf/ts12.dbf' size 3M;
把file$中6号文件状态改为2 创建成功
select ts#,name,online$ from ts$ where name like 'TS%' order by ts#;
 TS# NAME   ONLINE$
---- ------ -------
   5 TS1          1
   8 TS2          1
  12 TS11         1
  13 TS12         1
   
select * from file$ where ts# in(5,8,12,13) order by ts#;
FILE# STATUS$ BLOCKS  TS# RELFILE#  MAXEXTEND INC CRSCNWRP CRSCNBAS   SPARE1 SPARE2 SPARE3 SPARE4
----- ------- ------ ---- -------- ---------- --- -------- -------- -------- ------ ------ ------
    6       2    384    5        6          0   0        0  3389564 25165826
    7       2  12800    8        7          0   0        0  1412697 29360130
    9       2    384   12        9          0   0        0  3485696 37748738
   10       2    384   13       10          0   0        0  3485989 41943042

drop tablespace ts11 including contents and datafiles;
select ts#,name,online$ from ts$ where name like 'TS%' order by ts#;
 TS# NAME               ONLINE$
---- --------------- ----------
   5 TS1                      1
   8 TS2                      1
  12 TS11                     3
  13 TS12                     1

select * from file$ where ts# in(5,8,12,13) order by ts#;
select * from file$ where file#=9;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS   SPARE1 SPARE2 SPARE3 SPARE4
----- ------- ------ --- -------- --------- --- -------- -------- -------- ------ ------ ------
    6       2    384   5        6         0   0        0  3389564 25165826
    7       2  12800   8        7         0   0        0  1412697 29360130
   10       2    384  13       10         0   0        0  3485989 41943042
    9       1    384                      0   0        0  3485696 37748738

-- 删除表空间之后，file$基表中 status$修改为1 ts#和rfile#置空
复制