Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
DWH loading : Do you use 2 types of tablespaces ?

DWH loading : Do you use 2 types of tablespaces ?

2004-06-01       - By NGUYEN Philippe (Cetelem)
Reply:     1     2     3     4     5     6  

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 3.2//EN " >
<HTML >
<HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<META NAME= "Generator " CONTENT= "MS Exchange Server version 5.5.2657.73 " >
<TITLE >DWH loading : Do you use 2 types of tablespaces ? </TITLE >
</HEAD >
<BODY >

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

</BODY >
</HTML >