The most seen format of tables in Oracle are the general tables, views, synonyms etc . But we alos have others like _B,__TL and _VL which have their own specific operation and usage. Let’s see what they actually define within them and how they are different from general tables,views etc.
_B these are the BASE tables.
- They are very important and the data is stored in the table with all validations.
- It is supposed that these tables will always contain the perfect format data.
- If anything happens to the BASE table data, then it is a data corruption issue.
_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
What does this mean, let get into details with an example.
Let’s say we have a record in BASE table with some name as TABLENAME_B. This can have multiple records in TABLENAME_TL table with LANGUAGE column different for each record.
Now how does TABLENAME_VL come into usage is when a user runs a select on this table only one record is displayed based on his language setting if at all that language exists in the TABLENAME_TL table.
Let’s say we have a record in BASE table with some name as TABLENAME_B. This can have multiple records in TABLENAME_TL table with LANGUAGE column different for each record.
Now how does TABLENAME_VL come into usage is when a user runs a select on this table only one record is displayed based on his language setting if at all that language exists in the TABLENAME_TL table.
If you want to see the records, you have to change the NLS_LANGUAGE parameter as per the language availability in _TL tables.
