DWH loading : Do you use 2 types of tablespaces ? 2004-06-01 - By Darrell Landrum
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@(protected) '
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 (See ORA-03297.ora-code.com) (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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD > <TITLE >DWH loading : Do you use 2 types of tablespaces ? </TITLE >
<META http-equiv=Content-Type content= "text/html; charset=iso-8859-1 " >
<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR >
<STYLE > </STYLE >
</HEAD >
<BODY bgColor=#ffffff >
<DIV > <FONT face=Arial size=2 >Do your alter table move again, then try to resize
your datafiles. </FONT > </DIV >
<BLOCKQUOTE
style= "PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px " >
<DIV style= "FONT: 10pt arial " >-- -- Original Message -- -- </DIV >
<DIV
style= "BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black " > <B >From: </B >
<A title=philippe.nguyen@(protected)
href= "mailto:philippe.nguyen@(protected) " >NGUYEN Philippe (Cetelem) </A > </DIV >
<DIV style= "FONT: 10pt arial " > <B >To: </B > <A title=oracle-l@(protected)
href= "mailto: 'oracle-l@(protected) ' " > 'oracle-l@(protected) ' </A > </DIV >
<DIV style= "FONT: 10pt arial " > <B >Sent: </B > Tuesday, June 01, 2004 2:14
AM </DIV >
<DIV style= "FONT: 10pt arial " > <B >Subject: </B > DWH loading : Do you use 2 types
of tablespaces ? </DIV >
<DIV > <BR > </DIV >
<P > <FONT size=2 >Oracle 9R2, </FONT > <BR > <FONT size=2 >Tablepsace LMT / extent
uniform size </FONT > </P >
<P > <FONT size=2 >Hi gurus! </FONT > <BR > <FONT size=2 >after loading my table with
an ETL my my_big_table took 22Go. </FONT > <BR > <FONT size=2 >after a "alter
table big_table move " --> move in the same tablespace, it decreased to 10
Go. </FONT > <BR > <FONT size=2 >So, now I want to retrieve the unused space from
my tablespace again but reducing datafiles give me an ORA-03297 (See ORA-03297.ora-code.com) (files
contains used data beyond requested RESIZE value). </FONT > </P >
<P > <FONT size=2 >I guess alter 'table my_big_table move tablespace
an_other_tablespace ' would solve the problem so my questions are : </FONT >
<BR > <FONT size=2 >1. For a DWH , do you use to use 2 type of tablespace , one
for the loading operation and one for de definitive use. </FONT > <BR > <FONT
size=2 >2. Is there other solution avoid the use of two tablespaces ? </FONT >
</P >
<P > <FONT size=2 >Thanks in advance ! </FONT > <BR > <FONT size=2 >Philippe </FONT >
</P > <BR > <BR > </BLOCKQUOTE > </BODY > </HTML >
|
|
