Home » RDBMS Server » Server Administration » ORA-01691: unable to extend lob segment
ORA-01691: unable to extend lob segment [message #208083] Fri, 08 December 2006 01:57 Go to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Using 8.0.5

Dear Friends

I have table

SQL> desc ppimage
 Name                            Null?    Type
 ------------------------------- -------- ----
 MPPNO                                    NUMBER(6)
 MSUBPP                                   VARCHAR2(2)
 ACCES                                    VARCHAR2(10)
 IMAGE                                    BLOB


our user have inserted 494 rows but Now when users try to insert picture in this table show the error

ORA-01691: unable to extend lob segment

I think I have to resize our table space but I dont have any idea how to resize

please help

best regards

Re: ORA-01691: unable to extend lob segment [message #208126 is a reply to message #208083] Fri, 08 December 2006 04:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just add another datafile to the existing tablespace.
sql> alter tablespace SOMETABLESPACE add datafile 'SOMEDATAFILE' size somesize_in_megs m;

or even better,
just extend the datafile (find how big the datafile is first)
sql> alter database datafile 'SOMEDATAFILE' resize somesize_in_megs m;
Re: ORA-01691: unable to extend lob segment [message #208153 is a reply to message #208126] Fri, 08 December 2006 06:25 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
when I am using this command shows the error

ORA-01031: insufficient privileges
Re: ORA-01691: unable to extend lob segment [message #208155 is a reply to message #208153] Fri, 08 December 2006 06:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What command?
Did you login as a DBA?
Re: ORA-01691: unable to extend lob segment [message #208272 is a reply to message #208155] Sat, 09 December 2006 00:14 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
How can I login as DBA

Which login & password I have to use I am using oracle 8.0.5.
Re: ORA-01691: unable to extend lob segment [message #208273 is a reply to message #208272] Sat, 09 December 2006 00:18 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

as dba privs. "DBA" is role or lots of privs system privs.

you can connect like through

1. system user.
2. sys as sysdba user.

i don't know about oracle 8 but try

1.on cmd prompt.

cmd>sqlplus "system/pwd@tns_entry" like "system/oracle@orcl"

or
cmd>sqlplus "sys as sysdba"
or
on sql prompt
sql>conn / as sysdba
or
sql>conn sys as sysdba
provide password.

hope this helps
Taj

let me know what you do.
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/dba.htm#852

[Updated on: Sat, 09 December 2006 00:20]

Report message to a moderator

Re: ORA-01691: unable to extend lob segment [message #208275 is a reply to message #208273] Sat, 09 December 2006 00:30 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
when I try to connect on SQL


SQL> connect / as sysdba;
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> connect sys as sysdba;
ERROR:
ORA-01031: insufficient privileges
Re: ORA-01691: unable to extend lob segment [message #208283 is a reply to message #208275] Sat, 09 December 2006 01:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

are you check ur sqlnet.ora files.


in sqlnet.ora file check.
sqlnet.authentication.services=NTS
if here specify "NONE" then change is to "NTS" then try to connect




regards
Taj
Re: ORA-01691: unable to extend lob segment [message #208286 is a reply to message #208283] Sat, 09 December 2006 01:32 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Deat Taj

I have a table ppimage
SQL> desc ppimage
 Name                            Null?    Type
 ------------------------------- -------- ----
 MPPNO                                    NUMBER(6)
 MSUBPP                                   VARCHAR2(2)
 ACCES                                    VARCHAR2(10)
 IMAGE                                    BLOB


I didnt add any tablespace at the time of creating this table and current oracle user , now we cannot add picture in this table because defualt size increased what can I do??

== Now I have to create tablespace because i didnt create. if yes then how to create? table name is ppimage must be define with tablespace or login user will also invlove in this situation.

[Updated on: Sat, 09 December 2006 01:40]

Report message to a moderator

Re: ORA-01691: unable to extend lob segment [message #208287 is a reply to message #208286] Sat, 09 December 2006 01:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

go through below procedure.

select table_name, tablespace_name 
from dba_tables
where TABLE_NAME = 'PPIIMAGE' or  OWNER = '&owner_of_table_user';

here you get tablespace name where table is created.

now add datafile on above tablespace.

alter tablespace &tbs_name add datafile 'location_of_file' size &put_size_according_you;

hope this helps
Taj

[Updated on: Sat, 09 December 2006 01:41]

Report message to a moderator

Re: ORA-01691: unable to extend lob segment [message #208288 is a reply to message #208287] Sat, 09 December 2006 01:48 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Dear Taj

Please see I did
SQL> desc ppimage
 Name                            Null?    Type
 ------------------------------- -------- ----
 MPPNO                                    NUMBER(6)
 MSUBPP                                   VARCHAR2(2)
 ACCES                                    VARCHAR2(10)
 IMAGE                                    BLOB

SQL> select table_name, tablespace_name 
  2  from dba_tables
  3  where TABLE_NAME = 'PPIIMAGE' or  OWNER = '&owner_of_table_user';
Enter value for owner_of_table_user: scott;
old   3: where TABLE_NAME = 'PPIIMAGE' or  OWNER = '&owner_of_table_user'
new   3: where TABLE_NAME = 'PPIIMAGE' or  OWNER = 'scott;'

no rows selected

Re: ORA-01691: unable to extend lob segment [message #208290 is a reply to message #208288] Sat, 09 December 2006 01:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
user name put in caps lock "SCOTT" not in "scott"


taj
Re: ORA-01691: unable to extend lob segment [message #208303 is a reply to message #208290] Sat, 09 December 2006 04:47 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Hi

Thank you Taj your cooperation

I have done this but same problem still there.

Re: ORA-01691: unable to extend lob segment [message #208306 is a reply to message #208303] Sat, 09 December 2006 05:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi
but same problem still there

which problem ?

post here.
we will try.

Taj
Re: ORA-01691: unable to extend lob segment [message #208307 is a reply to message #208306] Sat, 09 December 2006 05:09 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
our user have inserted 494 pictures but Now when users try to insert picture in this table show the error

FRM-40508 Oracle Error Unable to insert record.


when I see display error in the help in form 6i in our software

ORA-01691: unable to extend lob segment
Re: ORA-01691: unable to extend lob segment [message #208309 is a reply to message #208307] Sat, 09 December 2006 05:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
you post some output here.

select d.tablespace_name, (d.bytes/1024/1024),t.table_name, (t.bytes/1024/1024)
from dba_data_files d, dba_tables t
where d.tablespace_name = t.tablespace_name
and t.owner = '&usrname'
and t.table_name = 'PPIIMAGE';



again ur problem is space bcoz you insert 500 picture on ur database and i will take lot of space. just add more datafile on ur tbs and work on it.


hope this helps
Taj.
Re: ORA-01691: unable to extend lob segment [message #208312 is a reply to message #208309] Sat, 09 December 2006 06:14 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
I have added 3 DBF files with 200m size but still not working

[Updated on: Sat, 09 December 2006 06:15]

Report message to a moderator

Re: ORA-01691: unable to extend lob segment [message #208313 is a reply to message #208312] Sat, 09 December 2006 06:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ok

total ur tablespace size is 600MB. and may be ur picture size more than 600MB. that is why you get above error.
just add more datafile and work...


YOu have any problem with space on ur hard disk. if no plz. add more datafile with sufficient space.

Regards
Taj
Re: ORA-01691: unable to extend lob segment [message #208314 is a reply to message #208313] Sat, 09 December 2006 06:35 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Let me know one thing

ppimage is a table and in this table 494 pictures are saved I dont know how much MB covered.

I addedd 2 datafile (image.dbf,image1.dbf) with this table (ppimage) now oracle will shfit more pictures in these datafile ??

and now I am inserting pictures having 142KB size I think it should be add.
Re: ORA-01691: unable to extend lob segment [message #208315 is a reply to message #208314] Sat, 09 December 2006 06:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Try

SELECT *
      FROM dba_tablespace_usage_metrics order by used_percent desc



http://www.cryer.co.uk/brian/oracle/ORA01691.htm
http://forums.oracle.com/forums/thread.jspa?messageID=1471354

go through above link...

hope this helps
Taj
Re: ORA-01691: unable to extend lob segment [message #208605 is a reply to message #208290] Mon, 11 December 2006 09:06 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
user52 wrote on Sat, 09 December 2006 02:51
hi,
user name put in caps lock "SCOTT" not in "scott"


taj

And don't put a semicolon as part of the name.
Re: ORA-01691: unable to extend lob segment [message #208607 is a reply to message #208605] Mon, 11 December 2006 09:15 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

on which semicolon you are talking here.

reply...
regards
Taj
Re: ORA-01691: unable to extend lob segment [message #208645 is a reply to message #208607] Mon, 11 December 2006 12:21 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Ooops, sorry user52. I thought I quoted the OP message. You'll see that he enter scott; for username, including the colon.
Previous Topic: performace issue with indexing
Next Topic: Ora-29532 java.lang.OutOfMemory
Goto Forum:
  


Current Time: Fri Sep 20 05:38:13 CDT 2024