In
previous post I talked about how to use the
DBMS_METADATA.GET_DDL() function to obtain the data definition of tables and indexes. But as you know there are a few more types of object which are considered as important as table, like user-defined function, trigger and notably stored procedure. How do we actually retrieve the source code of those? By selecting the data from the view
DBA_SOURCE.
DBA_SOURCE, the view that returns the source of all stored objects in the database. For example, you have a procedure named "MyProcedure", simply execute the query:
SELECT text FROM dba_source WHERE name='MyProcedure'
returns you the source of your procedure "MyProcedure". What if you want to retrieve all the source of the objects owned by you? Then you can do this:
SELECT text FROM dba_source WHERE owner=<your database login>
which equals to:
SELECT text FROM user_source
In fact
USER_* are those views that work specifically for the current database user, which save your time of filtering out the objects you probably don't need. In a nutshell, below are the views you might find useful:
USER_TABLES - information about your tables
USER_INDEXES - information about your indexes
USER_SOURCE - information about your stored objects
USER_VIEWS - information about your views
Is there more? Absolutely, see all by
SELECT * FROM dict
