Does the oracle view use a table?

Cause:

1. incomplete documentation

2. Years of forgetting

3. Employee resignation

Result: A table (XZF_CASE_DISCUSS) does not know what the table is used in the view. Is it a waste table? You cannot view the view one by one!

Method: Of course not required. oracle provides a variety of data registration forms and views, and all user objects can be queried. For example, if we want to query a view, we will involve a table: user_views.

select * from user_views;

The TEXT field stores the statements generated by the view. You only need to traverse these statements and check whether the statements contain the target table name to find out where the table is used.

select instr(text,'XZF_CASE_DISCUSS',1,1) from user_views;

This seems to solve the problem, but I suddenly found that the TEXT field is of the LONG type, so we can only accept the error result:

I checked the data and found that the LONG type is not easy to operate. It is a data type not recommended by oracle. Although it is still in use, it is estimated that it will expire soon. Here is an improvement method:

Create TABLE testBak (col1 clob);  insert into testBak select text from user_views;

In this way, the TEXT field of the LONG type is converted to the clob type, so we are familiar with the clob type. You can directly use the instr function to operate it.

select instr(col1,'XZF_CASE_DISCUSS',1,1) from  testBak 

It can be seen that all records are 0, indicating that the XZF_CASE_DISCUSS table has not been used in all views. Although it cannot be determined that the table is a waste table, it can be proved that there is no such thing in the view.

Summary: oracle is the most powerful relational database on the planet. If you have an idea, there is nothing impossible. As long as you are willing to move your mind and look up information, the solution will certainly be available.