---------------------------------------------------
sqlplus d0read/reader@d0ofprd1
sqlplus d0read/reader@d0ofdev1

set pagesize 2000;
set linesize 256;
set heading on;
set echo on;
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
July 2004
This section looks at the views owned by RUNS to try to get the
source for the view definition for runs.RUN_LUM_V and runs.RUN_QUAL_V
and how they are populated.

desc runs.RUN_LUM_V
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 RUN_NUMBER                                NOT NULL NUMBER(38)
 BEGIN_LBN                                 NOT NULL NUMBER(38)
 END_LBN                                   NOT NULL NUMBER(38)

desc runs.RUN_QUAL_V
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 RUN_NUMBER                                         NUMBER(38)
 MUO_QUALITY                                        VARCHAR2(20)
 MET_QUALITY                                        VARCHAR2(20)
 JET_QUALITY                                        VARCHAR2(20)
 SMT_QUALITY                                        VARCHAR2(20)
 CFT_QUALITY                                        VARCHAR2(20)
 CAL_QUALITY                                        VARCHAR2(20)
 CTT_QUALITY                                        VARCHAR2(20)

desc all_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

desc all_source;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

column OWNER  format a10;
column created format a20;
column OBJECT_NAME format a20;
column OBJECT_TYPE format a10;
select OBJECT_NAME,OBJECT_TYPE,CREATED,owner from all_objects 
	where owner='RUNS';
select OBJECT_NAME,OBJECT_TYPE,CREATED,owner from all_objects 
	where OBJECT_TYPE='VIEW'
	and   owner='RUNS';

RUN_LUM_V            VIEW       27-JAN-04            RUNS
RUN_QUAL_V           VIEW       27-JAN-04            RUNS

column OWNER  format a10;
column NAME   format a10;
column TYPE   format a10;
column LINE   format 999;
column TEXT   format a100;

select OWNER, NAME, TYPE, LINE, TEXT from all_source
	where owner='RUNS';

It's not here. Anil says look in ALL_VIEWS
desc all_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)


column OWNER  format a10;
column VIEW_NAME  format a10;
column TYPE_TEXT  format a10;
column OID_TEXT  format a10;
column VIEW_TYPE_OWNER  format a10;
column VIEW_TYPE        format a10;
column SUPERVIEW_NAME  format a10;

column TEXT_LENGTH format 9999;
column TYPE_TEXT_LENGTH  format 9999;
column OID_TEXT_LENGTH  format 9999;

select * from ALL_VIEWS 
	where owner='RUNS';
select count(*) from ALL_VIEWS 
	where owner='RUNS';
  COUNT(*)
----------
         2

set long 100000;
select  OWNER,VIEW_NAME,TEXT_LENGTH,TYPE_TEXT_LENGTH, 
	TYPE_TEXT, OID_TEXT_LENGTH,OID_TEXT,VIEW_TYPE_OWNER,VIEW_TYPE
	from ALL_VIEWS 
	where owner='RUNS';
select  OWNER,VIEW_NAME,TEXT_LENGTH, TEXT
	from ALL_VIEWS 
	where owner='RUNS';


OWNER      VIEW_NAME  TEXT_LENGTH TEXT
---------- ---------- ----------- --------------------------------------------------------------------------------
RUNS       RUN_LUM_V          193 SELECT a.run_number
                                         , a.lbn
                                         , b.lbn
                                  from runs.run_lums a
                                        ,runs.run_lums b
                                  where a.run_number = b.run_number
                                       and a.flag = 'BEGIN'
                                       and b.flag = 'END'
                                  WITH READ ONLY

RUNS       RUN_QUAL_V         883 SELECT muo.run_number
                                         , muo.quality
                                         , met.quality
                                         , jet.quality
                                         , smt.quality
                                         , cft.quality
                                         , cal.quality
                                         , ctt.quality
                                  from runs.run_quality muo
                                        ,runs.run_quality met
                                        ,runs.run_quality jet
                                        ,runs.run_quality smt
                                        ,runs.run_quality cft
                                        ,runs.run_quality cal
                                        ,runs.run_quality ctt
                                  where muo.run_number = met.run_number(+)
                                       and muo.run_number = jet.run_number(+)
                                       and muo.run_number = smt.run_number(+)
                                       and muo.run_number = cft.run_number(+)
                                       and muo.run_number = cal.run_number(+)
                                       and muo.run_number = ctt.run_number(+)
                                       and 'MUO' = muo.qual_group(+)
                                       and 'MET' = met.qual_group(+)
                                       and 'JET' = jet.qual_group(+)
                                       and 'SMT' = smt.qual_group(+)
                                       and 'CFT' = cft.qual_group(+)
                                       and 'CAL' = cal.qual_group(+)
                                       and 'CTT' = ctt.qual_group(+) WITH READ ONLY


