Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 8i CLOB data needs a labotomy

RE: 8i CLOB data needs a labotomy

From: Steve Orr <sorr_at_arzoo.com>
Date: Tue, 27 Jun 2000 14:48:23 -0700
Message-Id: <10541.110591@fatcity.com>


This is a multi-part message in MIME format.

------=_NextPart_000_0014_01BFE046.BE1A2420 Content-Type: text/plain;

        charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

What about the CRTS_QISI_LOB tablespace? What's the before/after on it? Did the clobs start out in the CRTS_DATA tablespace? No lobs in the system tablespace, right? Check the tablespace_name in dba_segments where segment_type in('LOBSEGMENT','LOBINDEX'). Just some ideas... happy lob hunting.

HTH,
Steve Orr

  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of John Barron   Sent: Tuesday, June 27, 2000 2:28 PM
  To: Multiple recipients of list ORACLE-L   Subject: 8i CLOB data needs a labotomy

  Hi

  I think my 8i CLOB data needs a labotomy. This is my problem and my work around. We use perl DBD::Oracle to load our database from flat files. Part of it is to load embedded text data into CLOB columns in our table CR_HLD_QISI_CALLS. The CLOB cols are PROBLEMTEXT, ANALYSISTEXT, etc. When we run the perl script the 100M text file mushrooms out to 900M in the tablespace. Using the tablespace coalesce command has no affect on the tablespaces. If we MOVE the tables and CLOB data to another tablespace (or even in the same tablespace) and compress to one extent it shrinks the 800M down to 40M.

  Why does it take so much tablespace? Is it the way perl loads data? We have set default storage on the tablespace and tables to PCTFREE=0 as there will be no updates to this table. The database is UTF8 with NLSLANG=ja16euc and we do have some indexes on the tables but none of this accounts for the huge space requirements for the initial upload.

  Any comments, pointers would be appreciated. Ive added our move sql and the space used before and after the table move and coalesce.

  tia

  John Barron

  The SQL for moving the CLOBS and tables



  alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_DATA   lob (PROBLEMTEXT) store as (tablespace CRTS_QISI_LOB   storage(minextents 1));
  alter table CRTS.CR_HLD_QISI_CALLS move   lob (ANALYSISTEXT) store as (tablespace CRTS_QISI_LOB   storage(minextents 1));
  alter table CRTS.CR_HLD_QISI_CALLS move   lob (HISTORY) store as (tablespace CRTS_QISI_LOB   storage(minextents 1));

  alter tablespace CRTS_QISI_DATA coalesce;

  alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_QISI_DATA   lob (ESC_SUMMARY) store as (tablespace CRTS_QISI_LOB   storage(minextents 1));

  The BEFORE and AFTER space output



  Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production   With the Partitioning option
  JServer Release 8.1.6.0.0 Production
  Table                CLOB Col
  CR_HLD_QISI_CALLS    PROBLEMTEXT                64,430,080
  CR_HLD_QISI_CALLS    ANALYSISTEXT              469,114,880
  CR_HLD_QISI_CALLS    HISTORY                   279,552,000
  CR_HLD_QISI_CALLS    ESC_SUMMARY                 1,597,440
  CR_HLD_QISI_CALLS    Total                     814,694,400

  Table altered.

  Table altered.

  Table altered.

  Tablespace altered.

  Table altered.

  CR_HLD_QISI_CALLS    PROBLEMTEXT                39,936,000
  CR_HLD_QISI_CALLS    ANALYSISTEXT                  532,480
  CR_HLD_QISI_CALLS    HISTORY                       532,480
  CR_HLD_QISI_CALLS    ESC_SUMMARY                   532,480
  CR_HLD_QISI_CALLS    Total                      41,533,440


