/* CREATE OR REPLACE PROCEDURE D2OraArchive Version: 1.3 Date : 12.12.2007 (c) IPESOFT 1993, 2009. All rights reserved. Purpose: creation of tablespaces, users and tables needed for D2000 application Usage: SET SERVEROUTPUT ON <- turn on writing lines SET LINE 260 <- set line width SPOOL c:\D2ORAARC_out.sql <- set output file EXEC D2OraArchive; <- execute this procedure SPOOL OFF <- close output file and continue according to instructions in D2000 help */ AS AppName VARCHAR(20) := '???'; /*@@@ Name of application */ TempTablespace VARCHAR(20) := 'TEMP'; /*@@@ Always 'TEMP' unless you have modified Oracle database */ DatafilePath VARCHAR(80) := '???'; /*@@@*/ /* These parameters are used to create ARCHIV tablespace */ ArchivProcess VARCHAR(20) := 'SELF'; /*@@@ Name of archive process (/Wname, default 'SELF') */ ArchivInSyscfg BOOLEAN := False; /*@@@ Are Logfile tables located in Syscfg tablespace? */ ArchivCountDatafiles NUMBER := 1; /*@@@ Number of archiv datafiles */ ArchivStartDatafileSize VARCHAR(10) := '1000M'; /*@@@ Initial size of datafile ('10M','3000K' etc) */ ArchivMaxDatafileSize VARCHAR(10) := '2000M'; /*@@@ Maximum size of datafile (or '' if not extendible) */ ArchivDatafileSizeStep VARCHAR(10) := '100M'; /*@@@ Increment of datafile (or '' if db default ) */ AppNameU VARCHAR(20) := Upper(AppName); ArchivProcessU VARCHAR(20) := Upper(ArchivProcess); SyscfgTS VARCHAR(30) := AppNameU || '_TS_SYSCFG'; ArchivTS VARCHAR(50) := AppNameU || '_TS_ARCHIV'; PROCEDURE WriteInfoHeader IS FUNCTION B2Txt( B in BOOLEAN) return VARCHAR2 IS begin IF B THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; end; BEGIN DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */'); DBMS_OUTPUT.PUT_LINE('/* D2OraArchive v1.2 was run with these parameters '); DBMS_OUTPUT.PUT_LINE(' AppName : ' || AppName ); DBMS_OUTPUT.PUT_LINE(' TempTablespace : ' || TempTablespace); DBMS_OUTPUT.PUT_LINE(' DatafilePath : ' || DatafilePath); DBMS_OUTPUT.PUT_LINE(' ArchivInSyscfg : ' || B2Txt(ArchivInSyscfg)); DBMS_OUTPUT.PUT_LINE(' ArchivProcess : ' || ArchivProcess); DBMS_OUTPUT.PUT_LINE(' ArchivCountDatafiles : ' || ArchivCountDatafiles); DBMS_OUTPUT.PUT_LINE(' ArchivStartDatafileSize : ' || ArchivStartDatafileSize); DBMS_OUTPUT.PUT_LINE(' ArchivMaxDatafileSize : ' || ArchivMaxDatafileSize); DBMS_OUTPUT.PUT_LINE(' ArchivDatafileSizeStep : ' || ArchivDatafileSizeStep); DBMS_OUTPUT.PUT_LINE('*/'); DBMS_OUTPUT.PUT_LINE('/* ----------------------------------------------- */'); END WriteInfoHeader; procedure CreateNewArchiv is ArchivUser VARCHAR(50) := AppNameU || '_ARCHIV'; begin if ArchivProcessU <> 'SELF' then ArchivUser := AppNameU || '_' || ArchivProcess || '_ARCHIV'; ArchivTS := AppNameU || '_' || ArchivProcessU || '_TS_ARCHIV'; end if; DBMS_OUTPUT.PUT_LINE('/* Start of creating archiv */'); if not ArchivInSyscfg then DBMS_OUTPUT.PUT_LINE('CREATE TABLESPACE "' || ArchivTS || '" LOGGING DATAFILE '); FOR i IN 1 .. ArchivCountDatafiles LOOP DBMS_OUTPUT.PUT( '''' || DatafilePath || ArchivTS || i || '.ORA'' SIZE ' || ArchivStartDatafileSize); IF Length(TRIM(ArchivMaxDatafileSize)) <> 0 THEN DBMS_OUTPUT.PUT(' AUTOEXTEND ON'); IF LENGTH(TRIM(ArchivDatafileSizeStep)) <>0 THEN DBMS_OUTPUT.PUT(' NEXT ' || ArchivDatafileSizeStep); END IF; DBMS_OUTPUT.PUT(' MAXSIZE ' || ArchivMaxDatafileSize); END IF; IF i <> ArchivCountDatafiles THEN DBMS_OUTPUT.PUT_LINE(', '); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;'); DBMS_OUTPUT.PUT_LINE(chr(0)); end if; DBMS_OUTPUT.PUT_LINE('CREATE USER "' || ArchivUser || '" PROFILE "DEFAULT" IDENTIFIED BY "swx" DEFAULT TABLESPACE'); if ArchivInSyscfg then DBMS_OUTPUT.PUT_LINE(' "' || SyscfgTS || '"'); else DBMS_OUTPUT.PUT_LINE(' "' || ArchivTS || '"'); end if; DBMS_OUTPUT.PUT_LINE('TEMPORARY TABLESPACE "' || TempTablespace ||'" ACCOUNT UNLOCK;'); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE('GRANT UNLIMITED TABLESPACE TO "' || ArchivUser || '";'); DBMS_OUTPUT.PUT_LINE('GRANT "CONNECT" TO "' || ArchivUser || '";'); DBMS_OUTPUT.PUT_LINE('GRANT "DBA" TO "' || ArchivUser || '";'); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE('/* End of creating archiv */'); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE('/* Start of creating objects in archiv */'); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('/* Table: ARCHIV_DEF */'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('create table ' || ArchivUser || '.ARCHIV_DEF ('); DBMS_OUTPUT.PUT_LINE(' ID INTEGER not null,'); DBMS_OUTPUT.PUT_LINE(' NAME CHAR(65) not null,'); DBMS_OUTPUT.PUT_LINE(' DESCRIPT CHAR(129),'); DBMS_OUTPUT.PUT_LINE(' TYP SMALLINT not null,'); DBMS_OUTPUT.PUT_LINE(' PARENT INTEGER not null,'); DBMS_OUTPUT.PUT_LINE(' VALUE_TYPE SMALLINT not null,'); DBMS_OUTPUT.PUT_LINE(' REFNR SMALLINT not null,'); DBMS_OUTPUT.PUT_LINE(' MODIFY_TIME DATE,'); DBMS_OUTPUT.PUT_LINE(' TEXT_PALETTE INTEGER,'); DBMS_OUTPUT.PUT_LINE(' TECH_UNIT CHAR(13),'); DBMS_OUTPUT.PUT_LINE(' VHL FLOAT,'); DBMS_OUTPUT.PUT_LINE(' HL FLOAT,'); DBMS_OUTPUT.PUT_LINE(' LL FLOAT,'); DBMS_OUTPUT.PUT_LINE(' VLL FLOAT,'); DBMS_OUTPUT.PUT_LINE(' VHL_ID INTEGER,'); DBMS_OUTPUT.PUT_LINE(' HL_ID INTEGER,'); DBMS_OUTPUT.PUT_LINE(' LL_ID INTEGER,'); DBMS_OUTPUT.PUT_LINE(' VLL_ID INTEGER,'); DBMS_OUTPUT.PUT_LINE(' PASREFNR INTEGER,'); DBMS_OUTPUT.PUT_LINE(' READ_ONLY SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' SOURCE_OBJ INTEGER,'); DBMS_OUTPUT.PUT_LINE(' FUNCTION SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' DEEP_TIME INTEGER not null,'); DBMS_OUTPUT.PUT_LINE(' PERIOD INTEGER,'); DBMS_OUTPUT.PUT_LINE(' OFFSET INTEGER,'); DBMS_OUTPUT.PUT_LINE(' FILTER_VALUE FLOAT,'); DBMS_OUTPUT.PUT_LINE(' HIGH_LIMIT FLOAT,'); DBMS_OUTPUT.PUT_LINE(' HIGH_FILTER FLOAT,'); DBMS_OUTPUT.PUT_LINE(' LOW_LIMIT FLOAT,'); DBMS_OUTPUT.PUT_LINE(' LOW_FILTER FLOAT,'); DBMS_OUTPUT.PUT_LINE(' START_TRIG INTEGER,'); DBMS_OUTPUT.PUT_LINE(' STOP_TRIG INTEGER,'); DBMS_OUTPUT.PUT_LINE(' K_INT SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' VALID_PERC SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' START_TRIG_COND SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' STOP_TRIG_COND SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' INVERT_START_TRIG SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' INVERT_STOP_TRIG SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' ARCHIV SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' WRITE_METHOD SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' WRITE_DATA SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' ACTIVE_OBJ SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' SAVE_DATA SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' START_INT SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' IN_REORG SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' SOURCE_ROW SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' SOURCE_COL SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' REF_VALUE FLOAT,'); DBMS_OUTPUT.PUT_LINE(' STATISTIC_INTERVAL INTEGER,'); DBMS_OUTPUT.PUT_LINE(' MonotonicTime SMALLINT,'); DBMS_OUTPUT.PUT_LINE(' UTC_Offset INTEGER,'); DBMS_OUTPUT.PUT_LINE(' CALCSTATONDEMAND SMALLINT'); DBMS_OUTPUT.PUT_LINE(')'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('/* Table: LOG_DATA */'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('create table ' || ArchivUser || '.LOG_DATA ('); DBMS_OUTPUT.PUT_LINE(' EVENT CHAR(20),'); DBMS_OUTPUT.PUT_LINE(' EV_TIME DATE not null'); DBMS_OUTPUT.PUT_LINE(')'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || ArchivUser || '.LOG_DATA ( EVENT, EV_TIME) VALUES '); DBMS_OUTPUT.PUT_LINE(' (''MONOTONIC_TIME'', To_date(''01.01.2000 00:00'',''DD.MM.YYYY HH24:MI''))'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || ArchivUser || '.LOG_DATA ( EVENT, EV_TIME) VALUES '); DBMS_OUTPUT.PUT_LINE(' (''DIFF_PERIOD_TIME'', To_date(''01.01.2000 00:00'',''DD.MM.YYYY HH24:MI''))'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('/* Table: TABVER */'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('create table ' || ArchivUser || '.TABVER ('); DBMS_OUTPUT.PUT_LINE(' NAME CHAR(20) not null,'); DBMS_OUTPUT.PUT_LINE(' VERSION SMALLINT not null,'); DBMS_OUTPUT.PUT_LINE(' PREV_VER SMALLINT'); DBMS_OUTPUT.PUT_LINE(')'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('/* Index: UNIK_TABVER */'); DBMS_OUTPUT.PUT_LINE('/*==============================================================*/'); DBMS_OUTPUT.PUT_LINE('create unique index ' || ArchivUser || '.UNIK_TABVER on ' || ArchivUser || '.TABVER ('); DBMS_OUTPUT.PUT_LINE(' NAME ASC'); DBMS_OUTPUT.PUT_LINE(')'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || ArchivUser || '.TABVER ("NAME" ,"VERSION" ,"PREV_VER") VALUES (''ARCHIV_DEF'',9,0)'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || ArchivUser || '.TABVER ("NAME" ,"VERSION" ,"PREV_VER") VALUES (''LOG_DATA'',1,0)'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('commit;'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('/* End of creating objects in archiv */'); DBMS_OUTPUT.PUT_LINE(chr(0)); DBMS_OUTPUT.PUT_LINE(chr(0)); end CreateNewArchiv; begin dbms_output.enable(buffer_size => null); WriteInfoHeader; CreateNewArchiv; DBMS_OUTPUT.PUT_LINE('SPOOL OFF'); DBMS_OUTPUT.PUT_LINE('EXIT;'); end; /