8i CLOB data needs a labotomy

From: John Barron <jbarron_at_windriver.com>
Date: Tue, 27 Jun 2000 13:17:24 -0700
Message-Id: <10541.110580@fatcity.com>

--=====================_109189015==_.ALT

Content-Type: text/plain; charset="us-ascii"

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.

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

--=====================_109189015==_.ALT

Content-Type: text/html; charset="us-ascii"

<html>
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

------------------------------------------------------<br>

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

-------------------------------------------------<br>

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 
</html>

--=====================_109189015==_.ALT--



------------------------------

From: Ashish Shah <ar_shah_at_yahoo.com>
Date: Tue, 27 Jun 2000 13:16:46 -0700 (PDT) Subject: Re: 2 Listeners

yes, you can have multiple listerner running on different port..I have worked with this before.

Make sure you change tnsnames.ora file with new port numbers.

"Olson, David" <OlsonD_at_PIOS.COM> wrote: > DBA's, > > We have 5 instances running on one large UNIX box. > They are all the same > version of oracle, 8.0.5. Connections to the > database is starting to get > the following error: > > [Microsoft][ODBC driver for > Oracle][Oracle]ORA-12500: TNS:listener failed to > start a dedicated server process > > The error message is not constant abd not always > from an ODBC connection. > Talking to our UNIX admin group, we are hitting a > max number of child > process for our listener. Is it possible to run > multiple listeners, > listening to different ports on the same server? > Or is there another work > around for this problem. > > Thanks, > Dave > > Dave Olson > DBA - Tech. Support Team > 216-332-3195 > > > > -------- > If you're bored, then visit the list's website: > http://www.lazydba.com (updated daily) > to unsubscribe, send a blank email to > oracledba-unsubscribe_at_quickdoc.co.uk > to subscribe send a blank email to > oracledba-subscribe_at_quickdoc.co.uk >

Ashish
Toronto, Canada

Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/

Received on Tue Jun 27 2000 - 15:17:24 CDT