Table Name:- Silly But Interesting

Today happened a silly but little interesting incident with a table name. Non-Partitioned table OUT_OF_BOX was to be converted into a partitioned table.A friend of mine took the export backup of the non-partitioned, renamed it to OUT_OF_BOX_old (using toad schema browser), created the new partitioned table with the same name OUT_OF_BOX as previous one and imported the data.

Till here everything was successful, but the moment he fired

test@TEST> select count(1) from out_of_box_old;
select count(1) from out_of_box_old
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Hmmm...where did the table go???it was renamed successfully then why is it showing "table or view does not exist".

01:01:27 system@TEST>select owner,segment_name,segment_type from dba_segments where segment_name like 'OUT_OF_BOX_o%';

OWNER                           SEGMENT_NAME                           SEGMENT_TYPE                                                                           
------------------------------ ----------------------------------- ------------------                                                                     
TEST                            OUT_OF_BOX_old                           TABLE                                                                                  

1 row selected.

This shows the table exists, then how come select is giving "table or view does not exist".So in this case

test@TEST>select count(1) from "OUT_OF_BOX_old";

 COUNT(1)
----------
 14222655

1 row selected.

The double quote is important.By default,Oracle save the names of all database objects , everything in CAPITAL letters if  " "(double quotes) are not given.Now when my friend renamed the table in TOAD using the schema browser section the below query  ran in  background :-

rename "OUT_OF_BOX" to "OUT_OF_BOX_old";

cause of which the oracle stored the word "old" in small letters and rest (OUT_OF_BOX)  in caps and when oracle would be searching for out_of_box_old name used in the query it would be searching as "OUT_OF_BOX_OLD" and couldn't find it (cause it is stored as "OUT_OF_BOX_old" ) so the error popped up.

So be careful while renaming the table using TOAD :)

About these ads
  1. November 10th, 2009

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: