Hello.
I just finished importing a database v11.1.0.7 into a new database v.11.2.0.4 on RHEL 6.4 using DataPump.
The import completed with one major error: it ran out of temp space when attempting to build a huge index.
The details of the database are quite simple: only 8 tables, one table is 186G, other tables are much smaller.
Table is simple table with only a handful of columns, but there is a composite PK index on 4 or 5 columns.
The index in the original database is 230G.
See below:
1 select segment_name, segment_type, round(sum(bytes)/1024/1024/1024,1) as "GB" from dba_segments
2 where owner = 'CS_APP'
3 group by segment_name, segment_type
4* order by 3
SQL> /
SEGMENT_NAME SEGMENT_TYPE GB
-------------------------------- ------------------ ---------------
SYS_LOB0000014307C00036$$ LOBSEGMENT 0
COMMENT_WORDS_TEMP TABLE 0
SYS_IL0000014307C00036$$ LOBINDEX 0
TOAD_PLAN_TABLE TABLE 0
COMMENT_QTY TABLE 0
IDX_CMNT_WORDS_TEMP_WORD INDEX 0
IDX_CMNT_DICT_WORD_ID INDEX .8
IDX_CMNT_DICT_WORD INDEX 1.3
COMMENT_DICTIONARY TABLE 1.8
IDX_CMNT_DATE_DATE INDEX 8.8
IDX_CMNT_DATE_USER INDEX 9
IDX_CMNT_DT_ORD_SEQ_VER INDEX 10.9
COMMENT_DATE TABLE 12.8
IDX_CMNT_WORDS_WORD_ID INDEX 135
COMMENT_WORDS TABLE 185.6
IDX_CMNT_WORDS_PK INDEX 230.6
16 rows selected.
Since the index was the only failed object in the import, I decided to extend the temp tablespace by several GB and run the index creation script manually using dbms_metadata to get the DDL for the PK constraint that creates the index.
Doing this, I watched the space utilization of the TEMP tablespace increase from 0 to 243461 MB, and file system increasing along with it (temp tablespace files set to autoextend).
Then, it stopped increasing. And of course, so did the file system usage.
Its been sitting there appearing to do nothing for 1-1/2 hours and I'm trying to figure out what it is doing.
I've checked v$session_longops and TIME_REMAINING = 0 for everything.
I've checked v$transaction and I only see one entry which shows active.
I've queried v$sqlarea and v$session and it just shows the active statement as ALTER TABLE "CS_APP"....
But, again, as I said, i can not see it is doing anything. Space is not growing in the tablespace the index is being created, and there is plenty of space on the file system.
Database is not in archivelog mode, and there are no errors or indications in the alert log of anything hanging.
My guess is that it may be computing statistics.
Here is the DDL that I used to create the PK constraint and index (as extracted from the original database).
SQL> ALTER TABLE "CS_APP"."COMMENT_WORDS" ADD PRIMARY KEY ("ORDER_ID", "COMMENT_SEQ_NO", "VERSION_NO", "WORD_SEQ_NO")
2 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
3 STORAGE(INITIAL 10485760000 NEXT 1073741824 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
5 TABLESPACE "CS_DATA" ENABLE
6 parallel (degree 4);
Is there any way I can confirm what is actually happening? I suppose I could run awrrpt.sql, but wondering if there was a query that would give me the info.