Форум OlegON > Программы и оборудование для автоматизации торговли > Системы автоматизации торговли > Супермаг Плюс (Супермаг 2000)

Автоматизация инициализации базы данных : Супермаг Плюс (Супермаг 2000)

25.04.2024 8:45


« 2 3 4
█ 17.10.2016 10:00
OlegON
 
Да сюда прямо...
█ 17.10.2016 10:20
gf777
 
DbInit_InstanceInit.sql

Код:
SYS/sys@AZS
-- Базовая инициализация.

/*
Script to initialize Oracle database for use by Supermag.
Must be run only once.
*/

begin
	if USER<>'SYS' then
		raise_application_error(-20000,'In order to run InstanceInit.sql current user must be SYS.');
	end if;
end;
/
/* Supermag "users" group */
create role SUPERMAG_USER;
grant create session to SUPERMAG_USER;

/* Supermag administrative rights - for Supermag schema user only */
create role SUPERMAG_ADMIN;
grant create session to SUPERMAG_ADMIN;
grant create procedure to SUPERMAG_ADMIN;
grant create sequence to SUPERMAG_ADMIN;
grant create table to SUPERMAG_ADMIN;
grant create trigger to SUPERMAG_ADMIN;
grant create cluster to SUPERMAG_ADMIN;
grant create view to SUPERMAG_ADMIN;
grant create user to SUPERMAG_ADMIN with admin option;
grant alter user to SUPERMAG_ADMIN with admin option;
grant drop user to SUPERMAG_ADMIN with admin option;
grant create role to SUPERMAG_ADMIN with admin option;
grant drop any role to SUPERMAG_ADMIN with admin option;
grant grant any role to SUPERMAG_ADMIN with admin option;


/*
Revoke roles just created from the current user (SYS)
to avoid enabling to many roles for SYS at logon (if number of user
default roles is greater than some system parameter (default 20) then
the user cannot logon)
*/
revoke SUPERMAG_ADMIN from SYS;
revoke SUPERMAG_USER from SYS;

exit
█ 17.10.2016 10:21
gf777
 
DbInit_SchemaSys.sql

Код:
SYS/sys@AZS
WHENEVER SQLERROR EXIT 1 ROLLBACK;
WHENEVER OSERROR EXIT 2 ROLLBACK;
begin
	if USER<>'SYS' then
		raise_application_error(-20000,'In order to run SchemaSys.tsq current user must be SYS.');
	end if;
end;
/
create user SUPERMAG identified by "qqq"
	default tablespace USERS
	temporary tablespace TEMP
	quota unlimited on USERS;
begin
	if upper(nvl('','USERS'))!=upper('USERS') then
		execute immediate 'alter user SUPERMAG quota unlimited on ';
	end if;
end;
/
grant SUPERMAG_ADMIN to SUPERMAG;
grant SUPERMAG_USER to SUPERMAG;
alter user SUPERMAG default role SUPERMAG_ADMIN, SUPERMAG_USER;
grant select on V_$SESSION to SUPERMAG;
grant select on V_$INSTANCE to SUPERMAG;
grant execute on DBMS_ALERT to SUPERMAG;
grant execute on DBMS_PIPE to SUPERMAG;
grant select on DBA_USERS to SUPERMAG with grant option;
grant select on DBA_ROLES to SUPERMAG;
grant select on dba_constraints to SUPERMAG with grant option; 
grant select on dba_tab_columns to SUPERMAG with grant option; 
grant select on dba_cons_columns to SUPERMAG with grant option; 
grant create role to SUPERMAG;
grant alter any role to SUPERMAG;
grant create library to SUPERMAG;
grant create public synonym to SUPERMAG;
grant drop public synonym to SUPERMAG;
grant administer database trigger to SUPERMAG;
grant execute on DBMS_LOCK to SUPERMAG;
grant select on dba_jobs to SUPERMAG with grant option;
grant create database link to SUPERMAG;
grant create snapshot to SUPERMAG;
grant create table to SUPERMAG;
grant ANALYZE ANY to SUPERMAG;
grant create any index to SUPERMAG;
grant drop any index to SUPERMAG;
grant global query rewrite to SUPERMAG;
grant alter system to SUPERMAG; 
grant create user to SUPERMAG with admin option;
grant alter user to SUPERMAG with admin option;
grant drop user to SUPERMAG with admin option;
grant create role to SUPERMAG with admin option;
grant drop any role to SUPERMAG with admin option;
grant grant any role to SUPERMAG with admin option;
COMMIT;

