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@xxxxxxxxx>
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@xxxxxxxxx
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@xxxxxxxxx>
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@xxxxxxxxx> wrote:
A LOBINDEX is automatically created for each LOBSEGMENT (in the sameMladen Gogala
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@xxxxxxxxx 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.
Database Consultant
Tel: (347) 321-1217
