1) no, that would be not a good idea.  You want a couple of chunks in an extent -- the rule would be "make the extent size a MULTIPLE of the chunk size".

An extent/ chunk would be a little over zealous, you want N chunks / extent.  You get to pick N -- it depends on the ultimate sum of the sizes of the lobs you'll be storing -- again, shooting for 1000 or less extents 


2) you cannot control it (technically).  The name should have no effect.  The following is not documented but appears to work (but could stop working unpredicably!)

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE T (X NUMBER, Y CLOB)
  2  LOB (Y) STORE AS
  3  ( INDEX foo )
  4  /

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE T2 (X NUMBER, Y CLOB)
  2  /

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> select index_name, index_type, table_name from user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
FOO                            LOB                         T
SYS_IL0000030677C00002$$       LOB                         T2

ops$tkyte@ORA920.US.ORACLE.COM> 

Use at your own risk for that reason.  It might be best to just leave it named by the system.  It is not like you can alter it or do anything to it. 

Nope.

backup -- it is just data, in the database. not much different from a "date"

export/import -- not with blobs (although you'll find imp does it a row at at time - could be a bit "slow")


you'll have to make decisions about

o log / nolog
o cache / nocache
o chunksize

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm

yes, a blob would be the correct type. depending on the format of the audio, 

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a88786/toc.htm <code>

might be appropriate as well.

in 9iR1, lobs could not be in ASSM tablespaces. in current releases (9ir2 and 10gr1) they can be.

it is allowed, it is neither "always recommended or always not recommended". Anything that starts with 'A' follows an 80/20 or maybe 95/5 rule -- it works in most cases better than good enough, but there will be times, certain use cases where it doesn't work as well as the old fashioned/manual way.


I would (do) use autoallocate for LMT's most of the time now myself, yes. uniform extents allow you to more easily monitor "hows this growing over time" (predicable) and "do i have enough room for the next extent" (same size, autoallocate does change as the object gets larger)

blob instead of long?

nope.

CLOB instead of long -- yes.


1) use a clob
2) use a clob and to_lob(long_column) will work.

clob is the only type to use.

that doesn't even begin to make sense to me.

I would suggest continue working with support to get the entire problem resolved.

you cannot as far as I know since the browser will not share that information with us (eg: I do not know of javascript that you could write that can query the file system and ask "what size is the file" before you start uploading)

this is something you would necessarily have to control on the client side.

define contention for me here and tell us how you measured it.

define how the lobs are stored (cached, not cached).

define your file system too - are you really used dedicated direct attach storage, or is there some raid level involved here.

256k extents seems a tad small for your application doesn't it?

so every read and write will be a physical IO, users will have to wait for all writes to fully complete as they write the data.

If you have lots of physical disks, you already have striped the data - that single file is over MANY physical disks.


It is not queueing on eachother (they are not queued for eachother in the database), they are queued at the SAN waiting for their writes to complete.


Have you considered cached lobs, which would allow you to write the lob into the buffer cache and have dbwr write them out later?

do you have access to my last book "Expert Oracle: Database Architecture" - I covered lobs in fairly great detail there, going through the cache, cache reads, nocache, their structures and so on?

... requests for sequence numbers for the bills, ...


