Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: lob
Compressed LOB–my table got bigger?
We had an interesting question on AskTOM the other day about LOB compression. Someone was very concerned that after applying compression to the LOBS in the column of their table, the table actually got larger! Cue much confusion and panic, but after a little…
LOBs vs Data – when compression occurs
Just a quick tip for SECUREFILE lobs. You may be familiar with basic compression on data in tables. It is a dictionary attribute of the table, which is then taken into account when you perform: a SQL Loader direct load, or an INSERT with…
Datatype conversion laziness … yet another reason
I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! There’s been many blog posts, videos, AskTOM questions etc over the years with numerous examples of how the optimizer gets confused, how the SQL engine gets confused…and…
Those pesky LONG columns
There was a time, many moons ago when CLOB, BLOB and BFILE did not exist as data types. So if you had anything longer than a few kilobytes of data to store, you had to use a LONG or a LONG RAW. But those…
LOBs from afar
This has always been a nuisance. There you are – getting all the bells and whistles with LOBs…until a database link enters the room — — Database: DB11 — SQL> create table t ( id int, c clob ); Table created. SQL> insert into…
LOBs and tiny typos
This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created. Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a…
Securefile in 12c – part 2
In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c. Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile…
Loading LOB from a file
I observed this idiosyncracy recently when loading some lob from external files using PL/SQL: First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size SQL> !echo “This is line 1” > /tmp/lobfile SQL> !echo “This is…
Converting LONG to CLOB
Some folks still are stuck with LONG columns, and are keen to move to LOB. Since version 9, we’ve had a nice facility to do that – just with a simple alter command. You can now simply issue “alter table (longcol CLOB)” to perform…
CLOBs are not just big varchars
We had a question on AskTom the other day, talking about comparing clobs. (Paraphrasing) the question inquired as to why we had a DBMS_LOB.COMPARE function, when you can you just compare clobs directly, using the following example: SQL> create table T ( x clob,…
CLOBS, from 11g to 12c
If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c. Notice the subtle difference: In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default SQL> select…
Continuous Delivery – Moving to SECUREFILE
You’ve been google-ing and you’ve seen articles (for example) like http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html and you’re pretty pumped about using SECUREFILE features. You’ve got lots of existing CLOB data and moving to SECUREFILE is going to make your life much easier. You’re also excited about the fact…
