/*
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;
/