Analysing the structure of our Oracle DB???

[eo]

I had to do this previously with SQL, and there was a convenient free tool that I used.
We have a number of Oracle DB'es, and some "bright spark" went and designed one of them using only numeric references for each table. It is this a "mamoth" task, every time you want to interrogate the database. The SQL solution previously used, simply connected to the database, and provided a full list of each table, and the fields contained within it. Any idea how this can be done within the Oracle Env?

EO
Hertfordshire, England

 

[SantaMufasa]

Sure, EO, Oracle can do this probably better than any other database platform. Please post a sample of the type of information that you would like to see for each table and whether you want this information on an single-user/schema basis or by schema for the entire Oracle installation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[eo]

Hi Mufasa

I would like to see only the basic layout. I hope and trust this was what you were after:

Via ODBC I connect to a database called "AFBEPROD" - In the "DEV" database within here there are around a 100 tables. 50% of which have been named descriptively like "Nominal_Extracts", "Nominal_History", "Risk_Extracts", etc. No problem here. But the remaining 50% have simply been named non-descriptively, such as "F03201", "F001", "F059", etc. I have to trawl throuigh all of them to see what they are made up of. So what would help is to see their structure (fields within the tables), for example:

F123 =
ACC_SET_NO
SET_STAT_ST_DT
STAT_CODE_1
STAT_CODE_2
etc

Can you help?

EO
Hertfordshire, England

 

[LKBrwnDBA]

1) You can use DESCRIBE <tableName>

2) You can reverse engineer the database with DDL Wizard (Free).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
You can use any report writer ( or even Access) to create a report on the all_tab_columns view..
That way you can document ( for future DBAs) the tables and determine if you want to rename them ( or create descriptive synonyms for them) ..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

And you can use my "Super-Describe" (sample output and code, below) that prints out a table's columns, their data types, any declared constraints, and what the constraints enforce (including Primary Keys, Unique Keys, Foreign Keys, and Check constraints including NOT NULLs). With a slight modification, you can have the script run (and print) for every table in a schema, then run it again for other schemas in which you are interested.

Following is a sample invocation of my "stru.sql" (Super-Structure) script:

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) ?
*********************************************************************************

To use my "stru.sql" script, you must first run a one-time-only script named "strumake.sql": this script creates a work table and a PL/SQL procedure that "stru.sql" uses.

One-time "strumake.sql" script:

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;
/

Regular-use script, "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 these scripts provide you with the results you needed.

If you are interested, I also have another script (PK_FK.sql) that prints our a hierarchical (text) diagram of all the Parent-Child relationships amongst your tables (provided that declared Primary Keys and Foreign Keys exist on each table).

Let me know if you want the PK_FK.sql script.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.