sqlplus d0read/reader@d0oflum1

-----------------------------------------------------------------
-- look for begin/end LBNs in RUN_LUMS that are not in LBNS.

desc run_lums;
 LBN                                       NOT NULL NUMBER(38)
 FLAG                                      NOT NULL VARCHAR2(8)
 RUN_NUMBER                                NOT NULL NUMBER(38)
desc lbns;
 LBN                                       NOT NULL NUMBER(38)
 DURATION                                  NOT NULL NUMBER
 BEAMX                                     NOT NULL NUMBER
 STATUS_ALL                                         VARCHAR2(200)
 STATUS_TK                                          VARCHAR2(200)
 STATUS_LBN                                         VARCHAR2(200)
 STATUS_LT                                          VARCHAR2(200)
 STATUS_PFC                                         VARCHAR2(200)
 STATUS_LS                                          VARCHAR2(200)
 STATUS_TR                                          VARCHAR2(200)
 STATUS_LF                                          VARCHAR2(200)
 STATUS_RF                                          VARCHAR2(200)
 MODIFY_DATE                                        DATE
 MODIFY_USER                                        VARCHAR2(30)
 FUZZY_TIME                                         DATE

select LBN-1 from run_lums ;
148980 rows selected.

select min(LBN) from run_lums;
        -1
select  min(LBN) from run_lums where LBN!=-1 ;
         0
select  min(LBN) from run_lums where LBN!=-1 and LBN!=0;
     53462
select max(LBN) from run_lums;
   4294937426
select max(LBN) from run_lums where LBN<4000000000;
   4609343

select count(*) from run_lums where LBN=-1 ;
       296
select count(*) from run_lums where LBN=0 ;
      1769
select count(*) from run_lums where LBN>4000000000;
     20108

select  min(LBN-1) from run_lums where LBN!=-1 and LBN!=0 and LBN<4000000000;
     53461

select RL.LBN-1 
	from run_lums RL
	where RL.LBN!=-1 and RL.LBN!=0 and RL.LBN<4000000000
	and exists
	(select LBNS.LBN from LBNS
	where LBNS.LBN=RL.LBN-1)
	;
104496 rows selected.

select RL.RUN_NUMBER,RL.LBN-1 
	from run_lums RL
	where RL.LBN!=-1 and RL.LBN!=0 and RL.LBN<4000000000
	and not exists
	(select LBNS.LBN from LBNS
	where LBNS.LBN=RL.LBN-1)
	;
45328 rows selected.



select RL.RUN_NUMBER,RL.LBN-1 
	from run_lums RL
	where RL.LBN!=-1 and RL.LBN!=0 and RL.LBN<4000000000
	and RL.RUN_NUMBER>=151817
	and exists
	(select LBNS.LBN from LBNS
	where LBNS.LBN=RL.LBN-1)
	;
103794 rows selected. - good match

select RL.RUN_NUMBER,RL.LBN-1 
	from run_lums RL
	where RL.LBN!=-1 and RL.LBN!=0 and RL.LBN<4000000000
	and RL.RUN_NUMBER>=151817
	and not exists
	(select LBNS.LBN from LBNS
	where LBNS.LBN=RL.LBN-1)
	;
2888 rows selected. - bad match

select RL.RUN_NUMBER,RL.LBN-1,RUNS.TRIG_CONFIG_NAME,RUNS.TRIG_CONFIG_VERS,RUNS.START_TIME
	from run_lums RL, ONL_RUNS RUNS
	where RL.LBN!=-1 and RL.LBN!=0 and RL.LBN<4000000000
	and RL.RUN_NUMBER>=151817
	and RUNS.RUN_NUMBER=RL.RUN_NUMBER
	and not exists
	(select LBNS.LBN from LBNS
	where LBNS.LBN=RL.LBN-1)
	;

set pagesize 256;
set LINESIZE 256;
COLUMN TRIG_CONFIG_NAME FORMAT A50;
COLUMN TRIG_CONFIG_VERS FORMAT A10;
COLUMN START_TIME FORMAT A15;
-----------------------------------------------------------------
-- I need to find the indexes

sqlplus d0read/reader@d0oflumd
spool look_lum_indexes-dev.out

sqlplus d0read/reader@d0oflump
spool look_lum_indexes-prd.out

set pagesize 100;
set echo on;

desc all_indexes;

select unique   INDEX_NAME              from  all_indexes where owner='LUMINOSITY'	order by  INDEX_NAME    ; 
select unique   OWNER                   from  all_indexes where owner='LUMINOSITY'	order by  OWNER    ; 
select unique   INDEX_TYPE              from  all_indexes where owner='LUMINOSITY'	order by  INDEX_TYPE    ; 
select unique   TABLE_OWNER             from  all_indexes where owner='LUMINOSITY'	order by   TABLE_OWNER   ; 
select unique   TABLE_NAME              from  all_indexes where owner='LUMINOSITY'	order by   TABLE_NAME   ; 
select unique   TABLE_TYPE              from  all_indexes where owner='LUMINOSITY'	order by  TABLE_TYPE    ; 
select unique   UNIQUENESS              from  all_indexes where owner='LUMINOSITY'	order by  UNIQUENESS    ; 
select unique   COMPRESSION             from  all_indexes where owner='LUMINOSITY'	order by  COMPRESSION    ; 
select unique   PREFIX_LENGTH           from  all_indexes where owner='LUMINOSITY'	order by    PREFIX_LENGTH  ; 
select unique   TABLESPACE_NAME         from  all_indexes where owner='LUMINOSITY'	order by   TABLESPACE_NAME   ; 
select unique   INI_TRANS               from  all_indexes where owner='LUMINOSITY'	order by  INI_TRANS    ; 
select unique   MAX_TRANS               from  all_indexes where owner='LUMINOSITY'	order by  MAX_TRANS    ; 
select unique   INITIAL_EXTENT          from  all_indexes where owner='LUMINOSITY'	order by INITIAL_EXTENT     ; 
select unique   NEXT_EXTENT             from  all_indexes where owner='LUMINOSITY'	order by   NEXT_EXTENT   ; 
select unique   MIN_EXTENTS             from  all_indexes where owner='LUMINOSITY'	order by   MIN_EXTENTS   ; 
select unique   MAX_EXTENTS             from  all_indexes where owner='LUMINOSITY'	order by   MAX_EXTENTS   ; 
select unique   PCT_INCREASE            from  all_indexes where owner='LUMINOSITY'	order by   PCT_INCREASE   ; 
select unique   PCT_THRESHOLD           from  all_indexes where owner='LUMINOSITY'	order by  PCT_THRESHOLD    ; 
select unique   INCLUDE_COLUMN          from  all_indexes where owner='LUMINOSITY'	order by   INCLUDE_COLUMN   ; 
select unique   FREELISTS               from  all_indexes where owner='LUMINOSITY'	order by  FREELISTS    ; 
select unique   FREELIST_GROUPS         from  all_indexes where owner='LUMINOSITY'	order by FREELIST_GROUPS     ; 
select unique   PCT_FREE                from  all_indexes where owner='LUMINOSITY'	order by  PCT_FREE    ; 
select unique   LOGGING                 from  all_indexes where owner='LUMINOSITY'	order by  LOGGING    ; 
select unique   BLEVEL                  from  all_indexes where owner='LUMINOSITY'	order by   BLEVEL   ; 
select unique   LEAF_BLOCKS             from  all_indexes where owner='LUMINOSITY'	order by  LEAF_BLOCKS    ; 
select unique   DISTINCT_KEYS           from  all_indexes where owner='LUMINOSITY'	order by  DISTINCT_KEYS    ; 
select unique   AVG_LEAF_BLOCKS_PER_KEY from  all_indexes where owner='LUMINOSITY'	order by   AVG_LEAF_BLOCKS_PER_KEY   ; 
select unique   AVG_DATA_BLOCKS_PER_KEY from  all_indexes where owner='LUMINOSITY'	order by  AVG_DATA_BLOCKS_PER_KEY    ; 
select unique   CLUSTERING_FACTOR       from  all_indexes where owner='LUMINOSITY'	order by  CLUSTERING_FACTOR    ; 
select unique   STATUS                  from  all_indexes where owner='LUMINOSITY'	order by  STATUS    ; 
select unique   NUM_ROWS                from  all_indexes where owner='LUMINOSITY'	order by  NUM_ROWS    ; 
select unique   SAMPLE_SIZE             from  all_indexes where owner='LUMINOSITY'	order by   SAMPLE_SIZE   ; 
select unique   LAST_ANALYZED           from  all_indexes where owner='LUMINOSITY'	order by   LAST_ANALYZED   ; 
select unique   DEGREE                  from  all_indexes where owner='LUMINOSITY'	order by  DEGREE    ; 
select unique   INSTANCES               from  all_indexes where owner='LUMINOSITY'	order by   INSTANCES   ; 
select unique   PARTITIONED             from  all_indexes where owner='LUMINOSITY'	order by  PARTITIONED    ; 
select unique   TEMPORARY               from  all_indexes where owner='LUMINOSITY'	order by  TEMPORARY    ; 
select unique   GENERATED               from  all_indexes where owner='LUMINOSITY'	order by   GENERATED   ; 
select unique   SECONDARY               from  all_indexes where owner='LUMINOSITY'	order by  SECONDARY    ; 
select unique   BUFFER_POOL             from  all_indexes where owner='LUMINOSITY'	order by BUFFER_POOL     ; 
select unique   USER_STATS              from  all_indexes where owner='LUMINOSITY'	order by  USER_STATS    ; 
select unique   DURATION                from  all_indexes where owner='LUMINOSITY'	order by DURATION     ; 
select unique   PCT_DIRECT_ACCESS       from  all_indexes where owner='LUMINOSITY'	order by PCT_DIRECT_ACCESS     ; 
select unique   ITYP_OWNER              from  all_indexes where owner='LUMINOSITY'	order by ITYP_OWNER     ; 
select unique   ITYP_NAME               from  all_indexes where owner='LUMINOSITY'	order by  ITYP_NAME    ; 
select unique   PARAMETERS              from  all_indexes where owner='LUMINOSITY'	order by  PARAMETERS    ; 
select unique   GLOBAL_STATS            from  all_indexes where owner='LUMINOSITY'	order by  GLOBAL_STATS    ; 
select unique   DOMIDX_STATUS           from  all_indexes where owner='LUMINOSITY'	order by  DOMIDX_STATUS    ; 
select unique   DOMIDX_OPSTATUS         from  all_indexes where owner='LUMINOSITY'	order by  DOMIDX_OPSTATUS    ; 
select unique   FUNCIDX_STATUS          from  all_indexes where owner='LUMINOSITY'	order by  FUNCIDX_STATUS    ; 
select unique   JOIN_INDEX              from  all_indexes where owner='LUMINOSITY'	order by  JOIN_INDEX    ; 
select unique   IOT_REDUNDANT_PKEY_ELIM from  all_indexes where owner='LUMINOSITY'	order by   IOT_REDUNDANT_PKEY_ELIM   ; 
select unique   DROPPED                 from  all_indexes where owner='LUMINOSITY'	order by DROPPED     ; 