------=_NextPart_000_0014_01BFE046.BE1A2420 Content-Type: text/html;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dwindows-1252" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D357294021-27062000>What=20
about the CRTS_QISI_LOB tablespace? What's the before/after on it? Did = the clobs=20
start out in the CRTS_DATA tablespace? No lobs in the system tablespace, = right?=20
Check the tablespace_name in dba_segments where segment_type=20 in('LOBSEGMENT','LOBINDEX'). Just some ideas... happy lob=20 hunting.<BR></SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D357294021-27062000>HTH,</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D357294021-27062000>Steve=20
Orr<BR></DIV></SPAN></FONT>
<BLOCKQUOTE>

  <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT = face=3DTahoma=20
  size=3D2>-----Original Message-----<BR><B>From:</B> root_at_fatcity.com=20   [mailto:root_at_fatcity.com]<B>On Behalf Of </B>John = Barron<BR><B>Sent:</B>=20
  Tuesday, June 27, 2000 2:28 PM<BR><B>To:</B> Multiple recipients of = list=20
  ORACLE-L<BR><B>Subject:</B> 8i CLOB data needs a=20   labotomy<BR><BR></DIV></FONT><FONT face=3D"Arial, Helvetica">Hi =
<BR><BR>I think=20

  my 8i CLOB data needs a labotomy. This is my problem and my work = around. We=20
  use perl DBD::Oracle to load our database from flat files. Part of it = is to=20
  load embedded&nbsp; text data into CLOB columns in our table=20   CR_HLD_QISI_CALLS. The CLOB cols are PROBLEMTEXT, ANALYSISTEXT, etc. = When we=20
  run the perl script the 100M text file mushrooms out to 900M in the=20   tablespace. Using the tablespace coalesce command has no affect on the =

  tablespaces. If we&nbsp; MOVE the tables and CLOB data to another = tablespace=20
  (or even in the same tablespace) and compress to one extent it shrinks = the=20
  800M down to 40M.<BR><BR>Why does it take so much tablespace? Is it = the way=20
  perl loads data? We have set default storage on the tablespace and =

tables to=20
  PCTFREE=3D0 as there will be no updates to this table. The database is =
UTF8 with=20
  NLSLANG=3Dja16euc and we do have some indexes on the tables but none =
of this=20
  accounts for the huge space requirements for the initial = upload.<BR><BR>Any=20
  comments, pointers would be appreciated. Ive added our move sql and = the space=20
  used before and after the table move and = coalesce.<BR><BR>tia<BR><BR>John=20
  Barron<BR><BR><BR>The SQL for moving the CLOBS and=20   =
tables<BR>------------------------------------------------------<BR></FON=
T><FONT=20
  face=3Dr_ansi>alter table CRTS.CR_HLD_QISI_CALLS move tablespace=20   CRTS_DATA<BR>lob (PROBLEMTEXT) store as (tablespace=20   CRTS_QISI_LOB<BR>storage(minextents 1));<BR>alter table = CRTS.CR_HLD_QISI_CALLS=20
  move<BR>lob (ANALYSISTEXT) store as (tablespace=20   CRTS_QISI_LOB<BR>storage(minextents 1));<BR>alter table = CRTS.CR_HLD_QISI_CALLS=20
  move<BR>lob (HISTORY) store as (tablespace = CRTS_QISI_LOB<BR>storage(minextents=20
  1));<BR><BR>alter tablespace CRTS_QISI_DATA coalesce;<BR><BR>alter = table=20
  CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_QISI_DATA<BR>lob = (ESC_SUMMARY)=20
  store as (tablespace CRTS_QISI_LOB<BR>storage(minextents=20   1));<BR><BR><BR></FONT><FONT face=3D"Arial, Helvetica">The BEFORE and = AFTER=20
  space=20
  =
output<BR>-------------------------------------------------<BR></FONT><FO=
NT=20
  face=3Dr_ansi>Oracle8i Enterprise Edition Release 8.1.6.0.0 - = Production<BR>With=20
  the Partitioning option<BR>JServer Release 8.1.6.0.0&nbsp;=20   =
Production<BR><BR>Table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   CLOB Col<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
PROBLEMTEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  64,430,080<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
ANALYSISTEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&= nbsp;&nbsp;&nbsp;=20
  469,114,880<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
HISTORY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   279,552,000<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
ESC_SUMMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  1,597,440<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   814,694,400<BR><BR>Table altered.<BR><BR><BR>Table = altered.<BR><BR><BR>Table=20
  altered.<BR><BR><BR>Tablespace altered.<BR><BR><BR>Table=20   altered.<BR><BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
PROBLEMTEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  39,936,000<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
ANALYSISTEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&= nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   532,480<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
HISTORY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   532,480<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
ESC_SUMMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   532,480<BR>CR_HLD_QISI_CALLS&nbsp;&nbsp;&nbsp;=20   =
Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= Received on Tue Jun 27 2000 - 16:48:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US