CODEWITHSUNDEEP
oracle-databaseoracle11g
Paul Stearns
Paul Stearns

Reputation: 906

In Oracle how can "select * from my_table;" return errror ora-00904?

In Oracle 11gR2 (v11.2.0.4.0)

I have a simple table;

CREATE TABLE "CLAIMS_PATIENT" ( "CLAIMS_PATIENT_ID" NUMBER NOT NULL ENABLE, "NAME_LASTNAME" VARCHAR2(60 BYTE), "NAME_FIRSTNAME" VARCHAR2(35 BYTE), "NAME_MIDDLENAME" VARCHAR2(25 BYTE), "ADDR_LINE" VARCHAR2(55 BYTE), "ADDR_CITY" VARCHAR2(30 BYTE), "ADDR_STATE" VARCHAR2(3 BYTE), "ADDR_ZIP" VARCHAR2(15 BYTE), "GENDER" VARCHAR2(1 BYTE), "DOB" DATE, "SSN" VARCHAR2(9 BYTE), "MATCH_PID" NUMBER, "MATCH_SCORE" NUMBER DEFAULT -1, "MATCH_DATE" DATE, "PACS_STATUS" VARCHAR2(1 BYTE) DEFAULT NULL, CONSTRAINT "CLAIMS_PATIENT_PK" PRIMARY KEY ("CLAIMS_PATIENT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MYDATA" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "CLAIMS" ; Which I have populated with some data. using a stored procedure. Now when I attempt to do any select on the table;

select * from claims_patient;
select count(*) from claims_patient;
select ssn from claims_patient;

It returns an ora-00904 error at the last character before the semi-colon. I created this table empty on my test db, using the ddl from live db, where everything works swimmingly. I have tried using both sqldeveloper and sqlplus.

At this point I suspect something crashed and corrupted the table in my test environment, but I have never seen a corrupt table in Oracle, and I have been working with it since 1995.

Before I wipe the table and start again, what should I do to uncover the underlying cause of this problem? At this point I am curious, in case I ever see it in production, it might be useful to know what the issue is.

As requested, the results of;

select table_name, dump(table_name) from user_tables where table_name like 'CLAIM%ENT'; is; Typ=1 Len=14: 67,76,65,73,77,83,95,80,65,84,73,69,78,84

Upvotes: 2

Views: 158

Answers (1)

Paul Stearns
Paul Stearns

Reputation: 906

Well, I did find the answer. 'Twas in an unlikely place. One of the indices, which I did not include the DDL for, was a function based index. The function became inaccessible (grant revoked) over night.

So if anyone ever finds this thread and has a table with a function based index, where the function (after the table and indices are built) becomes inaccessible, dropped, execute permissions changed, or whatever, you may get an ora-00904 when trying to access any data in the table.

It sure would be nice if Oracle were to define a more specific error that would point you in the general direction of an answer.

Upvotes: 3

Related Questions