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

Thanks Hemant and Mladen. The mismatch was caused by columns that were marked unused and not dropped. Technote 2237347.1 details it. Thanks again for your assistance.

On Tue, Nov 13, 2018 at 3:58 PM Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:

> 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