-- From these TEXT colums, I (EG) was able to reconstruct
-- the view definitions (see lum_qual_handwritten.vw) - 8-jul-2004

----------------------------------------------------------------------------------
-- list all tables owned 

column OBJECT_NAME format a30;
column OBJECT_TYPE format a30;

select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects;
select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='TABLE';
select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='SEQUENCE';
select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='TRIGGER';
select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='INDEX';
select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='INDEX'
	and OBJECT_NAME like '%_UK';


set pagesize 1600;
set linesize 256;
column owner format a15;
column table_name format a20;
column column_name format a18;
column comments format a100;

select * from  all_col_comments where owner='LUMINOSITY'
	order by TABLE_NAME;


select OBJECT_NAME,OBJECT_TYPE,CREATED from user_objects 
	where OBJECT_TYPE='TABLE';

select OBJECT_NAME,OBJECT_TYPE,CREATED from all_objects 
	where OBJECT_TYPE='TABLE' and owner='LUMINOSITY';

-- Here's the tables initially filled by sqlldr files:
select count(*) from TICKS                  ;
       160
select count(*) from EXPOSURE_GROUPS        ;
         8
select count(*) from LM_TYPES               ;
         8
select count(*) from QUAL_FLAGS             ;
         5
select count(*) from QUAL_GROUPS            ;
         8
select count(*) from QUAL_USERS             ;
         7


-- Here's the tables initially not filled:

select count(*) from DELIVERED_LUMS         ;
select count(*) from EXPOSED_FRACTIONS      ;
select count(*) from EXPOSED_LUMS           ;
select count(*) from LBNS                   ;
select count(*) from LBN_FACTORS            ;
select count(*) from LBN_QUALITIES          ;
select count(*) from LBN_QUALITY_PARAMS     ;
select count(*) from LBN_STREAMS            ;
select count(*) from LBN_TRIGGERS           ;
select count(*) from LM_RATES               ;
select count(*) from PRD_FILE_CHKS          ;
select count(*) from RANGED_FACTORS         ;
select count(*) from STATUS_EXPLAIN_LBNS    ;
select count(*) from STATUS_EXPLAIN_LFS     ;
select count(*) from STATUS_EXPLAIN_LSS     ;
select count(*) from STATUS_EXPLAIN_LTS     ;
select count(*) from STATUS_EXPLAIN_PFCS    ;
select count(*) from STATUS_EXPLAIN_RFS     ;
select count(*) from STATUS_EXPLAIN_TKS     ;
select count(*) from STATUS_EXPLAIN_TRS     ;
select count(*) from STATUS_TO_LBNS         ;
select count(*) from STATUS_TO_LFS          ;
select count(*) from STATUS_TO_LSS          ;
select count(*) from STATUS_TO_LTS          ;
select count(*) from STATUS_TO_PFCS         ;
select count(*) from STATUS_TO_RFS          ;
select count(*) from STATUS_TO_TKS          ;
select count(*) from STATUS_TO_TRS          ;
select count(*) from TRANSITION_RECORDS     ;

-- select in the order in Heidi's spreadsheet

select count(*) from LBNS                   ;
    237824
select count(*) from DELIVERED_LUMS         ;
   2783214
select count(*) from EXPOSED_FRACTIONS      ;
  19757417
select count(*) from EXPOSED_LUMS           ;
  16766251
select count(*) from LBN_TRIGGERS           ;
   9220813
select count(*) from LM_RATES               ;
   8349520

---------------------------------------------------------------
-- Sept 23, 2004
-- I will delete all groups and users to test the sqlldr files with fpd

desc QUAL_USERS
column UNAME      format a20;
column QUAL_GROUP format a20;
select UNAME,qual_group from QUAL_USERS;

-- delete from qual_users;
-- sqlldr luminosity/luminosity@d0ofdev1 

desc  QUAL_GROUPS            ;
column GID      format a20;
column GID_DESC format a30;
select GID,GID_DESC from  QUAL_GROUPS            ;
