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

[<ryan.gaffuri@xxxxxxx>]

it depends what you are doing. There are some casees where you want two 
instances. One that is non-archivelog mode to do your loads, then an archivelog 
mode database that you push data to with transportable tablespaces.

The answer is 'it depends'. 

Why do you need to do an alter table move on your table after ETL? I'm assuming 
you compacted the table by setting like 99 pctused and 1 pctfree? Why not do 
that from the beginning?

Title: DWH loading : Do you use 2 types of tablespaces ?

Do your alter table move again, then try to resize your datafiles.

----- Original Message -----

From: NGUYEN Philippe (Cetelem)

To: 'oracle-l@xxxxxxxxxxxxx'

Sent: Tuesday, June 01, 2004 2:14 AM

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