index creation problem

[mmbiswal]

Hi,
i'm facing this problem while trying to create one index.
This table has 5 million records and record size is 52 bytes.

the query and output are here
any suggestion will be highly appreciated.

thanx

SQL> CREATE UNIQUE INDEX PK_TIMESHEETENTRY ON TIMESHEETENTRY (EMPLOYEENO , ENTRYDATE , PROJECTID ,
TASKID , CHARGETYPECODE ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 M
INEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 5 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESP
ACE IPMS_INDX LOGGING
2 /
CREATE UNIQUE INDEX PK_TIMESHEETENTRY ON TIMESHEETENTRY (EMPLOYEENO , ENTRYDATE , PROJECTID , TASKI
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 1420 in tablespace IPMS_INDX

[marist89]

IPMS_INDX is not big enough. Add more space.

[mmbiswal]

HI
i added another datafile of size 200MB in IPMS_INDX tablespace, but no result.

[Orca777]

Hello;

please check your next-clause

1048576 M is a lot!

Orca

[mmbiswal]

Hi
that's not 1048576 M it is 1048576 MINEXTENTS.
By mistake 1048576 M in one line and INEXTENTS in other line.

thanx

[Orca777]

ok; sorry!
Then your Tablespace is still too small.
Do not add another Datafile, try to increase the size
the datafiles, so you have the opportunity to
shrink the TS again if you need to.

Orca

[SANJAY_G]

What is the temporary tablesapce assigned for the user?

Sanjay

[dbaman]

I've had this before, it was trying to create an extent that is bigger than any avialible extent.

Run this sql and look at the max chunk column and see if its big enough.

col psm format 99990.99 heading 'Physical|Space (Mb)'
col fsm format 99990.99 heading 'Total Free|Space (Mb)'
col pcu format 990 heading 'Pct Used'
col mfs format 99990.99 heading 'Max Free|Chunk (Mb)'
break on report
compute sum of psm fsm on report

select a.tablespace_name, a.physsp/(1024*1024) psm,
nvl(b.freesp,0)/(1024*1024) fsm,
nvl(b.mfreecnk,0)/(1024*1024) mfs,
100-((100*nvl(b.freesp,0))/a.physsp) pcu
from (select tablespace_name, sum(bytes) physsp
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) freesp, max(bytes) mfreecnk
from dba_free_space
group by tablespace_name) b
where b.tablespace_name (+) = a.tablespace_name
/

Cheers

[mmbiswal]

THANX EVERYBODY 4 UR RESPONSE.
I'll follow ur valuable suggestions and let u know.

once again thanx a lot