Re: datafiles/tablespace - howto

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.

hth
jerry gitomer

sxbktm_at_my-deja.com wrote in message
<814o26$gae$1_at_nnrp1.deja.com>...
|Hi all,
|
|Wanna ask you a question on tablespace/datafiles.
|
|I want to load 7.5 million records into a table using sqlloader.
|
|The directory for datafiles looks like following:
|
|-rw-r----- 1 oracle dba 1497088 Nov 19 19:46
control01.ctl
|-rw-r----- 1 oracle dba 1497088 Nov 19 19:46
control02.ctl
|-rw-r----- 1 oracle dba 1497088 Nov 19 19:46
control03.ctl
|-rw-r----- 1 oracle dba 104859648 Nov 19 19:27
rbs01.dbf
|-rw-r----- 1 oracle dba 512512 Nov 19 19:46
redoORCL01.log
|-rw-r----- 1 oracle dba 512512 Nov 19 19:07
redoORCL02.log
|-rw-r----- 1 oracle dba 512512 Nov 19 19:27
redoORCL03.log
|-rw-r----- 1 oracle dba 104859648 Nov 19 19:42
system01.dbf
|-rw-r----- 1 oracle dba 131074048 Nov 19 19:27
temp01.dbf
|-rw-r----- 1 oracle dba 314574848 Nov 19 19:42
tools01.dbf
|-rw-r----- 1 oracle dba 314574848 Nov 19 19:46
tools02.dbf
|-rw-r----- 1 oracle dba 104859648 Nov 19 19:27
users01.dbf
|
|
|This is the only table I want to load. But when I try to load
using
|SQL Loader, I get following message,
|
|ORA-02356: The database is out of space. The load cannot
continue
|ORA-01653: unable to extend table SYSTEM.URLS by 92169 in
tablespace
|TOOLS
|
|Load completed - logical record count
|3538680.
|
|It loaded only half of the number of rows.
|How would I be able to resolve this. Any help would be greatly
|appreciated.
|
|Thanks in advance.
|
|
|Sent via Deja.com http://www.deja.com/
|Before you buy.
Received on Sat Nov 20 1999 - 08:53:08 CST