desc all_ind_columns    

select unique INDEX_OWNER       from  all_ind_columns  where table_owner='LUMINOSITY'	order by  INDEX_OWNER    ; 
select unique INDEX_NAME        from  all_ind_columns  where table_owner='LUMINOSITY'	order by INDEX_NAME     ; 
select unique TABLE_OWNER       from  all_ind_columns  where table_owner='LUMINOSITY'	order by  TABLE_OWNER    ; 
select unique TABLE_NAME        from  all_ind_columns  where table_owner='LUMINOSITY'	order by TABLE_NAME     ; 
select unique COLUMN_NAME       from  all_ind_columns  where table_owner='LUMINOSITY'	order by COLUMN_NAME     ; 
select unique COLUMN_POSITION   from  all_ind_columns  where table_owner='LUMINOSITY'	order by COLUMN_POSITION     ; 
select unique COLUMN_LENGTH     from  all_ind_columns  where table_owner='LUMINOSITY'	order by COLUMN_LENGTH     ; 
select unique CHAR_LENGTH       from  all_ind_columns  where table_owner='LUMINOSITY'	order by CHAR_LENGTH     ; 
select unique DESCEND           from  all_ind_columns  where table_owner='LUMINOSITY'	order by DESCEND     ; 

spool off;
exit

-----------------------------------------------------------------
set linesize 256;
set pagesize 100;
column INDEX_NAME format a20;
column TABLE_NAME format a20;
column UNIQUENESS format a10;
column COLUMN_NAME format a20;
column COLUMN_POSITION format 99;

SELECT AI.INDEX_NAME,AI.TABLE_NAME,AI.UNIQUENESS,AIC.COLUMN_NAME,AIC.COLUMN_POSITION
from all_indexes AI, all_ind_columns AIC
where AI.owner='LUMINOSITY'
and AI.INDEX_NAME=AIC.INDEX_NAME
order by INDEX_NAME,COLUMN_POSITION; 

SELECT AI.INDEX_NAME,AI.TABLE_NAME,AI.UNIQUENESS,AIC.COLUMN_NAME,AIC.COLUMN_POSITION
from all_indexes AI, all_ind_columns AIC
where AI.owner='LUMINOSITY'
and AI.INDEX_NAME like '%UK'
and AI.INDEX_NAME=AIC.INDEX_NAME
order by INDEX_NAME,COLUMN_POSITION; 

INDEX_NAME           TABLE_NAME           UNIQUENESS COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------- -------------------- ---------------
LQ_UK                LBN_QUALITIES        UNIQUE     LBN_LO                             1
LQ_UK                LBN_QUALITIES        UNIQUE     LBN_HI                             2
LQ_UK                LBN_QUALITIES        UNIQUE     QUAL_GROUP                         3
LS_UK                LBN_STREAMS          UNIQUE     LBN                                1
LS_UK                LBN_STREAMS          UNIQUE     STREAM_NAME                        2
LS_UK                LBN_STREAMS          UNIQUE     RUN_NUMBER                         3
LT_UK                LBN_TRIGGERS         UNIQUE     LBN                                1
LT_UK                LBN_TRIGGERS         UNIQUE     L3_BIT_NAME                        2
LT_UK                LBN_TRIGGERS         UNIQUE     EG_NUM                             3
STELT_UK             STATUS_TO_ELTS       UNIQUE     CONDITION_ID                       1
STELT_UK             STATUS_TO_ELTS       UNIQUE     EG_NUM                             2
STELT_UK             STATUS_TO_ELTS       UNIQUE     LBN                                3
STELT_UK             STATUS_TO_ELTS       UNIQUE     TICK_NUM                           4

13 rows selected.

SELECT AI.INDEX_NAME,AI.TABLE_NAME,AI.UNIQUENESS,AIC.COLUMN_NAME,AIC.COLUMN_POSITION
from all_indexes AI, all_ind_columns AIC
where AI.owner='LUMINOSITY'
and AI.INDEX_NAME like '%UNIQUE'
and AI.INDEX_NAME=AIC.INDEX_NAME
order by INDEX_NAME,COLUMN_POSITION; 

-- none in production, only 2 on dev
INDEX_NAME           TABLE_NAME           UNIQUENESS COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------- -------------------- ---------------
LT_UNIQUE            LBN_TRIGGERS         UNIQUE     L3_BIT_NAME                        1
LT_UNIQUE            LBN_TRIGGERS         UNIQUE     LBN                                2
LT_UNIQUE            LBN_TRIGGERS         UNIQUE     EG_NUM                             3
PFC_UNIQUE           PRD_FILE_CHKS        UNIQUE     APPL_VERSION                       1
PFC_UNIQUE           PRD_FILE_CHKS        UNIQUE     DATA_TIER_ID                       2
PFC_UNIQUE           PRD_FILE_CHKS        UNIQUE     APPL_NAME                          3
PFC_UNIQUE           PRD_FILE_CHKS        UNIQUE     LS_ID                              4

7 rows selected. 
-----------------------------------------------------------------

select unique  INDEX_NAME      from  all_indexes where owner='LUMINOSITY'; 
87 rows selected.
select unique  INDEX_TYPE      from  all_indexes where owner='LUMINOSITY'; 
NORMAL
select unique  TABLE_NAME      from  all_indexes where owner='LUMINOSITY'; 
37 rows selected.
select unique  UNIQUENESS      from  all_indexes where owner='LUMINOSITY'; 
UNIQUE
NONUNIQUE
select unique  DISTINCT_KEYS   from  all_indexes where owner='LUMINOSITY'; 
       590077            5            8       532046            0      2427292            9          160

select unique  INCLUDE_COLUMN,PARAMETERS from  all_indexes where owner='LUMINOSITY'; 
0

select * from all_indexes where owner='LUMINOSITY' and index_name like 'LT_PK%'; 

select unique   OWNER                   from  all_indexes where owner='LUMINOSITY'; 
LUMINOSITY  
select unique   INDEX_NAME              from  all_indexes where owner='LUMINOSITY'; 
87 rows selected.
select unique   INDEX_NAME              from  all_indexes where owner='LUMINOSITY'
	and INDEX_NAME like '%_PK'; 
select unique   INDEX_NAME              from  all_indexes where owner='LUMINOSITY'
	and INDEX_NAME like '%_FK_I'; 
44 rows selected.

-----------------------------------------------------------------
-- I need to find the constraints 
-- After running the lum_cdsddl_0603.* DDL, these are the counts in development
-- I need to compare these counts to what is in production
--

desc all_constraints;

set echo on;
set linesize 256;
set pagesize 300;

column CONSTRAINT_NAME format a30;
column CONSTRAINT_TYPE format a3;
column TABLE_NAME format a20;
column INDEX_NAME format a10;
column SEARCH_CONDITION format a38;

select unique OWNER           from all_constraints;
TSMSYS LUMINOSITY TOOLMAN SYSTEM DBSNMP SYS

select unique CONSTRAINT_NAME from all_constraints
	where owner='LUMINOSITY';
-- 225

select unique CONSTRAINT_TYPE from all_constraints
	where owner='LUMINOSITY';
C-RUPC

select unique TABLE_NAME from all_constraints where owner='LUMINOSITY';
-- 37 rows selected.

select unique INDEX_NAME
	from all_constraints where owner='LUMINOSITY';
-- 42 rows selected.
select unique INDEX_NAME,TABLE_NAME 
	from all_constraints where owner='LUMINOSITY'
	order by TABLE_NAME;
-- 78 rows selected.

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME,SEARCH_CONDITION
	from all_constraints where OWNER='LUMINOSITY' 
	order by TABLE_NAME;
-- 225

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME,SEARCH_CONDITION
	from all_constraints where OWNER='LUMINOSITY'
	and CONSTRAINT_TYPE='P'
	order by TABLE_NAME;
-- 37 (primary key *_PK for each table)

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME,SEARCH_CONDITION
	from all_constraints where OWNER='LUMINOSITY'
	and CONSTRAINT_TYPE='C'
	order by TABLE_NAME;
-- 140 (CHK_%, AVCON% == value constraints and SYS_C% == NOT NULL constraints)

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME
	from all_constraints where OWNER='LUMINOSITY'
	and CONSTRAINT_TYPE='R'
	order by table_name;
-- 44 (Foreign Keys %_FK)

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME
	from all_constraints where OWNER='LUMINOSITY'
	and CONSTRAINT_TYPE='U'
	order by table_name;
CONSTRAINT_NAME                CON TABLE_NAME           INDEX_NAME
------------------------------ --- -------------------- ----------
LQ_UNIQUE                      U   LBN_QUALITIES        LQ_UK
LS_UNIQUE                      U   LBN_STREAMS          LS_UK
LT_UNIQUE                      U   LBN_TRIGGERS         LT_UNIQUE
PFC_UNIQUE                     U   PRD_FILE_CHKS        PFC_UNIQUE
-- Note: the TR_UNIQUE failed because there are duplicates.

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME
	from all_constraints where OWNER='LUMINOSITY'
	and CONSTRAINT_TYPE!='P';



-----------------------------------------------------------------
-- look at TRANSITION_RECORDS.TRANS_TIME

 desc TRANSITION_RECORDS

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRANS_ID                                  NOT NULL NUMBER(38)
 LBN                                       NOT NULL NUMBER(38)
 TRANS_TIME                                NOT NULL DATE
 PNAME                                     NOT NULL VARCHAR2(64)
 PVALUE                                             NUMBER

-- is it always filled ?
select count(*) 
	from  TRANSITION_RECORDS
	where TRANS_TIME is null;
-- 0 (good)

-- are there duplicates ?

select lbn,TRANS_TIME,PNAME,PVALUE,count(*) 
	from  TRANSITION_RECORDS
	GROUP BY lbn,TRANS_TIME,PNAME,PVALUE HAVING COUNT(*)>=2 ;

select lbn,TRANS_TIME,PNAME,PVALUE,count(*)
  2     from  TRANSITION_RECORDS
  3*    GROUP BY lbn,TRANS_TIME,PNAME,PVALUE HAVING COUNT(*)>=2
SQL> /

     LBN TRANS_TIM PNAME                    PVALUE   COUNT(*)
