Re: ORA-01654

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