Data dictinary for all tables

Posted on 2006-04-25
Last Modified: 2008-01-09
We have a system developed using Developer 2000 and it was developed by someone which no longer work with our department. The system work with Oracle8 and now we are thinking to migrate to Oracle 10G with new forms. I also want to trace back all tables that are used for this system so that I can redevelop new form (reverse engineering). I noticed that there are tables such as all_users, all_tables, all_views, all_constraint etc.... My question is, where can I find the data dictionary of those tables, describing every columns from every tables, what they are and what purposes. I have tried this sql command "select * from all_tables where owner = 'sam'; and this return all tables associated with this particular user.

Question by:KG1973
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    join ALL_TABLES with the ALL_TAB_COLUMNS to get the columns of the tables.

    LVL 142

    Accepted Solution

    LVL 2

    Assisted Solution

    DBA/ALL_TABLES       >     Description of all relational tables in the database
    DBA/ALL_TAB_COLUMNS     >  Columns of user's tables, views and clusters

    basically if u r only looking for columns info. DBA/ALL/USER_TAB_COLUMNS is sufficeint

    but if u want INDEXES & CONSTRAINTS too

    then u need to join all_indexes + all_ind_columns        > to get complete index info.
    and dba_constraints + dba_cons_columns                   > to get complete picture for constraints

    basically what I suggest over here that first u should check what you owner has, e.g.,

    select object_name, object_type, ... if u want some more
    from dba/all_objects          > according to ur privs
    where owner= 'SCOTT'

    this'll give u a complete picture of ur user ownership, then only u can start working,
    and one more Erwin is more appropriate to do reverse engineering.

    hope this helps!
    LVL 19

    Expert Comment

    also, you might need to check:

    to see all comments made before.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now