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

The assumption in your queries is incorrect. The LOB (LOB Segment) name isn't necessarily the same as the Column name. Query the DBA_LOBS view by the TABLE_NAME if you don't know the Column Name.

On Wed, 14 Nov 2018, 05:41 Sundar Mahadevan <sundarmahadevan82_at_gmail.com wrote:

> 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
> ------------------------------
> OBJECT_NAME
>
> --------------------------------------------------------------------------------------------------------------------------------
> SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
> OBJECT_TYPE CREATED LAST_DDL_TIME
> ------------------------------ -------------------- --------------------
> ------------------- -------------------- --------------------
> TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
> ------------------- ------- - - - --------------------
> ------------------------------
> ARGUS_APP
> LOB_CAS_AGENT_NOTES
> 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
>
>
> On Tue, Nov 13, 2018 at 1:19 PM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> 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