Re: datafiles/tablespace - howto

Jerry Gitomer wrote:

> 
> Hi,
> 
>     The first thing you want to do is to change the default
> tablespace for the user account that is loading the data.  The
> reason for this is that, in order to avoid problems down the
> road, you should NEVER create tables in the SYSTEM tablespace.
> The following SQL will change it:
> 
>     ALTER USER <myaccount> DEFAULT TABLESPACE user_data;
> 
>     The next step is to move the tables from the SYSTEM
> tablespace to the USER_DATA tablespace.  If you have Oracle 8.1.x
> (aka 8i) there is a SQL*Plus command that does this (sorry I am
> running an older version at home and don't remember the syntax).
> If running an older version of oracle you will have to drop the
> tables and recreate them or do something like:
> 
>     RENAME <tablename> TO temporary_name;
>     CREATE tablename AS SELECT * FROM temporary_name;
> 
> (As you might suspect I prefer this to dropping and recreating
> tables, but YMMV)
> 
>     The next thing you should do is increase the size of the
> datafile in USER_DATA.  The SQL to do this is:
> 
>     ALTER DATABASE DATAFILE
>     '/<full path>/users01.dbf' RESIZE 200M;
> 
> This will add approximately 100M to your tablespace which should
> make it more than large enough to hold your data.
> 
> A couple of other points.  First, you should, if you have more
> than one disk drive, move the redo logs, i.e. 'redoORCL0x.log'
> and 'control0x.dbf' to a different drive than the one used to
> hold the data.  In addition the log and control files should on
> different drives.  Second, you should create a separate
> tablespace for indexes and it should be about the same size as
> the USER_DATA tablespace.
> 

I would also add that your redo logs are pitifully small. Increase their size to at least 10M and if you have room, add a fourth redo log. Otherwise you will have a problem when you are processing a large number of transactions sequentially. I'm not sure why oracle set the default size of the redo logs so small.

.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
.............................................................
Received on Mon Nov 22 1999 - 07:07:09 CST