Solved

freeing up table

Posted on 2011-02-15
17
436 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
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: 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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 115
Oracle Verification of DataPump Export and Import 17 60
Oracle Insert not working 10 32
Oracle create type table from existing table%rowtype ? 6 35
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 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