datafile went offline.

Hi All,
Due to some reason(not know yet), one of the datafile went offline and cannot be bought offile as the file header is corrupt and does not match with control file.
Due to this two tables were effected. But we were able to access some of the records becuase they were part of other datafile and would fail when they try to access data in the offline datafile.

One of the table had a CLOB column, so customers are able to see all other column data except the CLOB column data.

So, when a records is saved it should save either completely or rollback completely. How can this CLOB column be missing.
Even if this clob column was part of the offline datafile, it should not show the enitre record, how can other columns data be visible and not this CLOB column value.

Can any one please explain. Our customers are bombarding us.

We are using oracle 8.1.7.4 and solaris 5.8.

regards
anandkl

how do you know the clobs were in the broken datafile

Hi,
This clob column is part of the tablespace contaning the offline datafile.

Is it possible that this clob column value was not commitedand all other column value commited.

regards
anandkl.

Quote:

---

Originally Posted by anandkl

Can any one please explain. Our customers are bombarding us.

---

Here's why. Because you've enabled storage in row (that's the default). That's why your customers are able to see CLOB data for 'inline' segments that get stored in the table's tablespace (rather than the CLOB ts). If the CLOB segments were > 32K they would've gone to your LOB tablespace..which was offline and they would see errors.

See below

Code:

---


SQL> c/log/lob
  1* create table t (id int, out_row clob) tablespace test1 lob (out_row) store as (tablespace test2 disable storage in row)
SQL> /

Table created.

SQL> insert into t values (1,'BLAH');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID
----------
OUT_ROW
--------------------------------------------------------------------------------
        1
BLAH


SQL> alter tablespace test2 offline;

Tablespace altered.

SQL> select * from t;
ERROR:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oradata/NETMSPRD/test02.dbf'

SQL> alter tablespace test2 online;

Tablespace altered.

SQL> create table t2 (id int, out_row clob) tablespace test1 lob (out_row) store as (tablespace test2 enable storage in row)
  2  /

Table created.

SQL> insert into t2 values (1,'BLAH2');

1 row created.

SQL> commit;

SQL> alter tablespace test2 offline;

Tablespace altered.

SQL> select * from t2;

        ID
----------
OUT_ROW
--------------------------------------------------------------------------------
        1
BLAH2

---

Thanks a lot Axr,
It was really help full.

regards
anandkl

Hi Axr,
Just one question, if the CLOB data is >32k and if we have mentioned "enable store in row", then would it still be in the same tablespace as the table was, or will it go to the LOB tablespace.

Also, will this insert work for tables having clob columns.

insert into new_Table select * from old_table;

Here old_table has clob values.
I am copying all data from old_table into new_table.

Will there be any issue with copying clob column values.

regards
anandkl

Hi Axr,
One more thing, both table and lob segments are in the same tablespace.


regards
anandkl

You need to store CLOB completely out of the data tablespace.
This will not only help performance but also recovery in case the datafile gets corrupted.

Tamil

Yes, i agree, But unfortunately it was done like this earlier.

Selven can you please let me know if i can use

insert into table_name as select * from old_table;

to insert clob values from other table.

regards
anandkl

Do you want insert only clob data into another table?

Tamil

No, all columns.

regards
anandkl

Quote:

---

Originally Posted by anandkl

Just one question, if the CLOB data is >32k and if we have mentioned "enable store in row", then would it still be in the same tablespace as the table was, or will it go to the LOB tablespace.
...
One more thing, both table and lob segments are in the same tablespace.
...
Selven can you please let me know if i can use
insert into table_name as select * from old_table;

---

1) LOB ts. That's the whole idea of specifying a separate LOB TS, isn't it?
...
2) Why?
...
3) Use sqlldr

2. Earlier it was designed like this.
3. Sqlldr, i cannot use becuase all the data is already in a table.For using Sqlldr, i need to spool the data and then load.
Again, how will the CLOB data be store in the spool file.

regards
anandkl

1) exp / imp

2) alter table your_table move lob(your_lob_col) store as (tablespace your_new_ts);