-------- --------- -------------------- ---------- ----------
 4230245 02-SEP-05 newDay               1125637253          2
 4230861 02-SEP-05 endStore             1125673114          2
 4231859 03-SEP-05 newDay               1125723652          2
 4231878 03-SEP-05 beginStore           1125724724          2
 4232560 03-SEP-05 endStore             1125763926          2
 4233369 04-SEP-05 newDay               1125810034          2
 4233534 04-SEP-05 beginStore           1125819672          2
 4234843 05-SEP-05 newDay               1125896424          2
 4234972 05-SEP-05 endStore             1125903990          2
 4235207 05-SEP-05 beginStore           1125915328          2
 4236378 06-SEP-05 newDay               1125982842          2
 4236867 06-SEP-05 endStore             1126011728          2
 4237149 06-SEP-05 beginStore           1126026100          2
 4237465 06-SEP-05 endStore             1126041699          2
 4237508 06-SEP-05 beginStore           1126043966          2
 4237623 06-SEP-05 endStore             1126050627          2
 4237673 06-SEP-05 beginStore           1126052886          2
 4237950 07-SEP-05 newDay               1126069230          2
 4238809 07-SEP-05 endStore             1126115935          2
 4238827 07-SEP-05 beginStore           1126116852          2
 4238917 07-SEP-05 endStore             1126121205          2
 4239786 08-SEP-05 newDay               1126155659          2
 4240773 08-SEP-05 beginStore           1126207525          2
 4241134 08-SEP-05 endStore             1126227108          2
 4241157 08-SEP-05 beginStore           1126228394          2
 4241226 08-SEP-05 endStore             1126232166          2
 4241392 09-SEP-05 newDay               1126242055          2
 4242013 09-SEP-05 beginStore           1126278129          2
 4242923 10-SEP-05 newDay               1126328433          2
 4243374 10-SEP-05 endStore             1126352357          2
 4244521 11-SEP-05 newDay               1126414845          2
 4244581 11-SEP-05 beginStore           1126417515          2
 4246046 12-SEP-05 newDay               1126501227          2
 4246237 12-SEP-05 endStore             1126512265          2
 4246448 12-SEP-05 beginStore           1126523220          2
 4465747 12-JAN-06 newDay               1137045622          2
 4467306 13-JAN-06 newDay               1137132038          2
 4467834 13-JAN-06 beginStore           1137159207          2

38 rows selected.





-- is it always filled ?
select lbn,PNAME,PVALUE,to_char(TRANS_TIME,'YYYY-MON-DD HH24:MI'),count(*) 
	from  transition_records
	GROUP BY lbn,PNAME,PVALUE,TRANS_TIME HAVING COUNT(*)>=2 
	ORDER BY lbn,PNAME;


-- is there a date but no time ?



-----------------------------------------------------------------
-- trying to find luminosity application definitions:
select TABLE_NAME from all_tables where OWNER='GALLAS';
select TABLE_NAME from all_tables where OWNER='LUMINOSITY';

TABLE_NAME
------------------------------
DELIVERED_LUMS
EXPOSED_FRACTIONS
EXPOSED_LUMS
EXPOSURE_GROUPS
LBNS
LBN_FACTORS
LBN_QUALITIES
LBN_QUALITY_PARAMS
LBN_STREAMS
LBN_TRIGGERS
LM_RATES
LM_TYPES
PRD_FILE_CHKS
QUAL_FLAGS
QUAL_GROUPS
QUAL_USERS
RANGED_FACTORS
STATUS_EXPLAIN_ELTS
STATUS_EXPLAIN_LBNS
STATUS_EXPLAIN_LFS
STATUS_EXPLAIN_LSS
STATUS_EXPLAIN_LTS
STATUS_EXPLAIN_PFCS
STATUS_EXPLAIN_RFS
STATUS_EXPLAIN_TKS
STATUS_EXPLAIN_TRS
STATUS_TO_ELTS
STATUS_TO_LBNS
STATUS_TO_LFS
STATUS_TO_LSS
STATUS_TO_LTS
STATUS_TO_PFCS
STATUS_TO_RFS
STATUS_TO_TKS
STATUS_TO_TRS
TICKS
TRANSITION_RECORDS

37 rows selected.


select unique username from all_users;
select unique username from all_users where username like '%LUM%';
-- found user LUMINOSITY

desc all_tables;
select unique owner from all_tables;

desc all_tab_columns
desc all_col_comments;

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

column TABLE_NAME       format a20
column COLUMN_NAME      format a18;
column DATA_TYPE        format a12;
column COLUMN_ID        format 9999;
column DATA_LENGTH      format 9999;
column DATA_PRECISION   format 99;
column NULLABLE          format a2;

select * from  all_col_comments where owner='GALLAS' and TABLE_NAME not like 'AC_%';
select * from  all_col_comments where owner='LUMINOSITY';

select * from  all_tab_columns where owner='GALLAS';
select * from  all_tab_columns where owner='LUMINOSITY';

select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,NULLABLE
	from all_tab_columns where owner='GALLAS'
	and TABLE_NAME NOT LIKE 'AC%';
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,NULLABLE
	from all_tab_columns where owner='LUMINOSITY';

-- get table column descriptions from user LUMINOSITY.
select  distinct atc.TABLE_NAME,atc.COLUMN_ID,atc.COLUMN_NAME,atc.DATA_TYPE,
        atc.DATA_LENGTH,atc.DATA_PRECISION,atc.NULLABLE,
	acc.comments
	from all_tab_columns atc,all_col_comments acc 
	where atc.owner='GALLAS' and atc.TABLE_NAME NOT LIKE 'AC%'
	and atc.TABLE_NAME=acc.TABLE_NAME
	and atc.COLUMN_NAME=acc.COLUMN_NAME
	order by TABLE_NAME,COLUMN_ID,NULLABLE;

select  distinct atc.TABLE_NAME,atc.COLUMN_ID,atc.COLUMN_NAME,atc.DATA_TYPE,
        atc.DATA_LENGTH,atc.DATA_PRECISION,atc.NULLABLE,
	acc.comments
	from all_tab_columns atc,all_col_comments acc 
	where atc.owner='LUMINOSITY'
	and atc.TABLE_NAME=acc.TABLE_NAME
	and atc.COLUMN_NAME=acc.COLUMN_NAME
	order by TABLE_NAME,COLUMN_ID,NULLABLE;



=================================================================================
select OWNER,TABLE_NAME,TABLESPACE_NAME from all_tables where OWNER='GALLAS';
select OWNER,TABLE_NAME,TABLESPACE_NAME from all_tables where OWNER='LUMINOSITY';

LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         FACTORS              LUM_DATA
LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         INS_LUM_VIEW         LUM_DATA
LUMINOSITY         LBNS                 LUM_DATA
LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         SCALER_TYPES         LUM_DATA
LUMINOSITY         STATUS_MASK_EXPLAIN  LUM_DATA
LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRIGGERS             LUM_DATA
LUMINOSITY         TRIG_LUM_VIEW        LUM_DATA

13 rows selected.

desc all_indexes;
select unique owner from all_indexes; 
select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from all_indexes
	where  OWNER='LUMINOSITY';

OWNER              INDEX_NAME                     INDEX_TYPE   TABLE_OWNER        TABLE_NAME           TABLESPACE_N
------------------ ------------------------------ ------------ ------------------ -------------------- ------------
LUMINOSITY         BY_TICKS_LBN_FKIDX             NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         BY_TICKS_PK                    NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         BY_TICKS_SCALER_ID_FKIDX       NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         FACTORS_PK                     NORMAL       LUMINOSITY         FACTORS              LUM_DATA
LUMINOSITY         GRP_STATUS_LBN_FKIDX           NORMAL       LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         GRP_STATUS_PK                  NORMAL       LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         LBNS_PK                        NORMAL       LUMINOSITY         LBNS                 LUM_DATA
LUMINOSITY         PRD_FILE_CHKS_PK               NORMAL       LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         PRD_FILE_CHKS_RN_SN_LBN_FKIDX  NORMAL       LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         RANGED_FACTORS_BEGIN_LBN_FKIDX NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         RANGED_FACTORS_END_LBN_FKIDX   NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         RANGED_FACTORS_PK              NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         SCALER_TYPES_PK                NORMAL       LUMINOSITY         SCALER_TYPES         LUM_DATA
LUMINOSITY         STATUS_MASK_EXPLAIN_PK         NORMAL       LUMINOSITY         STATUS_MASK_EXPLAIN  LUM_DATA
LUMINOSITY         STREAMS_LBN_FKIDX              NORMAL       LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         STREAMS_PK                     NORMAL       LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         TRANSITION_RECORDS_LBN_FKIDX   NORMAL       LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRANS_RECS_PK                  NORMAL       LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRIGGERS_LBN_FKIDX             NORMAL       LUMINOSITY         TRIGGERS             LUM_DATA
LUMINOSITY         TRIGGERS_PK                    NORMAL       LUMINOSITY         TRIGGERS             LUM_DATA

20 rows selected.

select synonym_name,owner 
  from all_synonyms where table_owner like 'LUMINOSITY'
  order by synonym_name;

-- 0

select view_name,owner from all_views where upper(owner) like '%LUM%';

VIEW_NAME                      OWNER
------------------------------ ------------------
BYTICKS_PIVOT                  LUMINOSITY

desc all_constraints; 
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from all_constraints
	where owner='LUMINOSITY';

-- 66 constraints

desc all_triggers;
select OWNER TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME from all_triggers
	where table_owner='LUMINOSITY';

select * from dba_role_privs where grantee='JEREMYS';

desc all_tab_privs_made;
select unique OWNER from  all_tab_privs_made;
select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY 
from  all_tab_privs_made;

-- 0

desc role_tab_privs;

 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ROLE                                                              NOT NULL VARCHAR2(30)
 OWNER                                                             NOT NULL VARCHAR2(30)
 TABLE_NAME                                                        NOT NULL VARCHAR2(30)
 COLUMN_NAME                                                                VARCHAR2(30)
 PRIVILEGE                                                         NOT NULL VARCHAR2(40)
 GRANTABLE                                                                  VARCHAR2(3)

SQL> select unique owner from role_tab_privs;

desc table_privileges;

select TABLE_NAME from all_tables where OWNER='LUMINOSITY';
select count(*) from luminosity.BY_TICKS;
-- 1
select count(*) from luminosity.FACTORS;
select count(*) from luminosity.GRP_STATUS;
select count(*) from luminosity.INS_LUM_VIEW;
-- 2
select count(*) from luminosity.LBNS;
-- 3 
select count(*) from luminosity.PRD_FILE_CHKS;
select count(*) from luminosity.RANGED_FACTORS;
select count(*) from luminosity.SCALER_TYPES;
-- 12
select count(*) from luminosity.STATUS_MASK_EXPLAIN;
select count(*) from luminosity.STREAMS;
select count(*) from luminosity.TRANSITION_RECORDS;
select count(*) from luminosity.TRIGGERS;
select count(*) from luminosity.TRIG_LUM_VIEW;

-- this is the only table with data we want to save:
desc luminosity.SCALER_TYPES; 
column SCALER_ID          format 999999;
column SCALER_TYPE       format a30;
column EXPOSURE_GROUP    format a30;
 select * from luminosity.SCALER_TYPES
SQL> /
        0 EXP0                           0
        1 EXP1                           1
        2 EXP2                           2
        3 EXP3                           3
        4 EXP4                           4
        5 EXP5                           5
        6 EXP6                           6
        7 EXP7                           7
        8 FASTZ
        9 PBARHALO
       10 PROTONHALO
       11 ANTI-PROTONHALO

12 rows selected.


---------------------------------------------------------------
desc user_objects;

set heading off;
set linesize 256;
set pagesize 000;

column OBJECT_NAME format a30;
column SUBOBJECT_NAME format a1;
column DATA_OBJECT_ID format 99999999;
column OBJECT_TYPE format a10;
--column CREATED format a25;
--column LAST_DDL_TIME format a25;
column TIMESTAMP format a25;
column STATUS format a10;
column TEMPORARY format a5;
column GENERATED format a5;
column SECONDARY format a5;

