Re: How to migrate/recreate LOB indexes to/on another database

The issue i face is that I find the LOB object_name in dba_objects but not in dba_lobs and i do not find any mention of this LOB column LOB_CAS_AGENT_NOTES in TOAD script generator while I see other LOB columns in there. In the same table, I notice other LOB columns created as BasicFile LOB. I wonder if the previous upgrade performed 8 years back that predated me had the metadata imported somehow while the LOB columns don't really exist. Is that even a possibility?

SQL>select OWNER,TABLE_NAME,COLUMN_NAME from dba_lobs where column_name = 'LOB_CAS_AGENT_NOTES'; no rows selected

SQL>select * from dba_objects where object_name = 'LOB_CAS_AGENT_NOTES';

OWNER

SUBOBJECT_NAME                            OBJECT_ID       DATA_OBJECT_ID
OBJECT_TYPE         CREATED              LAST_DDL_TIME
------------------------------ -------------------- --------------------
------------------- -------------------- --------------------
TIMESTAMP           STATUS  T G S            NAMESPACE EDITION_NAME
------------------- ------- - - - --------------------
------------------------------

                                              21706                21706
LOB                 2011-AUG-21 06:53:30 2011-AUG-21 06:53:30
2011-08-21:06:53:30 VALID   N N N                    8

> Is the original LOB tablespace BASICFILE or SECUREFILE? If you're trying
> to move BASICFILE LOBs from one 11g to 12c, there may be a problem. I am
> Mladen Gogala and I approve of this message.
> On 11/13/18 3:13 PM, Sundar Mahadevan wrote:
>
> Hi Hemanth,
> Thanks for reverting back. I am missing 8 LOBsegments/LOBIndexes on target
> after a TTS migration.
>
> ${ORACLE_HOME}/bin/sqlplus -s /nolog << eom
> set head off feedback off pages 0 lines 200 echo off time on timing on
> connect / as sysdba
> set pages 0
> SELECT OWNER || '|' || SEGMENT_TYPE || '|' || COUNT(*) FROM DBA_SEGMENTS
> WHERE SEGMENT_TYPE LIKE 'LOB%'
> and owner not in
>
> ('ANONYMOUS','APPQOSSYS','AUDSYS','CTXSYS','DBSFWUSER','DBSNMP','DIP','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER',
>
> 'GSMUSER','LBACSYS','MDDATA','MDSYS','OJVMSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','REMOTE_SCHEDULER_AGENT',
>
> 'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','PERFSTAT','SYS','SYSMAN','SYS$UMF','SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYSTEM',
>
> 'TSMSYS','WMSYS','XDB','XS$NULL','APEX_050000','APEX_PUBLIC_USER','FLOWS_FILES','DMSYS','DSDBA','ENV_QUERY','EXFSYS','MGMT_VIEW',
> 'OWBSYS','OWBSYS_AUDIT') group by owner, segment_type ORDER BY owner,
> segment_type;
> eom
>
>
> For the above query, here is the difference between source and target:
>
> SourceOwner SourceObjectType SourceCount TargetOwner TargetObjectType
> TargetCount DifferenceCount
> ARGUS_APP LOBINDEX 185 ARGUS_APP LOBINDEX
> 177 *8*
> ARGUS_APP LOBSEGMENT 185 ARGUS_APP LOBSEGMENT 177
> *8*
>
>
>
> On Mon, Nov 12, 2018 at 4:49 PM Hemant K Chitale <hemantkchitale_at_gmail.com>
> wrote:
>
>> A LOBINDEX is automatically created for each LOBSEGMENT (in the same
>> Tablespace).
>> Do you have LOB Segments -- in the source database ?
>> How are the LOB columns defined -- storage to be Inline pr OutOfLine
>> (i.e. in a separate LOB Segment) ?
>>
>> On Tue, 13 Nov 2018, 07:30 Sundar Mahadevan <sundarmahadevan82_at_gmail.com
>> wrote:
>>
>>> Hi All,
>>> Greetings. I recently performed a TTS migration and I notice that LOB
>>> indexes on a particular table are missing. How do i recreate them or
>>> migrate them from source? The sqlfile generated from full metadata from
>>> source does not have these indexes. Neither does Toad generate the sql for
>>> the lob indexes. Appreciate your assistance. Thanks.
>>>
>> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l