We help IT Professionals succeed at work.

Index creation keeps erroring out w/ORA-01652.  How do we estimate space needed for index creation?

cbetter
cbetter asked
on
946 Views
Last Modified: 2013-11-17
Oracle 9.2.0.5 on AIX
We use a vendor product for our application.  A developer is trying to load data and continues to receive the following error on the table that the data is being loaded into:  

CREATE INDEX XIE18CLINICAL_EVENT ON CLINICAL_EVENT (PERSON_ID,EVENT_END_DT_TM,VALID_UNTIL_DT_TM,ENCTR_ID,RESULT_STATUS_CD,PERFORMED_PRSNL_ID) STORAGE (INITIAL 1600000K NEXT 1024000E) LOGGING ONLINE TABLESPACE I_CLINICAL_EVENT COMPUTE STATISTICS')

Oracle-01652:  unable to extend temp segment by 128000 in tablespace I_CLINICAL_EVENT).



Tablespace I_CLINICAL_EVENT

   Initial size       160K
    Next Size     1024K
   Minimum Size 160K
   Increment by 0%
   Minimum Number 1
   Logging enabled
   Current Size  > 181G


 Clinical_Event Table

CLINICAL_EVENT_ID       NOT NULL      NUMBER
ENCNTR_ID                     NOT NULL            NUMBER
PERSON_ID                    NOT NULL           NUMBER
EVENT_START_DT_TM                         DATE
ENCNTR_FINANCIAL_ID      NOT NULL      NUMBER
EVENT_ID                       NOT NULL      NUMBER
VALID_UNTIL_DT_TM        NOT NULL      DATE
EVENT_TITLE_TEXT                            VARCHAR2(255)
VIEW_LEVEL                     NOT NULL          NUMBER
ORDER_ID                      NOT NULL      NUMBER
CATALOG_CD                     NOT NULL          NUMBER
SERIES_REF_NBR                                    VARCHAR2(100)
ACCESSION_NBR                                    VARCHAR2(20)
CONTRIBUTOR_SYSTEM_CD      NOT NULL      NUMBER
REFERENCE_NBR              NOT NULL      VARCHAR2(100)
PARENT_EVENT_ID              NOT NULL      NUMBER
EVENT_RELTN_CD              NOT NULL      NUMBER
VALID_FROM_DT_TM      NOT NULL      DATE
EVENT_CLASS_CD              NOT NULL      NUMBER
EVENT_TAG              NOT NULL      VARCHAR2(255)
EVENT_END_DT_TM              NOT NULL      DATE
EVENT_END_DT_TM_OS                    FLOAT(126)
RESULT_VAL                                     VARCHAR2(255)
RESULT_UNITS_CD              NOT NULL      NUMBER
RESULT_TIME_UNITS_CD      NOT NULL      NUMBER
TASK_ASSAY_CD              NOT NULL      NUMBER
RECORD_STATUS_CD      NOT NULL      NUMBER
RESULT_STATUS_CD      NOT NULL      NUMBER
AUTHENTIC_FLAG              NOT NULL      NUMBER
PUBLISH_FLAG              NOT NULL      NUMBER
QC_REVIEW_CD              NOT NULL      NUMBER
NORMALCY_CD              NOT NULL      NUMBER
NORMALCY_METHOD_CD      NOT NULL      NUMBER
INQUIRE_SECURITY_CD      NOT NULL      NUMBER
RESOURCE_GROUP_CD      NOT NULL      NUMBER
RESOURCE_CD              NOT NULL      NUMBER
SUBTABLE_BIT_MAP                            NUMBER
COLLATING_SEQ                                       VARCHAR2(40)
VERIFIED_DT_TM                                        DATE
VERIFIED_PRSNL_ID      NOT NULL      NUMBER
PERFORMED_DT_TM                                  DATE
PERFORMED_PRSNL_ID      NOT NULL      NUMBER
UPDT_DT_TM              NOT NULL      DATE
UPDT_ID                      NOT NULL      NUMBER
UPDT_TASK              NOT NULL      NUMBER
UPDT_CNT              NOT NULL      NUMBER
UPDT_APPLCTX              NOT NULL      NUMBER
NORMAL_LOW                            VARCHAR2(20)
NORMAL_HIGH                            VARCHAR2(20)
CRITICAL_LOW                            VARCHAR2(20)
CRITICAL_HIGH                            VARCHAR2(20)
EXPIRATION_DT_TM                    DATE
NOTE_IMPORTANCE_BIT_MAP                    NUMBER
EVENT_TAG_SET_FLAG                    NUMBER
CLINSIG_UPDT_DT_TM                    DATE
ORDER_ACTION_SEQUENCE      NOT NULL      NUMBER
ENTRY_MODE_CD                      NOT NULL      NUMBER
SOURCE_CD                           NOT NULL      NUMBER
CLINICAL_SEQ                            VARCHAR2(40)
EVENT_END_TZ                            NUMBER
EVENT_START_TZ                            NUMBER
PERFORMED_TZ                            NUMBER
VERIFIED_TZ                            NUMBER
TASK_ASSAY_VERSION_NBR                    FLOAT(126)
MODIFIER_LONG_TEXT_ID      NOT NULL      NUMBER
SRC_EVENT_ID                            NUMBER
SRC_CLINSIG_UPDT_DT_TM                    DATE
NOMEN_STRING_FLAG                    NUMBER


When error first occurred we added 2G datafile.  Second time around we added 3 more 2G datafiles.  The table was last analyzed 2 days ago.  Storage is dictionary managed, due to vendor limitations.  Vendor sets up configurations.  Would I just figure space needed as the following, from my figures above on field sizes:
(6.63 + 20 + 8 + 8 + 6.63) * 237699183 = 11709061754.5/1024

That seems way, way too large.  I'm sure that indexes would be stored in a differnet format (binary perhaps).  How can I figure out how much space to have free so that this index can be created?  
Comment
Watch Question

Allow that developer to have access to all that space then:

Alter user <user_name> QUOTA UNLIMITED on  I_CLINICAL_EVENT;
Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
>> STORAGE (INITIAL 1600000K NEXT 1024000E )
what's this? 160MB and Exabytes?
I would say, make INITIAL size of your tablespace as large as INITIAL size of index.
set INITAL= 1600000K for tablespace

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.