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

From: Sundar Mahadevan <sundarmahadevan82_at_gmail.com>
Date: Tue, 13 Nov 2018 12:13:44 -0800
Message-ID: <CADmQEr5ZTdb+H6u-WgokdoyX+O7920Mn6Xo_NrvELut4uObqPg_at_mail.gmail.com>



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.
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 13 2018 - 21:13:44 CET

Original text of this message