ORA-01654
Shredder
when trying to save data into our MRP database. The transactions are issued
using a front end to Oracle called Obvious MRP. I am running Oracle 8i on a
Netware 5.1 Server. I have verified that I have 15GB of free space on my
Volume. Can anyone give me any suggestions?
Thanks in advance,
Brian
ORA-01654: unable to extend index SYSADM.OPS_TRANS_IDX1 by 138254 in
tablespace SYSTEM ORA-27039: create file failed, file size limit reached
OSD-02067: illegal option specified
Sybrand Bakker
wrote:
First of all, do NOT create end-user objects in the SYSTEM tablespace
EVER.
Secondly, the error messages are quite clear
1 index needs to be extended, and can't be extended
2 The tablespace needs to extend, and this can't be done because the
size of the file reached a fixed Netware imposed limit.
Cure: you need to add another datafile to this tablespace,
but better still:
create a second tablespace
and issue
alter table <table_name> move ....
and
alter index rebuild .tablespace ...
commands.
Then of course: consult the manuals before creating havoc.
--
Sybrand Bakker, Senior Oracle DBA
Mark D Powell
product also did not understand Oracle very well.
You should read the DBA Administration manual as soon as you can. It
covers all the basic DBA management functions.
You should also look over the Backup and Recovery manual as soon as
possible. You will want to verify that you in fact have a usuable
backup and know how to perform recovery.
HTH -- Mark D Powell --
Joel Garry
in the system tablespace, during an import for example. These are the
sorts of things you need a qualified operations DBA to watch for and
prevent. If you have one MRP object in the system tablespace, you
probably have thousands. If so, your database is in fairly grave
danger of stopping suddenly and completely. I hope your business
doesn't depend on it. Sybrand's advice will get you out of immediate
danger, but you need some help, soon. Mark's advice is correct, but
you have some, um, learning experiences ahead before you'll be able to
read all that stuff.
jg
--
@home.com is bogus.
http://kevan.org/smaller.cgi
Shredder
I need to get this going. Can anyone recommend a qualified DBA in the Ohio
area?
"Shredder" <sh...@shredder.com> wrote in message
news:MInif.143432$tD4.1...@tornado.ohiordc.rr.com...
Shredder
this since I am a newbie.
In SQL I enter this?
Can this table be called anything ... like system1?
When I do the alter table system1 move .... what am I moving?
> create a second tablespace
> and issue
> alter table <table_name> move ....
> and
> alter index rebuild .tablespace ...
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:h47ko1980mlp84qrn...@4ax.com...
Joel Garry
Shredder wrote:
> Sorry, I want to be exact on this before I execute. Kinda nervous doing
> this since I am a newbie.
>
> In SQL I enter this?
>
connect SYS as SYSDBA
-- connect internal for older versions
set echo on
spool CreatefSystemFile.log
-- drop tablespace system1 including contents and datafiles;
-- the above line if you have to do it a second time - the datafiles
part may not
-- work on your version, can't recall, you may have to remove the
datafile after dropping,
-- or use the REUSE keyword.
Create Tablespace system1
Datafile '/pathtowhereyourdatafilesare/system1.dbf' size 2000M
/
-- or whatever size your OS can handle and you want. May take a while.
> Can this table be called anything ... like system1?
Anything that isn't already in use.
select tablespace_name from dba_tablespaces;
to see what is there.
What you really want to be doing, however, is creating a non-system
tablespace. You may have some tablespaces named by your app, in which
case you might want to continue any naming conventions you see. Your
app may put things in USER, in which case it is stupid.
There may be separate tablespaces for data versus indices. This is not
a performance thing (just warning you of an old myth that still comes
up), but rather for management ease.
> When I do the alter table system1 move .... what am I moving?
>
> > create a second tablespace
> > and issue
> > alter table <table_name> move ....
> > and
> > alter index rebuild .tablespace ...
>
You need to find out the names and sizes of all non-sys, non-system
objects that are in your system tablespace. Then move each one out.
spool bad_dba_objects
select object_name, owner from dba_objects where owner not in
('SYS','SYSTEM','P
UBLIC')
/
-- That might get you too many things, you may want to check where
owner='yourschemanamehere' instead.
If there are many things, you might want to generate sql to move them.
Something like (this is off the top of my head, check the syntax):
spool movealot
select 'alter table '||table_name||' move tablespace
yournewtablespacename;' from dba_tables where
owner='yourschemanamehere' and tablespace='SYSTEM';
select 'alter index '||index_name||' rebuild tablespace
yournewindextablespacename;' from dba_indexes where
owner='yourschemanamehere' and tablespace='SYSTEM';
spool off
-- @movealot
-- look at the sql before you execute it!
You also should check to be sure there is a proper default tablespace
for everybody:
select username, default_tablespace from dba_users;
People here don't like top posting, BTW.
If you have support (you _do_ have support, right?), you can have
Oracle help you, they'll stay on the phone to get you going if your
production system is down. metalink.oracle.com
https://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=131704.1
has some useful scripts, like Finding Objects in both SYSTEM and SYS
and Change the Default and Temporary Tablespace for Database Users
jg
--
@home.com is bogus.
"Sometimes you wake up in the morning and you want to strangle Mary
Poppins." - Randy "Duke" Cunningham