Re: [cx-oracle-users] LOB in temporary table
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] LOB in temporary table
|
From: Chris G. <chr...@to...> - 2012-09-20 15:52:30
|
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...> 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... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > |
