Re: datafiles/tablespace - howto

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 20 Nov 1999 00:41:57 +0800
Message-ID: <38357DD5.2651@yahoo.com>

sxbktm_at_my-deja.com wrote:
>
> 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.

Some things to look at:

• make TOOLS datafiles larger (cf: alter database datafile)
• make TOOLS datafiles autoextend (cf: alter database datafile)
• add datafiles to TOOLS (cf: alter tablespace add datafile)

On a different tack

• don't stick this stuff in the SYSTEM account
• create some application tablespaces to hold this stuff
• split your logs over different disks
• make your logs 10M not 0.5M
• split your control files over different disks
• split/stripe your datafiles with a reasonable volumne manager
• use direct load on your sql loader
• drop any indexes on the table before loading and then rebuild

and finally (and I don't mean this nastily) get some consultancy or allocate some good time to get stuck into the DBA manuals

Cheers
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Fri Nov 19 1999 - 10:41:57 CST