Improve company productivity with a Business Account.Sign Up

x
?
Solved

Shrink Oracle tables with LONG datatypes

Posted on 2008-06-09
9
Medium Priority
?
1,776 Views
Last Modified: 2013-12-19
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
Comment
Question by:mjimison1956
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 36

Expert Comment

by:johnsone
ID: 21744977
With a LONG, I beilieve the only option is exp/imp.
0
 
LVL 27

Expert Comment

by:Sujith
ID: 21749349
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
 

Author Comment

by:mjimison1956
ID: 21749900
Will recreating the table require a change to the application?  I can not make any changes to the application code.

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 36

Expert Comment

by:johnsone
ID: 21759033
Changing from LONG to LOB will most likely require code changes in the applications.
0
 

Author Comment

by:mjimison1956
ID: 21759090
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
 
LVL 4

Accepted Solution

by:
KICUSek earned 2000 total points
ID: 21760667
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
 

Author Comment

by:mjimison1956
ID: 21760710
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
 
LVL 4

Expert Comment

by:KICUSek
ID: 21760784
So i think step 1 could be beneficial.
Updates are performed on long columns or only on rest of them?
0
 

Author Comment

by:mjimison1956
ID: 21760937
On all columns.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question