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

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:
>
> SourceOwnerSourceObjectTypeSourceCountTargetOwnerTargetObjectTypeTargetCountDifferenceCount
> ARGUS_APPLOBINDEX      185       ARGUS_APPLOBINDEX177*_/8/_*
> ARGUS_APPLOBSEGMENT185               ARGUS_APPLOBSEGMENT177*_/8/_*
>
>
>
> On Mon, Nov 12, 2018 at 4:49 PM Hemant K Chitale
> <hemantkchitale_at_gmail.com <mailto: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 <mailto: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