can not update user_sdo_geom_metadata when creating spatial database in oracle

Question

i was using the oracle 11g and try to create a spatial database, i just copied the sample code from oracle document but when it comes to update the metadata part, it gave a duplicate entry error, and i tried delete from user_sdo_geom_metadata and it didnot give any error, and then tried to insert again, still got the duplicate entry error. I also tried select * from user_sdo_geom_metadata but got nothing. any one have idea why? thanks

THE sample code:

CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);

INSERT INTO cola_markets VALUES(
1,
'cola_a',
SDO_GEOMETRY(
  2003,  -- two-dimensional polygon
  NULL,
  NULL,
  SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
  SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
      -- define rectangle (lower left and upper right) with
      -- Cartesian-coordinate data
));

INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
 COLUMN_NAME,
 DIMINFO,
 SRID)
VALUES (
'cola_markets',
'shape',
SDO_DIM_ARRAY(   -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
 ),
NULL   -- SRID
);

error cause: there are duplicate entries for the give table and column value pair in the user_sdo_geom_metadata view.

Answer 1

Just to be sure, what does the following show ?

SELECT * FROM USER_SDO_GEOM_METADATA; 

Object names in dictionary views are stored in uppercase. So if you want to remove existing entries from the spatial metadata you need to do this (notice the uppper case table name):

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'COLA_MARKETS';
COMMIT;