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

[email protected]> 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 [email protected]>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

[email protected]>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 🙂