| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DWH loading : Do you use 2 types of tablespaces ?
Philippe
Let me make sure that I understand your question. You have a tablespace consisting of a single data file. You loaded one table in this tablespace. Then you altered that table to move it within the same tablespace. Now you want to shrink the datafile.
The problem is that the shrink datafile command can only release unused blocks from the END of the datafile. Otherwise it would have to move existing blocks that are in use so that it could free up space. In your situation, you probably have used blocks at the end of the datafile, and a lot of unused blocks before those used blocks. That is why you can't shrink your datafile. As Darrell points out, if you execute another move, that will probably cause the table to move back to the first blocks, which will release the end blocks, so you will be able to resize the datafile. However, your original question implies that you will be doing this on a regular basis, so I would say that yes, in the future using two separate tablespaces will enable you to avoid both unused space and additional data movement.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On
Behalf Of NGUYEN Philippe (Cetelem)
Sent: Tuesday, June 01, 2004 2:15 AM
To: 'oracle-l_at_freelists.org'
Subject: DWH loading : Do you use 2 types of tablespaces ?
Oracle 9R2,
Tablepsace LMT / extent uniform size
Hi gurus!
after loading my table with an ETL my my_big_table took 22Go.
after a "alter table big_table move " --> move in the same tablespace, it
decreased to 10 Go.
So, now I want to retrieve the unused space from my tablespace again but
reducing datafiles give me an ORA-03297 (files contains used data beyond
requested RESIZE value).
I guess alter 'table my_big_table move tablespace an_other_tablespace' would solve the problem so my questions are : 1. For a DWH , do you use to use 2 type of tablespace , one for the loading operation and one for de definitive use. 2. Is there other solution avoid the use of two tablespaces ?
Thanks in advance !
Philippe
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Tue Jun 01 2004 - 09:13:48 CDT
-----------------------------------------------------------------
 |
 |