oracle脚本示例,Oracle 脚本示例

Oracle 脚本示例

truncate_event_store_schema.sql

TRUNCATE TABLE eventdb_user.EMHostNameVistastccom8080;

create_event_store_schema.sql

create table eventdb_user.EMHostNameVistastccom8080(

id NUMBER CONSTRAINT ID_PK PRIMARY KEY,

timeStamp decimal,

physicalHostName varchar(256),

environmentName varchar(256),

logicalHostName varchar(256),

serverType varchar(256),

serverName varchar(256),

componentType varchar(256),

componentProjectPathName varchar(1024),

componentName varchar(256),

eventType varchar(256),

severity integer,

operationalState int,

messageCode varchar(256),

messageDetail varchar(4000),

observationalState int,

deploymentName varchar(256));

);

-- INSERT statement need to use it to insure autoincrement functionality

CREATE SEQUENCE eventdb_user.autoincrement_id;

create index eventTime on eventdb_user.EMHostNameVistastccom8080(timeStamp);

create_event_store_user.sql

--Create a tablespace named EVENTDB_USER_DB. Change this value if a different

name is desired.

--Specify the name and the location of the file where the data related to

the tablespace

--created above will be stored. The location is by default the location determined by

--the database server/instance on which this script is run

--For example, for Windows c:\MyDatafiles\EVENTDB_USER_DB.dat, for Unix

/dev/home1/EVENTDB_USER_DB.dat

--Note that the name of the actual file need not be EVENTDB_USER_DB.dat

--Specify the size constraints

-- Window and Oracle 9i there is a limitation on file size, it is 2 GB.

This by default creats 4GB, add more files if you need more than 4 GB.

--- provide abosolute path if you preference is not default location

'C:\OracleDirectory\EVENTDB_USER_DB.dat' SIZE 2000M,

CREATE TABLESPACE EM_EVENTSTORE_DB

DATAFILE

'C:\oracle\product\10.2.0\oradata\orcl\EVENTDB_USER_DB.dat' SIZE 512M REUSE

AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED;

-- 'C:\oracle\product\10.2.0\oradata\orcl\EVENTDB_USER_DB1.dat' SIZE 512M

REUSE AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED --- provide abosolute path

if you preference is not defaultlocation 'C:\OracleDirectory\EVENTDB_USER_DB1.dat'

SIZE 2000M

-- when TABLESPACE is created with these options performance is degrading

gradually as more and more records added to schema EXTENT MANAGEMENT LOCAL SEGMENT

SPACE MANAGEMENT AUTO

--Create a new user EVENTDB_USER. Change the name if so desired. Password will

be same as

--the user name by default. This username and password will be used to create the

--connection pool on the application server. Also specify the tablespace

and the quota on

--the tablespace the user has. Note that if you used a different tablespace

name above,

--you will have to specify that tablespace name here.

CREATE USER EVENTDB_USER IDENTIFIED BY EVENTDB_USER

DEFAULT TABLESPACE EM_EVENTSTORE_DB

QUOTA UNLIMITED ON EM_EVENTSTORE_DB

TEMPORARY TABLESPACE temp

QUOTA 0M ON system;

--Modify the user name if the default user name was changed

GRANT CREATE session to EVENTDB_USER;

GRANT CREATE table to EVENTDB_USER;

GRANT CREATE procedure to EVENTDB_USER;

drop_event_store_schema.sql

DROP TABLE eventdb_user.EMHostNameVistastccom8080;

drop sequence eventdb_user.autoincrement_id;

drop_event_store_user.sql

--Drop the user that was created earlier. Note that if you chose a

different name for the

--user while creating the user, you will have to specify that name here.

DROP USER EVENTDB_USER CASCADE;

--Drop the tablespace that was created earlier. Note that if you chose a

different name for

--the tablespace while creating the user, you will have to specify that name here.

DROP TABLESPACE EM_EVENTSTORE_DB INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

--Manually delete the datafiles that were created. If you used the defaults

while creating

--the datafiles, the names would be EVENTDB_USER_DB1.dat'and 'EVENTDB_USER_DB2.dat'