Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Newbie: ALTER TABLESPACE errors
"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