Eek, you're creating the table in the temporary tablespace! Don't do that!
As the docs say:
No permanent schema objects can reside in a temporary tablespace.Things won't work as you expect if you use this:
CREATE TABLE TABLE1 (
COL_1 NUMBER(38) NOT NULL,
COL_2 NUMBER(38) NOT NULL,
START_DT DATE NOT NULL,
END_DT DATE NOT NULL,
LOAD_ID NUMBER(38) NOT NULL
)
TABLESPACE TEMP ;
insert into TABLE1 values (1, 1, sysdate, sysdate, 1);
SQL Error: ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace
This may be related to why it's taking so long for you to create the table. Stripping out the tablespace sections it's fast for me (I've edited out most of the partitions for brevity):
SQL> set sqlbl on
SQL> set timing on
SQL> CREATE TABLE TABLE1 (
2 COL_1 NUMBER(38) NOT NULL,
3 COL_2 NUMBER(38) NOT NULL,
4 START_DT DATE NOT NULL,
5 END_DT DATE NOT NULL,
6 LOAD_ID NUMBER(38) NOT NULL
7 )
8 nocompress
9 RESULT_CACHE (MODE DEFAULT)
10 PCTUSED 40
11 PCTFREE 10
12 INITRANS 1
13 MAXTRANS 255
14 STORAGE (
15 BUFFER_POOL DEFAULT
16 FLASH_CACHE DEFAULT
17 CELL_FLASH_CACHE DEFAULT
18 )
19 PARTITION BY RANGE (START_DT)
20 SUBPARTITION BY HASH (COL_1)
21 (
22 PARTITION P201409 VALUES LESS THAN (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
23 NOLOGGING
24 NOCOMPRESS
25
26 PCTUSED 40
27 PCTFREE 10
28 INITRANS 1
29 MAXTRANS 255
30 STORAGE (
31 MAXSIZE UNLIMITED
32 BUFFER_POOL DEFAULT
33 FLASH_CACHE DEFAULT
34 CELL_FLASH_CACHE DEFAULT
35 )
36 SUBPARTITIONS 32 ,
...
442 PARTITION P999912 VALUES LESS THAN (TO_DATE(' 9999-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
443 NOLOGGING
444 NOCOMPRESS
445
446 PCTUSED 40
447 PCTFREE 10
448 INITRANS 1
449 MAXTRANS 255
450 STORAGE (
451 MAXSIZE UNLIMITED
452 BUFFER_POOL DEFAULT
453 FLASH_CACHE DEFAULT
454 CELL_FLASH_CACHE DEFAULT
455 )
456 SUBPARTITIONS 32
457 )
458 NOCACHE
459 NOPARALLEL
460 MONITORING;
Table created.
Elapsed: 00:00:01.16
SQL>
SQL> CREATE UNIQUE INDEX PK_TABLE1 ON TABLE1
2 (COL_1, COL_2, START_DT)
3
4 PCTFREE 10
5 INITRANS 2
6 MAXTRANS 255
7 STORAGE (
8 BUFFER_POOL DEFAULT
9 FLASH_CACHE DEFAULT
10 CELL_FLASH_CACHE DEFAULT
11 )
12 LOCAL (
13 PARTITION P201409
14 NOLOGGING
15 NOCOMPRESS
16
17 PCTFREE 10
18 INITRANS 2
19 MAXTRANS 255
20 STORAGE (
21 MAXSIZE UNLIMITED
22 BUFFER_POOL DEFAULT
23 FLASH_CACHE DEFAULT
24 CELL_FLASH_CACHE DEFAULT
25 )
26 ,
...
405 PARTITION P999912
406 NOLOGGING
407 NOCOMPRESS
408
409 PCTFREE 10
410 INITRANS 2
411 MAXTRANS 255
412 STORAGE (
413 MAXSIZE UNLIMITED
414 BUFFER_POOL DEFAULT
415 FLASH_CACHE DEFAULT
416 CELL_FLASH_CACHE DEFAULT
417 )
418 )
419 NOPARALLEL;
Index created.
Elapsed: 00:00:01.06
SQL>
SQL> ALTER TABLE TABLE1 ADD (
2 CONSTRAINT PK_TABLE1
3 PRIMARY KEY
4 (COL_1, COL_2, START_DT)
5 USING INDEX LOCAL
6 ENABLE VALIDATE);
Table altered.
Elapsed: 00:00:00.31
SQL>
SQL> select dbms_metadata.get_ddl('TABLE', 'TABLE1') from dual;
DBMS_METADATA.GET_DDL('TABLE','TABLE1')
--------------------------------------------------------------------------------
CREATE TABLE "CHRIS"."TABLE1"
( "COL_1" NUMBER(38,0) NOT NULL ENABLE,
"
Elapsed: 00:00:03.67
If it still takes 20+ hours on a normal tablespace, something's not right. Speak with support!