LOB question?
Can you have db A have a view w/ db link reference to db B blob data type?
I think so, but not 100% sure. The reason I ask is I have a 3rd party
app that wants to move 500gb worth of images to a remote db, but have the current db
reference it as if it was in the same db
All,
I'm being given a requirement to store a BLOB column in the database. I'm
being told that the average size of the file (it's a PDF) is 12,000 K. I'm
assuming that I should store this column in a separate tablespace from the
table data. If I use an LMT tablespace, what should I use for the
the LOB is stored in-line and it goes over 3964 bytes, it is
automatically store out of line in the same tablespace as the table. My
questions are these:
1) With LMTs and a CLOB stored out of line, does one CLOB equal one
extent in the out of line tablespace? If that is the case, we will chew
I am going to import an Oracle 7 database into an Oracle 8.1.7.0.0 instance
that I am going to make. I have 31 tables of which 11 hold LONG and/or LONG
RAW column datatypes. What storage clause should I put on the tablespace to
hold the LOBs most efficiently?
thanks,
David Ehresmann.
--
Plea
content_display(content_display_id,business_id, language_id,
content_type) )
LOB (detail) STORE AS
(TABLESPACE ts_lobs
CHUNK 16K PCTVERSION 10);
As can be seen in the script, the LOB is stored in a separate tablespace.
I am able to access the data in all the columns except the 'detail' colu
content_display(content_display_id,business_id, language_id,
content_type) )
LOB (detail) STORE AS
(TABLESPACE ts_lobs
CHUNK 16K PCTVERSION 10);
As can be seen in the script, the LOB is stored in a separate tablespace.
I am able to access the data in all the columns except the 'detail' colu
oint Road to visitor center, walk to falls.';
END;
This really floored me, because I didn't expect to be able to assign a
value to a CLOB variable without first selecting a LOB locator from
the database. In fact, I can remove the call to EMPTY_CLOB, and the
code still works! My first thought
I am not quite sure that you can reference a LOB in a remote database
through a database link. You are very likely to get the ORA-22992 error
when you attempt to do so.
22992, 0, "cannot use LOB locators selected from remote tables"
// *Cause: A remote LOB column cannot be
Thank you Winnie. Thats exactly what I was looking for!
Gene
>>> [EMAIL PROTECTED] 03/27/01 04:42PM >>>
I am not quite sure that you can reference a LOB in a remote database
through a database link. You are very likely to get the ORA-22992 error
when you attempt to do
ed as normal. If the lob size is > 4k,
does still generate big redo log?
Thanks,
Joan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, Ca
Hi!
On which version you are?
I would create fairly large extents, 64MB for example. LOBs are stored in
chunks anyway, extent size doesn't matter that much. One issue is, if you
create very large extent size, you might waste some space in LOB index which
is a separate, smaller segment (b
Tom
In this case, I would use a uniform extent size -- you know approximately
how large the data is today. Since there is no measureable overhead for
having multiple segments, I would go with something much smaller than a 12MB
extent size -- 512K to 1MB, depending on what your OS read size is.
Ke
list ORACLE-L
Hi!
On which version you are?
I would create fairly large extents, 64MB for example. LOBs are stored in
chunks anyway, extent size doesn't matter that much. One issue is, if you
create very large extent size, you might waste some space in LOB index which
is a separate, smalle
Hi there,
I have been importin 2gig of LOBs and it is extremely slow, is there any way how to speed up this process...
rgds
jbHöstrusk och grå moln - köp en resa till solen på Yahoo! Resor
You won't be storing LOB's, you will be storing LONG's.
Oracle does not automatically convert LONG to LOB for you.
You will have to do that yourself ( check on MetaLink ) after
you import the data.
Assuming that your new database will be using LMT's ( locally
managed tablesp
partitioned table
Indy Johal
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/28/03 06:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: LOB tablespace
You won't be storing
Sorry I press Sent button in mine earlier reply and what I missed is that you need to pre-create the table with LOB column before importing the LONG RAW column table export
Thanks
Indy Johal
Manager, Database Administration
PR Newswire
[EMAIL PROTECTED]
http://www.prnewswire.com
(201) 946-5687
column name given to BLOB column is same. This is export import from 81.7.3 non -partitioned table to 8.1.7.3 partitioned table. Make sure that you have pre-created the table with LOB column before importing the LONG RAW column table export
Indy Johal
Indy Johal
Manager, Database Administration
che, this could kill
your buffer cache, because LOB blocks read are placed to MRU (most recently
used) end of LRU list (at least in 8i I believe it used to be that
way).
* LOGGING - can be disabled when using NOCACHE
(direct writes). When logging is enabled with nocache, an update of data in LO
L PROTECTED]>
cc:
Subject:Re: LOB tablespace
HEre is the full reply as I am looking that mine second reply come before
the first reply and this happend few times today
This is not the case. I had imported a 150 Gig table containing LONG RAW
column to BLOB with simple E
le recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Accessing LOB
Friends,
Find below the script of a table with a CLOB datatype
CREATE TABLE lob_content_display
( content_display_id VARCHAR2(30),
business_id VARCHAR2(30),
language_
Title: LOB Help needed
Hi,
Has anyone any document about implementation of Lobs/BFILES using Oracle Developer 6/6i as front end tool. Manulplating LOBs within the database is not a big problem, but getting them out to display it is what I need. I have found a doc which explains the
Greetings All,
I am using Oracle 81630 on Solaris 7 and I have a table which contains
a
LOB (CLOB actually). The DDL statement is as follows ...
CREATE TABLE B_STATEMENT_TEXT_ORA (stmtnum NUMBER(10) NOT NULL,
statement_content CLOB)
STORAGE (INITIAL 100M NEXT 100M PCTINCREASE 0)
TABLESPACE
a=20Becker?= <[EMAIL PROTECTED]>
Date: Sat, 11 Oct 2003 10:37:58 +0200 (CEST)
Subject: Importing LOB segments
--0-100417243-1065861478=:60738
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi there,
I have been importin 2gig of LOBs and it is extremely slow,
while looking thru the migration guide(for downgrade info in case it
happens),
alter table modify clob;
and supposedly POOF its now a clob.
interesting.
joe
ffset,TRAW);
it is giving error "Invalid LOB locator
specified"
i would help from any of
u.
Thanx in advance
Shishir Kumar
MishraAgni Software (P) Ltd.,Bangalore-560055, India
racle 81630 on Solaris 7 and I have a table which contains a
LOB (CLOB actually). The DDL statement is as follows ...
My question is one related to data access for the table, lobsegment and
lobindex. Would there be any I/O performance benefit in having the
table, lobsegment and lobindex locat
Hi Listers,
I created a table containing a
LOB Column (icon) with the following storage clause :
LOB (ICON)
STORE AS (TABLESPACE QUARKDMS_BLOB
STORAGE(INITIAL 1M NEXT 5M)
CHUNK 4K PCTVERSION 10
NOCACHE LOGGING
INDEX (TABLESPACE QUARKDMS_INDEX
STORAGE (INITIAL 1M NEXT 1M))
DISABLE STORAGE
ging long to lob
while looking thru the migration guide(for downgrade info in case it
happens),
alter table modify clob;
and supposedly POOF its now a clob.
interesting.
joe
onday, August 20, 2001 4:26 PMTo: Multiple recipients of list
ORACLE-LSubject: changing long to lob
while looking thru the migration guide(for downgrade info in case it
happens),
alter table modify clob;
and supposedly POOF its now a clob.
interesting.
joe
In 8i you can use ... (straight from SQL reference manual)
TO_LOB
Purpose Converts LONG or LONG RAW values in the column long_column to LOB
values. You can apply this function only to a LONG or LONG RAW column, and
only in the SELECT list of a subquery in an INSERT statement (see "INSER
Hi!
I am new to working with LOBs. I want to insert data (through SQL or PL/SQL)
into a table that looks like this:
column1 varchar2(100)
column2 BLOB
column3 number
How can I insert data into that table directly?
insert into table (column1, colum2, column3) values ('sample text', ?,
> Hi!
>
> I am new to working with LOBs. I want to insert data (through
> SQL or PL/SQL)
> into a table that looks like this:
>
> column1 varchar2(100)
> column2 BLOB
> column3 number
>
> How can I insert data into that table directly?
>
> insert into table (column1, colum2, column3) va
Hi!
I am new to working with LOBs. I want to insert data (through SQL or PL/SQL)
into a table that looks like this:
column1 varchar2(100)
column2 BLOB
column3 number
How can I insert data into that table directly?
insert into table (column1, colum2, column3) values ('sample text', ?,
Hi all,
we are facing some serious problems with the following case:
we have an application which stores quite a lot of documents to database (average document size is 2mb). Application loads document template from database and when we are having 100-200 hundred users there will be a queue on
I seem to recall from Jonathan Lewis' book that moving a LOB index after
creation was possible in some versions of 8.0, but no longer possible in
8i. Unfortunately, I don't have the book in front of me right now so I
can'
y the
vendor. When I look at at the lobs, the definitions
are mixed. Most are nocache, logging yes. some are
no/no
I don't see much on metalink about this -- just a
couple of generic articles on lob storage.
Should I change the lobs to cache/logging across the
board?
Thanks for any
There is a note in one of the manuals about nologging
lobs (or nocache lob, I forget which) that points out
that the "unrecoverable SCN" for file that holds the
LOB has to be updated in the control file whenever the
LOB is updated.
If you actually have a performance problem becau
TECTED]>
cc:
Subject: Re: [Q] wait time /lob def
There is a note in one of the manuals about nologging
lobs (or nocache lob, I forget which) that points out
that the "unrecoverable SCN" for file that holds the
LOB has to be updated in the control file whenever the
Jonathan,
about which version are you talking here?
> (You do also have the option in more recent versions
> of refining the caching properties so the LOB can be
> readcache only, writecache only or read/write cache
> or nocache, I believe).
I haven't found a way to explicitly
One more similar reason for using different block
sizes for different LOBs is that a LOB item always allocates space in chunks for
it and a chunk can be a multiple of blocksize, not less. Big blocks mean
space wastage for lots of out of line small LOBs, however small blocks mean
performance
re nocache, logging yes. some are
> no/no
>
> I don't see much on metalink about this -- just a
> couple of generic articles on lob storage.
If you are up to attending RMOUG Training days in few weeks, then I'll be
speaking about LOB performance tuning there, you're mo
>
> > I've yet to capture the sid (and therefore track
> back
> > to the table) where the 'direct path write'
> occurs.
> > Definitions for the tables were supplied by the
> > vendor. When I look at at the lobs, the
> definitions
> > are mi
Tanel,
Oracle 9-2 SQL Ref manual. Lob storage clause.
The options for lobs on creation are:
CACHE
NO CACHE
CACHE READS
I don't think the last one appeared until 9.2
I was wrong about caching only writes, though -
one of the joys of trying to quote everything from
memory.
The
It's just one example of my general suggestion that messing about
with block sizes rarely has any direct performance benefit. But
if you can put something out of the way where it can't do so much
damage then the performance of everything else might benefit.
Regards
Jonathan Lewis
http://www.j
Hi Jonathan,
> I don't think the last one appeared until 9.2
> I was wrong about caching only writes, though -
> one of the joys of trying to quote everything from
> memory.
Thank you for this note, I had somehow missed this important improvement.
>
> The CACHE READS opti
Over the holidays, as is the wont of many a DBA, I was doing some work while the
databases were relatively quiet. One thing I did was to move a lob segment to another
tablespace. The lob segment contains employee pictures for our online phonebook.
After the move I looked at 50 of the
" with EMPTY_BLOB()
3. modify & execute the following procedure (copy&paste) from the "Oracle8i
Application Developer's Guide - Large Objects (LOBs) " chapter of the docs.
The procedure reads a BFILE from the directory created in step 1 and copies
its content in the
Hi all,
I know that it is kind of a stupid question. But I've been struggling with
the syntax to move the LOB index to a different tablespace. Would any one
please kindly help me out on this part? Thanks!!
:D
Winnie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
A
Hi Gunnar,
Can you please turn on event# 10046 at level 8 and
trace some of your sessions? You can then run 9i
tkprof on the trace files, to determine the most
expensive SQL statements, from a resource consumption
and wait perspective.
I am sure there is enough expertise on this list to
figure ou
Gunnar Berglund wrote:
>
> Hi all,
>
> we are facing some serious problems with the following case:
>
> we have an application which stores quite a lot of documents to
> database (average document size is 2mb). Application loads document
> template from database and when we are having 100-200 hu
The first time we tried importing our 8.1.5 [OPS] database
into 9.0.1 and 9.2.0.1 with
EXTENT_MANAGEMENT Local for ALL tablespaces
SEGMENT_SPACE_MANAGEMENT Auto
we found that tables with LOB segments could not be created.
This was a 9.0.1 bug which was expected to have been fixed in 9.2
[I
lespace.
In the 9i Application Developer's Guide - Large Objects (LOBs)
Release 2 9.2,
the chapter "What's New with Large Objects (LOBs) ?",
under
"LOB Features Introduced with Oracle 9i, Release 2 (9.2)"
,
under "Removed Restrictions" :
Locally managed tab
cally managed tablespace.
· You
cannot specify this clause for the SYSTEM
tablespace. In the 9i Application Developer's Guide -
Large Objects (LOBs) Release 2 9.2,the chapter "What's New with Large
Objects (LOBs) ?", under"LOB Features Introduced wi
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
in our 9202 RAC we have everything except SYSTEM,UNDO in AUTO and have NO problems at all. We have 7 such instances (all RAC, all 9202 except for on which is 9012). 4 Instances use CLOBs and intermedia extensively, one of these
PROTECTED]]Sent: Thursday, December 19, 2002
10:09 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
As part of a RAC benchmark with 9.2 we had faced severe LOCKING
on setting segment space management AUTO & had to REMOV
Depending on your circumstances, ASS Management
can eliminate severe contention on the freelists / freelist
groups area. However, because Oracle is overgenerous
with its allocation of bitmap blocks (which may turn
out to be in excess of 1% of your database), you
may end up thrashing your system b
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
ASS Management? I'm snickering, I know it's juvenile humor, but... LOL! Let me tell you, my ASS is not on the Free List. And thrashing? I'll leave that one alone!
Sorry everyone. Couldn't resis
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
:-0
Our
ASS managment does a lot of thrashing lately...
It's
close enuf to Friday and a 2 week vacation as well.
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Thursday, Decemb
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Does this 'Ass Management' apply to duhvelopers as much as it applies to
tablespaces ?? Thrashing will be more useful.
ps: Before you say something I have been a developer for about 9 years
(and still consider my s
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Oh
the good old days when at the state
government they decided to call us
Bureau of Application Development And System
Software
Yep,
until the initials became apparent.. I think
it
lasted about 2-4 weeks, and they changed it
It would appear we're looking into yet another hit ratio, namely the ASS
Hit Ratio. Used to be rather high in my younger days.
Mogens
Jonathan Lewis wrote:
Depending on your circumstances, ASS Management
can eliminate severe contention on the freelists / freelist
groups area. However, bec
27;mostly' LOBs?
>
> From: "Jonathan Lewis" <[EMAIL PROTECTED]>
> Date: 2004/01/30 Fri AM 04:09:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: [Q] wait time /lob def
>
>
> Tanel,
>
> Oracle 9-2 SQL Ref manual.
ine which files need to be backed up (since recovery information is
not logged for nologging operations).
Another option would be to use NOCACHE LOGGING operations, that way no
controlfile updates are needed, but I do not recommend it, because NOCACHE
LOGGING LOBs require the writing of the whole
PROTECTED] cc:
ford.EDU>Subject: ALTER TABLE MOVE LOB ... Watch
Out
Sent by:
I have several questions answers for wich I couldn't find in FM (not that I
tried too hard :-) ).
1. If LONG or LOB takes more than 1 block - how part of the last block left
free is used? Can it be used to store part of another LONG or LOB which more
thatn this free peace? I think not bu
Winnie,
According to the SQL Reference for 8.1.6 (Create Table):
LOB_index_clause
This clause is deprecated as of Oracle8i. Oracle generates
an index for each LOB column. Oracle names and manages the
LOB indexes internally.
Although it is still possible for you to specify this
clause, Oracle
Oracle recommends against this.
LOB_index_clause
This clause is deprecated as of Oracle8i. Oracle generates anindex for each LOB
column. The LOB indexes are system named
and system managed, and reside in the same tablespace as the LOB data segments. It is
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Oracle recommends against this.
LOB_index_clause
This clause is deprecated as of Oracle8i. Oracle generates anindex for each
LOB column. The LOB indexes are system na
Hi gurus,
Could you explain that.
Working with tablespace in locally managed, with uniform extends size, we
want to create LOB but we must have the uniform extend size 3 times the
db_block_size, right?
Otherwise we got ORA-03237.
With dictionnary managed tablespace we don't have this pr
NT_MANAGEMENT Local for ALL tablespaces
SEGMENT_SPACE_MANAGEMENT Auto
we found that tables with LOB segments could not be created. This was a 9.0.1 bug
which was expected to have been fixed in 9.2 [I believe it was Bug 1626182, referenced
in Note 159078.1]
However, after going to 9.2.0.2, I WAS
iRAC : Segment_Space_Management AUTO, with
LOB
<>
Bottomline : Either it was a bug or a restriction, it was not
possible in 9.0.1 and supposed to be possible in 9.2.0 ---
definitely
works in 9.2.0.2
Why do you say "In a single instance environment unless you are
doing something out of the ordin
Hi,
I got this error when process blob file:
ORA-01691: unable to extend lob segment ELI.SYS_LOB025110C2$$ by
100358 in tablespace DATA2
I added new data file for DATA2, but still the same error.
Please help!
Thanks,
Jack
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
ist ORACLE-L
> Subject: Storage allocation for LOB and LONG datatypes
>
> I have several questions answers for wich I couldn't find in FM (not that
> I
> tried too hard :-) ). [Kirti] Thanks for being so honest ...
>
> 1. If LONG or LOB takes more than 1 blo
Title: OFFTOPIC RE: Need help in moving LOB index
How far away is the tablespace?
You need to know this, because first you need to compute
"tablespaces/mile" before activating the LOB-Velocity filter.
This is a new 8i feature that some of the list gurus might
be able to hel
Hi
Gurus,
How do I convert a long datatype to clob datatype.
Say Table A
No number
Col1 long
Would like to convert to
Table A
No number
Col1 clob
Alter table A modify (col1 clob);
Gives an error as inco
m extent size of two database blocks, other
objects may
require larger extents than this, either by virtue of how they are built internally
(i.e. an RBS
requires at least four blocks and a LOB at least three) or by virtue of their storage
spe
L PROTECTED]>
cc:
Subject:ORA-01691: unable to extend lob segment
ELI.SYS_LOB025110C000
Hi,
I got this error when process blob file:
ORA-01691: unable to extend lob segment ELI.SYS_LOB025110C2$$ by
100358 in tablespace DATA2
I added new data file for DATA
Jared,
Thanks a lot.
I use 8k block size, so you mean 100358 is block number and I need total
about 781Mb free space?
I have about 300Mb free space for DATA2.
Here is some info about this lob segment:
select segment_name, tablespace_name,segment_type,
bytes,extents,initial_extent, next_extent
t's been set up with uniform size.
Jared
"Liu, Jack" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/22/2003 01:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:
Using the following
procedure, I'm getting a ora-22992 error.
declare locator_var clob; amount_var
number; offset_var number; output_var
varchar2(20);begin amount_var:=16; offset_var:=1; buffer_var:='This
is not text'; select b into locator_var from
lob_test@to_features where a =
1; d
tiple recipients of list ORACLE-L
Sent: Thursday, September 11, 2003 1:14
PM
Subject: converting long to lob - the
easiest and fastest way ?
Hi
Gurus,
How do I convert a long datatype to clob
datatype.
Say Table A
No
number
olumn col1 afterwards and if
needed, rename newcol to col1 using alter table rename column (or whatever the
syntax was).
You might want to do this in batches and commit
meanwhile if your table is big.
Also, if your table is big, then you should test
which mode lob segments are faster for y
]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tanel
Poder
Sent: Thursday, September 11, 2003
4:59 PM
To: Multiple recipients of list
ORACLE-L
Subject: Re: converting long to
lob - the easiest and fastest way ?
Sorry, I didn't notice in the end of your
: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Siddharth Haldankar
Sent: Thursday, September 11, 2003
3:44 PM
To: Multiple recipients of list
ORACLE-L
Subject: converting long to lob -
the easiest and fastest way ?
Hi Gurus,
How do I convert a long
datatype to clob datatype.
Say
OTECTED]
>
>
>
>-Original Message-
>
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Siddharth Haldankar
>
>Sent: Thursday, September 11, 2003
>3:44 PM
>
>To: Multiple recipients of list
>ORACLE-L
>
>Subject: converting long to lob -
>
umns, their datatypes and
>order (note that column names don't matter, at least in 9.2 they
>don't).
>
>Table altered.
>
>SQL>
>SQL> alter table t_tmp
>exchange partition t_tmp_p1 with table t without
>validation;
>
>Using without validation r
Using without validation reduces CPU usage
during exchange operation, because no check is done whether all rows in segment
really belong to specified partition (in this example we have only one partition
for all rows anyway).
If we had any indexes on T table, these
will be marked unusable (wi
PROTECTED] On Behalf Of Tanel
Poder
Sent: Thursday, September 11, 2003
9:40 PM
To: Multiple recipients of list
ORACLE-L
Subject: Re: (long and boring)
converting long to lob - the easiest and fastest way ?
Hm, yep, it seems it works only in
insert select command (I tried an update with subquery
90 matches
Mail list logo
