This discussion is archived
1 Reply Latest reply: Mar 8, 2012 3:07 PM by 794126 RSS

Materialised view dropping

823423 Newbie
Currently Being Moderated
HI Team,

I got a requirement to delete sum data from a table.When am trying to delete the data from the table I got an error like below

"ORA-01732: data manipulation operation not legal on this view".


When I checked for that it is a materialised view and the table is the one which is created with the materialised view.(both mview and table with same name) So i thought of deleting the mview and to perform the operations on the table and to recreate the view again. But when I dropped the view ,I am not able to find the table now. When Am trying to recreate the mview, I am getting error like TNS entry could not be found.I have added it to TNS ora.But issue still persists the same.Before dropping I have taken DDL of it .Kindly let me know how to get the table back

Awaiting for your valuable reply.


CREATE MATERIALIZED VIEW "SIEBEL98"."ONR_PAYLOAD" TABLESPACE "ONROUTE_DATA" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE ( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING USING INDEX TABLESPACE "ONROUTE_INDX"

PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) REFRESH FAST ON

DEMAND AS SELECT "ONR_PAYLOAD"."ROW_ID" "ROW_ID","ONR_PAYLOAD"."CREATED" "CREATED","ONR_PAYLOAD"."CREATED_BY"

"CREATED_BY","ONR_PAYLOAD"."LAST_UPD" "LAST_UPD","ONR_PAYLOAD"."LAST_UPD_BY" "LAST_UPD_BY","ONR_PAYLOAD"."MODIFICATION_NUM"

"MODIFICATION_NUM","ONR_PAYLOAD"."CONFLICT_ID" "CONFLICT_ID","ONR_PAYLOAD"."CUST_ID" "CUST_ID","ONR_PAYLOAD"."CUST_ID_TYPE"

"CUST_ID_TYPE","ONR_PAYLOAD"."INV_ST_NUM" "INV_ST_NUM","ONR_PAYLOAD"."INVOICE_DT" "INVOICE_DT","ONR_PAYLOAD"."DOCUMENT_TYPE"

"DOCUMENT_TYPE","ONR_PAYLOAD"."SCAN_TYPE" "SCAN_TYPE","ONR_PAYLOAD"."SCAN_TIME" "SCAN_TIME","ONR_PAYLOAD"."FACILITY_ID"

"FACILITY_ID","ONR_PAYLOAD"."FAC_ID_TRANS" "FAC_ID_TRANS","ONR_PAYLOAD"."OPERA_CD" "OPERA_CD","ONR_PAYLOAD"."OP_CD_TRANS"

"OP_CD_TRANS","ONR_PAYLOAD"."POST_NET_CD" "POST_NET_CD","ONR_PAYLOAD"."PLANET_CD" "PLANET_CD","ONR_PAYLOAD"."SBL_INVOICE_KEY"

"SBL_INVOICE_KEY" FROM "ONR_PAYLOAD"@ONRDBP1.CT.PB.COM "ONR_PAYLOAD"


Regards,
Sushma.
  • 1. Re: Materialised view dropping
    794126 Newbie
    Currently Being Moderated
    Hi There,

    I may be wrong but, it looks like your MView SQL is incomplete.

    However, when you're using database links, you should take note of the connection string used for the dblink, normally a TNS alias, this alias is the one the Oracle Server process sees during DB initialization.

    Check the information on your dba_db_links view for all the DBLinks involved on your query, check that if a TNS alias was used (in host field), it exists on the tnsnames.ora file on the server, if you configure the TNS_ADMIN environment variable on the server, check the file on that route, if not, check in $ORACLE_HOME/network/admin, they should be the same.

    Regards!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points