data dictionary 1

[jjpetrucelli]

what script can i use to provide a complete data dictionary?

 

[SantaMufasa]

JJ,

Sorry, you need to be a bit more specific...What precisely do you mean by, "What script can i use to provide a complete data dictionary?"

Do you mean:

1) I want to "display" the entire contents of the data dictionary?
2) I want to "re-create on another Oracle database" the entire contents of the data dictionary?
3) I want to "establish" a complete data dictionary where one does not exist?

et cetera.

Please advise us of your objective.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:35 (10Aug04) UTC (aka "GMT" and "Zulu"), 12:35 (10Aug04) Mountain Time)

 

[jjpetrucelli]

this would be to create a document for analasys

 

[SantaMufasa]

JJ,

Most of Oracle's data dictionary takes the form of views (owned by SYS) that query against underlying tables (also owned by SYS). By and large, meaningful data dictionary objects begin with the prefixes "DBA_..." or "V$...". (Subsets of the "DBA_..." views are: "USER_...", objects that the querying user owns; and "ALL_...", objects to which the querying user has access.)

There are other important queriable objects that comprise the data dictionary beside those that begin with the prefixes, above. Generally, any object the SYS owns and results from a database create (including the database utility scripts such as "catalog", "catproc", "catexp", et cetera) comprises the data dictionary. All of the resulting tables and views that SYS owns you can query if you are a DBA or you have SELECT privileges on those objects.

If you wish to formulate a list of all the tables and views that comprise the data dictionary, here is code:

select object_name from dba_objects
where owner = 'SYS' and object_type in ('TABLE','VIEW');

Be prepared for a list of 1,300+ objects.

For your "analysis" purposes, you will probably not need data from more than just a few of the 1,300+ objects. To see the types of columns that reside in each of the data dictionary objects, you can use the SQL*Plus "DESCRIBE <owner>.<object>" command, where <owner> = 'SYS' and <object> = some table or view that SYS owns.

You can create a SQL script to describe those objects:

set echo off
set trimspool on
set linesize 100
set pagesize 0
set feedback off
spool TempDescDD.sql
prompt Set echo on
select 'desc sys.'||object_name
from dba_objects
where owner = 'SYS' and object_type in ('TABLE','VIEW')
order by object_name;
spool off
set feedback on
@TempDescDD

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:30 (10Aug04) UTC (aka "GMT" and "Zulu"), 14:30 (10Aug04) Mountain Time)

 

[jjpetrucelli]

sorry for not being specific enough, im going to need the each table, all of the columns, the datatype, and constraints. For the constraints i am going to need to know the parent for any FK. I can handle everything besides showing that parent table , where is that info held?

 

[SantaMufasa]

JJ,

If what you want is the equivalent of a SQL*Plus "Describe" plus a listing of each constraint, including PKs and FKs and their specific enforcement, then you might like the following. (Although I could have written it in less code using PL/SQL, the original requester wanted it done all in SQL.):

Section 1 -- Sample invocation and output (that describes Oracle Education's "S_EMP" table):

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER(7)     [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER(7)     [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER(7)     [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER(11,2)

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
 11 COMMISSION_PCT     NUMBER(4,2)   [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?

Section 2 -- One-time-only script (save as "StruMake.sql") that CREATEs a utility table and a procedure that the "always-run" script needs:

REM **********************************************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages-LLC makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **********************************************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(2000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
  is
  cursor cons is
     select constraint_name,constraint_type,
            search_condition,r_constraint_name, status
            from user_constraints
            where table_name = upper(table_2_do);
  my_search_condition varchar(32767);
begin
  delete temp_cons;
  commit;
  for i in cons loop
     my_search_condition := i.search_condition;
     insert into temp_cons values
       (i.constraint_name,i.constraint_type,
        my_search_condition,i.r_constraint_name,i.status);
     commit;
  end loop;
end;

Sectioon 3 -- Code that displays structure and constraints (Save as "Stru.sql"):

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 80
set pagesize 23
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

Let us know if this solves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:15 (13Aug04) UTC (aka "GMT" and "Zulu"), 15:15 (13Aug04) Mountain Time)

 

[jjpetrucelli]

as usual!