[Rt-devel] oracle.Theme
Alexey G Misyurenko
mag at caravan.ru
Thu Jun 22 17:40:21 EDT 2006
Hello!
Our oracle DBA analize oracle usage and make decision that
attached theme is more effective.
We use Oracle 9.2.0.6
--
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794
-------------- next part --------------
CREATE SEQUENCE ATTACHMENTS_seq;
CREATE SEQUENCE QUEUES_seq;
CREATE SEQUENCE LINKS_seq;
CREATE SEQUENCE PRINCIPALS_seq;
CREATE SEQUENCE GROUPS_seq;
CREATE SEQUENCE SCRIPCONDITIONS_seq;
CREATE SEQUENCE TRANSACTIONS_seq;
CREATE SEQUENCE SCRIPS_seq;
CREATE SEQUENCE ACL_seq;
CREATE SEQUENCE GROUPMEMBERS_seq;
CREATE SEQUENCE CachedGroupMembers_seq;
CREATE SEQUENCE USERS_seq;
CREATE SEQUENCE TICKETS_seq;
CREATE SEQUENCE SCRIPACTIONS_seq;
CREATE SEQUENCE TEMPLATES_seq;
CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
CREATE SEQUENCE CUSTOMFIELDS_seq;
CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
CREATE SEQUENCE ATTRIBUTES_seq;
CREATE TABLE "ATTACHMENTS" (
ID NUMBER(11,0),
CONSTRAINT Attachments_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
TransactionId NUMBER(11,0) NOT NULL,
PARENT NUMBER(11,0) DEFAULT 0 NOT NULL,
MessageId VARCHAR2(160),
Subject VARCHAR2(255),
Filename VARCHAR2(255),
ContentType VARCHAR2(80),
ContentEncoding VARCHAR2(80),
Content CLOB,
Headers CLOB,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
)
TABLESPACE RTDATA LOB("CONTENT") STORE AS ( TABLESPACE
RTLOB
DISABLE
STORAGE IN ROW NOCACHE) LOB("HEADERS") STORE AS ( TABLESPACE
RTLOB
DISABLE
STORAGE IN ROW NOCACHE)
PARTITION BY RANGE ("CREATED") (
PARTITION "ATTACHMENTS_P0"
VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P1"
VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P2"
VALUES LESS THAN (TO_DATE('2006-02-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P3"
VALUES LESS THAN (TO_DATE('2006-03-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P4"
VALUES LESS THAN (TO_DATE('2006-04-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P5"
VALUES LESS THAN (TO_DATE('2006-05-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P6"
VALUES LESS THAN (TO_DATE('2006-06-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P7"
VALUES LESS THAN (TO_DATE('2006-07-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P8"
VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P9"
VALUES LESS THAN (TO_DATE('2006-09-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P10"
VALUES LESS THAN (TO_DATE('2006-10-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P11"
VALUES LESS THAN (TO_DATE('2006-11-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "ATTACHMENTS_P12"
VALUES LESS THAN (TO_DATE('2006-12-01','YYYY-MM-DD'))
TABLESPACE RTDATA );
CREATE INDEX IDX_ATTACHMENTS ON ATTACHMENTS ("CREATED") LOCAL (
PARTITION "ATTACHMENTS_IDX_P0" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P1" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P2" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P3" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P4" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P5" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P6" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P7" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P8" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P9" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P10" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P11" TABLESPACE RTINDEX ,
PARTITION "ATTACHMENTS_IDX_P12" TABLESPACE RTINDEX
);
CREATE INDEX Attachments2 ON Attachments (TransactionId) TABLESPACE RTINDEX;
CREATE INDEX Attachments3 ON Attachments (PARENT, TransactionId) TABLESPACE RTINDEX;
CREATE TABLE Queues (
ID NUMBER(11,0),
CONSTRAINT Queues_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(200) NOT NULL,
CONSTRAINT Queues_Name_Unique UNIQUE (NAME) USING INDEX TABLESPACE RTINDEX,
Description VARCHAR2(255),
CorrespondAddress VARCHAR2(120),
CommentAddress VARCHAR2(120),
InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
DefaultDueIn NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX Queues1 ON Queues (LOWER(NAME)) TABLESPACE RTINDEX;
CREATE INDEX Queues2 ON Queues (Disabled) TABLESPACE RTINDEX;
CREATE TABLE Links (
ID NUMBER(11,0),
CONSTRAINT Links_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
Base VARCHAR2(240),
Target VARCHAR2(240),
TYPE VARCHAR2(20) NOT NULL,
LocalTarget NUMBER(11,0) DEFAULT 0 NOT NULL,
LocalBase NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, TYPE) TABLESPACE RTINDEX;
CREATE INDEX Links2 ON Links (Base, TYPE) TABLESPACE RTINDEX;
CREATE INDEX Links3 ON Links (Target, TYPE) TABLESPACE RTINDEX;
CREATE INDEX Links4 ON Links(TYPE,LocalBase) TABLESPACE RTINDEX;
CREATE TABLE Principals (
ID NUMBER(11,0),
CONSTRAINT Principals_Key PRIMARY KEY(ID),
PrincipalType VARCHAR2(16),
ObjectId NUMBER(11,0),
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
)
ORGANIZATION
INDEX
TABLESPACE "RTINDEX";
CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId) TABLESPACE RTINDEX;
CREATE TABLE GROUPS (
ID NUMBER(11,0),
CONSTRAINT Groups_Key PRIMARY KEY(ID),
NAME VARCHAR2(200),
Description VARCHAR2(255),
Domain VARCHAR2(64),
TYPE VARCHAR2(64),
INSTANCE NUMBER(11,0) DEFAULT 0
)ORGANIZATION
INDEX
TABLESPACE "RTINDEX";
CREATE INDEX Groups1 ON GROUPS (LOWER( Domain), INSTANCE, LOWER(TYPE), ID) TABLESPACE RTINDEX;
CREATE INDEX Groups2 ON GROUPS (LOWER(TYPE), INSTANCE, LOWER(Domain)) TABLESPACE RTINDEX;
CREATE TABLE ScripConditions (
ID NUMBER(11, 0),
CONSTRAINT ScripConditions_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(200),
Description VARCHAR2(255),
ExecModule VARCHAR2(60),
Argument VARCHAR2(255),
ApplicableTransTypes VARCHAR2(60),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
CREATE TABLE Transactions (
ID NUMBER(11,0),
CONSTRAINT Transactions_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
ObjectType VARCHAR2(255),
ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
TimeTaken NUMBER(11,0) DEFAULT 0 NOT NULL,
TYPE VARCHAR2(20),
FIELD VARCHAR2(40),
OldValue VARCHAR2(255),
NewValue VARCHAR2(255),
ReferenceType VARCHAR2(255),
OldReference NUMBER(11,0),
NewReference NUMBER(11,0),
DATA VARCHAR2(255),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
)
PARTITION BY RANGE ("CREATED") (
PARTITION "TRANSACTIONS_P0"
VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P1"
VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P2"
VALUES LESS THAN (TO_DATE('2006-02-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P3"
VALUES LESS THAN (TO_DATE('2006-03-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P4"
VALUES LESS THAN (TO_DATE('2006-04-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P5"
VALUES LESS THAN (TO_DATE('2006-05-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P6"
VALUES LESS THAN (TO_DATE('2006-06-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P7"
VALUES LESS THAN (TO_DATE('2006-07-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P8"
VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P9"
VALUES LESS THAN (TO_DATE('2006-09-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P10"
VALUES LESS THAN (TO_DATE('2006-10-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P11"
VALUES LESS THAN (TO_DATE('2006-11-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TRANSACTIONS_P12"
VALUES LESS THAN (TO_DATE('2006-12-01','YYYY-MM-DD'))
TABLESPACE RTDATA );
CREATE INDEX IDX_TRANSACTIONS ON TRANSACTIONS ("CREATED") LOCAL (
PARTITION "TRANSACTIONS_IDX_P0" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P1" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P2" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P3" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P4" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P5" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P6" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P7" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P8" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P9" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P10" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P11" TABLESPACE RTINDEX ,
PARTITION "TRANSACTIONS_IDX_P12" TABLESPACE RTINDEX
);
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId) TABLESPACE RTINDEX;
CREATE TABLE Scrips (
ID NUMBER(11,0),
CONSTRAINT Scrips_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
Description VARCHAR2(255),
ScripCondition NUMBER(11,0) DEFAULT 0 NOT NULL,
ScripAction NUMBER(11,0) DEFAULT 0 NOT NULL,
ConditionRules CLOB,
ActionRules CLOB,
CustomIsApplicableCode CLOB,
CustomPrepareCode CLOB,
CustomCommitCode CLOB,
Stage VARCHAR2(32),
Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
TEMPLATE NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
)
LOB(ConditionRules) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW CACHE)
LOB(ActionRules) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW CACHE)
LOB(CustomIsApplicableCode) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW CACHE)
LOB(CustomPrepareCode) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW CACHE)
LOB(CustomCommitCode) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW CACHE);
CREATE TABLE ACL (
ID NUMBER(11,0),
CONSTRAINT ACL_Key PRIMARY KEY(ID),
PrincipalType VARCHAR2(25) NOT NULL,
PrincipalId NUMBER(11,0) NOT NULL,
RightName VARCHAR2(25) NOT NULL,
ObjectType VARCHAR2(25) NOT NULL,
ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
DelegatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
DelegatedFrom NUMBER(11,0) DEFAULT 0 NOT NULL
)ORGANIZATION
INDEX
TABLESPACE "RTINDEX";
CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId) TABLESPACE RTINDEX;
CREATE TABLE GroupMembers (
ID NUMBER(11,0),
CONSTRAINT GroupMembers_Key PRIMARY KEY(ID),
GroupId NUMBER(11,0) DEFAULT 0 NOT NULL,
MemberId NUMBER(11,0) DEFAULT 0 NOT NULL
)ORGANIZATION
INDEX
TABLESPACE "RTINDEX";
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId) TABLESPACE RTINDEX;
CREATE TABLE CachedGroupMembers (
ID NUMBER(11,0),
CONSTRAINT CachedGroupMembers_Key PRIMARY KEY(ID),
GroupId NUMBER(11,0),
MemberId NUMBER(11,0),
Via NUMBER(11,0),
ImmediateParentId NUMBER(11,0),
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
)ORGANIZATION
INDEX
TABLESPACE "RTINDEX";
CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled) TABLESPACE RTINDEX;
CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId) TABLESPACE RTINDEX;
CREATE TABLE Users (
ID NUMBER(11,0),
CONSTRAINT Users_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(200) NOT NULL,
CONSTRAINT Users_Name_Unique UNIQUE(NAME) USING INDEX TABLESPACE RTINDEX,
PASSWORD VARCHAR2(40),
Comments CLOB,
Signature CLOB,
EmailAddress VARCHAR2(120),
FreeFormContactInfo CLOB,
ORGANIZATION VARCHAR2(200),
RealName VARCHAR2(120),
NickName VARCHAR2(16),
Lang VARCHAR2(16),
EmailEncoding VARCHAR2(16),
WebEncoding VARCHAR2(16),
ExternalContactInfoId VARCHAR2(100),
ContactInfoSystem VARCHAR2(30),
ExternalAuthId VARCHAR2(100),
AuthSystem VARCHAR2(30),
Gecos VARCHAR2(16),
HomePhone VARCHAR2(30),
WorkPhone VARCHAR2(30),
MobilePhone VARCHAR2(30),
PagerPhone VARCHAR2(30),
Address1 VARCHAR2(200),
Address2 VARCHAR2(200),
City VARCHAR2(100),
State VARCHAR2(100),
Zip VARCHAR2(16),
Country VARCHAR2(50),
TIMEZONE VARCHAR2(50),
PGPKey CLOB,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
)
LOB(Comments) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE)
LOB(Signature) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE)
LOB(FreeFormContactInfo) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE)
LOB(PGPKey) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE)
PARTITION BY RANGE ("CREATED") (
PARTITION "USERS_P0"
VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P1"
VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P2"
VALUES LESS THAN (TO_DATE('2006-02-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P3"
VALUES LESS THAN (TO_DATE('2006-03-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P4"
VALUES LESS THAN (TO_DATE('2006-04-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P5"
VALUES LESS THAN (TO_DATE('2006-05-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P6"
VALUES LESS THAN (TO_DATE('2006-06-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P7"
VALUES LESS THAN (TO_DATE('2006-07-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P8"
VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P9"
VALUES LESS THAN (TO_DATE('2006-09-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P10"
VALUES LESS THAN (TO_DATE('2006-10-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P11"
VALUES LESS THAN (TO_DATE('2006-11-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "USERS_P12"
VALUES LESS THAN (TO_DATE('2006-12-01','YYYY-MM-DD'))
TABLESPACE RTDATA );
CREATE INDEX IDX_USERS ON USERS ("CREATED") LOCAL (
PARTITION "USERS_IDX_P0" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P1" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P2" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P3" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P4" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P5" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P6" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P7" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P8" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P9" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P10" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P11" TABLESPACE RTINDEX ,
PARTITION "USERS_IDX_P12" TABLESPACE RTINDEX
);
-- CREATE UNIQUE INDEX Users1 ON Users (Name);
CREATE INDEX Users2 ON Users( LOWER(NAME)) TABLESPACE RTINDEX;
CREATE INDEX Users4 ON Users (LOWER(EmailAddress)) TABLESPACE RTINDEX;
CREATE TABLE Tickets (
ID NUMBER(11, 0),
CONSTRAINT Tickets_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
TYPE VARCHAR2(16),
IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
Subject VARCHAR2(200) DEFAULT '[no subject]',
InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
Status VARCHAR2(10),
TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
Told DATE,
Starts DATE,
Started DATE,
Due DATE,
Resolved DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
)
PARTITION BY RANGE ("CREATED") (
PARTITION "TICKETS_P0"
VALUES LESS THAN (TO_DATE('2005-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P1"
VALUES LESS THAN (TO_DATE('2006-01-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P2"
VALUES LESS THAN (TO_DATE('2006-02-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P3"
VALUES LESS THAN (TO_DATE('2006-03-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P4"
VALUES LESS THAN (TO_DATE('2006-04-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P5"
VALUES LESS THAN (TO_DATE('2006-05-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P6"
VALUES LESS THAN (TO_DATE('2006-06-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P7"
VALUES LESS THAN (TO_DATE('2006-07-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P8"
VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P9"
VALUES LESS THAN (TO_DATE('2006-09-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P10"
VALUES LESS THAN (TO_DATE('2006-10-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P11"
VALUES LESS THAN (TO_DATE('2006-11-01','YYYY-MM-DD'))
TABLESPACE RTDATA ,
PARTITION "TICKETS_P12"
VALUES LESS THAN (TO_DATE('2006-12-01','YYYY-MM-DD'))
TABLESPACE RTDATA );
CREATE INDEX IDX_TICKETS ON TICKETS ("CREATED") LOCAL (
PARTITION "TICKETS_IDX_P0" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P1" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P2" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P3" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P4" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P5" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P6" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P7" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P8" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P9" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P10" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P11" TABLESPACE RTINDEX ,
PARTITION "TICKETS_IDX_P12" TABLESPACE RTINDEX
);
CREATE INDEX Tickets1 ON Tickets (Queue, Status) TABLESPACE RTINDEX;
CREATE INDEX Tickets2 ON Tickets (Owner) TABLESPACE RTINDEX;
CREATE INDEX Tickets4 ON Tickets (ID, Status) TABLESPACE RTINDEX;
CREATE INDEX Tickets5 ON Tickets (ID, EffectiveId) TABLESPACE RTINDEX;
CREATE INDEX Tickets6 ON Tickets (EffectiveId, TYPE) TABLESPACE RTINDEX;
CREATE TABLE ScripActions (
ID NUMBER(11,0),
CONSTRAINT ScripActions_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(200),
Description VARCHAR2(255),
ExecModule VARCHAR2(60),
Argument VARCHAR2(255),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
CREATE TABLE Templates (
ID NUMBER(11,0),
CONSTRAINT Templates_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
NAME VARCHAR2(200) NOT NULL,
Description VARCHAR2(255),
TYPE VARCHAR2(16),
LANGUAGE VARCHAR2(16),
TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL,
Content CLOB,
LastUpdated DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
)
LOB(Content) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE);
CREATE TABLE ObjectCustomFields (
ID NUMBER(11,0),
CONSTRAINT ObjectCustomFields_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
CustomField NUMBER(11,0) NOT NULL,
ObjectId NUMBER(11,0) NOT NULL,
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
CREATE TABLE ObjectCustomFieldValues (
ID NUMBER(11,0),
CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
CustomField NUMBER(11,0) NOT NULL,
ObjectType VARCHAR2(25) NOT NULL,
ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
Content VARCHAR2(255),
LargeContent CLOB,
ContentType VARCHAR2(80),
ContentEncoding VARCHAR2(80),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
)
LOB(LargeContent) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE);
CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content) TABLESPACE RTINDEX;
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId) TABLESPACE RTINDEX;
CREATE TABLE CustomFields (
ID NUMBER(11,0),
CONSTRAINT CustomFields_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(200),
TYPE VARCHAR2(200),
MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
Pattern VARCHAR2(255),
Repeated NUMBER(11,0) DEFAULT 0 NOT NULL,
Description VARCHAR2(255),
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
LookupType VARCHAR2(255),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE TABLE CustomFieldValues (
ID NUMBER(11,0),
CONSTRAINT CustomFieldValues_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
CustomField NUMBER(11,0),
NAME VARCHAR2(200),
Description VARCHAR2(255),
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
CREATE TABLE ATTRIBUTES (
ID NUMBER(11,0),
CONSTRAINT Attributes_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
NAME VARCHAR2(255) NOT NULL,
Description VARCHAR2(255),
Content CLOB,
ContentType VARCHAR(16),
ObjectType VARCHAR2(25) NOT NULL,
ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
)
LOB(Content) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE);
CREATE INDEX Attributes1 ON ATTRIBUTES(NAME) TABLESPACE RTINDEX;
CREATE INDEX Attributes2 ON ATTRIBUTES(ObjectType, ObjectId) TABLESPACE RTINDEX;
CREATE TABLE sessions (
ID VARCHAR2(32),
CONSTRAINT Sessions_Key PRIMARY KEY(ID) USING INDEX TABLESPACE RTINDEX,
a_session CLOB,
LastUpdated DATE
)
LOB(a_session) STORE AS ( TABLESPACE RTLOB
DISABLE STORAGE IN ROW NOCACHE);
More information about the Rt-devel
mailing list