• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1526
  • Last Modified:

check table exists in oracle

is there a command in oracle to check if table already exists before creating a new table name - can this statement be in create like in create or replace objects (procedure or functions) is this simiar to creating or replace table if already exists?
0
mahjag
Asked:
mahjag
  • 6
  • 3
1 Solution
 
sdstuberCommented:
there is no create or replace for tables, you'll have to do that check yourself.

you can select for the table first though...


select * from dba_tables where owner = 'YOUR_OWNER' and table_name = 'YOUR_TABLE';

or, if it will be your own table....

select * from user_tables where table_name = 'YOUR_TABLE';
0
 
sdstuberCommented:
you could also try querying all_tables, but if you don't have access to the table you won't see it but you won't be able to create it either
0
 
karunamoorthyCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
karunamoorthyCommented:
or you can try
select table_name from tab where table_name = 'blah'
select table_name from cat where table_name = 'blah'
0
 
karunamoorthyCommented:
CREATE TABLE IF EXISTS MyTable (..);
0
 
sdstuberCommented:
definitely don't use the "select xxxx from your_table"  methods.

if you don't have access the error is "table or view does not exist"
so, you can get a false negative

to check for existence, you must use the data dictionary which is what the dba_tables and user_tables queries posted above (and inside the link) do.

the "desc"  keyword is a sql*plus command so it's only relevant inside that tool or other tools that support sql*plus commands (like Toad)  but all "describe" does is issue commands against the data dictionary for you.
0
 
sdstuberCommented:
this isn't legal syntax in Oracle

CREATE TABLE IF EXISTS MyTable (..);

0
 
mahjagAuthor Commented:
I get missing or invalid option when I run using if exists clause in oracle ORA-00922
0
 
sdstuberCommented:
create table if exists  is not legal syntax
0
 
sdstuberCommented:
you have to check before hand yourself using one of the data dictionary queries above
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now