You Asked
HI TOM,
1)DESTINATION TABLE NAME IS:TEMP(EMPNO NUMBER(3) CONSTRAINT PK_EMPNO PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(10),SAL INTEGER)
I'M LOADING DATA FROM FLAT FILE into TEMP TABLE USING SQL*LOADER THROUGH DIRECT METHOD
FLAT FILE IS
--------------
1, MARTIN,SALESMAN , 1400
2,raghu,SALESMAN , 1400
3,kalam,scientist,1400
3,kalam,scientist , 10375
1, MARTIN,SALESMAN ,1400
1, MARTIN,SALESMAN ,1037
2,raghu,SALESMAN , 1400
3,kalam,scientist ,10375
3,kalam,scientist,1400
1, MARTIN, SALESMAN,,10375
After loading the above data into temp table in the database
Again i'm try to insert data into temp table in that particular schema i got an error
i.e(ERROR at line 1:
ORA-01502: index 'KRISHNA.PK_EMPNO' or partition of such index is in unusable state) how to resolve the issue, and i want to know what about the constraint primary key of temp table and how to enable it?
and Chris said...
Primary keys are supported by unique indexes.
If the index is unusable, you'll need to rebuild it:
SQL> create table t (
2 EMPNO NUMBER(3) CONSTRAINT PK_EMPNO PRIMARY KEY,
3 ENAME VARCHAR2(10),JOB VARCHAR2(10),SAL INTEGER
4 ) partition by range (sal) interval (10) (
5 partition p0 values less than (10),
6 partition p1 values less than (20)
7 );
Table created.
SQL> insert into t
2 select rownum, 'Chris', 'Advocate', rownum from dual connect by level <= 20;
20 rows created.
SQL>
SQL> alter table t drop partition p0;
Table altered.
SQL>
SQL> select index_name, status from user_indexes
2 where table_name = 'T';
INDEX_NAME STATUS
-------------------- --------
PK_EMPNO UNUSABLE
SQL>
SQL> insert into t values (21, 'Chris', 'Advocate', 21);
insert into t values (21, 'Chris', 'Advocate', 21)
*
ERROR at line 1:
ORA-01502: index 'CHRIS.PK_EMPNO' or partition of such index is in unusable state
SQL>
SQL> alter index PK_EMPNO rebuild;
Index altered.
SQL>
SQL> select index_name, status from user_indexes
2 where table_name = 'T';
INDEX_NAME STATUS
-------------------- --------
PK_EMPNO VALID
SQL>
SQL> insert into t values (21, 'Chris', 'Advocate', 21);
1 row created.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment