Urgent (re: CHAINED ROWS) !!! Please Help

[dbafreak]

Hi,

I have two problems:

1)I have a table with chained rows. But the table contains a LONG column. How to solve this problem? Because if there is a LONG Column, we cannot create temporary table to hold the chained rows (as the create temp as select from the chained table fails). How to remove chanined rows in case of a table having LONG columns??

2)How can I modify the storage parameter PCT_FREE for a table. I am getting the following error:

structure of TEMP:

x varchar2(10)
y varchar2(10)
z long

alter table temp storage(pct_free 20);

ORA-02143: invalid STORAGE option


PLEASE HELP as it is very urgent!

Thanks in advance for the time and patience

[nabaig]

alter table temp (pctfree 20);

(We aould appreciate it if once in a while, people check out the documentation also) Oracle's the best dcumentation I have come across.

Nizar

[gnagesh]

You can solve your problem no 2 in this way:

alter table temp pctfree 20;

[dbafreak]

Thanks for the response. The second problem is solved.


The first one looks complicated.

Anyone who is a Oracle Tuning expert - pls advise asap.

[dbafreak]

Originally posted by nabaig
alter table temp (pctfree 20);

(We aould appreciate it if once in a while, people check out the documentation also) Oracle's the best dcumentation I have come across.

Nizar

Prefectly agree. Specially this one I could have solved by looking into the documentation.

What abt the first question??? I have seen several docs. All docs (including Oracle doc) say only one thing: Chaining is "often" unavoidable for tables having LONG columns. Period. No more explanation.

What does this mean??? Are there any work arounds? The word "often" only sugessts that there are possible solutions in some cases. What are those cases and how to solve them? If you or anyone can get the info from Oracle documentation I would really appreciate.

P.S: I am facing this a real time problem here. I need to fix it as soon as possible.

[gnagesh]

Hi,

You can use COPY command to create temporary table to hold your chained rows, this command works, even your table contains LONG column. Since your problem is creating temporary table it can be resolved through COPY command, I hope you know better than me to solve your chained rows problem.

[ovidius]

Depending of the size of your long datatype, there often is really not possible to solve this problem, it is very clear. It is not a large backdraw, in fact. Oracle accepted this.
Do you have a real problem, like a performance one, based only on this issue, or the problem is the chained rows themselves? If they are not really bothering you, let them alone!
In case you really need to repair this, try export the table data, re-create the table with some storage parameters that fit your needs, then load the data.
If is not possible to drop and re-create the table, then is nothing to do.
My oppinion. Bye!

[mike9]

If, as Ovidius already said, you have a real problem with chained row you could try to use a larger block size for this table. This is possible by using the mutiple block size feature (which is only available since Oracle9i).
To do this you would have to create a new tablespace with a large block size (32k). Then move your table to the newly created tablespace (alter table move). Depending on how big your long data are this should reduce/elimate the row chaining. But I have to admit that I have no idea if this way of reducing the chaining will realy help you.

If you want to understand why "chaining is often unavoidable for tables having LONG columns" I recommand you to have a look at
http://otn.oracle.com/docs/products/...schem.htm#2763
where it is written that:
...if all of a row's data cannot be inserted into a single data block ... Oracle stores the row using multiple row pieces. ... When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

[dbafreak]

Perhaps the COPY or the EXP/IMP should solve my problem persumably. Let me try it out. Thanks for the help.

P.S. This is not just a chained-row issue. It is a performance problem I am facing and is very critical. I have to solve this at any cost.

[denevge]

I always try to avoid LONG columns.
When i need them, I prefer creating a separate table to store LONG field.eg. LONG_TABLE( LONG_ID number, LONG_FIELD LONG ) In the table to which the LONG_FIELD belongs, I just put the LONG_ID. ( can be filled using a sequence )

This will cause the original table to be much smaller.
Only when we need the data from the LONG field, you need to do an extra select on LONG_TABLE using the LONG_ID found in your record.

Hope this helps
Gert