select unique OBJECT_NAME from USER_OBJECTS;
select unique SUBOBJECT_NAME from USER_OBJECTS;
select unique DATA_OBJECT_ID from USER_OBJECTS;
select unique OBJECT_TYPE from USER_OBJECTS;
select unique CREATED from USER_OBJECTS;
select unique LAST_DDL_TIME from USER_OBJECTS;
select unique TIMESTAMP from USER_OBJECTS;
select unique STATUS from USER_OBJECTS;
select unique TEMPORARY from USER_OBJECTS;
select unique GENERATED from USER_OBJECTS;
select unique SECONDARY from USER_OBJECTS;

desc user_objects;

select * from user_objects;
-- 35 objects found owned by luminosity:

SQL> select * from user_objects;
BYTICKS_PIVOT                        142170                VIEW       16-MAY-03 16-MAY-03 2003-05-16:14:18:56       VALID      N     N     N
BY_TICKS                             139803         139803 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:06       VALID      N     N     N
BY_TICKS_LBN_FKIDX                   139825         139825 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
BY_TICKS_PK                          139804         139804 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:06       VALID      N     N     N
BY_TICKS_SCALER_ID_FKIDX             139826         139826 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
FACTORS                              139805         139805 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
FACTORS_PK                           139806         139806 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
GRP_STATUS                           139807         139807 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
GRP_STATUS_LBN_FKIDX                 139827         139827 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
GRP_STATUS_PK                        139808         139808 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
INS_LUM_VIEW                         139609         139609 TABLE      21-APR-03 22-APR-03 2003-04-22:14:45:26       VALID      N     N     N
LBNS                                 139809         139809 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
LBNS_PK                              139810         139810 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS                        139811         139811 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS_PK                     139812         139812 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS_RN_SN_LBN_FKIDX        139828         139828 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS                       139813         139813 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
RANGED_FACTORS_BEGIN_LBN_FKIDX       139829         139829 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS_END_LBN_FKIDX         139830         139830 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS_PK                    139814         139814 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
RECORDED_LUM                         141976                FUNCTION   14-MAY-03 20-AUG-03 2003-05-14:16:00:39       INVALID    N     N     N
SCALER_TYPES                         139815         139815 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
SCALER_TYPES_PK                      139816         139816 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STATUS_MASK_EXPLAIN                  139817         139817 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STATUS_MASK_EXPLAIN_PK               139818         139818 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STREAMS                              139819         139819 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STREAMS_LBN_FKIDX                    139831         139831 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
STREAMS_PK                           139820         139820 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
TRANSITION_RECORDS                   139821         139821 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRANSITION_RECORDS_LBN_FKIDX         139832         139832 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRANS_RECS_PK                        139822         139822 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS                             139823         139823 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS_LBN_FKIDX                   139833         139833 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS_PK                          139824         139824 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIG_LUM_VIEW                        139618         139618 TABLE      24-APR-03 24-APR-03 2003-04-24:15:27:24       VALID      N     N     N

35 rows selected.


                 

-- delete the views first ---------------------------------

drop view BYTICKS_PIVOT;

-- now delete the tables  -----------------------------------------

> select * from user_objects where OBJECT_TYPE='TABLE';

BY_TICKS                             139803         139803 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:06       VALID      N     N     N
FACTORS                              139805         139805 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
GRP_STATUS                           139807         139807 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
INS_LUM_VIEW                         139609         139609 TABLE      21-APR-03 22-APR-03 2003-04-22:14:45:26       VALID      N     N     N
LBNS                                 139809         139809 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS                        139811         139811 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
RANGED_FACTORS                       139813         139813 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
SCALER_TYPES                         139815         139815 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STATUS_MASK_EXPLAIN                  139817         139817 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STREAMS                              139819         139819 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
TRANSITION_RECORDS                   139821         139821 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS                             139823         139823 TABLE      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIG_LUM_VIEW                        139618         139618 TABLE      24-APR-03 24-APR-03 2003-04-24:15:27:24       VALID      N     N     N

13 rows selected.

drop table BY_TICKS;
drop table FACTORS;
drop table GRP_STATUS;
drop table INS_LUM_VIEW;
drop table LBNS;
drop table PRD_FILE_CHKS;
drop table RANGED_FACTORS;
drop table SCALER_TYPES;
drop table STATUS_MASK_EXPLAIN;
drop table STREAMS;
drop table TRANSITION_RECORDS;
drop table TRIGGERS;
drop table TRIG_LUM_VIEW;

-- now delete the indexes -----------------------------------------

SQL>  select * from user_objects where OBJECT_TYPE='INDEX';
BY_TICKS_LBN_FKIDX                   139825         139825 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
BY_TICKS_PK                          139804         139804 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:06       VALID      N     N     N
BY_TICKS_SCALER_ID_FKIDX             139826         139826 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
FACTORS_PK                           139806         139806 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
GRP_STATUS_LBN_FKIDX                 139827         139827 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
GRP_STATUS_PK                        139808         139808 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
LBNS_PK                              139810         139810 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS_PK                     139812         139812 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
PRD_FILE_CHKS_RN_SN_LBN_FKIDX        139828         139828 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS_BEGIN_LBN_FKIDX       139829         139829 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS_END_LBN_FKIDX         139830         139830 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
RANGED_FACTORS_PK                    139814         139814 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
SCALER_TYPES_PK                      139816         139816 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STATUS_MASK_EXPLAIN_PK               139818         139818 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
STREAMS_LBN_FKIDX                    139831         139831 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
STREAMS_PK                           139820         139820 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:07       VALID      N     N     N
TRANSITION_RECORDS_LBN_FKIDX         139832         139832 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRANS_RECS_PK                        139822         139822 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS_LBN_FKIDX                   139833         139833 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N
TRIGGERS_PK                          139824         139824 INDEX      30-APR-03 30-APR-03 2003-04-30:11:58:08       VALID      N     N     N

