From oracle-l-bounce@freelists.org Tue Jun 1 07:04:01 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i51C3kC12096 for ; Tue, 1 Jun 2004 07:03:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i51C3a612045 for ; Tue, 1 Jun 2004 07:03:46 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5ACD472C230; Tue, 1 Jun 2004 06:50:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 05963-41; Tue, 1 Jun 2004 06:50:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 592D472C0C0; Tue, 1 Jun 2004 06:50:26 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 01 Jun 2004 06:49:02 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CACA372C112 for ; Tue, 1 Jun 2004 06:49:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01888-93 for ; Tue, 1 Jun 2004 06:49:01 -0500 (EST) Received: from lakermmtao02.cox.net (lakermmtao02.cox.net [68.230.240.37]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D6ECD72C19F for ; Tue, 1 Jun 2004 06:49:00 -0500 (EST) Received: from smtp.central.cox.net ([172.18.52.58]) by lakermmtao02.cox.net (InterMail vM.6.01.03.02 201-2131-111-104-20040324) with SMTP id <20040601120704.JFLM19971.lakermmtao02.cox.net@smtp.central.cox.net> for ; Tue, 1 Jun 2004 08:07:04 -0400 From: To: oracle-l@freelists.org, Subject: Re: Re: DWH loading : Do you use 2 types of tablespaces ? Date: Tue, 1 Jun 2004 8:07:04 -0400 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=____1086091624773_dj76QlwD)E" Message-Id: <20040601120704.JFLM19971.lakermmtao02.cox.net@smtp.central.cox.net> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1676 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan.gaffuri@cox.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ------=____1086091624773_dj76QlwD)E Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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? ------=____1086091624773_dj76QlwD)E Content-Type: text/html; name="replyAll" Content-Disposition: inline; filename="replyAll" DWH loading : Do you use 2 types of tablespaces ?
Do your alter table move again, then try to resize your datafiles.
----- Original Message -----
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



------=____1086091624773_dj76QlwD)E-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org 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 -----------------------------------------------------------------