SQL> set echo on;
SQL> ALTER TABLE LBN_TRIGGERS
  2      DROP CONSTRAINT AVCON_1102465571_EG_NU_000;

Table altered.

SQL> 
SQL> 
SQL> ALTER TABLE LBN_TRIGGERS
  2      DROP CONSTRAINT LT_LBN_FK;

Table altered.

SQL> 
SQL> ALTER TABLE STATUS_TO_LTS
  2      DROP CONSTRAINT STLT_LT_FK;

Table altered.

SQL> 
SQL> ALTER TABLE LBN_TRIGGERS
  2      DROP CONSTRAINT LT_PK;

Table altered.

SQL> select OWNER,CONSTRAINT_NAME,SEARCH_CONDITION  FROM all_constraints
  2       	where TABLE_NAME='LBN_TRIGGERS';

OWNER                          CONSTRAINT_NAME                                                                          
------------------------------ ------------------------------                                                           
SEARCH_CONDITION                                                                                                        
--------------------------------------------------------------------------------                                        
LUMINOSITY                     SYS_C001748                                                                              
"LT_ID" IS NOT NULL                                                                                                     
                                                                                                                        
LUMINOSITY                     SYS_C001749                                                                              
"LBN" IS NOT NULL                                                                                                       
                                                                                                                        
LUMINOSITY                     SYS_C001750                                                                              
"L3_BIT_NAME" IS NOT NULL                                                                                               
                                                                                                                        
LUMINOSITY                     SYS_C001751                                                                              
"EG_NUM" IS NOT NULL                                                                                                    
                                                                                                                        
LUMINOSITY                     SYS_C001752                                                                              
"DECORR_SCALER" IS NOT NULL                                                                                             
                                                                                                                        
LUMINOSITY                     SYS_C001753                                                                              
"L1_ACCEPTS" IS NOT NULL                                                                                                
                                                                                                                        
LUMINOSITY                     SYS_C001754                                                                              
"L2_ACCEPTS" IS NOT NULL                                                                                                
                                                                                                                        
LUMINOSITY                     SYS_C001755                                                                              
"REC_ACCEPTS" IS NOT NULL                                                                                               
                                                                                                                        
LUMINOSITY                     SYS_C001756                                                                              
"PRESCALE" IS NOT NULL                                                                                                  
                                                                                                                        

9 rows selected.

SQL> DROP TRIGGER TRIG_I_LT;

Trigger dropped.

SQL> RENAME LBN_TRIGGERS to LBN_TRIGGERS2;

Table renamed.

SQL> desc  LBN_TRIGGERS2;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 LT_ID                                                             NOT NULL NUMBER(38)
 LBN                                                               NOT NULL NUMBER(38)
 L3_BIT_NAME                                                       NOT NULL VARCHAR2(100)
 EG_NUM                                                            NOT NULL NUMBER(2)
 DECORR_SCALER                                                     NOT NULL NUMBER
 L1_ACCEPTS                                                        NOT NULL NUMBER
 L2_ACCEPTS                                                        NOT NULL NUMBER
 REC_ACCEPTS                                                       NOT NULL NUMBER
 PRESCALE                                                          NOT NULL NUMBER
 RUN_NUMBER                                                                 NUMBER(38)
 L1_LUM_NIM                                                                 NUMBER
 REC_LUM_NIM                                                                NUMBER
 L1_LUM_VME                                                                 NUMBER
 REC_LUM_VME                                                                NUMBER

SQL> select count(*) from LBN_TRIGGERS2;

  COUNT(*)                                                                                                              
----------                                                                                                              
  99052607                                                                                                              

