| data block corruption [message #172536] |
Wed, 17 May 2006 02:35  |
oradba16
Messages: 54
Registered: September 2005
|
Member |
|
|
hi,
when i try to access particular table in the schema 'TEST' it throws error
stating that data block is corrupted.
i illustrated here wat i did:
SQL>connect test/tes@sona;
connected.
select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPARTMENT TABLE
EMPLOYEE TABLE
2 rows selected.
SQL>select * from employee;
select * from employee
*
ERROR at line:1
ORA-01578:ORACLE data block corrupted(file# 12,block#17)
ORA-01110:datafile:'D:\ORACLE\ORADATA\SONA\TEST01.DBF'
SQL>select * from department;
select * from department
*
ERROR at line:1
ORA-01578::ORACLE data block corrupted(file# 12,block#9)
ORA-01110:datafile:'D:\ORACLE\ORADATA\SONA\TEST01.DBF'
now i connected as 'sys'
then executed the following queries to determine which segment got affected either
table or indexes but it return no rows.........
SQL>connect sys/sys@sona as sysdba;
connected.
SQL> select ds.* from dba_segments ds, sys.uet$ e where ds.header_file=e.segfil
e# and ds.header_block=e.segblock# and e.file#=12 and 17 between e.block# and e.block#+e.length-1;
no rows selected.
SQL> select ds.* from dba_segments ds, sys.uet$ e where ds.header_file=e.segfil
e# and ds.header_block=e.segblock# and e.file#=12 and 9 between e.block# and e.block#+e.length-1;
no rows selected.
SQL>connect test/tes@sona;
connected.
create table emp as select /*+index(employee emp_pk_idx)empno,ename,job,mgr,hiredate,sal,comm,deptno from employee;
*
this throws error that it cant create the table.
so wat i want to do at this stage is, i want to recreate the table without the corrupted blocks.i dont want to restore from valid backup and recover the file or to import the table from export utility.only to recreate.....
how can i do this,,,,,, anyone help me..... plz... to resove this......
with regards
Boo
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