exit
█ 17.10.2016 10:22
gf777
 
DbInit_Sys.sql

Код:
SUPERMAG/qqq@AZS
WHENEVER SQLERROR EXIT 1 ROLLBACK;
WHENEVER OSERROR EXIT 2 ROLLBACK;
create or replace 
view SUPERMAG_SESSIONS as select * from SYS.V_$SESSION
where status<>'KILLED';
CREATE TABLE SSMessages
(
	ErrCode number(10) not null primary key, 
	MsgText varchar2(4000) not null, 
	constraint SSMessages_BadCode check(ErrCode between -20999 and -20000)
);
CREATE TABLE SSLocks
(
	ObjType char(2) not null,	
	ID varchar2(150) not null,		
	SID number not null,	
	SERNUM number not null, 
	LockLabel varchar2(255) null, 
	TimePoint DATE default(sysdate) not null, 
	ClientProc number null, 
	constraint SSLocks_PK
		primary key (ObjType,ID),
	constraint SSCLocks_Proc
		check(ObjType='CP' or ClientProc is not null)
);
CREATE TABLE SSObjectTypes
(
	ObjType char(2) not null, 
	TypeName varchar2(100) not null,	
	ShortName varchar2(100) null,		
	IsDocument char(1) not null, 
	PostLevel number(5) default(2) not null, 
	constraint SSCObjectTypes_PK
		primary key(ObjType),
	constraint SSCObjectTypes_PostLvl
		check(PostLevel between 0 and 2)
);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel)
	values('DB','База данных','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel)
	values('CF','Конфигурация базы данных','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel)
	values('MR','Правила почтовой рассылки','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RB','База отчетов','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RF','Справочник','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PT','Почтовый модуль','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('CP','Пользовательская сессия','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('CM','Кассовый модуль','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('LK','Критическая секция','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SI','Пункт спецификации','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('UP','Упаковка','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DT','Тип дисконтной карты','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DC','Дисконтная карта','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DR','Скидки по типам дисконтных карт для артикулов','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DI','Скидки по дисконтным картам для артикулов','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('XC','Наценки по группам товаров','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('XA','Наценки по карточкам','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('GL','Уровни складских запасов','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('LP','Локальное ценообразование','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RV','Переоценки','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RA','Максимальные переоценки','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RI','Минимальные переоценки','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('XD','Скидки по группам товаров','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('LD','Пределы скидок по группам товаров','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('BP', 'Параметры дифференцированного ценообразования по маркетинговым группам', '0', 2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RR', 'Правила округления цен для карточек', '0', 2);
insert into SSObjectTypes(ObjType,TypeName,ShortName,IsDocument,PostLevel) 
	values('CD','Карточка складского учета','Карточка','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('BC','Штриховой код','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SH','Склад или магазин','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DG','Группа отделов','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('DU','Отдел','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SC','Кассир','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SS','Продавец-консультант','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('TO','Оператор ТСД','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PZ','Производственный участок','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('CL','Клиент или поставщик','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AS','Артикул поставщика','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RG','Группа отчётов','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('CZ','Кассовый отчет','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RQ','Удаленная команда','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PS','Должность','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('CH','Кассовый чек','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('FT','Свойства для артикулов','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PU','Список PLU для весов','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SP','План цен','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('OA','Массив объектов','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AP','Типы ценников для артикулов','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AI','Дополнительная информация для ценников','0',2);
insert into SSObjectTypes(ObjType, TypeName, IsDocument, PostLevel) 
    values('PR', 'Штриховой код с ценой', '0', 0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AT','Активность покупателя','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AX','Активность покупателя детальная','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AO','Активность покупателя оперативная','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('AY','Активность покупателя оперативная детальная','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SY','Запрос на синхронизацию баз данных','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PC','Процесс','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('BX','Короб','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('SA','Сервер приложений','0',0);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('RC','Остаток на складе закрытого периода','0',1);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('S1','Заказ от покупателя SAP','0',2);
insert into SSObjectTypes(ObjType,TypeName,IsDocument,PostLevel) 
	values('PG','Планограмма','0',2);
insert into SSObjectTypes(ObjType,TypeName,ShortName,IsDocument,PostLevel) 
	values('FI','Финансовое обязательство по поставке','Обязательство по поставке','0',2);
insert into SSObjectTypes(ObjType,TypeName,ShortName,IsDocument,PostLevel) 
	values('FO','Финансовое обязательство по отгрузке','Обязательство по отгрузке','0',2);
commit;	
CREATE TABLE SSDatabases
(
	DbID raw(16) not null, 
	DbName varchar2(255) not null, 
	constraint SSCDatabases_PK
		primary key(DbID),
	constraint SSCDatabases_Name
		unique(DbName)
);
CREATE TABLE SSSysInfo
(
	ParamName varchar2(50) not null,
	ParamValue varchar2(255) not null,
	constraint SSSysInfo_PK primary key(ParamName),
	constraint SSSysInfo_Pseudo
		check(ParamName not like 'Pseudo.'||'%')
);
CREATE TABLE SSSysInfoBackup
(
	BackupID number(10) not null, 
	BackupTime date not null, 
	ParamName varchar2(50) not null,
	ParamValue varchar2(255) not null,
	constraint SSSysInfoBackup_PK primary key(BackupID,ParamName)
);
CREATE TABLE SSSysInfoEx
(
	DbID raw(16) not null, 
	ParamName varchar2(50) not null,
	ParamValue varchar2(255) not null,
	constraint SSSysInfoEx_PK primary key(DbID,ParamName),
	constraint SSSysInfoEx_Db
		foreign key(DbID)
		references SSDatabases(DbID)
		on delete cascade
);
insert into SSSysInfo(ParamName,ParamValue) values('ConfigFlags',TO_CHAR(0));
begin
insert into SSSysInfo(ParamName,ParamValue) values('Version','Generating '||'1.029');
end;
/
insert into SSSysInfo(ParamName,ParamValue) values('Customer',0);
insert into SSSysInfo(ParamName,ParamValue) values('ExtendedCardLog','0');
insert into SSSysInfo(ParamName,ParamValue) values('ExtendedClientLog','0');
insert into SSSysInfo(ParamName,ParamValue) values('UpgradeStep','Upgrade in progress');
insert into SSSysInfo(ParamName,ParamValue) values('GrpViewMode','1');
insert into SSSysInfo(ParamName,ParamValue) values('NotEditBoxForActiveCard','1');
insert into SSSysInfo(ParamName,ParamValue) values('OnlyOneBoxForCard','1');
insert into SSSysInfo(ParamName,ParamValue) values('GenTime',to_char(sysdate,'DD.MM.YYYY HH24:MI:SS'));
insert into SSSysInfo(ParamName,ParamValue) values('StoreLocMin','1');
insert into SSSysInfo(ParamName,ParamValue) values('CompanyMin','1');
insert into SSSysInfo(ParamName,ParamValue) values('DocNumberSize','10');
insert into SSSysInfo(ParamName,ParamValue) values('CashTimeSpan',to_char(10));
insert into SSSysInfo(ParamName,ParamValue) values('CashDocGen','1');
insert into SSSysInfo(ParamName,ParamValue) values('FixPriceNoDisc','1');
insert into SSSysInfo(ParamName,ParamValue) values('AuctionNoDisc','1');
insert into SSSysInfo(ParamName,ParamValue) values('TimeSpanSaleDocs', '0');
insert into SSSysInfo(ParamName,ParamValue) values('CalcTaxes', '1');
insert into SSSysInfo(ParamName,ParamValue) values('Composition', '0');
insert into SSSysInfo(ParamName,ParamValue) values('POSLoadCardType',	 
	to_char((power(2,0))+(power(2,1))+(power(2,5))));
insert into SSSysInfo(ParamName,ParamValue) values('PostDataTypes',	 
	to_char(1));
insert into SSSysInfo(ParamName,ParamValue) values('PostDataTypesFull',	 
	to_char(1
			+2
			+4
			+8
			+16
			+32
			+64));
insert into SSSysInfo(ParamName,ParamValue) values('OperPollRate',0);
insert into SSSysInfo(ParamName,ParamValue) values('PosDepartments',0);
insert into SSSysInfo(ParamName,ParamValue) values('ShowRequest',0);
insert into SSSysInfo(ParamName,ParamValue) values('ShowRqPrinter','*');
insert into SSSysInfo(ParamName,ParamValue) values('ComplexArticlePriceOriginAC', '0');
insert into SSSysInfo(ParamName,ParamValue) values('Licenses', '0');
begin
	if not FALSE then
		insert into SSSysInfo(ParamName,ParamValue) values('DBID', SYS_GUID());
	end if;
end;
/
insert into SSSysInfo(ParamName,ParamValue) values('Legislation', '0');
insert into SSSysInfo(ParamName,ParamValue) values('DbInitCreateCardClass', '0');
insert into SSSysInfo ( ParamName, ParamValue ) values ( 'PurgeEqualPricesACWI', '1' );
insert into SSSysInfo ( ParamName, ParamValue ) values ( 'PurgeEqualPricesACIW', '1' );
insert into SSSysInfo ( ParamName, ParamValue ) values ( 'SetCashPrice', '0');
insert into SSSysInfo ( ParamName, ParamValue ) values ( 'RepriceCopyCountry', 'NOCOUNTRY' );
insert into SSSysInfo ( ParamName, ParamValue ) values ( 'PCR_GenActsDuplicateArticlesTreatment', '4' );
insert into SSSysInfo ( ParamName, ParamValue ) values ('FifoDefAlg', 0);
insert into SSSysInfo ( ParamName, ParamValue ) values ('FifoAnyLocIncome', '1');
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('FifoCauseForOperations', to_char(2)||','||
											to_char(7)||','||
											to_char(8)||','||
											to_char(17)||','||
											to_char(3)||','||
											to_char(4));
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('FeaturesMaxValueLength', 10);
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('ClientIDZone', -1);
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('ClientIDZonesEx', 0);
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('LocalUTCOffset', '0');
insert into SSSysInfo ( ParamName, ParamValue ) values 
	('SummerTime', '0');
insert into SSSysInfo(ParamName, ParamValue) values('CheckPostVer', '0');
commit;
CREATE TABLE SSMigratorArc
(
	ArcCode varchar2(30) not null primary key,	
	ImportedComps number(38) default(0) not null 
);
CREATE TABLE SSMigratorLog
(
	ArcCode varchar2(30) not null,	
	CompID number(38) not null,		
	Amount number(38) not null,		
	primary key(ArcCode,CompID)
);
create global temporary table TTObjList
(
	ObjType char(2) not null,	
	ID varchar2(150) not null,		
	constraint TTCObjList_PK
		primary key (ObjType,ID)
) on commit preserve rows;
create global temporary table TTFailedLock
(
	ObjType char(2) not null,				
	ID varchar2(150) not null,					
	ErrCode number(10) not null,						
	Message varchar2(4000) null	
) on commit preserve rows;
CREATE SEQUENCE SSEventLogSeq MAXVALUE 1000000000 CYCLE ORDER;
CREATE TABLE SSEventLog
(
	EventTime date not null, 
	RecID number default 0 not null, 
	MsgLine number(38) default(1) not null, 
	EventSession number not null, 
	EventMessage varchar2(1000) not null, 
	EventLogin varchar2(30) null, 
	EventSource varchar2(255) null, 
	Employee number(10) default -2 not null,
	WSName varchar2(100) null, 
	UserName varchar2(30) null , 
	OsUser varchar2(30) null, 
	constraint SSCEventLog_PK
		primary key (EventTime, RecID, MsgLine)
);
CREATE TABLE SAHolidaysCategory
(
	ID number(5) not null,							
	Name varchar2(255) not null,		
	Preset char(1) default '0' not null,	
	Periodicity number(5) not null,				
	constraint SACHolidaysCategory_PK		primary key (ID),
	constraint SACHolidaysCategory_Name		unique (Name),
	constraint SACHolidaysCategory_NegID	check(ID>=0)
);
Insert into SAHolidaysCategory(ID, Name, Preset, Periodicity)
	values(0, 'праздничные дни', '1', 0);
Insert into SAHolidaysCategory(ID, Name, Preset, Periodicity)
	values(1, 'дополнительные выходные дни', '1', 1);
Insert into SAHolidaysCategory(ID, Name, Preset, Periodicity)
	values(2, 'дополнительные рабочие дни', '1', 1);
CREATE TABLE SAHolidays
(
	DayType number(5) not null,	
	TheDay date not null,			
	constraint SACHolidays_PK
		primary key (DayType, TheDay),
	constraint SACHolidays_Type 
		foreign key(DayType)
		references SAHolidaysCategory(ID)
		on delete cascade
);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('01.01.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('02.01.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('07.01.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('23.02.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('08.03.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('01.05.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('09.05.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('12.06.1904','DD.MM.YYYY'), 0);
Insert into SAHolidays(TheDay, DayType)
	values(To_Date('04.11.1904','DD.MM.YYYY'), 0);
create global temporary table TTHolyNames
(
	TheDay date not null,			
	TheTitle varchar2(255) not null 
) on commit delete rows;
CREATE TABLE SSAppServerUsers
(
	AppServer varchar2(100) not null,		
	SessionID varchar2(40) not null,	
	SessionType number(5) default(0) not null, 
	Employee number(10) not null,							 
	WSName varchar2(100) null,				
	UserName varchar2(30) null,				
	OsUser varchar2(30) null,					
	Program varchar2(48) null,					
	TimePoint DATE default(sysdate) not null,			
	constraint SSAppServerUsers_PK
		primary key (AppServer,SessionID),
	constraint SSAppServerUsers_ST
		check (SessionType in ( 0, 
								2,
								3,
								5,
								6,
								8,
								9))
);
CREATE TABLE SSAppServerSessions
(
	AUDSID number not null,									
	AppServer varchar2(100) not null,		
	SessionID varchar2(40) null,		
	constraint SSAppServerSessions_PK
		primary key (AUDSID),
	constraint SSAppServerSessions_Ses
		foreign key(AppServer,SessionID)
		references SSAppServerUsers(AppServer,SessionID)
);
COMMIT;

exit
█ 17.10.2016 10:23
gf777
 
DbInit_SysStats.sql

Код:
SYS/sys@AZS
begin
	if USER<>'SYS' then
		raise_application_error(-20000,'In order to run SysStats.sql current user must be SYS.');
	end if;
end;
/

BEGIN
	FOR T IN ( SELECT TABLE_NAME
                    FROM ALL_TABLES
                    WHERE	TEMPORARY = 'N'  
						AND OWNER LIKE 'SYS')
    LOOP
		BEGIN
			EXECUTE IMMEDIATE 'ANALYZE TABLE "'||T.TABLE_NAME||'" COMPUTE STATISTICS';
		EXCEPTION WHEN OTHERS THEN
			RAISE_APPLICATION_ERROR(-20000,'Analyze table '||T.TABLE_NAME||' error:'||SQLERRM);
		END; 
    END LOOP;
    FOR I IN ( SELECT INDEX_NAME
                    FROM ALL_INDEXES
                    WHERE	TEMPORARY	= 'N' 
						AND INDEX_TYPE	= 'NORMAL'   
						AND OWNER LIKE 'SYS')
    LOOP
		BEGIN
			EXECUTE IMMEDIATE 'ANALYZE INDEX "'||I.INDEX_NAME||'" COMPUTE STATISTICS';
		EXCEPTION WHEN OTHERS THEN
			RAISE_APPLICATION_ERROR(-20000,'Analyze index '||I.INDEX_NAME||' error:'||SQLERRM);
		END; 
    END LOOP;
END;
/

exit
█ 17.10.2016 10:25
OlegON
 
Ох, жесть :) В расширенном режиме просто приаттачить можно к сообщению, если кому вдруг понадобятся...
█ 17.10.2016 10:30
gf777
 
Звиняйте, щас исправим)
█ 17.10.2016 10:35
gf777
 
См. вложение

Там четыре скрипта:
DbInit_InstanceInit.sql
DbInit_SchemaSys.sql
DbInit_Sys.sql
DbInit_SysStats.sql

Остальное берётся из ResForInit.exe
Вложения
Тип файла: 7z DbInit.7z (4.6 Кб, 29 просмотров)
█ 17.10.2016 12:32
gf777
 
Примерный скрипт инициализации во вложении.
Скрипты не тронуты, только вот здесь добавил exit, а то в этих конкретных sql-файлах не было exit'а
Код:
(
echo @%TMP%\DbInit\Operation.sql
echo @%TMP%\DbInit\Cards.sql
echo @%TMP%\DbInit\Clients.sql
echo @%TMP%\DbInit\Price.sql
echo @%TMP%\DbInit\RepCfg.sql
echo exit
) | %sqlplusExe% -s %user%/%pass%@%ORACLE_SID%
Порядок выполнения сдамплен с помощью procmon'а.
Фильтр:
Process Name - sqlplus.exe
Operation Name - Process Create

Версия СМ - 1.0.29
Вложения
Тип файла: zip DbInit.zip (1.7 Кб, 25 просмотров)
█ 17.10.2016 13:07
OlegON
 
Думаю, что за исключением входных скриптов, остальные можно ловить не procmon, а просто скопировать текст из верхнего окошка генератора после инициализации, там скрипты и перечисляются...
Часовой пояс GMT +3, время: 08:45.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.