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 10.2.0.2 on Linux
mjimison1956Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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?
0
 
johnsoneSenior Oracle DBACommented:
With a LONG, I beilieve the only option is exp/imp.
0
 
sujith80Commented:
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

0
Never miss a deadline with monday.com

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.

0
 
johnsoneSenior Oracle DBACommented:
Changing from LONG to LOB will most likely require code changes in the applications.
0
 
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?
0
 
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.

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

From novice to tech pro — start learning today.