SQL> @0508_create_table_lt_part1.sql
SQL> -- E. Gallas  Aug 2005
SQL> --
SQL> -- Create the LBN_TRIGGERS table (extracted from the ddl lum_cdsddl.tab).
SQL> -- Modify to change the type of PRESCALE column from NUMBER to VARCHAR2(64)
SQL> ----------------------------------------------------------------------------
SQL> 
SQL> PROMPT Creating Table 'LBN_TRIGGERS'
Creating Table 'LBN_TRIGGERS'
SQL> CREATE TABLE LBN_TRIGGERS
  2   (LT_ID NUMBER(38) NOT NULL
  3   ,LBN NUMBER(38,0) NOT NULL
  4   ,L3_BIT_NAME VARCHAR2(100) NOT NULL
  5   ,EG_NUM NUMBER(2,0) DEFAULT 8 NOT NULL
  6   ,DECORR_SCALER NUMBER NOT NULL
  7   ,L1_ACCEPTS NUMBER NOT NULL
  8   ,L2_ACCEPTS NUMBER NOT NULL
  9   ,REC_ACCEPTS NUMBER NOT NULL
 10   ,PRESCALE 	   VARCHAR2(64) 	       NOT NULL
 11   ,RUN_NUMBER NUMBER(38,0)
 12   ,L1_LUM_NIM NUMBER
 13   ,REC_LUM_NIM NUMBER
 14   ,L1_LUM_VME NUMBER
 15   ,REC_LUM_VME NUMBER
 16   )
 17   PCTFREE 10
 18   STORAGE
 19   (
 20  	INITIAL 7800M
 21  	NEXT 7800M
 22  	PCTINCREASE 0
 23  	MINEXTENTS 1
 24  	MAXEXTENTS 4
 25   ) TABLESPACE LUM_DATA
 26  /

Table created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.LT_ID IS 'Primary Key for the table Sequence Generated . Populated by trigger'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.L3_BIT_NAME IS 'trigger name'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.EG_NUM IS 'exposure group'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.L1_ACCEPTS IS 'number of L1 Accepts during this LBN for this trigger'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.L2_ACCEPTS IS 'number of L2 accepts during this LBN for this trigger'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.REC_ACCEPTS IS 'number of recorded accepts during this LBN for this trigger'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.PRESCALE IS 'Level 1 prescale applied'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.RUN_NUMBER IS 'Run Number used in online data taking'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.L1_LUM_NIM IS 'Level 1 Luminosity measured by Run 1 NIM electronics'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.REC_LUM_NIM IS 'Recorded Luminosity measured by Run 1 NIM electronics'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.L1_LUM_VME IS 'Level 1 Luminosity measured by Run 2 VME electronics'
  2  /

Comment created.

SQL> 
SQL> COMMENT ON COLUMN LBN_TRIGGERS.REC_LUM_VME IS 'Recorded Luminosity measured by Run 2 VME electronics'
  2  /

Comment created.

SQL> 
SQL> desc  LBN_TRIGGERS;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 LT_ID                                                             NOT NULL NUMBER(38)
 LBN                                                               NOT NULL NUMBER(38)
 L3_BIT_NAME                                                       NOT NULL VARCHAR2(100)
 EG_NUM                                                            NOT NULL NUMBER(2)
 DECORR_SCALER                                                     NOT NULL NUMBER
 L1_ACCEPTS                                                        NOT NULL NUMBER
 L2_ACCEPTS                                                        NOT NULL NUMBER
 REC_ACCEPTS                                                       NOT NULL NUMBER
 PRESCALE                                                          NOT NULL VARCHAR2(64)
 RUN_NUMBER                                                                 NUMBER(38)
 L1_LUM_NIM                                                                 NUMBER
 REC_LUM_NIM                                                                NUMBER
 L1_LUM_VME                                                                 NUMBER
 REC_LUM_VME                                                                NUMBER

SQL> select count(*) from LBN_TRIGGERS;

  COUNT(*)                                                                                                              
----------                                                                                                              
         0                                                                                                              

SQL> COMMIT;

Commit complete.

SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
28-AUG-05 04.07.59.450014 PM                                                                                            

