tplai
asked on
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
Hi Markgeer
As far as i know INITIAL and NEXT should be same for TEMPORARY tablespace,you have mentioned as INITIAL 1m NEXT 1m.
As far as i know INITIAL and NEXT should be same for TEMPORARY tablespace,you have mentioned as INITIAL 1m NEXT 1m.
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.
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.
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.