0

I am not able to drop a table from my Database.

For the query select owner from ALL_TABLES where TABLE_NAME ='db_schema_version'; I see the result as OWNER_FC

For the query show user; I see the result USER is "OWNER_FC"

But when I try to drop the table using the query drop table db_schema_version cascade constraints; then I get the below error:

drop table db_schema_version cascade constraints
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Since I am the owner I dont understand why I am not able to drop the table? I think this table is created by Flyway but I am not sure if that is relevant information here

firstpostcommenter
  • 2,328
  • 4
  • 30
  • 59
  • https://stackoverflow.com/a/13015653/6700081 – firstpostcommenter Oct 02 '20 at 10:25
  • If `where TABLE_NAME ='db_schema_version'` indeed returns something, then you need `drop table "db_schema_version";` - `db_schema_version` is a different name than `"db_schema_version"` –  Oct 02 '20 at 10:25
  • I tried with double quotes and got the same error. I dont think query syntax is the issue because I was able to delete other tables. only this table is giving problem – firstpostcommenter Oct 02 '20 at 10:38
  • That answer you link to in your first comment only works with upper-case table names. If the table name is lower-case (or starts with a number or is a keyword) then it will fail and you need to use double-quotes around the case-sensitive identifier (as per the accepted answer in that linked question). – MT0 Oct 02 '20 at 10:49

1 Answers1

1

For the query

select owner from ALL_TABLES where TABLE_NAME ='db_schema_version';

I see the result as OWNER_FC

The table name is in lower-case in the data dictionary. This means you need to provide the table name in lower-case in your query, for which you need to surround it in double-quotes, and you may need to specify the schema:

DROP TABLE "db_schema_version" CASCADE CONSTRAINTS;

or

DROP TABLE OWNER_FC."db_schema_version" CASCADE CONSTRAINTS;
MT0
  • 143,790
  • 11
  • 59
  • 117