Skip to Main Content
  • Questions

  • ORA-01659 on creation of a not unique global partitioned index

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, antonio.

Asked: May 22, 2018 - 1:13 pm UTC

Last updated: May 22, 2018 - 1:45 pm UTC

Version: ORACLE RDBMS 12c

Viewed 1000+ times

You Asked

Dear Tom,

I have a table that stores climatic data with this layer: idcell,day,field1,....

This table is locally partitioned by range on day and it has a local PK index: idcell,day.

I want to create a not unique global partitioned index on it with about 23000 partitions, one for each cell.

For this purpose I created this table space where to store the global index:

CREATE TABLESPACE TEST_GLOIDX DATAFILE 
  '/oradata2/test01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 32767M
LOGGING
DEFAULT 
  NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


Theoretically the Oracle engine should create 23000 segments of 1MB, when i try to create the index but after several minutes, I forever receive the message: ORA-01659 unable to allocate MINEXTENTS beyond n in table space.

Can you suggest me a workaround ?

Thanks,

Tony

and Chris said...

The docs have this to say about ORA-01659:

Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE

https://docs.oracle.com/en/database/oracle/oracle-database/18/errmg/ORA-01500.html#GUID-65B2B9E5-7075-4D53-91B8-FCAECA0AEE0E

So check: do you have ~23Gb of free space in the tablespace's data files?

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.