Shrink Oracle tables with LONG datatypes

What is the preferred method to shrink a table that contains a LONG Datatype?  I can not change the data type due to the third party software application.  I need to find a way to shrink these tables.

Would it benefit to have all tables with LONG's in them in a separate table space.  Tables range in size from 50 rows to 4mm rows.

Version is on Linux
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

KICUSekConnect With a Mentor Commented:
My proposition:
1/ create separate tablespace with possibly large datablocks (16/32kB - depends on platform)
2/ migrate tables to new tablespace (exp/imp or other piece of code)

What operations are most common on this tables? inserts/updates/deletes or just selects?
johnsoneSenior Oracle DBACommented:
With a LONG, I beilieve the only option is exp/imp.
LONG datatype is already depricated by oracle. A better way will be to re-create the tables using LOB data types. LOBs offer a lot of flexibilities and features.
See the illustration below.

SQL> create table t1(id number, val long);
Table created.
SQL> insert into t1 values(10, 'test');
1 row created.
SQL> commit;
Commit complete.
SQL> create table t2 as select id, to_lob(val) val from t1;
Table created.
SQL> desc t2
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------
 ID                                                       NUMBER
 VAL                                                      CLOB
SQL> select * from t2;
        ID VAL
---------- -----------------------------------------------------
        10 test

Open in new window

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

mjimison1956Author Commented:
Will recreating the table require a change to the application?  I can not make any changes to the application code.

johnsoneSenior Oracle DBACommented:
Changing from LONG to LOB will most likely require code changes in the applications.
mjimison1956Author Commented:
Can not change the application due to cost and project.  What a mess.  Performance is terrible on these longs.

Any ideas on how to improve performance?
mjimison1956Author Commented:
The tables are used to set up a data scenario.  For example.  hundreds and thousands of insert, updates on the table, then a select is executed against the newly formatted data, passed to the application server, when the app server is complete a delete of all the newly inserted and updated data is executed.

Non sequential reads, I/O, CPU and performance is a nightmare.  I am constantly rebuilding indexes which also need shrinks.

So i think step 1 could be beneficial.
Updates are performed on long columns or only on rest of them?
mjimison1956Author Commented:
On all columns.  
All Courses

From novice to tech pro — start learning today.