In what tablespace are journal tables created?

[peter_strauss]

Hello,

When rebuilding an index, what tablespace is the corresponding journal table created in?

Kind regards,

Peter

[lbreddemann · Accepted solution]

> When rebuilding an index, what tablespace is the corresponding journal table created in?

For 10.2.0.4 it's the same tablespace where the index is located.

For other versions I guess it's the same...

regards,

Lars

[Former Member]

Hi,

The journal table is created in the target tablesace of the index that is being rebuilt. By default this is the tablespace where the index resides now, unless you use "ALTER INDEX ... REBUILD ONLINE TABLESPACE <newtsp>" in which case the journal table will be in <newtsp>.

The journal table is an index-organized table (IOT) with a system-generated name beginning with 'SYS_JOURNAL_'. If you want to know its size and its tablespace while the index is being rebuilt use a query like the following:

select segment_name,tablespace_name,bytes from dba_segments
where segment_name in 
(select index_name from dba_indexes
where table_name like 'SYS_JOURNAL%');

Regards,

Mark

[Former Member]

Hi,

Used same tablespace for index as well as tables like PSAPSR3..

U can run beow statement before and after rebuild index

select index_name,table_name,tablespace_name from dba_indexes;

SurendraJain

[lbreddemann]

>

> Hi,

> Used same tablespace for index as well as tables like PSAPSR3..

>

> U can run beow statement before and after rebuild index

> select index_name,table_name,tablespace_name from dba_indexes;

That's totally besides the point of the question!

It was asked where the JOURNAL TABLE is created that is used to keep track of data changes during the index rebuild.

Since this table gets removed after the index rebuild automatically, you cannot query any dictionary view for it.

regards,

Lars

[Former Member]

Lars,

You are right. But it can be view from dictionary as written by mark.

select segment_name,tablespace_name,bytes from dba_segments

where segment_name in

(select index_name from dba_indexes

where table_name like '%JOURNAL%')

SurendraJain