Tags: based, column, database, dbms_xmldom, extend, lob, mysql, ora-01691, oracle, package, running, schema, segment, sized, sql, transformation, unable, xml
ORA-01691: unable to extend lob segment
10,415 words with 14 Comments; publish: Wed, 13 Feb 2008 10:07:00 GMT; (25062.01, « »)
Hi,
I was running an XML transformation using dbms_xmldom package and when I do this for a 1.65 MB sized XML ( the XML is a NON SCHEMA BASED column in a table).
I got this error:
ORA-1691: unable to extend lobsegment XXXXXXX.SYS_LOB0000073404C00015$$ by 1024 in tablespace XXXXX
I would think that I am getting this error because I have built the table by default parameters. Just a create statement. Need some help in tweaking with the parameters while creating the table and the tablespace too.
I would be happy to send the parameters I used for this if you say they are relevant here. Please let me know what exactly would you want me to provide. I also have a trace file when I ran that procedure.
Thank you,
Rahul.
http://oracle.itags.org/q_oracle_165064.html
All Comments
Leave a comment...
- 14 Comments
- What database version are you on?
Normally this means that the tablespace is full OR that there is not an extent (aka chunk) available that fits the size needed. If so... Check dba_data_files, dba_tablespaces, v$datafile to see how tablespace XXXXX is build up or use:
set long 100000select dbms_metadata.get_ddl('TABLESPACE','XXXXX') from dual;or you could, alter database datafile '???.dbf' resize {bigger size};
or you could, alter database datafile '??.dbf' autoextend on next 10M maxsize 2048M
You could try to free up space via a coalesce tablespace statement of free space a different way. You can check in dba_free_space if there is still a chunck of space free (probably none or <
1024 in size)
#1; Sat, 23 Feb 2008 13:41:00 GMT
- Marco,
Version 10.2.0.1.0.
Ok, I have run this.
SQL> select dbms_metadata.get_ddl('TABLESPACE','XXXXX') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','XXXXX')
----
CREATE TABLESPACE "XXNCH" DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\YYYYY\XXXXX.DBF' SIZE 52428800,
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\YYYYY\XXXXX_1.DBF' SIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> alter tablespace xxxxx resize 256M;
alter tablespace xxxxx resize 256M
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace XXXXX
Also, I was checking in TOAD (too lazy and time constrained to use scripts). and this is what I got:
That one LOBSegment was occupying 83 MB of space.
P.S: also, I am masking any tablespace and schema or user names.
Message was edited by:
rputtagunta
#2; Sat, 23 Feb 2008 13:42:00 GMT
- Also,
I got this;
SQL> select * from dba_free_space where tablespace_name = 'XXXXX' order by bytes desc;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO-- -- -- -- -- --XXXXX 7 12553 2031616 248 7XXXXX 7 1689 917504 112 7XXXXX 6 5697 589824 72 6XXXXX 7 2881 589824 72 7XXXXX 6 4105 589824 72 6XXXXX 6 3337 524288 64 6XXXXX 7 3337 458752 56 7XXXXX 7 3929 393216 48 7XXXXX 7 3593 393216 48 7XXXXX 7 2825 393216 48 7XXXXX 6 2577 262144 32 6XXXXX 6 3177 262144 32 6XXXXX 7 3081 262144 32 7XXXXX 7 3689 262144 32 7XXXXX 7 2537 262144 32 7XXXXX 7 1929 262144 32 7XXXXX 6 5401 196608 24 6XXXXX 6 3145 196608 24 6XXXXX 7 3441 196608 24 7XXXXX 6 5281 131072 16 6XXXXX 7 3849 131072 16 7XXXXX 6 4217 131072 16 6XXXXX 7 2505 131072 16 7XXXXX 7 2025 131072 16 7XXXXX 6 5233 131072 16 6XXXXX 7 345 131072 16 7XXXXX 6 5153 131072 16 6XXXXX 7 281 65536 8 7XXXXX 6 5057 65536 8 6XXXXX 6 3929 65536 8 6XXXXX 6 5377 65536 8 6XXXXX 6 5657 65536 8 6XXXXX 7 3201 65536 8 7
#3; Sat, 23 Feb 2008 13:44:00 GMT
- I'm not a TOAD user but is there a tablespace almost 100% full?
What are the default en temporary tablespace names from the user (aka schema) you are using during the action you are doing.
select default_tablespace, temporary_tablespacefrom dba_userswhere username = 'XXXXX';
or select default_tablespace, temporary_tablespacefrom all_userswhere username = 'XXXXX';
DO you have the PRIVILIGEs to create the objects in that tablespace?
Is it possible to clean the recyclebin?
If so as a normal user you could do:
purge recyclebin;
as the sys user (connect sys as sysdba)
purge dba_recyclebin;
or be more specific by
purge tablespace XXXXX;
or purge tablespace XXXXX user XXX;
Message was edited by:
mgralike
#4; Sat, 23 Feb 2008 13:45:00 GMT
- Ok, here you go. This is a developing instance, and for the time being, I am the only one managing it.
U:\>sqlplus sys as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 21 16:31:03 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to:Oracle Database 10g Release 10.2.0.1.0 - Production SQL> purge dba_recyclebin;
DBA Recyclebin purged. SQL> purge tablespace XXXXX;
Tablespace purged. SQL> set lines 399SQL> set pages 300SQL> select * from dba_tablespaces;
SQL> set lines 600SQL> / TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NOUNDOTBS1 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NOSYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NOTEMP 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NOUSERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NOEXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NOXXXXX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO 7 rows selected. SQL> SELECT * 2 FROM dba_tablespace_usage_metrics order by used_percent desc;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT-- -- -- --XXXXX 17904 19200 93.25SYSTEM 68512 4194302 1.63345415SYSAUX 53984 4194302 1.28707947EXAMPLE 9896 4194302 .235939138USERS 1888 4194302 .045013449TEMP 1408 4194302 .033569352UNDOTBS1 160 4194302 .003814699 7 rows selected. SQL>
#5; Sat, 23 Feb 2008 13:46:00 GMT
- Marco,
This worked for me. Now , I will run my program and see what happens.
SQL> ALTER DATABASE 2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\XXXXX.DBF' 3 RESIZE 256M;
Database altered. SQL> select * from dba_tablespace_usage_metrics order by used_percent desc;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT-- -- -- --XXXXX 17904 45568 39.2907303SYSTEM 68512 4194302 1.63345415SYSAUX 53984 4194302 1.28707947EXAMPLE 9896 4194302 .235939138USERS 1888 4194302 .045013449TEMP 1408 4194302 .033569352UNDOTBS1 160 4194302 .003814699 7 rows selected. SQL>
#6; Sat, 23 Feb 2008 13:47:00 GMT
- Before you did anything, you had 10Mb free on tablespace XXXXX. Anyway if you re-read it said ALTER DATABASE not ALTER TABLESPACE.
The datafile has to be extended and as a result the tablespace will become bigger, not the other way around. Tablespaces (from a database viewpoint) are a logical entity, a datafile is a fysical entity. We need fysical stuff here ;-)
#7; Sat, 23 Feb 2008 13:48:00 GMT
- OK, I see that I was too late. Your have seen the mix up.#8; Sat, 23 Feb 2008 13:49:00 GMT
- Hmmm, thought I was really getting old now (didn't know dba_tablespace_usage_metrics). Which wasn't handy refering to anyhow, we were not only interested in %USED and because I don't know for certain what you tablespace / database block_size is if you hadn't given also the dba_tablespace output.
The following blog gives a description of the view used: http://www.edhanced.com/ask-mred/?q=node/view/176
That advice mentioned there sounds very valid, I hope you have read it.
Message was edited by:
mgralike
#9; Sat, 23 Feb 2008 13:50:00 GMT
- Marco,
Thank you. I will read the post.
btw, I got this new error.
begin*ERROR at line 1:ORA-04030: out of process memory when trying to allocate 1016 bytes (qmxlu subheap,qmemNextBuf:alloc)ORA-06512: at "XXYYYYY.EDI", line 1804ORA-06512: at line 2
Also, I was tracing this session. Need to tkprof it to see what is happening.
#10; Sat, 23 Feb 2008 13:51:00 GMT
- Looks like you maybe have hit a bug and will have to create a tar with Oracle support if the solution given in the URL below doesn't help.
See the following forum entry: http://forums.oracle.com/forums/message.jspa?messageID=885836
Found some related bugs on Metalink, if it is a bug... You could ask Mark for extra help. None of the related bugs mentioned on Metalink are really Windows related (yet).
Message was edited by:
mgralike
#11; Sat, 23 Feb 2008 13:52:00 GMT
- For you to run out of memory either one of three things has happened
1. You are processing a very large XML document
2. Your XSLT is somehow going into an endless loop
3. There is a bug in our XSLT engine...
Can you post the XSLT and Instance document. ?
If the Instance document schema based or non-schema-based ?. It it's schema based can you also post the XML Schema
Have you tried applying the stylesheet to the instance document using some other XSLT engine, eg XMLSpy. Does it generate the correct results.
#12; Sat, 23 Feb 2008 13:53:00 GMT
- Mark,
I am sorry if I misled you, but, I wasn't using a XSLT transformation. I was actually using just DBMS_XMLDOM and it's setnodevalue procedure among others to update an element with another one.
Anyway, the instance document is NOT schema based.
And it worked when I started with smaller sized xml's but, this particular XML was around 1.5 MB. I will post the code tomorrow.
Rahul.
#13; Sat, 23 Feb 2008 13:54:00 GMT
- I faced with the same problem. Rahul, have you solved the out of process memory error on dbms_xmldom?#14; Sat, 23 Feb 2008 13:55:00 GMT