Sundaramurthy Blog

June 8, 2010

How to findout corrupted object from oracle error

Filed under: Oracle Block,Oracle internals — sundar5 @ 9:09 pm

From alertlog file.

——————-

Example

ORA-01578: ORACLE data block corrupted (file # 9, block # 15)

ORA-01110: data file 9: ‘/mnt/poc/ETLTEST/deleterow.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

AFN :  file 9: Absolute FILE NUMBER


SQL>SELECT tablespace_name, segment_type, owner, segment_name,FILE_ID,BLOCK_ID,

BLOCKS  FROM dba_extents WHERE file_id = 9 and 15 between block_id

AND block_id + blocks – 1

/

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME  FILE_ID BLOCK_ID  BLOCKS

————— ———— —– ————- ——- ——–  ——

DELETEROW       TABLE        SYS   T10                 9        9       8

or

SQL> set long 64000

SQL> select dbms_metadata.get_ddl(segment_type, segment_name, owner)

FROM dba_extents WHERE file_id=9 AND 15 BETWEEN block_id AND block_id + blocks -1;

DBMS_METADATA.GET_DDL(SEGMENT_TYPE,SEGMENT_NAME,OWNER)

——————————————————————————–

CREATE TABLE “SYS”.”T10″

(    “N1” NUMBER,

“DESC1” VARCHAR2(10)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “DELETEROW”

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: