finding out the metadata and joins Suppose I have a database with a large number of tables. 


Is there any command that I can type in PL/SQL which display only the field names of a given table? 
Given any table is there any command that I can type that would show all the relations that it has with other tables?


For example , I have a table "TAB_1" , can I type a command that would show all the tables related to and for any table "TAB_2" that it is related to it would show which field in "TAB_1" corresponds to which field in "TAB_2 " .
You can query the system views and look at metadata.


SYS.ALL_OBJECTS gives you a list of all the objects that are accessible by the user
SYS.DBA_OBJECTS is similar and list all the object in the database 
SYS.USER_OBJECTS lists all the object owned by the user


You will get a list of tables using this view:


SYS.ALL_TABLES = tables accessible by the user
SYS.DBA_TABLES = all tables in the database
SYS.USER_TABLES = tables owned by the user.


For a list of columns, you can join the TABLES list with the column views:


Table Columns SYS.ALL_TAB_COLUMNS, SYS.DBA_TAB_COLUMNS and SYS.USER_TAB_COLUMNS


There are similar views for:


Constraints = SYS.ALL_CONSTRAINTS, SYS.DBA_CONSTRAINTS and SYS.USER_CONSTRAINTS
Columns used by constraints = SYS.ALL_CONS_COLUMNS, SYS.DBA_CONS_COLUMNS and SYS.USER_CONS_COLUMNS


You can then join some of them and write such a query for a list of columns involved in referential integrity contraints:

SELECT ...
FROM SYS.ALL_CONS_COLUMNS col
JOIN SYS.ALL_CONSTRAINTS con con ON col.owner = con.owner AND col.constraint_name = con.constraint_name
JOIN SYS.ALL_CONS_COLUMNS fk on fk.owner = con.owner and fk.r_constraint_name = con.constraint_name
WHERE con.constraint_type = 'R' AND col.table_name = '...'


With R being (referential integrity) for constraint_type.