1

I've tried to research about the subject, but it seems to be this error is more related to views and materialized views than to tables... Having attempted to solve it by my own in vain, here's my simplified setup.

I've got a table DDL similar to the following:

  CREATE TABLE "TEST"."COUNTRY" 
   (    "ID_COUNTRY" NUMBER INVISIBLE NOT NULL ENABLE, 
    "COD_COUNTRY" CHAR(3 BYTE) DEFAULT NULL, 
    "DESC_COUNTRY" CHAR(3 BYTE) DEFAULT NULL,
    "ID_XYZ" CHAR(5 BYTE), 
     PRIMARY KEY ("ID_COUNTRY")
  TABLESPACE "MY_TBS"  ENABLE;


  GRANT DELETE ON "TEST"."COUNTRY" TO "USER01";
  GRANT INSERT ON "TEST"."COUNTRY" TO "USER01";
  GRANT SELECT ON "TEST"."COUNTRY" TO "USER01";
  GRANT UPDATE ON "TEST"."COUNTRY" TO "USER01";

Trying to re-order the columns of the table with the invisible/visible method, I was able to do it easily in some tables as expected. Yet there are others I have trouble doing so that doesn't seem to have anything special or different to the others this method worked... Here's an example using the table I shared above:

ALTER TABLE COUNTRY MODIFY (COD_COUNTRY INVISIBLE, DESC_COUNTRY INVISIBLE)

Table COUNTRY altered.


ALTER TABLE COUNTRY MODIFY (COD_COUNTRY VISIBLE, DESC_COUNTRY VISIBLE)

Error report -
ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"
*Cause:    
*Action:

I'd like to be able to make those columns visible again and understand why I got this error about views even if I'm attempting these statements in a table.

Metafaniel
  • 29,318
  • 8
  • 40
  • 67
  • [Seems OK](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6baaec372ce74e42ef624de65abb2044), at least once the ID column is made visible. Wondered about a DDL trigger but I think you'd see more in the error. Can you recreate as a fiddle? – Alex Poole Feb 17 '22 at 16:56
  • @AlexPoole Thank you for your interest. I understand the error you've got in your fiddle. This is because I oversimplified my problem, so I got more columns. To avoid this in the example, I've added an extra column. Here's the error I got in a [DBFiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ada83594f701221d2d841366644be4a9) as you requested. In the fiddle, I know if I click on `add batch` and cut there the visible statement it will work. But in my worksheet in SQL Developer, I have the visible statement alone and I got the error. Any ideas? – Metafaniel Feb 17 '22 at 17:50
  • That's a different error though - ORA-01735, not ORA-01732 - from having the two `alter` statements in one textbox (so treating as a single statement); [it's OK as two](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=705f3caf215dffe74744564fee78171f); as you seem to know. So I don't know how to recreate or explain what you're seeing, I'm afraid. I can't see anything similar in My Oracle Support either. – Alex Poole Feb 17 '22 at 18:06
  • Maybe something about `tablespace`? It can't be tested in the fiddle, right? – Metafaniel Feb 17 '22 at 18:20
  • You can specify the `USERS` tablespace, but that's the only one available, yes. I don't see why that (or the grants) would matter - though as something weird seems to be happening I guess they *might*... It might be worth ruling out a DDL trigger, or even DDL auditing. – Alex Poole Feb 17 '22 at 18:25

0 Answers0