Re: import problems Solaris 8 and 8.1.7

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 6 Dec 2002 10:49:38 -0000
Message-ID: <3df080c3$0$227$ed9e5944@reading.news.pipex.net>

"Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message news:uv0snd6ga83197_at_corp.supernews.com...
> Hi Norman,
>
> Sorry, had a day off yesterday. I am pretty sure it is in blocks but
cannot
> find any evidence for it so far. Not on Metalink, not in the Oracle doc's.
> They simply never tell us if we're dealing with blocks or bytes.

I believe that the following test demonstrates that Peter is correct. demo.txt is a 554k file. I have an 8k block size and a uniform extent size in my lob tablespace of 128k or 16 blocks. There should be room for exactly 3 extents (if my maths is correct). The ora-01691 comes up with 16 when trying to allocate the 4th extent not 128k.( or170k which would be the amount of the clob not loaded again assuming I can add up). Any corrections/debunkings welcomed as usual.

As for the original poster I rather suspect that the export was done with compress=Y.

SQL> SHOW PARAMETER BLOCK

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
---
db_block_buffers                     integer     0
db_block_checking                    boolean     FALSE
db_block_checksum                    boolean     TRUE
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     16

SQL> CREATE TABLESPACE TEST_LOB
2 DATAFILE 'C:\ORACLE\ORADATA\NL9IWK\TEST_LOB.DBF' SIZE 448K 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; Tablespace created.

SQL> ALTER USER NIALL QUOTA UNLIMITED ON TEST_LOB; User altered.

SQL> CREATE TABLE TEST_LOB
  2 (
  3 ID NUMBER,
  4 LOB_LENGTH NUMBER,
  5 LOB_COL CLOB)
  6 TABLESPACE USERS
  7 LOB(LOB_COL) STORE AS (TABLESPACE TEST_LOB); Table created.

SQL> CREATE OR REPLACE DIRECTORY TEST
2 AS 'C:\';

Directory created.

SQL> DECLARE

  2  M_FILE BFILE;
  3  M_BYTES INTEGER;
  4  M_ID INTEGER;
  5  M_LOB CLOB;

6 BEGIN
7 M_FILE :=BFILENAME('TEST','DEMO.TXT'); 8 M_BYTES :=DBMS_LOB.GETLENGTH(M_FILE); 9 DBMS_LOB.FILEOPEN(M_FILE);
10 INSERT INTO TEST_LOB(ID,LOB_COL) VALUES(1,EMPTY_CLOB()) 11 RETURN LOB_COL INTO M_LOB;
12 DBMS_LOB.LOADFROMFILE(M_LOB,M_FILE,M_BYTES); 13 COMMIT;
14 DBMS_LOB.FILECLOSE(M_FILE);
15* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01691: unable to extend lob segment NIALL.SYS_LOB0000030327C00003$$ by 16 in tablespace TEST_LOB
ORA-06512: at "SYS.DBMS_LOB", line 616
ORA-06512: at line 12

SQL> SPOOL OFF Received on Fri Dec 06 2002 - 04:49:38 CST

Re: import problems Solaris 8 and 8.1.7 - urgent