Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1693
  • Last Modified:

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
0
mjimison1956
Asked:
mjimison1956
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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
 
mjimison1956Author Commented:
Will recreating the table require a change to the application?  I can not make any changes to the application code.

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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
 
KICUSekCommented:
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now