Yep, the queires were wrong. Some days it doesn't pay to multi-task. 1. The problem in dropping the objects will be the recursive sql oracle = will issue that will probably cause the drops to fail as they try to = touch the corrupted block for the delete of rows in the data dictionary. = You could test on the tables, since you say they are not necessary = anymore. 2. This is the standard plain jane way to get around dictionary = corruption if you do not have any good backups, ( of course it doesn't = help when the people wait for long periods of time thinking the problem = can be solved in the same way at any point in time). 3. Do the views and synonym belong to the peoplesoft application? If not = then you could use different named objects with synonyms to get around = the views that are bad. Getting around the synonym might be a little = more complex depending on if it is a a public synonym or a private = synonym. If it is a peoplesoft application then it is more complex if = you ever try to upgrade the application. 4. The other options available would depend on you answering the = questions in my original email and providing the appropriate data (dd = block dump and block trace of the corrupted block to determine the type = of corruption and whether the block can be easily repaired or whether = data extraction and reinsertion is the only way around the problem).=20 Along the lines of the original email, when you say you don't have any = good backups, what does that mean? (You have no backups at all? You have = no complete backups including any copies of the system datafile? You = have backups with no archive logs to recover it from? You have no = backups prior to the detection of the corrupted block?) -----Original Message----- From: Vlado Barun [mailto:vlado@xxxxxxxxxx]=20 Sent: Saturday, January 15, 2005 1:38 PM To: Parker, Matthew; oracle-l@xxxxxxxxxxxxx Subject: RE: Identifying the content of a corrupted block in system = tablespace Thanks for the queries however they returned no rows, which I accutally = expected, since obj$ is not part of the c_obj# cluster, which is the = cluster that has the corrupted block (see my original post). So, I = didn't even need to use the alter session... BTW, in the second and third query you need to adjust the index name... So, what I did is to loop through each object in obj$ and try to access = the data. See code below. In this way I identified 3 tables, 2 views and = a synonym that are affected by the corrupted block. Fortunately, the = three tables are not needed anymore, and I should be able to rebuild the = 2 views and synonym. =20 So, my next question is what are my options in regards to resolving this = problem? Currently, I see the following options: 1) As somebody mentioned before, if the row data and not the block = header data is corrupt, then by just dropping the objects, the = corruption might go away.=20 2) Create an empty database, export objects from corrupted database and = import all the objects into the new database. I like option 1, but if it doesn't work is there any other less resource = intensive way to resolve this than option 2? create or replace procedure access_all_data as l_sql_string varchar2(2000); l_type number; l_object_name varchar2(30); l_owner varchar2(30); l_cursor_name INTEGER; l_ignore integer; l_sqlerrm varchar2(4000); l_row_count number; cursor objs is select o.name object_name , o.type# type , u.name owner from sys.obj$ o , sys.user$ u where o.owner# =3D u.user# and u.name not in ('SYSTEM', 'SYS') -- and rownum <11 ORDER BY u.name, o.name; begin for obj_rec in objs loop begin l_object_name:=3D obj_rec.object_name; l_owner:=3D obj_rec.owner; l_type:=3D obj_rec.type; =09 l_sql_string:=3D'select /*+ full(t) noparallel(t) */ count(*) from ' || l_owner || '.' || l_object_name || ' T'; l_cursor_name :=3D dbms_sql.open_cursor; dbms_sql.parse(l_cursor_name , l_sql_string , SYS.DBMS_SQL.native); DBMS_SQL.DEFINE_COLUMN(l_cursor_name, 1, l_row_count); l_ignore :=3D dbms_sql.execute(l_cursor_name); l_ignore :=3D DBMS_SQL.FETCH_ROWS(l_cursor_name); DBMS_SQL.COLUMN_VALUE(l_cursor_name, 1, l_row_count); =09 dbms_sql.close_cursor(l_cursor_name); insert into object_avail(owner, object_name, type, row_count)=20 values (l_owner, l_object_name, l_type, l_row_count); exception when others then l_sqlerrm :=3D sqlerrm; insert into object_avail(owner, object_name, type, error_msg)=20 values (l_owner, l_object_name, l_type, l_sqlerrm); dbms_sql.close_cursor(l_cursor_name); end; end loop; end; / Vlado Barun, M.Sc. Mobile: 865 335 7652 AIM: vbarun2 -----Original Message----- From: Parker, Matthew [mailto:matthewp@xxxxxxxxxx] Sent: Friday, January 07, 2005 10:56 AM To: vlado@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: RE: Identifying the content of a corrupted block in system = tablespace Here are the three queries (Jeremiah was referring to, (nice to know = your still having fun jeremiah although your company name is kind of = funny)) in order to return the most data available, easily: --So the the second part of the minus will bypass the bad block. ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; select /*+ INDEX (o I_OBJ_1) */ obj# from obj$ o minus select /*+ FULL = (o) */ obj# from obj$ o; select /*+ INDEX (o I_OBJ_1) */ owner#,name,namespace,remoteowner,linkname,subname from obj$ o minus = select /*+ FULL (o) */ = owner#,name,namespace,remoteowner,linkname,subname from obj$ o; select /*+ INDEX (o I_OBJ_1) */ oid$ from obj$ o minus select /*+ FULL = (o) */ oid$ from obj$ o; To fix this becomes more complex: 1. Does your friend have any backups of the database? How old are the = backups? Is there a continuous redo chain for the backup? 2. Actual repair of the block would require at least a trace dump and a = block dump. alter system dump datafile '/u100/oradata/fstst/system01.dbf' block = 8002; (You will need to fill in the appropriate database blocksize for = the bs=3D paramter in bytes) dd if=3D/u100/oradata/fstst/system01.dbf = of=3Ddd_df_1_bl_8002_curr.dd bs=3D8192 skip=3D8002 count=3D1 conv=3Dnotrunc Once this information is available then some choices can be made to fix = the system. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx = [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Vlado Barun Sent: Thursday, January 06, 2005 6:24 PM To: oracle-l@xxxxxxxxxxxxx Subject: Identifying the content of a corrupted block in system = tablespace I have a corrupted block in the system tablespace.=20 I used the standard query to identify the object that the block but it failed: =20 SQL>=20 1 SELECT segment_name 2 , segment_type 3 , owner 4 , tablespace_name 5 , block_id 6 , blocks 7 FROM sys.dba_extents 8 WHERE file_id =3D 1 9* AND 8002 BETWEEN block_id and block_id + blocks -1 FROM = sys.dba_extents * ERROR at line 7: ORA-01578: ORACLE data block corrupted (file # 1, block # 8002) ORA-01110: data file 1: '/u100/oradata/fstst/system01.dbf' =20 Then I did this: ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; =20 select owner , segment_name , segment_type , tablespace_name , block_id , blocks from dba_extents where file_id=3D1 and block_id in ( select max(block_id) from dba_extents where file_id=3D1 and block_id <=3D 8002 ); =20 OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BLOCKS ----- ------------ ------------ ---------------- -------- ------ SYS C_OBJ# CLUSTER SYSTEM 7978 25 =20 So, the object that the corrupt block belongs to is c_obj#.=20 =20 Is that correct? =20 If so, and since c_obj# stores data about objects (metadata), it = basically means that I can not access the object whose metadata is in = that block. =20 So, I'm trying to identify which object is lost.=20 For example, if it's just an index, I should be able to export all the = objects from database into a new database and rebuild the index, and = resolve the corruption in that way. Any idea how I can identify the = object whose metadata is lost?=20 =20 I'm aware that Oracle support should be contacted, however a friend of = mine asked me to look into this before they contact Oracle Support = since they don't have a support contract anymore...=20 BTW, this is 8.0.6, they identified this problem 3 months ago in their = Peoplesoft application, and of course they don't have a good backup from = which to recover...=20 =20 Vlado Barun, M.Sc. Mobile: 865 335 7652 AIM: vbarun2 =20 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l