Reorganizing a Table with LOB columns?

[Former Member]

The BRSPACE Documentation states...

BRSPACE also supports the reorganization of tables with all types of large object (LOB) columns. Large objects are recreated with the same physical characteristics as before the reorganization.

I can't figure out the syntax to reorganize a Table and it's LOBs.

Here's the error I get, when I try to reorganize a LOBINDEX. Can anyone explain why I'm sending "SYS_IL0021404147C00007$$", but it's trying to rebuild 'SYS_IL0021404147C0000723706', which it can't find........

sapbwpci01:orabwp 72% brspace -p initBWP.sap -confirm force -s 20 -l E -f idrebuild -a rebuild -s PSAPBWP -o SAPBWP -i "SYS_IL0021404147C00007$$"

BR1001I BRSPACE 6.40 (40)

BR1002I Start of BRSPACE processing: sdunhqqs.idr 2007-02-02 12.56.58

BR0280I BRSPACE time stamp: 2007-02-02 12.56.58

BR1009I Name of database instance: BWP

BR1010I BRSPACE action ID: sdunhqqs

BR1011I BRSPACE function ID: idr

BR1012I BRSPACE function: idrebuild

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRSPACE time stamp: 2007-02-02 12.56.59

BR1039I Selecting information about tables and indexes...

BR0285I This function can take several seconds/minutes - be patient...

BR0280I BRSPACE time stamp: 2007-02-02 12.57.06

BR0814I Number of tables/partitions in schema of owner SAPBWP: 22111/10977

BR0814I Number of tables in schema of owner SAPBWPDB: 142

BR0280I BRSPACE time stamp: 2007-02-02 12.57.18

BR0815I Number of indexes/partitions in schema of owner SAPBWP: 30760/61459

BR0815I Number of indexes in schema of owner SAPBWPDB: 224

BR1179E Index(es) 'SYS_IL0021404147C0000723706' not found

BR0182E Checking parameter/option '-i|rebuild_index' failed

BR0280I BRSPACE time stamp: 2007-02-02 12.57.18

BR0669E Cannot continue due to previous warnings or errors

BR0280I BRSPACE time stamp: 2007-02-02 12.57.18

BR0700E Fatal errors occurred - terminating processing...

BR1023I Number of indexes processed: 0

BR1004E BRSPACE function 'idrebuild' failed

BR1008I End of BRSPACE processing: sdunhqqs.idr 2007-02-02 12.57.18

BR0280I BRSPACE time stamp: 2007-02-02 12.57.18

BR1007I BRSPACE terminated with errors

Thanks,

Scott

[Former Member · Accepted solution]

Hello,

The object that you are trying to reorganize "belongs" to a table ( it is part of the table, not sure if you understant or I explain ) and it cannot be "reorganized" independent of the table.

You have to reorganize the table to wich this object belong, not the object itself.

Fidel

[Former Member · Accepted solution]

Fidel,

Thank you for clarifying. I did some additional reading about what you said, and understand better now.

If I perform an on-line reorg of a table with BRSPACE, will it's LOBINDEX and LOBSEGMENT Segements move too within the Tablspace?

Also, does anyone have a query that I can join dba_extents with, that'll tell me what table the LOBINDEX and LOBSEGMENTS belong to?

BTW, here's the query I'm using to find the High-Water-Mark Segments in every datafile in the database. Which is now identifying LOBINDEX and LOBSEGMENT type Segments in the Highest position in several datafiles.

select a.file_id, b.file_name, a.owner, a.segment_name, a.partition_name, a.segment_type, a.block_id

from dba_extents a, dba_data_files b

where a.file_id = b.file_id and block_id = (select max(block_id)

from dba_extents c

where a.file_id = c.file_id)

order by 1

Here's two examples...

FILE_ID FILE_NAME OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BLOCK_ID

23 /oracle/BWP/sapdata6/bwp_14/bwp.data14 SAPBWP SYS_IL0021404147C00007$$ LOBINDEX 1719601

15 /oracle/BWP/sapdata5/bwp_6/bwp.data6 SAPBWP SYS_LOB0017931286C00016$$ LOBSEGMENT 1619489

Thanks,

Scott

[Former Member · Accepted solution]

Hello,

To find out to wich table the lob segments belong you have to query DBA_LOBS:

SELECT owner, table_name, column_name, segment_name, index_name

FROM DBA_LOBS

WHERE <condition>

The condition will depend a little.

SEGMENT_NAME is the name of the "lobsegment"

INDEX_NAME is the name of the "lobindex"

in your case you see:

SYS_IL0021404147C00007$$ is the lobindex

SYS_LOB0017931286C00016$$ in the lobsegment

probably they belong to the same table but you can check it:

SELECT owner, table_name, column_name, segment_name, index_name

FROM DBA_LOBS

WHERE segment_name = 'SYS_LOB0017931286C00016$$';

SELECT owner, table_name, column_name, segment_name, index_name

FROM DBA_LOBS

WHERE index_name = 'SYS_IL0021404147C00007$$';

[Former Member · Accepted solution]

Fidel,

Works like a charm! I manually reorged several tables using BRSPACE, that had LOBSEGMENT and LOBINDEX components to them, and it reorged both the tables and LOBs.

This, of course, accomplished what I had set out to do, which was to move the LOBs from the High-Water-Mark position, and resize (shrink) the datafiles.

I've now incorporated this mthode of identifying a LOB's parent table into my automated Shrink Tablespace script.

Thanks so much for your help with this!

Scott

[Former Member · Accepted solution]

Can u please tell in detail how u reorganized the lOB segemnt reorganized through BRTOOLS.

Yogesh

[former_member185954]

Hi Scott,

Try giving the Owner in the index prefix.

brspace -p initBWP.sap -confirm force -s 20 -l E -f idrebuild -a rebuild -s PSAPBWP -o SAPBWP -i "SAPBWP"."SYS_IL0021404147C00007$$"

Regards,

Siddhesh