Unable to rename column-CodePudding

I am attempting to rename a column on a table which it looks like someone misspelled, but Oracle doesn't like the command I am sending.

ALTER TABLE VW_SUBSTANCE_FULL RENAME COLUMN SV_CHARATERISTICS TO SV_CHARACTERISTICS;

Error report -
ORA-23291: Only base table columns may be renamed
23291. 00000 -  "Only base table columns may be renamed"
*Cause:    Tried to rename a column of a non-base table, like object table/
           nested table/ materialized view table.
*Action:   None. This is not allowed.

Obviously it is not allowed, but I've researched and can't find the alternative.

Here is the table SQL:

  CREATE TABLE "M_INFO"."VW_SUBSTANCE_FULL" 
   (    "SUBSTANCE_ID" NUMBER(20,0), 
    "BARCODE" VARCHAR2(765 BYTE), 
    "BCODE" VARCHAR2(765 BYTE), 
    "LOT" NUMBER(10,0), 
    "FW" NUMBER(28,6), 
    "CORE_MOLECULAR_WEIGHT" NUMBER(28,6), 
    "EXACT_MASS" NUMBER(28,6), 
    "SV_CHARATERISTICS" VARCHAR2(720 BYTE), 
    "PROJECT" VARCHAR2(765 BYTE), 
    "VENDOR_CAT_ID" VARCHAR2(765 BYTE), 
    "REGISTRATION_DATE" DATE, 
    "EXTERNAL_CODE" VARCHAR2(720 BYTE), 
    "COMMON_NAME" VARCHAR2(765 BYTE), 
    "SCAFFOLD" VARCHAR2(765 BYTE), 
    "SUBSCAFFOLD" VARCHAR2(765 BYTE), 
    "CRO_CODE" VARCHAR2(720 BYTE)
   ) 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 "M_INFO_D" ;

Any assistance or direction is appreciated.

Attempted to rename a column, but Oracle does not like that action.

CodePudding user response：

If VW_SUBSTANCE_FULL is actually a materialized view, as the name hints and the error suggests, then you can't just change the name. The materialized view will need to be recreated.

You could potentially drop the view while retaining the base table, rename the column in the base table as you'd already attempted, and then recreate the view using the existing table. That might save a bit of rebuild time.

I'd previously said you could change the target of the rename from the base table to the view itself:

ALTER MATERIALIZED VIEW VW_SUBSTANCE_FULL RENAME COLUMN SV_CHARATERISTICS TO SV_CHARACTERISTICS;

... but that doesn't work in Oracle; it seems to in PostgreSQL, which doesn't help.