SQL> @0508_populate_table_lt.sql
SQL> -- The columns in lbn_triggers:
SQL> --  LT_ID,LBN,L3_BIT_NAME,EG_NUM,DECORR_SCALER,L1_ACCEPTS,L2_ACCEPTS,
SQL> --  REC_ACCEPTS,PRESCALE,RUN_NUMBER,L1_LUM_NIM,REC_LUM_NIM,L1_LUM_VME,REC_LUM_VME
SQL> 
SQL> declare
  2  
  3  /* keep track of commits by chunks of 999	*/
  4  counter number :=0;
  5  
  6  /*  these are to double check the counts at the end */
  7  old_count number :=0;
  8  new_count number :=0;
  9  
 10  /*  get each record 1 at a time  */
 11  cursor old_data is
 12    select LT_ID,
 13  	     LBN,
 14  	     L3_BIT_NAME,
 15  	     EG_NUM,
 16  	     DECORR_SCALER,
 17  	     L1_ACCEPTS,
 18  	     L2_ACCEPTS,
 19  	     REC_ACCEPTS,
 20  	     PRESCALE,
 21  	     RUN_NUMBER,
 22  	     L1_LUM_NIM,
 23  	     REC_LUM_NIM,
 24  	     L1_LUM_VME,
 25  	     REC_LUM_VME
 26  	     from lbn_triggers2;
 27  
 28  begin
 29    dbms_output.enable(100000);
 30    for newtable in old_data loop
 31  	 begin
 32  	   insert into lbn_triggers
 33  	     (LT_ID,
 34  	     LBN,
 35  	     L3_BIT_NAME,
 36  	     EG_NUM,
 37  	     DECORR_SCALER,
 38  	     L1_ACCEPTS,
 39  	     L2_ACCEPTS,
 40  	     REC_ACCEPTS,
 41  	     PRESCALE,
 42  	     RUN_NUMBER,
 43  	     L1_LUM_NIM,
 44  	     REC_LUM_NIM,
 45  	     L1_LUM_VME,
 46  	     REC_LUM_VME)
 47  	   values
 48  	     (newtable.LT_ID,
 49  	     newtable.LBN,
 50  	     newtable.L3_BIT_NAME,
 51  	     newtable.EG_NUM,
 52  	     newtable.DECORR_SCALER,
 53  	     newtable.L1_ACCEPTS,
 54  	     newtable.L2_ACCEPTS,
 55  	     newtable.REC_ACCEPTS,
 56  	     newtable.PRESCALE,
 57  	     newtable.RUN_NUMBER,
 58  	     newtable.L1_LUM_NIM,
 59  	     newtable.REC_LUM_NIM,
 60  	     newtable.L1_LUM_VME,
 61  	     newtable.REC_LUM_VME);
 62  	   counter:=counter+1;
 63  
 64  	   /*  if counter > 999 then commit */
 65  	   if counter>999 then
 66  	     commit;
 67  	     counter:=0;
 68  	   end if;
 69  
 70  	 /*  end of second begin loop  */
 71  	 end;
 72  
 73    /*  end of for loop; */
 74    end loop;
 75  
 76    /*  final commit */
 77    commit;
 78  
 79    /*  check results */
 80    select count(*) into old_count from lbn_triggers2;
 81    select count(*) into new_count from lbn_triggers;
 82    dbms_output.put_line ('old count is '||to_char(old_count));
 83    dbms_output.put_line ('new count is '||to_char(new_count));
 84  
 85  /*  end of first begin loop; */
 86  end;
 87  /

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 12.16.25.737889 AM                                                                                            

SQL> 
SQL> select count(*) from LBN_TRIGGERS;

  COUNT(*)                                                                                                              
----------                                                                                                              
  99052607                                                                                                              

SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 12.18.44.896822 AM                                                                                            

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> drop Index LT_UK;

Index dropped.

SQL> 
SQL> drop Index LT_LBN_FK_I;

Index dropped.

SQL> 
SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 08.09.08.815444 AM                                                                                            

SQL> PROMPT Creating Index 'LT_UK'
Creating Index 'LT_UK'
SQL> CREATE UNIQUE INDEX LT_UK ON LBN_TRIGGERS
  2   (LBN
  3   ,L3_BIT_NAME
  4   ,EG_NUM)
  5   STORAGE
  6   (
  7     INITIAL 7800M
  8     NEXT 7800M
  9     PCTINCREASE 0
 10     MINEXTENTS 1
 11     MAXEXTENTS 4
 12   ) TABLESPACE LUM_IDX
 13  /

Index created.

SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 08.55.28.303995 AM                                                                                            

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> PROMPT Creating Index 'LT_LBN_FK_I'
Creating Index 'LT_LBN_FK_I'
SQL> CREATE INDEX LT_LBN_FK_I ON LBN_TRIGGERS
  2   (LBN)
  3   PCTFREE 10
  4   STORAGE
  5   (
  6     INITIAL 7700M
  7     NEXT 7700M
  8     PCTINCREASE 0
  9     MINEXTENTS 1
 10     MAXEXTENTS 4
 11   ) TABLESPACE LUM_IDX
 12  /

Index created.

SQL> 
SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 09.17.45.347201 AM                                                                                            

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 09.18.41.073346 AM -05:00                                                                                     
29-AUG-05 09.18.41.073346 AM                                                                                            
                                                                                                                        

