Bikle.com

Ajax Demo:

Plain HTML
(no Ajax here):

of URLs:

eventDependencies.html 10-20-2001 Bikle ------------------------ This page documents dependencies (and how I found them) on the event table. We start our quest for dependencies by running a query against DBA_DEPENDENCIES: 16:58:11 SQL> L 1 SELECT OWNER,NAME,TYPE 2 FROM DBA_DEPENDENCIES 3 WHERE REFERENCED_OWNER='BIKLE' 4* AND REFERENCED_NAME='EVENT' 16:58:12 SQL> / OWNER NAME TYPE ------------------------------ ------------------------------ ------------ BIKLE LOG_EVENT_AUTONOMOUS PROCEDURE BIKLE LOG_EVENT PROCEDURE BIKLE PKG_CALCULATE_SUMMARY PACKAGE BODY BIKLE PKG_CALCULATE_SUMMARY_MONTHLY PACKAGE BODY BIKLE EVENT_TIME_TRIG TRIGGER 16:58:12 SQL> Next, we query the state of the above objects: bikle01-oracle-% sqi SQL*Plus: Release 8.1.6.0.0 - Production on Sat Oct 20 17:12:17 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production 17:12:17 SQL> select owner, object_type,OBJECT_NAME,STATUS from dba_objects 17:12:40 2 where OBJECT_NAME in 17:12:45 3 ( 17:12:47 4 select NAME from DBA_DEPENDENCIES where REFERENCED_OWNER='BIKLE' AND REFERENCED_NAME='EVENT' 17:12:52 5 ) 17:12:54 6 / OWNER OBJECT_TYPE ------------------------------ ------------------ OBJECT_NAME ------------------------------------------------- STATUS ------- BIKLE TRIGGER EVENT_TIME_TRIG VALID BIKLE PROCEDURE LOG_EVENT VALID BIKLE PROCEDURE LOG_EVENT_AUTONOMOUS VALID BIKLE PACKAGE PKG_CALCULATE_SUMMARY VALID BIKLE PACKAGE BODY PKG_CALCULATE_SUMMARY VALID BIKLE PACKAGE PKG_CALCULATE_SUMMARY_MONTHLY VALID BIKLE PACKAGE BODY PKG_CALCULATE_SUMMARY_MONTHLY VALID 7 rows selected. Notice that all of the above objects are "VALID". We need to make sure this is still the case after our manipulations of the event table. Next, we run a ROWS=N export of the event table to see what shows up in the export file: bikle01-oracle-% exp bikle file=event.nr.dmp rows=n tables=event Export: Release 8.1.6.0.0 - Production on Sat Oct 20 17:17:47 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Password: Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... . . exporting table EVENT Export terminated successfully without warnings. bikle01-oracle-% bikle01-oracle-% strings event.nr.dmp EXPORT:V08.01.06F UBIKLE RTABLES 1024 4000 Sat Oct 20 17:17:49 2001event.nr.dmp #G## #G## -07:00 8.1.6 TABLE "EVENT" CREATE TABLE "EVENT" ("EVENT_TIME" DATE, "EVENT_TYPE" VARCHAR2(16), "VDATA1" VARCHAR2(250), "VDATA2" VARCHAR2(250), "NDATA1" NUMBER, "NDATA2" NUMBER, "MEMBER_ID" NUMBER, "MEMBER_ID_REAL" NUMBER, "TAG" VARCHAR2(32)) PCTFREE 10 PCTUSED 40 INITRANS 5 MAXTRANS 255 LOGGING STORAGE(INITIAL 1510268928 NEXT 755048448 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIKLE_DATA" GRANT SELECT ON "EVENT" TO "BIKLEREAD" ANALSTATS T "EVENT" BEGIN DBMS_STATS.SET_TABLE_STATS(NULL,'EVENT',NULL,NULL,NULL,13126067,85418,46,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '77C70B130A102C'; SREC.MAXVAL := '7865040C0C1728'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY( 2451502.38591435,2452012.4740625& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','EVENT_TIME',NULL,NULL,NULL,13126067,.0000000761842827710692,0,srec,7,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '414343455054'; SREC.MAXVAL := '5749544844524157'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY(I 338863546986673000000000000000000000,453217120124764000000000000000000000& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( 0,1} ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','EVENT_TYPE',NULL,NULL,NULL,28,.0357142857142857,0,srec,7,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '20'; SREC.MAXVAL := 'E0BEC5A7CAD2A1C5'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY(J 166153499473115000000000000000000000,1166943814013700000000000000000000000& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','VDATA1',NULL,NULL,NULL,47674,.0000209757939338004,5632512,srec,9,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '206D6F7373626572674077736A2E636F6D'; SREC.MAXVAL := '7A796C6F4065617274686C696E6B2E6E6574'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY(I 168373112176219000000000000000000000,635922979375528000000000000000000000& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','VDATA2',NULL,NULL,NULL,18650,.0000536193029490617,2748358,srec,12,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '80'; SREC.MAXVAL := 'C40233054B'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY( 0,1500474& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','NDATA1',NULL,NULL,NULL,9814,.000101895251681272,3564515,srec,2,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C302022D'; SREC.MAXVAL := 'C402330512'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY( 10144,1500417& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','NDATA2',NULL,NULL,NULL,33835,.0000295551943254027,10333816,srec,2,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C3020915'; SREC.MAXVAL := 'C402330512'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY( 10820,1500417& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','MEMBER_ID',NULL,NULL,NULL,29771,.000033589734976991,10491732,srec,2,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL := NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY(& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 0; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','MEMBER_ID_REAL',NULL,NULL,NULL,0,.0000000761842827710692,13126067,srec,1,0); END; ANALSTATS T "EVENT" DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3030333537363534393032373331393734353133'; SREC.MAXVAL := '79636F6E3037'; SREC.EAVS := 0; SREC.CHVALS := NULL; # SREC.NOVALS := DBMS_STATS.NUMARRAY(I 250207861976388000000000000000000000,630284706861154000000000000000000000& ); SREC.BKVALS := DBMS_STATS.NUMARRAY( ); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'EVENT','TAG',NULL,NULL,NULL,706,.00141643059490085,11838734,srec,1,0); END; TABLE "EVENT" CREATE FORMAT71TRIG CREATE TRIGGER event_time_trig BEFORE INSERT OR UPDATE ON event FOR EACH ROW x BEGIN IF :new.event_time IS NULL THEN :new.event_time := SYSDATE; END IF; END; ALTER TRIGGER "EVENT_TIME_TRIG" ENABLE ENDTABLE TABLE "EVENT" CREATE BITMAP INDEX "EVENT_EVENT_TYPE_IX" ON "EVENT" ("EVENT_TYPE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 272629760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIKLE_INDEX" LOGGING ANALSTATS I "" BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'EVENT_EVENT_TYPE_IX',NULL,NULL,NULL,1903582.35997522,15709,22,714,86526,1903582,2,0); END; ENDTABLE TABLE "EVENT" ANALCOMPUTE T "EVENT" ANALYZE TABLE "EVENT" ESTIMATE STATISTICS ENDTABLE EXIT EXIT Analysis of the above output reveals three useful sql commands we should make note of: GRANT SELECT ON EVENT TO BIKLEREAD CREATE BITMAP INDEX EVENT_EVENT_TYPE_IX ON EVENT (EVENT_TYPE ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 272629760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE BIKLE_INDEX LOGGING ANALYZE TABLE EVENT ESTIMATE STATISTICS Also, we note that the table has no primary key so this means we may ignore the possibility of foreign key constraints.