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 🙂
One thought on “Table Name:- Silly But Interesting”