SQL> PROMPT Creating Primary Key on 'LBN_TRIGGERS'
Creating Primary Key on 'LBN_TRIGGERS'
SQL> ALTER TABLE LBN_TRIGGERS
  2   ADD (CONSTRAINT LT_PK PRIMARY KEY
  3    (LT_ID)
  4   USING INDEX
  5   PCTFREE 10
  6   STORAGE
  7   (
  8     INITIAL 7700M
  9     NEXT 7700M
 10     PCTINCREASE 0
 11     MINEXTENTS 1
 12     MAXEXTENTS 4
 13   ) TABLESPACE LUM_IDX)
 14  /

Table altered.

SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 09.44.15.008360 AM -05:00                                                                                     
29-AUG-05 09.44.15.008360 AM                                                                                            
                                                                                                                        

SQL> 
SQL> PROMPT Creating Check Constraint on 'LBN_TRIGGERS'
Creating Check Constraint on 'LBN_TRIGGERS'
SQL> ALTER TABLE LBN_TRIGGERS
  2   ADD (CONSTRAINT AVCON_1102465571_EG_NU_000
  3  	CHECK (EG_NUM IN (0, 1, 2, 3, 4, 5, 6, 7, 8)))
  4  /

Table altered.

SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 09.45.43.436339 AM -05:00                                                                                     
29-AUG-05 09.45.43.436339 AM                                                                                            
                                                                                                                        

SQL> 
SQL> PROMPT Creating Foreign Key on 'LBN_TRIGGERS'
Creating Foreign Key on 'LBN_TRIGGERS'
SQL> ALTER TABLE LBN_TRIGGERS ADD (CONSTRAINT
  2   LT_LBN_FK FOREIGN KEY
  3    (LBN) REFERENCES LBNS
  4    (LBN))
  5  /

Table altered.

SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 09.48.09.828386 AM -05:00                                                                                     
29-AUG-05 09.48.09.828386 AM                                                                                            
                                                                                                                        

SQL> 
SQL> PROMPT Creating Foreign Key on 'STATUS_TO_LTS'
Creating Foreign Key on 'STATUS_TO_LTS'
SQL> ALTER TABLE STATUS_TO_LTS ADD (CONSTRAINT
  2   STLT_LT_FK FOREIGN KEY
  3    (LT_ID) REFERENCES LBN_TRIGGERS
  4    (LT_ID))
  5  /

Table altered.

SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 10.00.20.101734 AM -05:00                                                                                     
29-AUG-05 10.00.20.101734 AM                                                                                            
                                                                                                                        

SQL> 
SQL> PROMPT Creating Trigger 'TRIG_I_LT'
Creating Trigger 'TRIG_I_LT'
SQL> CREATE OR REPLACE TRIGGER TRIG_I_LT
  2   BEFORE INSERT
  3   ON LBN_TRIGGERS
  4   FOR EACH ROW
  5  DECLARE
  6  
  7     temp_seq  number(38);
  8  begin
  9     select SEQ_LT.nextval into temp_seq from dual;
 10     :new.LT_ID := temp_seq;
 11  end TRIG_I_LT;
 12  /

Trigger created.

SQL> SHOW ERROR
No errors.
SQL> /

Trigger created.

SQL>  SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                                                                                                       
---------------------------------------------------------------------------                                             
LOCALTIMESTAMP                                                                                                          
---------------------------------------------------------------------------                                             
29-AUG-05 10.00.21.114817 AM -05:00                                                                                     
29-AUG-05 10.00.21.114817 AM                                                                                            
                                                                                                                        

SQL> GRANT INSERT, SELECT ON LBN_TRIGGERS TO LM_INSERT
  2  
SQL> GRANT SELECT ON LBN_TRIGGERS TO LM_READ
  2  /

Grant succeeded.

SQL> 
SQL> GRANT DELETE, INSERT, SELECT, UPDATE ON LBN_TRIGGERS TO LM_MASTER
  2  /

Grant succeeded.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> GRANT INSERT, SELECT ON LBN_TRIGGERS TO LM_INSERT
  2  /

Grant succeeded.

SQL> 
SQL> GRANT SELECT ON LBN_TRIGGERS TO LM_READ
  2  /

Grant succeeded.

SQL> 
SQL> GRANT DELETE, INSERT, SELECT, UPDATE ON LBN_TRIGGERS TO LM_MASTER
  2  /

Grant succeeded.

SQL> spool
currently spooling to 0508_log1.log
SQL> spool off