ahhhhh run away, run away !!! "gap free sequences" :(

absolutely.

where else would you store a blob - blobs only exist in databases.

files live outside of databases.

So, I suppose you really mean "files versus blobs"

To me, it is really "data", period.


data in a database is

o secured
o backed up
o recoverable
o audited
o accessible

files in a file system are not.

It comes down to that. Yes, I would strongly recommend blobs over files for storing data you actually care about.

You would use Oracle Text, yes.

What "operating system tool" are you referring to. I am aware of various utilies like "grep" - but they won't be of much use against a PDF.

I believe you'll find the flexibility and feature set available in the database to be slightly "larger" than grep.

Look at the advanced search tab on this site for example. Easy in SQL, not so easy with grep.

Oracle will preserve "pctversion" (or use RETENTION) of the old ones for read consistency (LOBs don't do UNDO like regular data, they are versioned in the lob tablespace).

So, if you just "insert" and later "reset" (eg: updates don't really happen), you can set pctversion very low in order to maximize your ability to "reclaim" space as soon as possible.

But yes, space should be reused in the lob segment and lob index segments.


<quote src=Expert Oracle Database Architecture>

PCTVERSION Clause

The CREATE TABLE statement returned from DBMS_METADATA previously included the following:

LOB ("TXT") STORE AS ( … PCTVERSION 10 … )

This is used to control the read consistency of the LOB. In previous chapters, we’ve discussed read consistency, multi-versioning, and the role that undo plays in that. Well, when it comes to LOBs, the way read consistency is implemented changes. The LOBSEGMENT does not use undo to record its changes; rather, it versions the information directly in the LOBSEGMENT itself. The LOBINDEX generates undo just as any other segment would, but the LOBSEGMENT does not. Instead, when you modify a LOB, Oracle allocates a new CHUNK and leaves the old CHUNK in place. If you roll back your transaction, the changes to the LOB index are rolled back and the index will point to the old CHUNK again. So the undo maintenance is performed right in the LOB segment itself. As you modify the data, the old data is left in place and new data created. 

This comes into play for reading the LOB data as well. LOBs are read consistent, just as all other segments are. If you retrieve a LOB locator at 9:00 am, the LOB data you retrieve from it will be “as of 9:00 am.” Just like if you open a cursor (a resultset) at 9:00 am, the rows it produces will be as of that point in time. Even if someone else comes along and modifies the LOB data and commits (or not), your LOB locator will be “as of 9:00 am,” just like your resultset would be. Here, Oracle uses the LOBSEGMENT along with the read-consistent view of the LOBNIDEX to undo the changes to the LOB, to present you with the LOB data as it existed when you retrieved the LOB location. It does not use the undo information for the LOBSEGMENT, since none was generated for the LOBSEGMENT itself.

We can see that LOBs are read consistent easily. Consider this small table with an out-of-line LOB (it is stored in the LOBSEGMENT):

ops$tkyte@ORA10G> create table t
  2  ( id int   primary key,
  3    txt      clob
  4  )
  5  lob( txt) store as ( disable storage in row )
  6  /
Table created.
 
ops$tkyte@ORA10G> insert into t values ( 1, 'hello world' );
1 row created.
 
ops$tkyte@ORA10G> commit;
Commit complete.  

If we fetch out the LOB locator and open a cursor on this table as follows:

ops$tkyte@ORA10G> declare
  2          l_clob  clob;
  3
  4          cursor c is select id from t;
  5          l_id    number;
  6  begin
  7          select txt into l_clob from t;
  8          open c;

and then we modify that row and commit:

  9
 10          update t set id = 2, txt = 'Goodbye';
 11          commit;
 12

we’ll see upon working with the LOB locator and opened cursor that the data is presented “as of the point in time we retrieved or opened them”:

 13          dbms_output.put_line( dbms_lob.substr( l_clob, 100, 1 ) );
 14          fetch c into l_id;
 15          dbms_output.put_line( 'id = ' || l_id );
 16          close c;
 17  end;
 18  /

hello world
id = 1
 
PL/SQL procedure successfully completed.

But the data is most certainly updated/modified in the database:

ops$tkyte@ORA10G> select * from t;
 
        ID TXT
---------- ---------------
         2 Goodbye

The read-consistent images for the cursor C came from the undo segments, whereas the read-consistent images for the LOB came from the LOB segment itself.
So, that gives us a reason to be concerned: if the undo segments are not used to store rollback for LOBs and LOBs support read consistency, how can we prevent the dreaded ORA-1555: snapshot too old error from occurring? And, as important, how do we control the amount of space used by these old versions? That is where PCTVERSION comes into play.

PCTVERSION controls the percentage of allocated (used by LOBs at some point and blocks under the LOBSEGMENT’s HWM) LOB space that should be used for versioning of LOB data. The default of 10 percent is adequate for many uses since many times you only ever INSERT and retrieve LOBs (updating of LOBs is typically not done; LOBs tend to be inserted once and retrieved many times). Therefore, not much space, if any, needs to be set aside for LOB versioning.

However, if you have an application that does modify the LOBs frequently, the default of 10 percent may be too small if you frequently read LOBs at the same time some other session is modifying them. If you hit an ORA-22924 while processing a LOB, the solution is not to increase the size of your undo tablespace, or increase the undo retention, or add more RBS space if you are using manual undo management. Rather you should use the following:

ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );

and increase the amount of space to be used in that LOBSEGMENT for versioning of data.

RETENTION Clause

This is a mutually exclusive alternative to the PCTVERSION clause, and it is valid when you are using automatic undo management in the database. Rather than reserving some percentage of space in the LOBSEGMENT to version LOBs, the RETENTION clause uses the same time-based mechanism to retain data. The database would have the parameter UNDO_RETENTION set to specify how long to retain undo information for consistent reads. This parameter would also apply to LOB data in this case.

It should be noted that you cannot specify the retention time using this clause; rather, it is inherited from the database setting of UNDO_RETENTION itself.
</quote> 

you break things up - for most people the unit of backup and recovery is historically "a tablespace".

If you have two large tablespaces
versus
one huge one...

you would have to decompress the data, yes.

write your own function.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5533095920114 <code>

never tried it with pdfs - is it getting filtered, when you query the dictionary tables text fills in with the keywords - does it look reasonably filled in?

I've never done the custom function with pdfs.

I'll have to take a look at it, but probably not until next week.

not sure that you can - the filter that can read pdf, doc, ppt, etc isn't expecting compressed data.

I thought about that - but - I'm assuming plsql and the use of utl_compress here, could be really messy.

But thanks for the note - if they want to experiment - they can.

you don't say what is in the blobs. does it need to be filtered.

And if it is encrypted, you'll really have to tell us the point of "search"? What does it meant to search an encrypted blobs.

Far far too many people (meaning more than zero) equate ENCRYPTION with ACCESS CONTROL.

encryption is good for protecting your data from theft. when someone steals your backup.

access control is *access control*. So, what are you using encryption here for?

tablespaces are all about "administrative ease of use". They are not about "performance" (all tablespaces could be on same disk, we can stripe disk...)

The benefits you would be looking for would be "it makes MY life better to put them into a separate tablespace".

So, would it make your life better?

<quote src=Expert Oracle Database Architecture>

PCTVERSION Clause
\
The CREATE TABLE statement returned from DBMS_METADATA previously included the following:

LOB ("TXT") STORE AS ( ¿ PCTVERSION 10 ¿ )

This is used to control the read consistency of the LOB. In previous chapters, we¿ve discussed read consistency, multi-versioning, and the role that undo plays in that. Well, when it comes to LOBs, the way read consistency is implemented changes. The LOBSEGMENT does not use undo to record its changes; rather, it versions the information directly in the LOBSEGMENT itself. The LOBINDEX generates undo just as any other segment would, but the LOBSEGMENT does not. Instead, when you modify a LOB, Oracle allocates a new CHUNK and leaves the old CHUNK in place. If you roll back your transaction, the changes to the LOB index are rolled back and the index will point to the old CHUNK again. So the undo maintenance is performed right in the LOB segment itself. As you modify the data, the old data is left in place and new data created. 
This comes into play for reading the LOB data as well. LOBs are read consistent, just as all other segments are. If you retrieve a LOB locator at 9:00 am, the LOB data you retrieve from it will be ¿as of 9:00 am.¿ Just like if you open a cursor (a resultset) at 9:00 am, the rows it produces will be as of that point in time. Even if someone else comes along and modifies the LOB data and commits (or not), your LOB locator will be ¿as of 9:00 am,¿ just like your resultset would be. Here, Oracle uses the LOBSEGMENT along with the read-consistent view of the LOBNIDEX to undo the changes to the LOB, to present you with the LOB data as it existed when you retrieved the LOB location. It does not use the undo information for the LOBSEGMENT, since none was generated for the LOBSEGMENT itself.
We can see that LOBs are read consistent easily. Consider this small table with an out-of-line LOB (it is stored in the LOBSEGMENT):
ops$tkyte@ORA10G> create table t
  2  ( id int   primary key,
  3    txt      clob
  4  )
  5  lob( txt) store as ( disable storage in row )
  6  /
Table created.
 
ops$tkyte@ORA10G> insert into t values ( 1, 'hello world' );
1 row created.
 
ops$tkyte@ORA10G> commit;
Commit complete.  
If we fetch out the LOB locator and open a cursor on this table as follows:
ops$tkyte@ORA10G> declare
  2          l_clob  clob;
  3
  4          cursor c is select id from t;
  5          l_id    number;
  6  begin
  7          select txt into l_clob from t;
  8          open c;
and then we modify that row and commit:
  9
 10          update t set id = 2, txt = 'Goodbye';
 11          commit;
 12
we¿ll see upon working with the LOB locator and opened cursor that the data is presented ¿as of the point in time we retrieved or opened them¿:
 13          dbms_output.put_line( dbms_lob.substr( l_clob, 100, 1 ) );
 14          fetch c into l_id;
 15          dbms_output.put_line( 'id = ' || l_id );
 16          close c;
 17  end;
 18  /
hello world
id = 1
 
PL/SQL procedure successfully completed.
But the data is most certainly updated/modified in the database:
ops$tkyte@ORA10G> select * from t;
 
        ID TXT
---------- ---------------
         2 Goodbye
The read-consistent images for the cursor C came from the undo segments, whereas the read-consistent images for the LOB came from the LOB segment itself.
So, that gives us a reason to be concerned: if the undo segments are not used to store rollback for LOBs and LOBs support read consistency, how can we prevent the dreaded ORA-1555: snapshot too old error from occurring? And, as important, how do we control the amount of space used by these old versions? That is where PCTVERSION comes into play.
PCTVERSION controls the percentage of allocated (used by LOBs at some point and blocks under the LOBSEGMENT¿s HWM) LOB space that should be used for versioning of LOB data. The default of 10 percent is adequate for many uses since many times you only ever INSERT and retrieve LOBs (updating of LOBs is typically not done; LOBs tend to be inserted once and retrieved many times). Therefore, not much space, if any, needs to be set aside for LOB versioning.
However, if you have an application that does modify the LOBs frequently, the default of 10 percent may be too small if you frequently read LOBs at the same time some other session is modifying them. If you hit an ORA-22924 while processing a LOB, the solution is not to increase the size of your undo tablespace, or increase the undo retention, or add more RBS space if you are using manual undo management. Rather you should use the following:
ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );
and increase the amount of space to be used in that LOBSEGMENT for versioning of data.

RETENTION Clause

This is a mutually exclusive alternative to the PCTVERSION clause, and it is valid when you are using automatic undo management in the database. Rather than reserving some percentage of space in the LOBSEGMENT to version LOBs, the RETENTION clause uses the same time-based mechanism to retain data. The database would have the parameter UNDO_RETENTION set to specify how long to retain undo information for consistent reads. This parameter would also apply to LOB data in this case.
It should be noted that you cannot specify the retention time using this clause; rather, it is inherited from the database setting of UNDO_RETENTION itself.

</quote>

and what happened if you left the dbms_output's in. Why do you think they have any relation to the issue at all?


All this means is the second time around, the read consistent image we needed was still in the lob segment - you had made it grow enough with your prior update activity.

After looking at this closer, it is obvious.

If you don't reference the clob, why would it throw an ora-1555? You only referenced the clob (actually tried to access the data it pointed to) when you tried to dbms_output it. If you didn't dbms_output it, you didn't reference it, it would never throw an ora-1555.

You have to reference it, it doesn't take dbms_output.

Your code on line 25 is in error by the way, you already fetched it, you are forgetting to check "c_reader%found" and do proper error handling.

This is all you need to see the 1555.

ops$tkyte%ORA11GR2> DECLARE
  2      l_txt CLOB;
  3      l_data long;
  4  
  5      CURSOR c_reader IS SELECT txt FROM t WHERE id = 10;
  6  BEGIN
  7      OPEN c_reader;
  8      FETCH c_reader INTO l_txt;
  9      CLOSE c_reader;
 10  
 11      UPDATE t SET    txt = LOWER(txt) WHERE  id = 10;
 12      COMMIT;
 13  
 14      dbms_lock.sleep(20);
 15  
 16      UPDATE t SET    txt = LOWER(txt) WHERE  id = 10;
 17      COMMIT;
 18  
 19      l_data := l_txt;
 20  END;
 21  /
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
ORA-06512: at line 19

The FETCH just gets the lob locator, it doesn't dereference it, it doesn't actually touch the lob segment at all.


LONG is a varchar2(32765) in plsql - it is a string.
CLOB is just a pointer


if you assign a clob to a string, it is like this:


str := TO_CHAR(clob);


it converts the clob into a string, in order to do that, it has to actually GET the data.

If you assign a clob to a clob, that is an "fast" thing, you are just assigning a pointer to a pointer. We don't have to copy anything else.

It reserves about 10% of the allocated space for 'undo' - however, it is not a LIMIT on the maximum amount of undo. It can save as much as it wants.

There is also the additional fact that commits in plsql are asynchronous by default - you might not be able to emulated everything you want in a single plsql block like that.

pctversion saves about that percentage for undo from the total allocated space. If you have pctversion 10, it'll save about 10% of the allocated space for previous versions.

About 10%
Not precisely 10%

and it is under no obligation to immediately expire undo at any given point in time.

That you can sometimes hit it (and sometimes not) at precisely the point you think you should goes towards that "about X%"

commits in plsql do not wait for lgwr to finish writing to the redo log, they do not wait for the commit to "finish", they just post lgwr and ask him to commit.

therefore, your commits may not yet be done in your example until the plsql block completes.

for example:

begin
   for i in 1 .. 100
   loop
      insert into t values ( i );
      commit;  -- async, does not wait for commit to finish
   end loop;

   << IMPLICIT WAIT FOR LAST COMMIT TO ACTUALLY FINISH >>
end;

is how plsql would work under the covers - only when the block of code submitted by the client application is finished does plsql wait for the commits it performed to be "done".

So, the commits in the above example - which in theory would/could release the undo - might not yet be completed until the example is basically "done" already.

My understanding is that
VARCHAR2 is more efficient in terms of storage & retrieval.

that doesn't matter - you pick a data type based on your storage needs, nothing else.

You use dates for dates
You use numbers for numbers
You use strings for strings
You use blobs or raw for binary data


1) numerous. character set translation is the first one. Unless every client that ever connects to your database uses the same exact character set as the database uses - character set translation will take place, you'll scramble your encrypted data like an egg. It will effectively corrupt it.

even if you use the same - you will still have character set issues when you access this data. The data in the column will NOT conform to the rules of the character set you are using.

also, you have to use a clob, a clob is not a varchar2 - so it is not even relevant to thinks about varchar2.

and lastly, a varchar2 is not "more efficient in terms of storage and retrieval" in general. If you have a date, a number - it would be horribly inefficient to store them in a varchar2. If you have data that exceeds 4000 bytes, it would be pretty much not possible to store it in a varchar2. A varchar2 is used when you have text data of 4000 bytes or less - for nothing else.


2) we already inline data like that - in both a blob and a clob. Just use a blob. (raw and blob are 'faster' than varchar2 and clob by the way - they are the 'fastest' types, we don't have to do anything to them, just store them)

3) blob is the correct approach, yes.