Solved

freeing up table

Posted on 2011-02-15
17
429 Views
Last Modified: 2012-05-11
We are trying to free up some space in our old database, there is one table(Table_Test)  which is using most of the space. How can we keep the structure of the table and all its indexes intact and just delete some data (retaining only latest 2 months data). We still use this database and its tables for weekly loads and append it to the new database.
The indexes of Table_Test is bigger than the table itself..
Please let me know your comments and suggestions

Thanks
0
Comment
Question by:D-pk
  • 8
  • 5
  • 2
  • +2
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34903050
To remove data:  delete from table_test where some_date_column < add_months(sysdate,-2);
Þ

To free up allocated extents, look up coalesce.

To free up disk space from datafile size is more difficult.  How far do you want to go?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34903061
Apologies.  It's not coalesce.  It's deallocate unused.


http://www.dba-oracle.com/t_coalesce_deallocate_unused_space.htm
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 167 total points
ID: 34903284
Hi,

You can delete the data which are older than two months then do an defragmentation activity on the table and rebuild the indexes. By doing this the tablespace space consumption by the table and the index will be reduced highly.

To defrag the table use.

SQL> alter table table_test move tablespace tablespace_name;

Once the move activity is completed then rebuild the indexes.

SQL>  alter index index_name rebuild ;

-- you can also use rebuild online if the index is currently in use.

Once done you can check the tablespace usgae for these segments, it will get reduced.

Also collect the statistics once completed, using dbms_stats.gather_table_stats

Thanks.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34903371
>> alter table table_test move tablespace tablespace_name;


I don't believe this frees up space by default.  I think you need to specify new storage parameters to actually reduce usage.
0
 
LVL 5

Expert Comment

by:manzoor_dba
ID: 34903578
Hi,

Yes... it will free up the stoage space..

for eg.. consider i have a table with 5 million rows which occupies 30G of tablespace space. Now i have deleted 3 million rows in that table..  i need only 12G of tablespace inorder to store the remaining 2 millon rows.. so i will execute the command alter table ... move inorder to move the table to the new location in the same tablespace or i can move it to some different tablespace.. while moving it will take up only the used data blocks to the new location...  once it is completed the segment size will shows as only 12G..

Thanks..
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34906356
if you are using 10g and above you could use

alter table XXX enable row movement ;
alter table shrink space compact;
then you can reorg all your indices.

if your using 9i and below then the other posts are good for you.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34907074
>>Yes... it will free up the stoage space..

OK, it should shrink it if possible but I believe down to a minimum of the initial extent size.  If the table has an initial extent of 20G, the 'new' table will not be 12G.
0
 

Author Comment

by:D-pk
ID: 34910123
Thanks everyone.. one more question,
If I try the techniques above will i be able to revert it to the existing structure? the data is not important for me. I just want to make sure if something goes wrong, and  if I just take the backup of the table script will I be able to go back to the existing version.. again I am not worried about the data but the table structure because all the new data are loaded into this table weekly..
Thoughts?
Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34910197
I believe move will invalidate any indexes on the table.  
0
 

Author Comment

by:D-pk
ID: 34910212
any workaround?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34910253
From my first post: http:#a34903050, how far you you want to free up the space?

deallocate unused will release the space in the tablespace to be used by other objects.  You need to shrink the datafile to free up filesystem space.

What do you mean by 'free up'?
0
 

Author Comment

by:D-pk
ID: 34910347
I am very new to this, so please excuse my jargon...
the database I am talking about is very small and its filling up fast. So the goal is we are moving it to a bigger DB (which we already did). but still we are using the old database as a staging table to load new data and move it to the new database incrementally. And we will be removing the data from the old database by just keeping 2 months old data.
Since we are piling up more data to the old database now without deleting the transformation takes long time.
Does this makes sense?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 333 total points
ID: 34910863
>>Does this makes sense?

A little.  Let's see if this does:  A high level view of how Oracle works.

Oracle's smallest unit of allocation is a block.  Many blocks make up an Extent.  Extents are 'assigned/allocated' to an object (a table/index/???).

A tablespace can have one or more datafiles.  Each datafile is chopped up into contiguous blocks for each extent.  If a table needs more room to store data, it allocates a new extent.  If there is no more free/unallocated extents and a datafiles is set to auto-extend, it grabs more diskspace from the filesystem and allocates the extent to the table.

Now:  You delete rows from a table, no extents are removed from the table.  They are just marked as 'usable'.

If you are going to keep loading/deleting from the same table, do nothing to 'free' up anything.  Allocating extents is pretty expensive and autoextending a datafile is even more expensive.

Just let the 'new' rows use the same old allocated extents from the 'old' rows.
0
 

Author Comment

by:D-pk
ID: 34913664
I ve heard ppl talk about purging. What exactly is purging process and its consequences in oracle perspective...
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 34913732
As for your table script question, you can use this in sql plus to extract the table structure:

select dbms_metadata.get_ddl('TABLE','<tablename>','<schema>') from dual;

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
ID: 34915772
>>I ve heard ppl talk about purging.

That can mean many things.  You need to ask the people using that term what they meant by it.

Without knowing the context it was used and from you asking this question:   I would "guess" removing necessary data from a table.

If you can define that term for me, I'll attempt to answer from an Oracle perspective.
0
 

Author Closing Comment

by:D-pk
ID: 34919343
Thanks everyone for your inputs...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now