Migration LONG to LOB in manual space management tablespaces

[former_member209959]

Hi everyone,

We have Netweaver 7.0 and Oracle 11g and we are considering to migrate our manual tablespaces to ASSM. The first problem we've found is we can't do that online due to the LONG data type. So we decided to migrate LONG to LOB first. Note 835552 says that for Oracle version 11g the conversion is from LONG to SECUREFILE. But we follow seeking notes and in 1426979 (Oracle 11g: SecureFiles - The new way to store LOB data) one of the prerequisites for the conversion is to use ASSM tablespaces.

At this point, we can't migrate (online) to ASSM due to LONG data and neither can migrate to SECUREFILE due to the ASSM.

My question is: is it possible to make the migration from LONG to SECUREFILE specifying in the brspace a new tablespace with ASSM?

Thanks in advanced.

[ACE-SAP · Accepted solution]

Hello

It is in fact possible to reorganize segment to a specific target tablespace using brspace option -n|-newts

brspace -u / -f tbreorg -a long2lob -o sapsr3 -s <source_tablesapce> -t allsel -n <target_tablespace_in_ASSM>

It is odd that in 11g you are not in ASSM, so you upgrade to that version and never performed tablespace reorg ?

620803 - Oracle 9i: Automatic Segment Space Management

The new ASSM function is available as of Oracle Release 9i. As of Oracle 10g, ASSM is already contained in the standard system, and new tablespaces are by default created as ASSM tablespaces.

Regards

[former_member209959 · Accepted solution]

Hi Yves,

I know the option you say me, but my doubt is if I can do that when my source tablespace is not ASSM due to the note 1426979. Casually Carsten Leidner made a question in this forum yesterday (you answered him too) and he explained that it's possible to convert fields LONG to LOB with not ASSM tablespaces and in the same step, doing the migration in a new ASSM tablespace.

I'll try to that and when I finish (not very early) I'll write down here the results.

Regards.

[ACE-SAP · Accepted solution]

Hi Maximino

Carsten did get an error when trying to reorganize within the original tablespace that was not defined with ASSM (point 1). If you do it specify a target tablespace that is ASSM it will work... as it is mentioned in Carsten's post (point 3).

Regards