20 rows selected.

drop index BY_TICKS_LBN_FKIDX;
drop index BY_TICKS_PK;
drop index BY_TICKS_SCALER_ID_FKIDX;
drop index FACTORS_PK;
drop index GRP_STATUS_LBN_FKIDX;
drop index GRP_STATUS_PK;
drop index LBNS_PK;
drop index PRD_FILE_CHKS_PK;
drop index PRD_FILE_CHKS_RN_SN_LBN_FKIDX;
drop index RANGED_FACTORS_BEGIN_LBN_FKIDX;
drop index RANGED_FACTORS_END_LBN_FKIDX;
drop index RANGED_FACTORS_PK;
drop index SCALER_TYPES_PK;
drop index STATUS_MASK_EXPLAIN_PK;
drop index STREAMS_LBN_FKIDX;
drop index STREAMS_PK;
drop index TRANSITION_RECORDS_LBN_FKIDX;
drop index TRANS_RECS_PK;
drop index TRIGGERS_LBN_FKIDX;
drop index TRIGGERS_PK;

-- see what is left -----------------------------

select * from user_objects;


-------------------------------------------------------------
-- get a list of tables their synonyms and their columns


select TABLE_NAME from all_tables where OWNER='LUMINOSITY';

TABLE_NAME
------------------------------
BY_TICKS
FACTORS
GRP_STATUS
LBNS
PRD_FILE_CHKS
RANGED_FACTORS
SCALER_TYPES
STATUS_MASK_EXPLAIN
STREAMS
TRANSITION_RECORDS
TRIGGERS

11 rows selected.

set pagesize 0;
column synonym_name format a20;
column table_name format a20;
select synonym_name,owner,table_name
  from all_synonyms where table_owner like 'LUMINOSITY'
  order by synonym_name;

BY_TICK              PUBLIC                         BY_TICKS
BY_TICKS             PUBLIC                         BY_TICKS
FACTOR               PUBLIC                         FACTORS
FACTORS              PUBLIC                         FACTORS
GRP_STATUS           PUBLIC                         GRP_STATUS
LBN                  PUBLIC                         LBNS
LBNS                 PUBLIC                         LBNS
PRD_FILE_CHK         PUBLIC                         PRD_FILE_CHKS
PRD_FILE_CHKS        PUBLIC                         PRD_FILE_CHKS
RANGED_FACTOR        PUBLIC                         RANGED_FACTORS
RANGED_FACTORS       PUBLIC                         RANGED_FACTORS
SCALAR_TYPE          PUBLIC                         SCALER_TYPES
SCALAR_TYPES         PUBLIC                         SCALER_TYPES
STATUS_MASK_EXPLAIN  PUBLIC                         STATUS_MASK_EXPLAIN
STATUS_MASK_EXPLAINS PUBLIC                         STATUS_MASK_EXPLAIN
STREAM               PUBLIC                         STREAMS
STREAMS              PUBLIC                         STREAMS
TRANSITION_RECORD    PUBLIC                         TRANSITION_RECORDS
TRANSITION_RECORDS   PUBLIC                         TRANSITION_RECORDS
TRIGGERI             PUBLIC                         TRIGGERS
TRIGGERIS            PUBLIC                         TRIGGERS

21 rows selected.

-- get the columns in each table:

desc BY_TICKS;
desc FACTORS;
desc GRP_STATUS;
desc LBNS;
desc PRD_FILE_CHKS;
desc RANGED_FACTORS;
desc SCALAR_TYPES;
desc STATUS_MASK_EXPLAIN;
desc STREAMS;
desc TRANSITION_RECORDS;
desc TRIGGERiS;

select TABLE_NAME from all_tables where OWNER='LUMINOSITY';
DELIVERED_LUMS
EXPOSED_FRACTIONS
EXPOSED_LUMS
EXPOSURE_GROUPS
GRP_STATUS_MASK
LBNS
LBN_FACTORS
LBN_STATUS_MASKS
LBN_STREAMS
LBN_TRIGGERS
LM_RATES
LM_TYPES
PRD_FILE_CHKS
RANGED_FACTORS
STATUS_MASK_EXPLAINS
TICKS
TRANSITION_RECORDS


-- get the columns in each table:
desc LBNS
desc EXPOSURE_GROUPS
desc LM_TYPES
desc TICKS

desc TRANSITION_RECORDS

desc LBN_TRIGGERS

desc DELIVERED_LUMS
desc EXPOSED_FRACTIONS
desc EXPOSED_LUMS
desc LM_RATES

desc LBN_FACTORS
desc RANGED_FACTORS

desc STATUS_MASK_EXPLAINS
desc GRP_STATUS_MASK
desc LBN_STATUS_MASKS

desc LBN_STREAMS
desc PRD_FILE_CHKS

----------------------------------------
set echo on;DELIVERED_LUMS

-- tables with initial records
select count(*) from TICKS;
select count(*) from EXPOSURE_GROUPS;
select count(*) from LM_TYPES;
select count(*) from QUAL_FLAGS;
select count(*) from QUAL_GROUPS;
select count(*) from QUAL_USERS;

-- tables with no initial records
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_ELTS;
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_ELTS;
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;

37 rows selected.
