OraFAQ Forum: Server Administration » Reclaiming Actual Physical Disk Space

Home » RDBMS Server » Server Administration » Reclaiming Actual Physical Disk Space (Oracle 10.2.05)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reclaiming Actual Physical Disk Space [message #521900]

Sat, 03 September 2011 07:50

liverpooltone
Messages: 3
Registered: September 2011

Junior Member

Hi,

I look after a team of DBAs and I have a request to free up space on our very expensive storage system. However the answers on how to do this differ and i'd like to ask for external input

So not being a techincal person I see the world as quite black and white. Meaning that you delete data and you free space but after doing much reading I understand this is not the case, as you essentially create data fragmentation within the datafile resulting in the db having lots more space to write into but not actually freeing space, even if you shrink the file it doesnt free space or do a reorg?

We have as an example a DB with 2 billion rows of data in 1 table, no partioning just one large table.

We have worked out that we can probably delete 1 billion rows or even better only keep a rolling 3 month window of data.

What would be the suggestion on deleting this data and reclaiming the disk space to actually see additional disk space made available at the os level.

So my questions is

How would you suggest deleting the data and reclaiming the space.

Through reading it looks like it might be something like, delete, creating new table space partitions from this data. This in theory would create new a tablespace in newly created data files which would result in the data being reorganised and taking up less physical space and when completed you point to the newly created partitions and drop the old tables.

I'd appreciate peoples input on how they have done this as it must be a common problem that people have created some different solutions. What commands, procedures have been used?

Thanks

[Updated on: Sat, 03 September 2011 09:35] by Moderator

Report message to a moderator

Re: Reclaiming Actual Physical Disk Space [message #521901 is a reply to message #521900]

Sat, 03 September 2011 08:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What would be the suggestion on deleting this data and reclaiming the disk space to actually see additional disk space made available at the os level.

What do you perceive to be the benefit to see more disk space at OS level?

Do you realize that Oracle never directly utilizes OS disk free space?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

disk space is neither created or destroyed. It is fixed & finite.
When data is DELETEd by Oracle, then that space ( as shown by DBA_FREE_SPACE) is now available to be occupied by new data

SQL> desc dba_free_space
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME				    VARCHAR2(30)
 FILE_ID					    NUMBER
 BLOCK_ID					    NUMBER
 BYTES						    NUMBER
 BLOCKS 					    NUMBER
 RELATIVE_FNO					    NUMBER

  1  select tablespace_name, round(sum(bytes/(1024*1024))) from dba_data_files
  2* group by tablespace_name order by 1
SQL> /

TABLESPACE_NAME 	       ROUND(SUM(BYTES/(1024*1024)))
------------------------------ -----------------------------
EXAMPLE 						 101
SYSAUX						       10300
SYSTEM							 740
UNDOTBS1						9130
USERS							1478

post FORMATED result to SQL above so we can see what you start with

[Updated on: Sat, 03 September 2011 09:40]

Report message to a moderator

Re: Reclaiming Actual Physical Disk Space [message #521903 is a reply to message #521900]

Sat, 03 September 2011 09:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Export all what you want to keep.
Drop then all what is inside the database.
Shrink all tablespace files.
Re-import.

"All" means all customer objects not Oracle ones, so keep off SYSTEM and SYSAUX tablespaces.

Regards
Michel

Report message to a moderator

Re: Reclaiming Actual Physical Disk Space [message #521904 is a reply to message #521900]

Sat, 03 September 2011 10:55

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Hi - Michel has given you the most effective technique. It is also a technique that involves massive downtime.
With minimal downtime, you should be able to delete a lot of rows, then free up space within the tablespaces by rebuilding the indexes and shrinking the tables. An alternative technique is to use online table redefinition, but that requires more space while it is in progress.
The hard part comes next, which is resizing the datafiles, downwards.
But you say that you manage a team of DBAs. They can do this. If they don't know how, they aren't DBAs.

Key words: SHRINK SPACE, REBUILD, ONLINE REDEFINITION, RESIZE.

Report message to a moderator

Re: Reclaiming Actual Physical Disk Space [message #521917 is a reply to message #521904]

Sun, 04 September 2011 03:52

liverpooltone
Messages: 3
Registered: September 2011

Junior Member

All,

thanks for your feedback its helping alot.

Truncation, shrink, and rebuild are known to me and massive downtime also scares me.

I like the idea of being able to resize the data files online. The steps in my head would be

1. Delete datab. I assume i should still do this to make step 2b easier.
2. Rebuild (Indexes?) Redefinition/Reorganisation (Tablse spaces)
2a. Rebuild index. To defrag and reduce size but there is still unused data blocks at the end of the index
2b. Redef/reorg. To create a newly orgnised table from the original with only the data you want. Resulting in a brand new table that takes less space.
3. Create new index for new table?
4. Start to use newly created table.

Thanks

Report message to a moderator