Re: [cx-oracle-users] LOB in temporary table

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi Chris,

Thanks for your reply. Indeed I am using an 11.2.0.2 database and the deferred segment creation is on.
So this seems to be the problem.

Kind regards,

Matthias Meyer

________________________________
Von: Chris Gould [mailto:chr...@to...]
Gesendet: Donnerstag, 20. September 2012 17:52
An: cx-...@li...
Betreff: Re: [cx-oracle-users] LOB in temporary table

What version of Oracle are you using?  In 11 a change was introduced called "deferred segment creation" which means that when create statement is executed, segments are not actually created - this is deferred until the time when the first INSERT (or MERGE) statement tries to create rows.  It's caused problems for me in the past (not specifically with Python).  But you can turn this option off or override it when you create objects. It maybe that if you're expecting the segment to exist after the create statement has run, but before you've added any data, this is causing the problem.

On 20 September 2012 13:09, Meyer Matthias <Mat...@ge...<mailto:Mat...@ge...>> wrote:
Hi all,

I'm having difficulties describing a temporary table, which contains a CLOB column.
The storage segment of the lob is not found and the export fails with "Unable to locate LOB segment"

I dug around the oracle docs and found this:
"Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created"
(http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i16096)

This can be verified, as SYS.DBA_SEGMENT does not contain a line for the SEGMENT_NAME of the LOB in SYS.DBA_LOBS.

My guess is, we need a new class in Object.py that represents LOBs without storage segments and is created in the _RetrieveColumns()-method of the Table-class if the table is temporary. But I'm not sure about this...

You can reproduce this as following:

Create the temporary table:

CREATE GLOBAL TEMPORARY TABLE TEST_TEMP_CLOB (
  key1 NUMBER,
  clobcol CLOB
)
ON COMMIT PRESERVE ROWS
RESULT_CACHE ( MODE DEFAULT)
NOCACHE;

Check the sys-views:
SELECT *
  FROM SYS.DBA_SEGMENTS
 WHERE SEGMENT_NAME = (SELECT SEGMENT_NAME
                         FROM SYS.DBA_LOBS
                        WHERE COLUMN_NAME = 'CLOBCOL');

no rows selected

Export the object using cx_OracleTools:
$ > python DescribeObject.py --schema=DWH/<mypw>@DWHT.WORLD -t TEST_TEMP_CLOB
14:00:39.242 Python exception encountered:
14:00:39.242     Message: Unable to locate LOB segment DWH.CLOBCOL
14:00:39.242     Template Id: 0
14:00:39.242     Arguments:
14:00:39.242         name => 'CLOBCOL'
14:00:39.242         owner => 'DWH'
14:00:39.242     Traceback:
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.243         file DescribeObject.py, line 59, in <module>
14:00:39.243     Details:
14:00:39.243         Local Variables:
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           row -> None
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243           statement -> "\n                    select\n                      tablespace_name,\n                      initial_extent,\n                      next_extent,\n                      min_extents,\n                      max_extents,\n                      pct_increase\nfrom user_segments\nwhere segment_type = 'LOBSEGMENT' and segment_name = :segmentName"
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           name -> 'CLOBCOL'
14:00:39.243           owner -> 'DWH'
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.243           classFactory -> <class 'cx_OracleObject.Object.Lob'>
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           statement -> '\n        select\n          o.owner,\n          o.column_name,\n          o.table_name,\n          o.segment_name,\n          o.in_row\n        from %(p_ViewPrefix)s_lobs o\n        %(p_WhereClause)s\n        order by o.column_name'
14:00:39.243           tag -> 'Lobs'
14:00:39.243           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.243           c -> ('CLOBCOL', 'CLOB', 'Y', None, None, 4000, None)
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           lobColumns -> ['CLOBCOL']
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243           duration -> 'SYS$SESSION'
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           iotType -> None
14:00:39.243           name -> 'TEST_TEMP_CLOB'
14:00:39.243           owner -> 'DWH'
14:00:39.243           partitioned -> 'NO'
14:00:39.243           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243           temporary -> 'Y'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.244           classFactory -> <class 'cx_OracleObject.Object.Table'>
14:00:39.244           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.244           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.244           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.244           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           tag -> 'Default_TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.261           name -> 'TEST_TEMP_CLOB'
14:00:39.261           objectFunction -> <class 'cx_OracleObject.Object.Table'>
14:00:39.261           owner -> 'DWH'
14:00:39.261           self -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           type -> 'TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.261           objectName -> 'TEST_TEMP_CLOB'
14:00:39.261           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           self -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262         file DescribeObject.py, line 59, in <module>
14:00:39.262           Options -> <module 'Options' from '/home/matze/gehe/cx_OracleTools-8.0/Options.pyc'>
14:00:39.262           connection -> <cx_OracleEx.Connection to DWH@DWHT.WORLD>
14:00:39.262           cx_LoggingOptions -> <module 'cx_LoggingOptions' from '/usr/local/lib/python2.7/dist-packages/cx_LoggingOptions.pyc'>
14:00:39.262           cx_OptionParser -> <module 'cx_OptionParser' from '/usr/local/lib/python2.7/dist-packages/cx_OptionParser.pyc'>
14:00:39.262           cx_OracleObject -> <module 'cx_OracleObject' from '/usr/local/lib/python2.7/dist-packages/cx_OracleObject/__init__.pyc'>
14:00:39.262           cx_OracleUtils -> <module 'cx_OracleUtils' from '/usr/local/lib/python2.7/dist-packages/cx_OracleUtils.pyc'>
14:00:39.262           describer -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.262           objectName -> 'TEST_TEMP_CLOB'
14:00:39.262           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           options -> <Values at 0xb73d60cc: {'help': None, 'asOfScn': None, 'logLevel': 'error', 'wantStorage': True, 'useDbaViews': False, 'wantComments': True, 'wantGrants': True, 'wantTablespace': True, 'showBanner': None, 'wantViewColumns': False, 'wantSynonyms': False, 'wantRelated': True, 'version': None, 'mergeGrants': True, 'schema': 'DWH/<mypw>@DWHT.WORLD', 'objectName': 'TEST_TEMP_CLOB', 'maxLongSize': 131072, 'fileName': None, 'wantTriggers': True, 'logPrefix': '%t', 'asOfTimestamp': None, 'traceback...
14:00:39.262           outFile -> <open file '<stdout>', mode 'w' at 0xb777b078>
14:00:39.262           parser -> <cx_OptionParser.OptionParser instance at 0xb73d4c2c>
14:00:39.262           sys -> <module 'sys' (built-in)>

Thanks and kind regards,

Matthias Meyer

GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
Neckartalstr. 155, D-70376 Stuttgart
Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs GmbH, Stuttgart, AG Stuttgart 15843
Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart HRB 14591
USt-IdNr.: DE 811 655 252

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://ad.doubleclick.net/clk;258768047;13503038;j?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html
_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

________________________________
GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
Neckartalstr. 155, D-70376 Stuttgart
Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs GmbH, Stuttgart, AG Stuttgart 15843
Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart HRB 14591
USt-IdNr.: DE 811 655 252

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________