ORA-01652 : unable to extend temp segment by 2560 in tablespace ANNUAIRE

Sun OS 5.6, Oracle 8.0.4

Error : ORA-01652 : unable to extend temp segment by 2560 in tablespace ANNUAIRE

This occurs during the index creation.

Creating tablespace --> OK (see crespace.sql)
Creating table --> OK (see cretable.sql)
Loading data --> OK
Creating index --> Problem (see creindex.sql)

CRESPACE.SQL
==========
connect system/manager;
create role dgi_role;
grant alter any index, create any index, create cluster, create database link, create procedure,
create sequence, create session, create snapshot, create synonym, create table, create tablespace,
create trigger, create view, drop any index, drop tablespace, execute any procedure,
select any sequence, select any table to dgi_role;
grant dgi_role, unlimited tablespace to mefi identified by dgi;

connect mefi/dgi;
create tablespace annuaire
datafile '/medoc/vol3/WMEFIDGI/fich/ficora/annuaire_1.dbf' size 1000m
default storage (initial 1m next 5m pctincrease 0);

connect system/manager;
alter user mefi default tablespace annuaire;
alter user mefi temporary tablespace annuaire;

quit


CRETABLE.SQL
==========
connect mefi/dgi;

---------------------------------------------------------
-- Table des regions et departements                   --
---------------------------------------------------------
create table regdep (
  typezone varchar2(1),
  codezone varchar2(3),
  libezone varchar2(30),
  coderegi varchar2(2)
)
tablespace annuaire
storage (initial 10k next 10k pctincrease 0);


---------------------------------------------------------
-- Table des entités topographiques                    --
---------------------------------------------------------
create table fantoi (
  codetopo varchar2(10),
  libetopo varchar2(30),
  motclass varchar2(8)
)
tablespace annuaire
storage (initial 500m next 50m pctincrease 0);


---------------------------------------------------------
-- Table des services                                  --
---------------------------------------------------------
create table fimoca (
  codeense varchar2(2),
  codeua   varchar2(10),
  datevali varchar2(7),
  codetua  varchar2(5),
  rangcomp varchar2(2),
  natulong varchar2(30),
  denomina varchar2(30),
  adrecomp varchar2(175),
  telephon varchar2(37)
)
tablespace annuaire
storage (initial 40m next 20m pctincrease 0);

---------------------------------------------------------
-- Table des competences territoriales des services    --
-- (lien entre tables FANTOI et FIMOCA).               --
---------------------------------------------------------
create table fimoct (
  codeense varchar2(2),
  codeua   varchar2(10),
  datevali varchar2(7),
  codetopo varchar2(10),
  codepari varchar2(1),
  bornesup varchar2(5),
  dateval2 varchar2(7),
  rangcomp varchar2(2),
  dateffet varchar2(7),
  nivaffec varchar2(20)
)
tablespace annuaire
storage (initial 200m next 50m pctincrease 0);

quit


CREINDEX.SQL
==========
connect mefi/dgi;

---------------------------------------------------------
-- Index sur regions et departements                   --
---------------------------------------------------------
create index ix_regdep_typezone ON regdep(typezone);
create index ix_regdep_codezone ON regdep(codezone);
create index ix_regdep_coderegi ON regdep(coderegi);


---------------------------------------------------------
-- Index sur entités topographiques                    --
---------------------------------------------------------
create index ix_fantoi_codetopo ON fantoi(codetopo);
create index ix_fantoi_motclass ON fantoi(motclass);


---------------------------------------------------------
-- Suppression des enregistrements services parasites  --
---------------------------------------------------------
delete from fimoca where rangcomp = '71';

---------------------------------------------------------
-- Index sur services                                  --
---------------------------------------------------------
create index ix_fimoca_codeense ON fimoca(codeense);
create index ix_fimoca_codeua   ON fimoca(codeua);
create index ix_fimoca_datevali ON fimoca(datevali);
create index ix_fimoca_codetua  ON fimoca(codetua);
create index ix_fimoca_rangcomp ON fimoca(rangcomp);


---------------------------------------------------------
-- Index sur competences territoriales des services    --
---------------------------------------------------------
create index ix_fimoct_codeense ON fimoct(codeense);
create index ix_fimoct_codetopo ON fimoct(codetopo);

quit

Thanks for your answer.

Thierry
tplaiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bellyboyCommented:
Thierry

It is usual to set the temporary tablespace for a user to a separate tablespace (normally TEMP). If you do not do this temp segments will be allocated and dropped in your main storage tablespace causing a "honeycomb" effect where there are unused holes in your tablespace.

If you have Enterprise manager Tuning Pack the Tablespace manager gives a graphical display which is good for spotting this type of problem.

The other thing I would recommend is to separate the indexes into a separate tablespace to allow IO balancing (I don't know you system so this might not be possible)

So in summary
Use TEMP as user's temporary tablespace.
Move indexes to separate tablespace.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mshaikhCommented:
As bellyboy says you need to create a seperate tablespace, say TEMP, and mark it TEMPORARY. and  alter the users temporary space to TEMP.

But, doing this may or may not take care of the problem you are having.
What you may need to do is increase the tablspace size for ANNUAIRE tablespace. This type of error happens even if you have a seperate tablespace defined as TEMPORARY for that user.
0
Mark GeerlingsDatabase AdministratorCommented:
I basically agree with bellyboy.  You should create at least two additional tablespaces: temp and annuaire_index.  Also, your annuaire tablespace will have serious problems with fragmented freespace

I would add these to your crespace.sql file:
create tablespace temp datafile [filename] size 1000m default storage (initial 1m next 2m pctincrease 100);

alter tablespace temp temporary;

create tablespace annuaire_index datafile [filename] size 1000m default storage (initial 1m next 2m pctincrease 100);

alter user mefi temporary tablespace temp;

(Ideally the data files for your temp and index tablespaces will be on different disks than your main data and system tablespace files.)


You will need to add "tablespace annuaire_index" to all of your create index statements.

It may be better yet to have three of four separate tablespaces for data and as many for indexes, for different sized tables and indexes so that all the objects in a tablespace can have the same storage parameters.  Otherwise, if objects are ever dropped, the freespace becomes fragmented, and there may not be enough contiguous freespace for the larger objects.

Also you had "pctincrease 0" in your create tablespace statement.  Are you aware that that prevents Oracle from automatically combining (coalescing) the freespace for you in that tablespace?
0
vemulayugandharCommented:
Hi Markgeer
As far as i know INITIAL and NEXT should be same for TEMPORARY tablespace,you have mentioned as INITIAL 1m NEXT 1m.
0
Mark GeerlingsDatabase AdministratorCommented:
To vemulayugandhar:

Whether initial and next should be the same or not for temporary tablespaces is debatable.  There are advantages and disdavantages.  The pctincrease value is also important.

The main advantage of having initial and next equal and pctincrease of 0 is the fact that then all temporary segments will be the same size, so fragmentation of freespace in the temporary tablespace will not occur.  Any transaction that requires a temporary segment then can use any previously-used extent.

There are two disadvantages though:
1. all transactions need to use the same size extent, and this must be quite large, because...
2. additional extents for very large transactions will be the same size as the initial extent, so a large nember of extents will be required for large transactions.  It is possible then for a large transaction to hit the maximum number of extents allowed, before it finishes.

For these reasons, I set the "next" value to be 2X the initial and set pctincrease to 100 for temporary tablespaces.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.