Lesson 11

I have to add a few columns to a table and I also need to add these columns to all the views that use this table.
Question: Is it possible to get a list of all the views in a database that use a certain table?
Solution 1:

SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%[YourTableName]%'

Solution 2:

SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION 
like '%YourTableName.%' OR 
VIEW_DEFINITION like '%YourTableName]%' OR 
VIEW_DEFINITION like '%YourTableName %' 

In the next module, you will learn about using views to show information in your tables in a slightly different manner. Views are important tools that can make using the information in your databases more straightforward, and can save you time and effort if you provide access to your database tables to other users.
When you CREATE OR REPLACE a PL/SQL program, the source code for that program along with other representations of that software is stored in the database itself and exposed through a wide range of data dictionary views. This is a tremendous advantage for two key reasons:

1. Information about that code is available to you via the SQL language.
   I can write queries and even entire PL/SQL programs to read the contents of these data dictionary views and obtain lots of fascinating and useful information about my code base.
2. The database manages dependencies between your stored objects
   For example, if a stored function relies on a certain table, and that table's structure is changed, the status of that function is automatically set to INVALID.
   Recompilation then takes place automatically when someone tries to execute that function.

This SQL interface to your code base allows you to manage your code repository running analyses on your code, documenting what has been written and changed, and so on.
The following sections introduce you to some of the most commonly accessed sources of information in the data dictionary.