Discussion:
How to migrate/recreate LOB indexes to/on another database
Sundar Mahadevan
2018-11-12 23:28:21 UTC
Permalink
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.
Hemant K Chitale
2018-11-13 00:49:28 UTC
Permalink
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) ?
Post by Sundar Mahadevan
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.
Sundar Mahadevan
2018-11-13 20:13:44 UTC
Permalink
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*
Post by Hemant K Chitale
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) ?
Post by Sundar Mahadevan
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
2018-11-13 21:18:32 UTC
Permalink
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.
Post by Sundar Mahadevan
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
SourceOwnerSourceObjectTypeSourceCountTargetOwnerTargetObjectTypeTargetCountDifferenceCount
ARGUS_APPLOBINDEX      185       ARGUS_APPLOBINDEX177*_/8/_*
ARGUS_APPLOBSEGMENT185               ARGUS_APPLOBSEGMENT177*_/8/_*
On Mon, Nov 12, 2018 at 4:49 PM Hemant K Chitale
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
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
Sundar Mahadevan
2018-11-13 21:39:28 UTC
Permalink
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
Post by Mladen Gogala
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.
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
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*
Post by Hemant K Chitale
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) ?
Post by Sundar Mahadevan
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
Hemant K Chitale
2018-11-13 23:58:46 UTC
Permalink
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.
Post by Sundar Mahadevan
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
Post by Mladen Gogala
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.
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
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 <
Post by Hemant K Chitale
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) ?
Post by Sundar Mahadevan
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
Sundar Mahadevan
2018-11-14 02:05:44 UTC
Permalink
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.
Post by Hemant K Chitale
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.
Post by Sundar Mahadevan
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
Post by Mladen Gogala
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.
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
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 <
Post by Hemant K Chitale
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 <
Post by Sundar Mahadevan
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
Mladen Gogala
2018-11-13 21:16:15 UTC
Permalink
LOB indexes are created automatically, you don't need to create them. I
am Mladen Gogala and I approve of this message.
Post by Sundar Mahadevan
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
Loading...