ORA-00942: table or view does not exist



在sql中增加一個表格，

CREATE TABLE SSN_INFO
(
  NEW_SN VARCHAR2(50) NOT NULL,
  CHECKSUM VARCHAR2(20) NOT NULL
)

然後在代碼中執行

INSERT INTO SSN_INFO ( NEW_SN,CHECKSUM ) VALUES ( ?,? )

報錯如下：

bad SQL grammar [ INSERT INTO SSN_INFO ( NEW_SN,CHECKSUM ) VALUES ( ?,? ) ]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

看起來是權限問題，請教同事，給了一份增加表格的sample：

--执行备注：
--AS CN AU AM EU 五大区正式机建立此table

//1新建表格

CREATE TABLE CC.RM_WTP_IMEI_STOCK
(
  IMEI           VARCHAR2(20 BYTE) ,
  IMEIPN           VARCHAR2(32 BYTE) ,
  MODEL            VARCHAR2(30 BYTE) ,
  SKU              VARCHAR2(24 BYTE) ,
  APPLY_NO         NUMBER(38,0) ,
  RMA_NO           VARCHAR2(12 BYTE) ,
  ISN              VARCHAR2(42 BYTE) ,
  APPLY_DATE       DATE ,
  INJECT_STATUS    VARCHAR2(15 BYTE)  ,
  RETURN_FLAG      VARCHAR2(5 BYTE)  ,
  CREATE_BY     VARCHAR2(36 BYTE) ,
  CREATE_DATE      DATE
)
TABLESPACE TP
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

2、表格中的內容備註

COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.IMEI IS 'IMEI 15码';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.IMEIPN IS 'IMEI PN';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.MODEL IS 'IMEI MODEL';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.SKU IS 'IMEI usage country/region';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.APPLY_NO IS 'IMEI对应RMA单的APPLY_NO';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.RMA_NO IS 'IMEI对应RMA单号';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.ISN IS 'IMEI ISN';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.APPLY_DATE IS 'IMEI apply申请使用date';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.INJECT_STATUS IS 'IMEI配给后使用状态';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.RETURN_FLAG IS 'IMEI使用后回传总表状态';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.CREATE_BY IS '创建人';
COMMENT ON COLUMN CC.RM_WTP_IMEI_STOCK.CREATE_DATE IS '创建时间';
COMMENT ON TABLE  CC.RM_WTP_IMEI_STOCK  IS 'IMEI STOCK存量表';

3、非必須內容

  
CREATE UNIQUE INDEX CC.RM_WTP_IMEI_STOCK_PK ON CC.RM_WTP_IMEI_STOCK
(IMEI)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

ALTER INDEX CC.RM_WTP_IMEI_STOCK_PK
  MONITORING USAGE;

 
 
CREATE INDEX CC.RM_WTP_IMEI_STOCK_INDX ON CC.RM_WTP_IMEI_STOCK
(APPLY_NO,ISN)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

ALTER INDEX CC.RM_WTP_IMEI_STOCK_INDX
  MONITORING USAGE;

4、同義查詢

CREATE OR REPLACE PUBLIC SYNONYM RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

CREATE OR REPLACE SYNONYM CSCADM.RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

CREATE OR REPLACE SYNONYM CSC.RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

CREATE OR REPLACE SYNONYM EREPAIR_RPT.RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

CREATE OR REPLACE SYNONYM EREPAIR.RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

CREATE OR REPLACE SYNONYM ESUPPORT.RM_WTP_IMEI_STOCK FOR CC.RM_WTP_IMEI_STOCK;

5、增加權限

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO CC_BASIC;

GRANT INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO CSCADMIN;

GRANT INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO CSCADM_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO ELM;

GRANT SELECT ON CC.RM_WTP_IMEI_STOCK TO EREPAIR_RPT;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO ESUPPORT;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.RM_WTP_IMEI_STOCK TO SYNC;

按照上面的sample改成：

CREATE TABLE CC.SSN_INFO
(
  NEW_SN VARCHAR2(50) NOT NULL,
  CHECKSUM VARCHAR2(20) NOT NULL
)

TABLESPACE TP
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN CC.SSN_INFO.NEW_SN  IS '新的SN';
COMMENT ON COLUMN CC.SSN_INFO.CHECKSUM  IS 'check number';
COMMENT ON TABLE  CC.SSN_INFO  IS '保存SN/CN';

CREATE OR REPLACE PUBLIC SYNONYM SSN_INFO FOR CC.SSN_INFO;

CREATE OR REPLACE SYNONYM CSCADM.SSN_INFO FOR CC.SSN_INFO;

CREATE OR REPLACE SYNONYM CSC.SSN_INFO FOR CC.SSN_INFO;

CREATE OR REPLACE SYNONYM EREPAIR_RPT.SSN_INFO FOR CC.SSN_INFO;

CREATE OR REPLACE SYNONYM EREPAIR.SSN_INFO FOR CC.SSN_INFO;

CREATE OR REPLACE SYNONYM ESUPPORT.SSN_INFO FOR CC.SSN_INFO;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.SSN_INFO TO CC_BASIC;

GRANT INSERT, SELECT, UPDATE ON CC.SSN_INFO TO CSCADMIN;

GRANT INSERT, SELECT, UPDATE ON CC.SSN_INFO TO CSCADM_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.SSN_INFO TO ELM;

GRANT SELECT ON CC.SSN_INFO TO EREPAIR_RPT;

GRANT DELETE, INSERT, SELECT, UPDATE ON CC.SSN_INFO TO ESUPPORT;

同時在代碼中使用：

INSERT INTO CC.SSN_INFO ( NEW_SN,CHECKSUM ) VALUES ( ?,? )，

OK，插入成功