[cx-oracle-users] LOB in temporary table
Brought to you by:
atuining
Menu
▾
▴
[cx-oracle-users] LOB in temporary table
|
From: Meyer M. <Mat...@ge...> - 2012-09-20 12:36:46
|
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 ______________________________________________________________________ |
