Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Newbie: ALTER TABLESPACE errors

Re: Newbie: ALTER TABLESPACE errors

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 1 Feb 2001 20:51:33 +0100
Message-ID: <t7jfeesreja7cb@beta-news.demon.nl>

"Richard" <huddler_at_earthlink.net> wrote in message news:MPG.14e370077409eb469897ff_at_news.earthlink.net...
> Oracle Enterprise 8.1.5
> SPARC 2.7 64 bit
>
> I'm trying to diagnose an ORA-01653 error generated in response to ODBC
> and JDBC processes inserting records into a database. It worked fine for
> about a week, up until two days ago.
>
> The process logs say:
>
> > ORA-01653: unable to extend table SYS.TCAS_TCPPKTDATA
> > by 12137 in tablespace SYSTEM
>
> When I attempt to use the SQLPLUS interfact the ALTER TABLESPACE ADD
> DATAFILE, I get ORA-01119 (no space on device). I directly specify the
> '/data/oracledb/oradata/TCAS/<datafile>.dbf' -- but I still get an ORA-
> 01119.
>
> /data is mounted to the sole partition on the physical device, and
> 'df -k' confirms there's 16GB FREE and AVAILABLE on that device.
>
> When I attempt to simply resize the datafile
> '/data/oracledb/oradata/TCAS/system01.dbf' I get a file not found error.
>
> I can 'ls' that file, currently sized at 183+ MB. I'm very sure I'm
> typing correctly when attempting to resize the datafile.
>
> I am going quite nuts over this. Any help greatly appreciated.
>
> --
> No one dies wishing they'd spent more time at the office.
>
> This post simply contains some of my personal opinions.
> ObURL: http://home.earthlink.net/~huddler

Sorry to say so but you violated two basic rules, which are there to prevent the problems you're now in.
1 You should *NEVER EVER* create your *OWN* objects under the user SYS (which you obviously have done)
2 You should *NEVER EVER* create your *OWN* objects in the tablespace SYSTEM. This tablespace will become fragmented as a result of your action, which is why you are hitting above errors. The space probably is there but your extent has to be created *contiguous*. You can try issuing an ALTER TABLESPACE SYSTEM coalesce before you proceed, provided there is sufficient space available (select sum(bytes) from dba_free_space where tablespace_name = 'SYSTEM') If that doesn't provide enough contiguous space, you can indeed resize a datafile, or add another one.
As you don't provide the complete command, I can only guess you are hitting the 2 G limit on datafiles.

Just to warn you, it seems you have also hit nono number 3 You should never ever have *all of Oracle * or *all of the database* on *1* filesystem. You're likely to run into trouble.

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Feb 01 2001 - 13:51:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US