FreeLists / oracle-l / RE: DWH loading : Do you use 2 types of tablespaces ?

[DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of NGUYEN Philippe (Cetelem)
Sent: Tuesday, June 01, 2004 2:15 AM
To: 'oracle-l@xxxxxxxxxxxxx'
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 



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------