Create a Case Sensitive Table in Oracle / Table Name in Lower Case

In the same oracle schema you can create 2 tables with same name one is in small case and second one with upper case. By default all the DML, DDL command applicable to the table name with upper case. If you wanted to access the table with lower case you have to specify the table name with double quote (“)

See some examples here.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE 

SQL> create table "dept" as select * from dept; 

Table created. 

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
dept                           TABLE

10 rows selected.

SQL> select count(*) from dept; 

  COUNT(*)
----------
         4
SQL> delete from "dept" where rownum<2 span="">

1 row deleted.

SQL> select count(*) from dept;

  COUNT(*)
----------
         4

SQL> select count(*) from "dept";

  COUNT(*)
----------
         3
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google