20Mg export file (NOT FOR A MODEM!!!)

Employees Schema

Accounts Schema
Utilities

Employees Schema ERDs

Simple Employees Schema

Complex Employees Schema

Employees Schema Creation Script


CREATE TABLE Task(
        task_id INTEGER NOT NULL
       ,project_id INTEGER NOT NULL
       ,name VARCHAR2(32) NOT NULL
       ,CONSTRAINT XPKTask PRIMARY KEY (task_id) USING INDEX TABLESPACE INDX
       ,CONSTRAINT FK1Task FOREIGN KEY (project_id) REFERENCES Project
) TABLESPACE DATA;

CREATE INDEX XFK1Task ON Task(project_id) TABLESPACE INDX;

DROP TABLE ProjectType CASCADE CONSTRAINTS;

CREATE TABLE ProjectType (
       projecttype_id	INTEGER NOT NULL,
       name		VARCHAR2(32) NOT NULL,
       CONSTRAINT	XPKProjectType PRIMARY KEY (projecttype_id)
);


DROP TABLE Division CASCADE CONSTRAINTS;

CREATE TABLE Division (
       division_id          INTEGER NOT NULL,
       name                 VARCHAR2(32) NOT NULL,
       city                 VARCHAR2(32) NULL,
       state                CHAR(2) NULL,
       country              VARCHAR2(32) NULL,
       CONSTRAINT XPKDivision 
              PRIMARY KEY (division_id)
);


DROP TABLE Department CASCADE CONSTRAINTS;

CREATE TABLE Department (
       department_id        INTEGER NOT NULL,
       name                 VARCHAR2(32) NOT NULL,
       division_id          INTEGER NOT NULL,
       city                 VARCHAR2(32) NULL,
       state                CHAR(2) NULL,
       country              VARCHAR2(32) NULL,
       CONSTRAINT XPKDepartment 
              PRIMARY KEY (department_id), 
       CONSTRAINT FK1Department
              FOREIGN KEY (division_id)
                             REFERENCES Division
);

CREATE INDEX XFK1Department ON Department
(
       division_id                    ASC
);


DROP TABLE Project CASCADE CONSTRAINTS;

CREATE TABLE Project (
       project_id           INTEGER NOT NULL,
       department_id        INTEGER NOT NULL,
       projecttype_id       INTEGER NOT NULL,
       name                 VARCHAR2(32) NOT NULL,
       completiondate       DATE NULL,
       budget               FLOAT NULL,
       cost                 FLOAT NULL,
       CONSTRAINT XPKProject 
              PRIMARY KEY (project_id), 
       CONSTRAINT FK1Project
              FOREIGN KEY (projecttype_id)
                             REFERENCES ProjectType, 
       CONSTRAINT FK2Project
              FOREIGN KEY (department_id)
                             REFERENCES Department
);

CREATE INDEX XFK1Project ON Project
(
       department_id                  ASC
);

CREATE INDEX XFK2Project ON Project
(
       projecttype_id                 ASC
);


DROP TABLE Task CASCADE CONSTRAINTS;

CREATE TABLE Task (
       task_id              INTEGER NOT NULL,
       project_id           INTEGER NOT NULL,
       name                 VARCHAR2(32) NOT NULL,
       CONSTRAINT XPKTask 
              PRIMARY KEY (task_id), 
       CONSTRAINT FK1Task
              FOREIGN KEY (project_id)
                             REFERENCES Project
);

CREATE INDEX XFK1Task ON Task
(
       project_id                     ASC
);


DROP TABLE Employee CASCADE CONSTRAINTS;

CREATE TABLE Employee (
       employee_id          INTEGER NOT NULL,
       manager_id           INTEGER,
       name                 VARCHAR2(32) NOT NULL,
       jobtitle             VARCHAR2(32) NULL,
       hiredate             DATE NULL,
       compensation         FLOAT NULL,
       contracttype         CHAR(16) NULL,
       skills               VARCHAR2(256) NULL,
       certifications       VARCHAR2(256) NULL,
       CONSTRAINT XPKEmployee 
              PRIMARY KEY (employee_id), 
       CONSTRAINT FK1Employee
              FOREIGN KEY (manager_id)
                             REFERENCES Employee
);

DROP TABLE Assignment CASCADE CONSTRAINTS;

CREATE TABLE Assignment (
       task_id              INTEGER NOT NULL,
       employee_id          INTEGER NOT NULL,
       CONSTRAINT XPKAssignment 
              PRIMARY KEY (task_id, employee_id), 
       CONSTRAINT FK1Assignment
              FOREIGN KEY (task_id)
                             REFERENCES Task, 
       CONSTRAINT FK2Assignment
              FOREIGN KEY (employee_id)
                             REFERENCES Employee
);

CREATE INDEX XFK1Assignment ON Assignment
(
       employee_id                    ASC
);

CREATE INDEX XFK2Assignment ON Assignment
(
       task_id                        ASC
);


DROP TABLE Product CASCADE CONSTRAINTS;

CREATE TABLE Product (
       product_id           INTEGER NOT NULL,
       project_id           INTEGER NOT NULL,
       name		    VARCHAR2(32) NOT NULL,
       revenue              FLOAT NULL,
       cost                 FLOAT NULL,
       CONSTRAINT XPKProduct 
              PRIMARY KEY (product_id), 
       CONSTRAINT FK1Product
              FOREIGN KEY (project_id)
                             REFERENCES Project
);

CREATE INDEX XFK1Product ON Product
(
       project_id                     ASC
);

DROP TABLE ManagerOf CASCADE CONSTRAINTS;

CREATE TABLE ManagerOf (
       manager_id           INTEGER NOT NULL,
       division_id          INTEGER,
       department_id        INTEGER,
       project_id           INTEGER,
       product_id           INTEGER,
       CONSTRAINT XPKManagerOf 
              PRIMARY KEY (manager_id), 
       CONSTRAINT FK1ManagerOf 
              FOREIGN KEY (division_id)
                             REFERENCES Division, 
       CONSTRAINT FK2ManagerOf 
              FOREIGN KEY (department_id)
                             REFERENCES Department, 
       CONSTRAINT FK3ManagerOf 
              FOREIGN KEY (product_id)
                             REFERENCES Product, 
       CONSTRAINT FK4ManagerOf 
              FOREIGN KEY (project_id)
                             REFERENCES Project, 
       CONSTRAINT FK5ManagerOf 
              FOREIGN KEY (manager_id)
                             REFERENCES Employee
);

CREATE INDEX XFK1ManagerOf ON ManagerOf
(
       project_id                     ASC
);

CREATE INDEX XFK2ManagerOf ON ManagerOf
(
       product_id                     ASC
);

CREATE INDEX XFK3ManagerOf ON ManagerOf
(
       department_id                  ASC
);

CREATE INDEX XFK4ManagerOf ON ManagerOf
(
       division_id                    ASC
);

Employees Schema Foreign Key Index Creation

drop index XFK1Department;
CREATE INDEX XFKDepartment_Division ON Department(division_id);

drop index XFK1Project;
drop index XFK2Project;
CREATE INDEX XFKProject_Department ON Project(department_id);
CREATE INDEX XFK1Project_ProjectType on project(projecttype_id);

drop index XFK1Task;
create index XFKTask_Project ON Task(project_id);

create index XFKEmployee_Manager ON Employee(manager_id);

drop index XFK1Assignment;
drop index XFK2Assignment;
create index XFKAssignment_employee ON Assignment(employee_id);
create index XFKAssignment_task ON Assignment(task_id);

drop index XFK1Product;
create index XFKProduct_product ON Product(project_id);

drop index XFK1ManagerOf;
drop index XFK2ManagerOf;
drop index XFK3ManagerOf;
drop index XFK4ManagerOf; 
create index XFKManagerOf_Project ON ManagerOf(project_id);
create index XFKManagerOf_Product ON ManagerOf(product_id);
create index XFKManagerOf_Department ON ManagerOf(department_id);
create index XFKManagerOf_Division ON ManagerOf(division_id);

Employees Schema Sequences

drop sequence division_seq;
drop sequence department_seq;
drop sequence project_seq;
drop sequence product_seq;
drop sequence projecttype_seq;
drop sequence task_seq;
drop sequence employee_seq;
drop sequence assignment_seq;

create sequence division_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence department_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence project_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence product_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence projecttype_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence task_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence employee_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence assignment_seq start with 1 increment by 1 nomaxvalue nocycle;

Employees Schema Data Generation

insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Technical Development','New York','NY','United States of America');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Sales Research','New York','NY','United States of America');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Market Research','Los Angeles','CA','United States of America');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Production','Los Angeles','CA','United States of America');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Technical Production','Los Angeles','CA','United States of America');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Executive Branch','London',NULL,'United Kingdom');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Head Office','Hong Kong','HK','China');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'China Office','Peking','MC','China');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Manufacturing','Tapei',NULL,'Taiwan');
insert into division(division_id,name,city,state,country) values(division_seq.nextval,'Prospecting','Houston','TX','United States of America');
commit;

DECLARE
	CURSOR cDivisions IS SELECT * FROM division;
	i INTEGER DEFAULT 10;
	j INTEGER DEFAULT 0;
BEGIN
	FOR rDivision IN cDivisions LOOP
		FOR j IN 1..i LOOP
			insert into department(department_id,division_id,name,city,state,country)
			values(department_seq.nextval,rDivision.division_id,'Division '||TRIM(TO_CHAR(j,'0990')),rDivision.city,rDivision.state,rDivision.country);
			commit;
		END LOOP;
	END LOOP;
END;
/

insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Research');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Development');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Maintenance');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Human Resources');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Medical Insurance');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Construction');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Military Procrument');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Retail Marketing');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Sales');
insert into projecttype(projecttype_id,name) values(projecttype_seq.nextval,'Sales Research');
commit;

DECLARE
	CURSOR cProjects IS SELECT d.department_id, p.projecttype_id FROM projecttype p, department d;
	i INTEGER DEFAULT 10;
	j INTEGER DEFAULT 0;
BEGIN
	FOR rProject IN cProjects LOOP
		FOR j IN 1..i LOOP
			insert into project(project_id,department_id,projecttype_id,name,completiondate,budget,cost)
			values(project_seq.nextval,rProject.department_id,rProject.projecttype_id,
			SUBSTR((SELECT name FROM department WHERE department_id = rProject.department_id)
			||(SELECT name FROM projecttype WHERE projecttype_id = rProject.projecttype_id),1,32)
			,SYSDATE+(j*10)
			,(i*j)+2000
			,((i*j)+2000)+10
			);
			commit;
		END LOOP;
	END LOOP;
END;
/

DECLARE
	CURSOR cTasks IS SELECT project_id FROM project;
	i INTEGER DEFAULT 3;
	j INTEGER DEFAULT 0;
BEGIN
	FOR rTask IN cTasks LOOP
		FOR j IN 1..i LOOP
			insert into task(task_id,project_id,name)
			values(task_seq.nextval,rTask.project_id,DECODE(j,1,'Task One',2,'Task Two','Other'));
			commit;
		END LOOP;
	END LOOP;
END;
/

DECLARE
	CURSOR cProducts IS SELECT project_id FROM project;
	i INTEGER DEFAULT 3;
	j INTEGER DEFAULT 0;
BEGIN
	FOR rProduct IN cProducts LOOP
		FOR j IN 1..i LOOP
			insert into product(product_id,project_id,name,revenue,cost)
			values(product_seq.nextval,rProduct.project_id,DECODE(j,1,'Product One',2,'Product Two','Other')
			,(i*j)+500
			,((i*j)+350)+10);
			commit;
		END LOOP;
	END LOOP;
END;
/

--
--employees
--

--CEO
insert into employee(employee_id,manager_id,name,jobtitle,hiredate,compensation,contracttype,skills,certifications)
values(employee_seq.nextval,NULL,'Jim the CEO','CEO',SYSDATE-365,250000,'Permanent','Smart dude','MBA,Long list of stuff');

--Division managers
DECLARE
	CURSOR cDivisions IS SELECT * FROM division;
BEGIN
	FOR rDivision IN cDivisions LOOP
		insert into employee(employee_id,manager_id,name,jobtitle,hiredate,compensation,contracttype,skills,certifications)
		values(employee_seq.nextval,(select employee_id from employee where jobtitle = 'CEO')
		,'Name '||TO_CHAR(employee_seq.currval,'0990'),SUBSTR(TRIM(rDivision.name)||' Division Manager',1,32)
		,SYSDATE-365,150000,'Permanent','Another smart dude','MBA,Long list of stuff');
		insert into managerof(manager_id,division_id) values(employee_seq.currval,rDivision.division_id);
		commit;
	END LOOP;
END;
/

--Department managers
DECLARE
	CURSOR cDepartments IS SELECT * FROM department;
BEGIN
	FOR rDepartment IN cDepartments LOOP
		insert into employee(employee_id,manager_id,name,jobtitle,hiredate,compensation,contracttype,skills,certifications)
		values(employee_seq.nextval
		,(select manager_id from managerof where division_id = rDepartment.division_id)
		,'Name '||TO_CHAR(employee_seq.currval,'0990'),SUBSTR(TRIM(rDepartment.name)||' Department Manager',1,32)
		,SYSDATE-365,100000,'Permanent','Another smart dude','MBA,Long list of stuff');
		insert into managerof(manager_id,department_id) values(employee_seq.currval,rDepartment.department_id);
		commit;
	END LOOP;
END;
/

Accounts Schema

Employees Schema
Utilities

Accounts Schema ERDs

Denormalized Accounts Schema

Normalized Accounts Schema

Accounts Schema Create User

set timing off;

spool log\createUser.log;

--drop user accounts cascade;
create user accounts identified by accounts
default tablespace data
temporary tablespace temp
quota unlimited on data
quota unlimited on INDX
quota unlimited on objects
quota unlimited on temp
;
grant connect,resource to accounts;
grant unlimited tablespace to accounts;
grant query rewrite to accounts;

spool off;

exit;

Accounts Schema Creation Script

set timing off;
spool log\schema.log;

CREATE TABLE Type(
   type       CHAR(1) NOT NULL,
   text       VARCHAR2(32) NULL,
   CONSTRAINT XPK_Type PRIMARY KEY (type) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE SubType(
   subtype    CHAR(1) NOT NULL,
   text       VARCHAR2(32) NULL,
   CONSTRAINT XPK_SubType PRIMARY KEY (subtype) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE COA(
   coa#       CHAR(5) NOT NULL,
   type       CHAR(1) NOT NULL,
   subtype    CHAR(1) NOT NULL,
   text       VARCHAR2(32) NOT NULL,
   lastdate   DATE NULL,
   balance    NUMBER(20,2) NULL,
   ytd        NUMBER NULL,
   CONSTRAINT XPK_COA PRIMARY KEY (coa#) USING INDEX TABLESPACE INDX, 
   CONSTRAINT FK_COA_Type FOREIGN KEY (type) REFERENCES Type,
   CONSTRAINT FK_COA_Subtype FOREIGN KEY (subtype) REFERENCES SubType, 
   CONSTRAINT XAK_COA UNIQUE (text) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;
CREATE INDEX XFK_COA_Type ON COA(type) TABLESPACE INDX;
CREATE INDEX XFK_COA_Subtype ON COA(subtype) TABLESPACE INDX;

CREATE TABLE Posting(
   posting    CHAR(32) NOT NULL,
   drcoa#     CHAR(5) NOT NULL,
   crcoa#     CHAR(5) NOT NULL,
   CONSTRAINT XPK_Posting PRIMARY KEY (posting) USING INDEX TABLESPACE INDX, 
   CONSTRAINT FK_Posting_DRCOA# FOREIGN KEY (drcoa#) REFERENCES COA,
   CONSTRAINT FK_Posting_CRCOA# FOREIGN KEY (crcoa#) REFERENCES COA 
) TABLESPACE DATA;
CREATE INDEX XFK_Posting_DRCOA# ON Posting(drcoa#) TABLESPACE INDX;
CREATE INDEX XFK_Posting_CRCOA# ON Posting(crcoa#) TABLESPACE INDX;

CREATE TABLE Customer(
   customer_id  NUMBER NOT NULL,
   name         VARCHAR2(64) NOT NULL,
   ticker       CHAR(10) NOT NULL,
   address      VARCHAR2(256) NULL,
   phone        VARCHAR2(20) NULL,
   fax          VARCHAR2(20) NULL,
   email        VARCHAR2(32) NULL,
   lastdate     DATE NULL,
   balance      NUMBER(10,2) NULL,
   days_credit  NUMBER NULL,
   CONSTRAINT   XPK_Customer PRIMARY KEY (customer_id) USING INDEX TABLESPACE INDX,
   CONSTRAINT   AK_Customer_Name UNIQUE (name) USING INDEX TABLESPACE INDX,
   CONSTRAINT   AK_Customer_Ticker UNIQUE (ticker) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE Supplier(
   supplier_id  NUMBER NOT NULL,
   name         VARCHAR2(64) NOT NULL,
   ticker       CHAR(10) NOT NULL,
   address      VARCHAR2(256) NULL,
   phone        VARCHAR2(20) NULL,
   fax          VARCHAR2(20) NULL,
   email        VARCHAR2(32) NULL,
   lastdate     DATE NULL,
   balance      NUMBER(10,2) NULL,
   days_credit  NUMBER NULL,
   CONSTRAINT   XPK_Supplier PRIMARY KEY (supplier_id) USING INDEX TABLESPACE INDX,
   CONSTRAINT   XAK_Supplier_Name UNIQUE (name) USING INDEX TABLESPACE INDX,
   CONSTRAINT   XAK_Supplier_Ticker UNIQUE (ticker) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE Orders(
   order_id     NUMBER NOT NULL,
   customer_id  NUMBER NULL,
   supplier_id  NUMBER NULL,
   type         CHAR(1) NOT NULL,
   amount       NUMBER(10,2) NOT NULL,
   dte          DATE NOT NULL,
   CONSTRAINT   XPK_Orders PRIMARY KEY (order_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT   FK_Orders_Type FOREIGN KEY (type) REFERENCES Type, 
   CONSTRAINT   FK_Orders_Customer FOREIGN KEY (customer_id) REFERENCES Customer,
   CONSTRAINT   FK_Orders_Supplier FOREIGN KEY (supplier_id) REFERENCES Supplier 
) TABLESPACE DATA;
CREATE INDEX XFK_Orders_Type ON Orders(type) TABLESPACE INDX;
CREATE INDEX XFK_Orders_Customer ON Orders(customer_id) TABLESPACE INDX;
CREATE INDEX XFK_Orders_Supplier ON Orders(supplier_id) TABLESPACE INDX;

CREATE TABLE Transactions(
   transaction_id NUMBER NOT NULL,
   type           CHAR(1) NOT NULL,
   customer_id    NUMBER NULL,
   supplier_id    NUMBER NULL,
   order_id       NUMBER NULL,
   amount         NUMBER(10,2) NOT NULL,
   dte            DATE NOT NULL,
   drcoa#         CHAR(5) NOT NULL,
   crcoa#         CHAR(5) NOT NULL,
   CONSTRAINT     XPK_Transactions PRIMARY KEY (transaction_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT     FK_Trans_Type FOREIGN KEY (type) REFERENCES Type, 
   CONSTRAINT     FK_Trans_DRCOA# FOREIGN KEY (drcoa#) REFERENCES COA, 
   CONSTRAINT     FK_Trans_CRCOA# FOREIGN KEY (crcoa#) REFERENCES COA, 
   CONSTRAINT     FK_Trans_Customer FOREIGN KEY (customer_id) REFERENCES Customer, 
   CONSTRAINT     FK_Trans_Supplier FOREIGN KEY (supplier_id) REFERENCES Supplier, 
   CONSTRAINT     FK_Trans_Orders FOREIGN KEY (order_id) REFERENCES Orders
) TABLESPACE DATA;
CREATE INDEX XFK_Trans_Type ON Transactions(type) TABLESPACE INDX;
CREATE INDEX XFK_Trans_DRCOA# ON Transactions(drcoa#) TABLESPACE INDX;
CREATE INDEX XFK_Trans_CRCOA# ON Transactions(crcoa#) TABLESPACE INDX;
CREATE INDEX XFK_Trans_Customer ON Transactions(customer_id) TABLESPACE INDX;
CREATE INDEX XFK_Trans_Supplier ON Transactions(supplier_id) TABLESPACE INDX;
CREATE INDEX XFK_Trans_Orders ON Transactions(order_id) TABLESPACE INDX;

CREATE TABLE CashBook(
   cheque_id    NUMBER NOT NULL,
   amount       NUMBER(10,2) NOT NULL,
   dte          DATE NOT NULL,
   text         VARCHAR2(32) NULL,
   drcoa#       CHAR(5) NOT NULL,
   crcoa#       CHAR(5) NOT NULL,
   CONSTRAINT   XPK_CashBook PRIMARY KEY (cheque_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT   FK_CashBook_DRCOA# FOREIGN KEY (drcoa#) REFERENCES COA,
   CONSTRAINT   FK_CashBook_CRCOA# FOREIGN KEY (crcoa#) REFERENCES COA 
) TABLESPACE DATA;
CREATE INDEX XFK_CashBook_DRCOA# ON CashBook(drcoa#) TABLESPACE INDX;
CREATE INDEX XFK_CashBook_CRCOA# ON CashBook(crcoa#) TABLESPACE INDX;

CREATE TABLE CashBookLine(
   cheque_id      NUMBER NOT NULL,
   seq#           NUMBER NOT NULL,
   transaction_id NUMBER NULL,
   amount         NUMBER(10,2) NOT NULL,
   CONSTRAINT     XPK_CashBookLine PRIMARY KEY (cheque_id, seq#) USING INDEX TABLESPACE INDX, 
   CONSTRAINT     FK_CBL_Transaction FOREIGN KEY (transaction_id) REFERENCES Transactions, 
   CONSTRAINT     FK_CBL_CashBook FOREIGN KEY (cheque_id) REFERENCES CashBook
) TABLESPACE DATA;
CREATE INDEX FK_CBL_Transaction ON CashBookLine(cheque_id) TABLESPACE INDX;
CREATE INDEX FK_CBL_CashBook ON CashBookLine(transaction_id) TABLESPACE INDX;

CREATE TABLE Category(
   category_id    NUMBER NOT NULL,
   text           VARCHAR2(32) NOT NULL,
   CONSTRAINT     XPK_Category PRIMARY KEY (category_id) USING INDEX TABLESPACE INDX,
   CONSTRAINT     XAK_Category_text UNIQUE (text) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE Stock(
   stock_id       NUMBER NOT NULL,
   category_id    NUMBER NOT NULL,
   text           VARCHAR2(128) NOT NULL,
   min            NUMBER NOT NULL,
   max            NUMBER NOT NULL,
   qtyonhand      NUMBER NULL,
   totalvalue     NUMBER(20,2) NULL,
   lastdate       DATE NULL,
   description    CLOB NULL,
   lastprice      NUMBER(10,2) NULL,
   image          BLOB NULL,
   CONSTRAINT     XPK_Stock PRIMARY KEY (stock_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT     FK_Stock_Category FOREIGN KEY (category_id) REFERENCES Category,
   CONSTRAINT     AK_Stock_text UNIQUE (text) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;
CREATE INDEX XFK_Stock_Category ON Stock(category_id) TABLESPACE INDX;

CREATE TABLE StockMovement(
   stockmovement_id NUMBER NOT NULL,
   stock_id         NUMBER NOT NULL,
   qty              NUMBER NOT NULL,
   price            NUMBER(10,2) NOT NULL,
   dte              DATE NOT NULL,
   CONSTRAINT       XPK_StockMovement PRIMARY KEY (stockmovement_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT       FK_SM_Stock FOREIGN KEY (stock_id) REFERENCES Stock
) TABLESPACE DATA;
CREATE INDEX XFK_SM_Stock ON StockMovement(stock_id) TABLESPACE INDX;

CREATE TABLE TransactionsLine(
   transaction_id   NUMBER NOT NULL,
   seq#             NUMBER NOT NULL,
   amount           NUMBER(10,2) NOT NULL,
   stockmovement_id NUMBER NOT NULL,
   CONSTRAINT       XPK_TransactionsLine PRIMARY KEY (transaction_id, seq#) USING INDEX TABLESPACE INDX, 
   CONSTRAINT       FK_TL_SM FOREIGN KEY (stockmovement_id) REFERENCES StockMovement, 
   CONSTRAINT       FK_TL_Trans FOREIGN KEY (transaction_id) REFERENCES Transactions
) TABLESPACE DATA;
CREATE INDEX XFK_TL_SM ON TransactionsLine(stockmovement_id) TABLESPACE INDX;
CREATE INDEX XFK_TL_Trans ON TransactionsLine(transaction_id) TABLESPACE INDX;

CREATE TABLE OrdersLine(
   order_id         NUMBER NOT NULL,
   seq#             NUMBER NOT NULL,
   amount           NUMBER(10,2) NOT NULL,
   stockmovement_id NUMBER NOT NULL,
   CONSTRAINT       XPK_OrdersLine PRIMARY KEY (order_id, seq#) USING INDEX TABLESPACE INDX, 
   CONSTRAINT       FK_OL_SM FOREIGN KEY (stockmovement_id) REFERENCES StockMovement, 
   CONSTRAINT       FK_OL_Orders FOREIGN KEY (order_id) REFERENCES Orders
) TABLESPACE DATA;
CREATE INDEX XFK_OL_SM ON OrdersLine(stockmovement_id) TABLESPACE INDX;
CREATE INDEX XFK_OL_Orders ON OrdersLine(order_id) TABLESPACE INDX;

CREATE TABLE Period(
   year         NUMBER NOT NULL,
   period       NUMBER NOT NULL,
   datelow      DATE NOT NULL,
   datehigh     DATE NOT NULL,
   text         VARCHAR2(32) NOT NULL,
   CONSTRAINT   XPK_Period PRIMARY KEY (year, period) USING INDEX TABLESPACE INDX
) TABLESPACE DATA;

CREATE TABLE PeriodSum(
   year         NUMBER NOT NULL,
   period       NUMBER NOT NULL,
   coa#         CHAR(5) NOT NULL,
   amount       NUMBER(20,2) NULL,
   CONSTRAINT   XPK_PeriodSum PRIMARY KEY (year, period, coa#) USING INDEX TABLESPACE INDX, 
   CONSTRAINT   FK_PeriodSum_COA# FOREIGN KEY (coa#) REFERENCES COA, 
   CONSTRAINT   FK_PeriodSum_YP FOREIGN KEY (year, period) REFERENCES Period
) TABLESPACE DATA;
CREATE INDEX XFK_PeriodSum_COA# ON PeriodSum(year,period) TABLESPACE INDX;
CREATE INDEX XFK_PeriodSum_YP ON PeriodSum(coa#) TABLESPACE INDX;

CREATE TABLE GeneralLedger(
   generalledger_id NUMBER NOT NULL,
   coa#             CHAR(5) NOT NULL,
   dr               NUMBER(10,2) NOT NULL,
   cr               NUMBER(10,2) NOT NULL,
   dte              DATE NOT NULL,
   CONSTRAINT       XPK_GeneralLedger PRIMARY KEY (generalledger_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT       FK_GL_COA# FOREIGN KEY (coa#) REFERENCES COA
) TABLESPACE DATA;
CREATE INDEX XFK_GL_COA# ON GeneralLedger(coa#) TABLESPACE INDX;
CREATE INDEX XAK_GL_COA#DTE ON GeneralLedger(coa#,dte) TABLESPACE INDX;

CREATE TABLE Supplier_Stock (
   supplier_id      NUMBER NOT NULL,
   stock_id         NUMBER NOT NULL,
   price            NUMBER(10,2) NOT NULL,
   CONSTRAINT       XPK_Supplier_Stock PRIMARY KEY (supplier_id, stock_id) USING INDEX TABLESPACE INDX, 
   CONSTRAINT       FK_Supplier_Stock FOREIGN KEY (stock_id) REFERENCES Stock, 
   CONSTRAINT       FK_Supplier_Supplier FOREIGN KEY (supplier_id) REFERENCES Supplier
) TABLESPACE DATA;
CREATE INDEX XFK_Supplier_Stock ON Supplier_Stock(supplier_id) TABLESPACE INDX;
CREATE INDEX XFK_Supplier_Supplier ON Supplier_Stock(stock_id) TABLESPACE INDX;

spool off;
exit;

Accounts Schema Sequences

set timing off;
spool log\sequences.log;

create sequence category_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence customer_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence supplier_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence stock_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence stockmovement_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence generalledger_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence orders_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence transactions_seq start with 1 increment by 1 nomaxvalue nocycle;
create sequence cheque_seq start with 1 increment by 1 nomaxvalue nocycle;

spool off;
exit;

Accounts Schema Triggers

set timing off;
spool c:\tmp\triggers.log;

--GL only ever inserted into, adjustmnents are new entries, could build two triggers which raise an error
create or replace trigger GeneralLedger after insert on generalledger for each row
declare
	vdrcoa# char(5);
	vcrcoa# char(5);
	vtype char(1);
begin
	select type into vtype from coa where coa# = :new.coa#;
	if vtype in ('A','E') then
		update coa set lastdate = :new.dte, balance = balance + :new.dr - :new.cr where coa# = :new.coa#;
	elsif vtype in ('L','I') then
		update coa set lastdate = :new.dte, balance = balance - :new.dr + :new.cr where coa# = :new.coa#;
	end if;	
exception when others then
	dbms_output.put_line('TRIG: GeneralLedger INSERT '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger GeneralLedger compile;

--
--serious performance problems
--
alter trigger GeneralLedger disable;
drop trigger GeneralLedger;

create or replace trigger StockMovement after insert on stockmovement for each row
declare
	vsupplier_id integer;
begin
	update stocksource set price = price*1.001 
	where supplier_id = 
	(
		select a.supplier_id
		from (
			select supplier_id, min(price) as price
			from stocksource
			where stock_id = :new.stock_id
			group by supplier_id
			order by price
		) a where rownum = 1
	)
	and stock_id = :new.stock_id;
exception when others then
	dbms_output.put_line('TRIG: StockMovement INSERT '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger StockMovement compile;

--
--serious performance problems (simply bumps prices, unneccessary!)
--
alter trigger StockMovement disable;
drop trigger StockMovement;

--insert - pass +ve, update - pass new-old, delete - pass -ve
create or replace trigger TransactionsInsert after insert on transactions for each row
begin
	if :new.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:new.drcoa#,ABS(:new.amount),0,:new.dte);
		insert into generalledger values (generalledger_seq.nextval,:new.crcoa#,0,ABS(:new.amount),:new.dte);
--
--serious performance problems
--
--		if :new.type = 'S' then
--			update customer set lastdate=:new.dte,balance=balance+:new.amount where customer_id=:new.customer_id;
--		elsif :new.type = 'P' then
--			update supplier set lastdate=:new.dte,balance=balance+:new.amount where supplier_id=:new.supplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: Transactions INSERT '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger TransactionsInsert compile;

create or replace trigger TransactionsUpdate after update of amount,dte on transactions for each row
begin
	if :new.amount-:old.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:new.drcoa#,0,ABS(:old.amount-:new.amount),:new.dte);
		insert into generalledger values (generalledger_seq.nextval,:new.crcoa#,ABS(:old.amount-:new.amount),0,:new.dte);
--
--serious performance problems
--
--		if :new.type = 'S' then
--			update customer set lastdate=:new.dte,balance=balance-(:old.amount-:new.amount) where customer_id=:new.customer_id;
--		elsif :new.type = 'P' then
--			update supplier set lastdate=:new.dte,balance=balance-(:old.amount-:new.amount) where supplier_id=:new.supplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: Transactions UPDATE '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger TransactionsUpdate compile;

create or replace trigger TransactionsDelete after delete on transactions for each row
begin
	if :old.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:old.drcoa#,0,ABS(:old.amount),:old.dte);
		insert into generalledger values (generalledger_seq.nextval,:old.crcoa#,ABS(:old.amount),0,:old.dte);
--
--serious performance problems
--
--		if :old.type = 'S' then
--			update customer set lastdate=:old.dte,balance=balance-:old.amount where customer_id=:old.customer_id;
--		elsif :old.type = 'P' then
--			update supplier set lastdate=:old.dte,balance=balance-:old.amount where supplier_id=:old.supplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: Transactions DELETE '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger TransactionsDelete compile;

--insert - pass +ve, update - pass new-old, delete - pass -ve
create or replace trigger CashbookInsert after insert on cashbook for each row
declare
	vdrcoa# char(5);
	vcrcoa# char(5);
	vtype char(1);
	vcustomer_id integer;
	vsupplier_id integer;
begin
	if :new.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:new.drcoa#,ABS(:new.amount),0,:new.dte);
		insert into generalledger values (generalledger_seq.nextval,:new.crcoa#,0,ABS(:new.amount),:new.dte);
--
--serious performance problems
--
--		select type,customer_id,supplier_id into vtype,vcustomer_id,vsupplier_id
--		from transactions where transaction_id = :new.transaction_id;
--
--serious performance problems
--
--		if vtype = 'S' then
--			update customer set lastdate=:new.dte,balance=balance-:new.amount where customer_id=vcustomer_id;
--		elsif vtype = 'P' then
--			update supplier set lastdate=:new.dte,balance=balance-:new.amount where supplier_id=vsupplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: CashBook INSERT '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger CashbookInsert compile;

create or replace trigger CashbookUpdate after update of amount,dte on cashbook for each row
declare
	vdrcoa# char(5);
	vcrcoa# char(5);
	vtype char(1);
	vcustomer_id integer;
	vsupplier_id integer;
begin
	if :new.amount-:old.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:new.drcoa#,0,ABS(:old.amount-:new.amount),:new.dte);
		insert into generalledger values (generalledger_seq.nextval,:new.crcoa#,ABS(:old.amount-:new.amount),0,:new.dte);
--
--serious performance problems
--
--		select type,customer_id,supplier_id into vtype,vcustomer_id,vsupplier_id
--		from transactions where transaction_id = :new.transaction_id;
--
--serious performance problems
--
--		if vtype = 'S' then
--			update customer set lastdate=:new.dte,balance=balance+(:old.amount-:new.amount) where customer_id=vcustomer_id;
--		elsif vtype = 'P' then
--			update supplier set lastdate=:new.dte,balance=balance+(:old.amount-:new.amount) where supplier_id=vsupplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: CashBook UPDATE '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger CashbookUpdate compile;

create or replace trigger CashbookDelete after delete on cashbook for each row
declare
	vdrcoa# char(5);
	vcrcoa# char(5);
	vtype char(1);
	vcustomer_id integer;
	vsupplier_id integer;
begin
	if :old.amount != 0 then
		insert into generalledger values (generalledger_seq.nextval,:old.drcoa#,0,ABS(:old.amount),:old.dte);
		insert into generalledger values (generalledger_seq.nextval,:old.crcoa#,ABS(:old.amount),0,:old.dte);
--
--serious performance problems
--
--		select type,customer_id,supplier_id into vtype,vcustomer_id,vsupplier_id
--		from transactions where transaction_id = :old.transaction_id;
--
--serious performance problems
--
--		if vtype = 'S' then
--			update customer set lastdate=:old.dte,balance=balance+:old.amount where customer_id=vcustomer_id;
--		elsif vtype = 'P' then
--			update supplier set lastdate=:old.dte,balance=balance+:old.amount where supplier_id=vsupplier_id;
--		end if;
	end if;
exception when others then
	dbms_output.put_line('TRIG: CashBook DELETE '||SQLERRM(SQLCODE));
	raise;
end;
/
alter trigger CashbookDelete compile;

spool off;
exit;

Accounts Schema Mass Activity Simulation

Accounts Schema Simulation Jobs Management Script

--add a job to execute every 1 second
DECLARE
	jobno NUMBER;
	i INTEGER DEFAULT 1;
BEGIN
	FOR j IN 1..50 LOOP
		DBMS_JOB.SUBMIT(jobno,'SIMULATE;',SYSDATE,'SYSDATE+1/86400');
	END LOOP;
	COMMIT;
END;
/
column user format a10;
select job,log_user "User",LAST_SEC "Prev",THIS_SEC "Curr",NEXT_SEC "Next",TOTAL_TIME "Time",decode(BROKEN,'N','Attempted','Y','No Attempt') "Broken",FAILURES "Failure",WHAT||' every '||trim(INTERVAL) "Job"
from user_jobs;

--remove all jobs
declare
	cursor cJobs is select job from user_jobs;
begin
	for rJob in cJobs loop
		dbms_job.remove(rJob.job);
	end loop;
end;
/
commit;

column user format a10;
select job,log_user "User",LAST_SEC "Prev",THIS_SEC "Curr",NEXT_SEC "Next",TOTAL_TIME "Time",decode(BROKEN,'N','Attempted','Y','No Attempt') "Broken",FAILURES "Failure",WHAT||' every '||trim(INTERVAL) "Job"
from user_jobs;

Accounts Schema Simulation Execution Script

set serveroutput on;
spool C:\Manuscripts\DigitalPress\Appendices\accounts\simulate\simulate.log;
exec dbms_output.enable(1000000);
exec simulate;
exec dbms_output.disable;
set serveroutput off;
spool off;

Accounts Schema Simulation Scripting

spool c:\tmp\simulate.log;

create or replace function rand(n IN NUMBER DEFAULT 1) return integer is
	rand integer;
begin
	--gives a random number between 1 and 1000
	select to_number(to_char(SYSTIMESTAMP,'FF3'))+1 into rand from dual;
	rand := (rand/1000)*n;
	if rand = 0 then rand := 1; end if;
	return rand;
exception when others then
	dbms_output.put_line('FUNC: rand '||SQLERRM(SQLCODE));
end;
/
alter function rand compile;

create or replace function getTime(ptime float default 0) return float is
begin
	return to_number(to_char(systimestamp,'SSSSS.FF')) - ptime;
exception when others then
	dbms_output.put_line('FUNC: getTime '||SQLERRM(SQLCODE));
end;
/
alter function getTime compile;

create or replace procedure getPrice
(
	 pstock_id IN integer default 0
	,psupplier_id OUT integer
	,pprice OUT float
) as
begin
	--find cheapest price for stock_id
	select a.supplier_id, a.price into psupplier_id, pprice
	from (
		select supplier_id, min(price) as price
		from stocksource
		where stock_id = pstock_id
		group by supplier_id
		order by price
	) a where rownum = 1;
exception when others then
	dbms_output.put_line('PROC: getPrice '||SQLERRM(SQLCODE));
end;
/
alter procedure getprice compile;

drop table auditSim;
--create table auditSim(objtype varchar2(16),name varchar2(32),total float,average float,executions integer);
create table auditSim(objtype varchar2(16),name varchar2(32),tme float,opt number);

create or replace procedure simAudit(pobjtype in varchar2,pname in varchar2,ptime float,popt integer) as
--	vaverage float;
--	vexecutions integer;
begin
	insert into auditSim values(pobjtype,pname,ptime,popt);
--	begin
--		select average,executions+1 into vaverage,vexecutions from auditSim where objtype=pobjtype and name=pname;
--		update auditSim set executions=vexecutions,total=total+ptime,average=(total+ptime)/vexecutions where objtype=pobjtype and name=pname; 
--	exception when no_data_found then
--		insert into auditSim(objtype,name,total,average,executions) values(pobjtype,pname,ptime,ptime,1);
--	end;
	commit;
exception when others then
	dbms_output.put_line('PROC: simAudit '||SQLERRM(SQLCODE));
end;
/
alter procedure simAudit compile;

create or replace procedure InsertSANDP (pdte IN date DEFAULT SYSDATE,popt in integer) as
	vcustomer_id integer;
	vsupplier_id integer;
	vstock_id integer;
	vorder_id integer;
	vstockmovement_id integer;
	vtransaction_id integer;
	vcheque_id integer;
	vqty integer;
	vlines integer;
	vqtyonhand integer;
	vprice float;
	vamount float;
	vseq# integer;
	vmarkup float default 1.01; 
	vtime float;
begin

	vtime := to_number(to_char(systimestamp,'SSSSS.FF'));

	select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
	select max(stock_id) into vstock_id from stock; vstock_id := rand(vstock_id);
	vqty := rand(20); vlines := vqty;
	select qtyonhand into vqtyonhand from stock where stock_id = vstock_id;

	if vqty - vqtyonhand > 0 then

		vqty := vqty - vqtyonhand; --vqty - vqtyonhand will always be positive
		getPrice(vstock_id,vsupplier_id,vprice);
		vamount := vqty * vprice;

		--place po, gen transaction and pay7 for it for vqty - vqtyonhand

		insert into orders values(orders_seq.nextval,null,vsupplier_id,'P',(vamount*-1),pdte)
			returning order_id into vorder_id;

		insert into transactions values(transactions_seq.nextval,'P',null,vsupplier_id,vorder_id,(vamount*-1),pdte
			,(select drcoa# from posting where posting='PurchaseInvoice')
			,(select crcoa# from posting where posting='PurchaseInvoice'))
			returning transaction_id into vtransaction_id;

		insert into cashbook values(cheque_seq.nextval,(vamount*-1),pdte,'Purchase Invoice '||to_char(vtransaction_id)
			,(select drcoa# from posting where posting='PurchaseInvoice_CashBook')
			,(select crcoa# from posting where posting='PurchaseInvoice_CashBook')
			,vtransaction_id)
			returning cheque_id into vcheque_id;

		for vseq# in 1..vlines loop

			insert into stockmovement values(stockmovement_seq.nextval,vstock_id,vqty,vprice,pdte)
				returning stockmovement_id into vstockmovement_id;

			insert into ordersline values(vorder_id,vseq#,(vprice*-1),vstockmovement_id);

			insert into transactionsline values(vtransaction_id,vseq#,(vprice*-1),vstockmovement_id);

			insert into cashbookline values(vcheque_id,vseq#,(vprice*-1));

		end loop;
		
		dbms_output.put_line('PROC: InsertSANDP Purchase Complete '||to_char(getTime(vtime)));

	end if;

	--make sale, gen transaction and pay for it for vqty - vqtyonhand

	getPrice(vstock_id,vsupplier_id,vprice);
	vprice := vprice * vmarkup;
	vamount := vqty * vprice;
 
	insert into orders values(orders_seq.nextval,vcustomer_id,null,'S',vamount,pdte)
		returning order_id into vorder_id;

	insert into transactions values(transactions_seq.nextval,'S',vcustomer_id,null,vorder_id,vamount,pdte
		,(select drcoa# from posting where posting='SalesInvoice')
		,(select crcoa# from posting where posting='SalesInvoice'))
		returning transaction_id into vtransaction_id;

	insert into cashbook values(cheque_seq.nextval,vamount,pdte,'Sales Invoice '||to_char(vtransaction_id)
		,(select drcoa# from posting where posting='SalesInvoice_CashBook')
		,(select crcoa# from posting where posting='SalesInvoice_CashBook')
		,vtransaction_id)
		returning cheque_id into vcheque_id;

	for vseq# in 1..vlines loop

		insert into stockmovement values(stockmovement_seq.nextval,vstock_id,(vqty*-1),vprice,pdte)
			returning stockmovement_id into vstockmovement_id;

		insert into ordersline values(vorder_id,vseq#,vprice,vstockmovement_id);

		insert into transactionsline values(vtransaction_id,vseq#,vprice,vstockmovement_id);

		insert into cashbookline values(vcheque_id,vseq#,vprice);

	end loop;

	dbms_output.put_line('PROC: InsertSANDP Sale Complete '||to_char(getTime(vtime)));
	simAudit('PROCEDURE','InsertSandP',getTime(vtime),popt);

	commit;
exception when others then
	dbms_output.put_line('PROC: InsertSANDP '||SQLERRM(SQLCODE));
	rollback;
end;
/
alter procedure InsertSANDP compile;

create or replace procedure UpdateSORP (pdte IN date DEFAULT SYSDATE,popt in integer) as
	vminorder_id integer;
	vmaxorder_id integer;
	vorder_id integer;
	vtransaction_id integer;
	vcheque_id integer;
	voption integer;
	vmod float;
	vtime float;
begin

	--not supposed to execute a stockmovement (too difficult)

	vtime := to_number(to_char(systimestamp,'SSSSS.FF'));

	select min(order_id),max(order_id) into vminorder_id,vmaxorder_id from orders;
	vorder_id := rand(vmaxorder_id-vminorder_id)+vminorder_id;
	select order_id into vorder_id from orders where order_id = vorder_id;
	
	voption := rand(2); if voption <= 1 then vmod := 1.1; else vmod := 0.9; end if;

	update ordersline set amount = amount*vmod where order_id = vorder_id;
	update orders set dte = pdte, amount = 
		(select sum(amount) from ordersline where order_id = vorder_id group by order_id)
		where order_id = vorder_id;

	select transaction_id into vtransaction_id from transactions where order_id = vorder_id;
	update transactionsline set amount = amount*vmod where transaction_id = vtransaction_id;
	update transactions set dte = pdte, amount =
		(select sum(amount) from transactionsline where transaction_id = vtransaction_id group by transaction_id)
		where transaction_id = vtransaction_id;

	select cheque_id into vcheque_id from cashbook where transaction_id = vtransaction_id;
	update cashbookline set amount = amount*vmod where cheque_id = vcheque_id;
	update cashbook set dte = pdte, amount = 
		(select sum(amount) from cashbookline where cheque_id = vcheque_id group by cheque_id)
		where cheque_id = vcheque_id;

	dbms_output.put_line('PROC: UpdateSORP Complete '||to_char(getTime(vtime)));
	simAudit('PROCEDURE','UpdateSorP',getTime(vtime),popt);

	commit;
exception when others then
	dbms_output.put_line('PROC: UpdateSORP '||SQLERRM(SQLCODE));
	rollback;
end;
/
alter procedure UpdateSORP compile;

create or replace procedure DeleteSORP(pdte IN DATE DEFAULT SYSDATE,popt in integer) as
	vminorder_id integer;
	vmaxorder_id integer;
	vorder_id integer;
	vtransaction_id integer;
	vstockmovement_id integer;
	vstock_id integer;
	vcheque_id integer;
	vqty integer;
	vprice float;
	vid integer;
	vtime float;
	type tStockMovement is ref cursor return StockMovement%rowtype;
	cStockMovements tStockMovement;
	rStockMovement StockMovement%rowtype;
begin

	vtime := to_number(to_char(systimestamp,'SSSSS.FF'));

	select min(order_id),max(order_id) into vminorder_id,vmaxorder_id from orders;
	vorder_id := rand(vmaxorder_id-vminorder_id)+vminorder_id;
	select order_id into vorder_id from orders where order_id = vorder_id;	
	select transaction_id into vtransaction_id from transactions where order_id = vorder_id;
	select cheque_id into vcheque_id from cashbook where transaction_id = vtransaction_id;

	BEGIN
		OPEN cStockMovements FOR
			select stockmovement_seq.nextval, sm.stock_id, (sm.qty*-1) as qty, sm.price, pdte from stockmovement sm
			where exists(	
				select stockmovement_id from transactionsline
				where stockmovement_id = sm.stockmovement_id and transaction_id = vtransaction_id);
		LOOP
			FETCH cStockMovements INTO rStockMovement;
			EXIT WHEN cStockMovements%NOTFOUND;
			insert into stockmovement values
				(rStockMovement.stockmovement_id,rStockMovement.stock_id,rStockMovement.qty,rStockMovement.price,rStockMovement.dte);
		END LOOP;
		CLOSE cStockMovements;
	EXCEPTION WHEN OTHERS THEN
		CLOSE cStockMovements;
	END;

	delete from cashbookline where cheque_id = vcheque_id;
	delete from cashbook where cheque_id = vcheque_id;
	delete from transactionsline where transaction_id = vtransaction_id;
	delete from ordersline where order_id = vorder_id;
	delete from transactions where transaction_id = vtransaction_id;
	delete from orders where order_id = vorder_id;

	dbms_output.put_line('PROC: DeleteSORP Complete '||to_char(getTime(vtime)));
	simAudit('PROCEDURE','DeleteSorP',getTime(vtime),popt);

	commit;
exception when others then
	dbms_output.put_line('PROC: DeleteSORP '||SQLERRM(SQLCODE));
	rollback;
end;
/
alter procedure DeleteSORP compile;

@@application.sql;

create or replace procedure simulate as
	voption integer;
	vrange integer;	
	vdte date default '01-MAR-03';
begin
	voption := rand(20); vdte := vdte + rand(366);
	dbms_output.put_line('PROC: Simulate ('||to_char(voption)||')'||to_char(vdte)||' '||to_char(voption));
	if voption in (1,2,3,4,5,6,7,8,9,10) then Reports(voption);
	elsif voption in (11,12,13,14,15) then InsertSANDP(vdte,voption);
	elsif voption in (16,17,18) then UpdateSORP(vdte,voption);
	elsif voption in (19,20) then DeleteSORP(vdte,voption);
	end if;
exception when others then
	dbms_output.put_line('PROC: Simulate '||SQLERRM(SQLCODE));
end;
/
alter procedure simulate compile;

spool off;
exit;

Accounts Schema Reporting Simulation Scripting (application.sql)

create or replace procedure Reports (popt IN integer) as
	vcustomer_id integer;
	vsupplier_id integer;
	vorder_id integer;
	vtransaction_id integer;
	vstock_id integer;
	vtime float;
	vcnt integer;

	type tOrders is ref cursor return Orders%rowtype;
	cOrders tOrders;
	rOrders Orders%rowtype; 

	type tOrdersLine is ref cursor return OrdersLine%rowtype;
	cOrdersLine tOrdersLine;
	rOrdersLine OrdersLine%rowtype; 

	type tTransactions is ref cursor return Transactions%rowtype;
	cTransactions tTransactions;
	rTransactions Transactions%rowtype; 

	type tTransactionsLine is ref cursor return TransactionsLine%rowtype;
	cTransactionsLine tTransactionsLine;
	rTransactionsLine TransactionsLine%rowtype; 

	type tCashbook is ref cursor return Cashbook%rowtype;
	cCashbook tCashbook;
	rCashbook Cashbook%rowtype; 

	type tCashbookLine is ref cursor return CashbookLine%rowtype;
	cCashbookLine tCashbookLine;
	rCashbookLine CashbookLine%rowtype; 

	type tCOA is ref cursor return COA%rowtype;
	cCOA tCOA;
	rCOA COA%rowtype; 

	type tStockMovement is ref cursor return StockMovement%rowtype;
	cStockMovement tStockMovement;
	rStockMovement StockMovement%rowtype; 

	voption integer;

begin

	vtime := to_number(to_char(systimestamp,'SSSSS.FF'));

	voption := rand(8);

	if voption = 1 then
		--print all orders
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		open cOrders for select * from orders where customer_id = vcustomer_id;
		loop
			fetch cOrders into rOrders;	
			exit when cOrders%notfound;
		end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		open cOrders for select * from orders where supplier_id = vsupplier_id;
		loop
			fetch cOrders into rOrders;	
			exit when cOrders%notfound;
		end loop;

	elsif voption = 2 then
		--print individual orders
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		select max(order_id) into vorder_id from orders where customer_id = vcustomer_id; vorder_id := rand(vorder_id);
		open cOrdersLine for select ol.* from orders o join ordersline ol on(ol.order_id = o.order_id) where o.customer_id = vcustomer_id;
		loop
			fetch cOrdersLine into rOrdersLine;	
			exit when cOrdersLine%notfound;
		end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		select max(order_id) into vorder_id from orders where supplier_id = vsupplier_id; vorder_id := rand(vorder_id);
		open cOrdersLine for select ol.* from orders o join ordersline ol on(ol.order_id = o.order_id) where o.supplier_id = vsupplier_id;
		loop
			fetch cOrdersLine into rOrdersLine;	
			exit when cOrdersLine%notfound;
		end loop;

	elsif voption = 3 then
		--print all transactions
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		open cTransactions for select * from transactions where customer_id = vcustomer_id;
		loop
			fetch cTransactions into rTransactions;	
			exit when cTransactions%notfound;
		end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		open cTransactions for select * from transactions where supplier_id = vsupplier_id;
		loop
			fetch cTransactions into rTransactions;	
			exit when cTransactions%notfound;
		end loop;

	elsif voption = 4 then
		--print individual transactions
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id);
		open cTransactionsLine for select tl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) where t.customer_id = vcustomer_id;
		loop
			fetch cTransactionsLine into rTransactionsLine;	
			exit when cTransactionsLine%notfound;
		end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id);
		open cTransactionsLine for select tl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) where t.supplier_id = vsupplier_id;
		loop
			fetch cTransactionsLine into rTransactionsLine;	
			exit when cTransactionsLine%notfound;
		end loop;

	elsif voption = 5 then
		--print all invoices
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id);
		open cCashbook for select cb.* from transactions t join cashbook cb on(cb.transaction_id = t.transaction_id) where t.customer_id = vcustomer_id;
		loop
			fetch cCashbook into rCashbook;	
			exit when cCashbook%notfound;
		end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id);
		open cCashbook for select cb.* from transactions t join cashbook cb on(cb.transaction_id = t.transaction_id) where t.supplier_id = vsupplier_id;
		loop
			fetch cCashbook into rCashbook;	
			exit when cCashbook%notfound;
		end loop;

	elsif voption = 6 then
		--print individual invoices
		select max(customer_id) into vcustomer_id from customer; vcustomer_id := rand(vcustomer_id);
		select max(transaction_id) into vtransaction_id from transactions where customer_id = vcustomer_id; vtransaction_id := rand(vtransaction_id);
		open cCashbookLine for select cbl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) join cashbook cb on(cb.transaction_id = t.transaction_id) join cashbookline cbl on(cbl.cheque_id = cb.cheque_id) where t.customer_id = vcustomer_id;
		loop	
		fetch cCashbookLine into rCashbookLine;	
			exit when cCashbookLine%notfound;
			end loop;
		select max(supplier_id) into vsupplier_id from supplier; vsupplier_id := rand(vsupplier_id);
		select max(transaction_id) into vtransaction_id from transactions where supplier_id = vsupplier_id; vtransaction_id := rand(vtransaction_id);
		open cCashbookLine for select cbl.* from transactions t join transactionsline tl on(tl.transaction_id = t.transaction_id) join cashbook cb on(cb.transaction_id = t.transaction_id) join cashbookline cbl on(cbl.cheque_id = cb.cheque_id) where t.supplier_id = vsupplier_id;
		loop
			fetch cCashbookLine into rCashbookLine;	
			exit when cCashbookLine%notfound;
		end loop;

	elsif voption = 7 then
		--coa
		open cCOA for select c.* from coa c, type t, subtype s where c.type = t.type and c.subtype = s.subtype order by c.coa#, t.type, s.subtype;
		loop
			fetch cCOA into rCOA;	
			exit when cCOA%notfound;
		end loop;

	elsif voption = 8 then
		--stock
		select max(stock_id) into vstock_id from stock; vstock_id := rand(vstock_id);
		open cStockMovement for select sm.* from category c join stock s on(s.category_id = c.category_id) join stockmovement sm on(sm.stock_id = s.stock_id) where s.stock_id = vstock_id;
		loop
			fetch cStockMovement into rStockMovement;	
			exit when cStockMovement%notfound;
		end loop;

	elsif voption in (9,10) then
		--sorting
		select count(*) into vcnt from (select * from customer order by address);
		select count(*) into vcnt from (select drcoa#, sum(amount) from transactions group by drcoa#);
		--selections from the generalledger and stockmovement tables are avoided here due to reverse index primary keys

	end if;

	dbms_output.put_line('PROC: Reports Complete '||to_char(getTime(vtime)));
	simAudit('PROCEDURE','Reports',getTime(vtime),popt);

exception when others then
	dbms_output.put_line('PROC: Reports '||SQLERRM(SQLCODE));
	rollback;
end;
/
alter procedure Reports compile;

Accounts Schema Simulation Summaries Scripting

--
--start of application procedures
--

--
--JUST TAKES TOO LONG TO RUN
--
--create table tmp_periodsum(year number,period number,coa# char(5),amount float);
create or replace procedure summaries as
	vtime float;
begin
	vtime := to_number(to_char(systimestamp,'SSSSS.FF'));

	--customer.balance
	update customer set balance = 
	(
		select a.amount from
		(
			select customer_id, sum(t.amount-cb.amount) as amount
			from transactions t, cashbook cb
			where t.transaction_id = cb.transaction_id 
			group by customer_id
		) a
		where a.customer_id = customer.customer_id
	);
	commit;

	--customer.lastdate
	update customer set lastdate = 
	(
		select a.dte from
		(
			select customer_id, max(dte) as dte
			from transactions
			group by customer_id
		) a		
		where a.customer_id = customer.customer_id
	);
	commit;

	--supplier.balance
	update supplier set balance = 
	(
		select a.amount from
		(
			select supplier_id, sum(t.amount-cb.amount) as amount
			from transactions t, cashbook cb
			where t.transaction_id = cb.transaction_id
			group by supplier_id
		) a
		where a.supplier_id = supplier.supplier_id
	);
	commit;

	--supplier.lastdate
	update supplier set lastdate = 
	(
		select a.dte from
		(
			select supplier_id, max(dte) as dte
			from transactions
			group by supplier_id
		) a		
		where a.supplier_id = supplier.supplier_id
	);
	commit;

	--coa.lastdate
	update coa set lastdate = 
	(
		select a.dte from
		(
			select coa#, max(dte) as dte
			from generalledger
			group by coa#
		) a
		where a.coa# = coa.coa#
	);
	commit;

	--periodsum
	insert into tmp_periodsum(year,period,coa#,amount)
		select 	 to_number(to_char(dte,'IYYY')) as year
			,to_number(to_char(dte,'MM')) as period
			,coa# as coa
			,abs(sum(dr-cr)) as amount
		from generalledger
		group by to_number(to_char(dte,'IYYY')), to_number(to_char(dte,'MM')), coa#;
	delete from periodsum;
	insert into periodsum select * from tmp_periodsum where period in(1,2);
	delete from tmp_periodsum where period in(1,2);
	update periodsum set year=year-1,period=period+10;
	update tmp_periodsum set period=period-2;
	insert into periodsum select * from tmp_periodsum;
	commit;

	--coa.balance
	update coa set balance =
	(
		select amount from periodsum
		where year=2003 and period=1 and coa#=coa.coa#
	);
	commit;

	update coa set ytd =
	(
		select sum(amount) from periodsum
		where year=2003 and coa#=coa.coa#
	);
	commit;

	--stock.lastdate
	update stock set lastdate = 
	(
		select a.dte from
		(
			select stock_id, max(dte) as dte
			from stockmovement
			group by stock_id
		) a
		where a.stock_id = stock.stock_id
	);
	commit;

	--stock.qtyonhand
	update stock set qtyonhand = 
	(
		select a.qty from
		(
			select stock_id, sum(qty) as qty
			from stockmovement
			group by stock_id
		) a
		where a.stock_id = stock.stock_id
	);
	commit;

	--stock.totalvalue
	update stock set totalvalue = 
	(
		select a.amount from
		(
			select stock_id, sum(qty*price) as amount
			from stockmovement
			group by stock_id
		) a
		where a.stock_id = stock.stock_id
	);
	commit;
	
	dbms_output.put_line('PROC: UpdateSummaries Complete '||to_char(getTime(vtime)));
	simAudit('PROCEDURE','UpdateSummaries',getTime(vtime),0);

exception when others then
	dbms_output.put_line('PROC: UpdateSummaries '||SQLERRM(SQLCODE));
	rollback;
end;
/
alter procedure summaries compile;
/

Accounts Schema Partitioning Scripting

Accounts Schema Partitioning Scripting Transaction Partitions

--logged in as system

create tablespace DATATSP logging datafile 'd:\oracle\oradata\dual\datatsp.dbf' size 8M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATATSS logging datafile 'e:\oracle\oradata\dual\datatss.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXTSP logging datafile 'd:\oracle\oradata\dual\indxtsp.dbf' size 4M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXTSS logging datafile 'e:\oracle\oradata\dual\indxtss.dbf' size 1M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
alter user accounts quota unlimited on datatsp quota unlimited on datatss;
alter user accounts quota unlimited on indxtsp quota unlimited on indxtss;

--logging in as accounts

drop table tsp;

CREATE TABLE tsp
(
	 transaction_id       NUMBER NOT NULL
	,type                 CHAR(1) NOT NULL
	,customer_id          NUMBER NULL
	,supplier_id          NUMBER NULL
	,order_id             NUMBER NULL
	,amount               NUMBER(10,2) NOT NULL
	,dte                  DATE NOT NULL
	,drcoa#               CHAR(5) NOT NULL
	,crcoa#               CHAR(5) NOT NULL
)
PARTITION BY LIST (type)
(
	 PARTITION purchases VALUES ('P') TABLESPACE DATATSP
	,PARTITION sales VALUES ('S') TABLESPACE DATATSS
);

set transaction use rollback segment rb01;
insert into tsp select * from transactions;
commit;

Accounts Schema Partitioning Scripting StockMovement Partitions

--
--logged in as system
--
spool log\sm.log;

create tablespace DATASMP25 logging datafile 'c:\oracle\oradata\test\datasmp25.dbf' size 25M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATASMP50 logging datafile 'c:\oracle\oradata\test\datasmp50.dbf' size 25M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATASMP75 logging datafile 'c:\oracle\oradata\test\datasmp75.dbf' size 25M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATASMP100 logging datafile 'c:\oracle\oradata\test\datasmp100.dbf' size 25M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATASMP125 logging datafile 'c:\oracle\oradata\test\datasmp125.dbf' size 25M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

create tablespace INDXSMP25 logging datafile 'c:\oracle\oradata\test\indxsmp25.dbf' size 10M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXSMP50 logging datafile 'c:\oracle\oradata\test\indxsmp50.dbf' size 10M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXSMP75 logging datafile 'c:\oracle\oradata\test\indxsmp75.dbf' size 10M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXSMP100 logging datafile 'c:\oracle\oradata\test\indxsmp100.dbf' size 10M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXSMP125 logging datafile 'c:\oracle\oradata\test\indxsmp125.dbf' size 10M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

alter user accounts quota unlimited on DATASMP25 quota unlimited on DATASMP50 quota unlimited on DATASMP75 quota unlimited on DATASMP100 quota unlimited on DATASMP125;
alter user accounts quota unlimited on INDXSMP25 quota unlimited on INDXSMP50 quota unlimited on INDXSMP75 quota unlimited on INDXSMP100 quota unlimited on INDXSMP125;

drop table smp;

CREATE TABLE smP
(
	 stockmovement_id     NUMBER NOT NULL
	,stock_id             NUMBER NOT NULL
	,qty                  NUMBER NOT NULL
	,price                NUMBER(10,2) NOT NULL
	,dte                  DATE NOT NULL
	,CONSTRAINT PK_SMP PRIMARY KEY (stockmovement_id) 
	,CONSTRAINT FK_SMP_1 FOREIGN KEY (stock_id) REFERENCES Stock
) tablespace indx --pushes PK into indx tablespace
partition by list (stock_id)
(
	  partition DATASMP25 	values (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25) tablespace DATASMP25
	 ,partition DATASMP50 	values (26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50) tablespace DATASMP50
	 ,partition DATASMP75 	values (51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75) tablespace DATASMP75
	 ,partition DATASMP100 	values (76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100) tablespace DATASMP100
	 ,partition DATASMP125 	values (101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125) tablespace DATASMP125
);

create index GK_SMP_1 ON SMP (stock_id) LOCAL
(
	  partition INDXSMP25 tablespace INDXSMP25
	 ,partition INDXSMP50 tablespace INDXSMP50
	 ,partition INDXSMP75 tablespace INDXSMP75
	 ,partition INDXSMP100 tablespace INDXSMP100
	 ,partition INDXSMP125 tablespace INDXSMP125
);

insert into smp(stockmovement_id,stock_id,qty,price,dte) select stockmovement_id,stock_id,qty,price,dte from stockmovement;
commit;

spool off;
exit;

Accounts Schema Partitioning Scripting OrderLines Partitions

--logged in as system

create tablespace DATAOL1 logging datafile 'c:\oracle\oradata\dual\dataol1.dbf' size 5M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATAOL2 logging datafile 'd:\oracle\oradata\dual\dataol2.dbf' size 5M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATAOL3 logging datafile 'e:\oracle\oradata\dual\dataol3.dbf' size 5M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXOL1 logging datafile 'c:\oracle\oradata\dual\indxol1.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXOL2 logging datafile 'd:\oracle\oradata\dual\indxol2.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXOL3 logging datafile 'e:\oracle\oradata\dual\indxol3.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

alter user accounts quota unlimited on dataol1 quota unlimited on dataol2 quota unlimited on dataol3;
alter user accounts quota unlimited on indxol1 quota unlimited on indxol2 quota unlimited on indxol3;

drop table olp;

CREATE TABLE olp(
        order_id             NUMBER NOT NULL
       ,seq#                 NUMBER NOT NULL
       ,amount               NUMBER(10,2) NOT NULL
       ,stockmovement_id     NUMBER NOT NULL)
PARTITION BY HASH(order_id) PARTITIONS 3 STORE IN(dataol1,dataol2,dataol3);



set transaction use rollback segment rb00;
INSERT INTO olp SELECT * FROM ordersline;
COMMIT;

ANALYZE TABLE olp COMPUTE STATISTICS;

Accounts Schema Partitioning Scripting General Ledger Partitions

--
--logged in as system
--
spool log\gl.log;

create tablespace DATAGLP1999 logging datafile 'c:\oracle\oradata\dual\dataglP1999.dbf' size 6M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATAGLP2000 logging datafile 'c:\oracle\oradata\dual\dataglP2000.dbf' size 6M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATAGLP2001 logging datafile 'd:\oracle\oradata\dual\dataglP2001.dbf' size 6M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace DATAGLP2002 logging datafile 'e:\oracle\oradata\dual\dataglP2002.dbf' size 6M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

create tablespace INDXGLP1999 logging datafile 'c:\oracle\oradata\dual\indxglP1999.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXGLP2000 logging datafile 'c:\oracle\oradata\dual\indxglP2000.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXGLP2001 logging datafile 'd:\oracle\oradata\dual\indxglP2001.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace INDXGLP2002 logging datafile 'e:\oracle\oradata\dual\indxglP2002.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

alter user accounts quota unlimited on DATAGLP1999 quota unlimited on DATAGLP2000 quota unlimited on DATAGLP2001 quota unlimited on DATAGLP2002;
alter user accounts quota unlimited on INDXGLP1999 quota unlimited on INDXGLP2000 quota unlimited on INDXGLP2001 quota unlimited on INDXGLP2002;

drop table glp;

--pushes PK into indx tablespace

CREATE TABLE glP
(
	 generalledger_id     NUMBER NOT NULL
	,coa#                 CHAR(5) NOT NULL
	,dr                   NUMBER(10,2) NOT NULL
	,cr                   NUMBER(10,2) NOT NULL
	,dte                  DATE NOT NULL
	,CONSTRAINT XPK_GLP PRIMARY KEY (generalledger_id)
	,CONSTRAINT FK_GLP_COA# FOREIGN KEY (coa#) REFERENCES COA
) TABLESPACE indx 
PARTITION BY RANGE(dte)
(
	 PARTITION DATAGLP1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP1999
	,PARTITION DATAGLP2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2000
	,PARTITION DATAGLP2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2001
	,PARTITION DATAGLP2002 VALUES LESS THAN (MAXVALUE) TABLESPACE DATAGLP2002
);

CREATE INDEX FKX_GLP_1 ON GLP(coa#) TABLESPACE indx;
--CREATE INDEX AK_GLP_1 ON GLP(dte, coa#) TABLESPACE indx;

CREATE INDEX LK_GLP_1 ON GLP (dte, coa#) LOCAL
(
	 PARTITION INDXGLP1999 TABLESPACE INDXGLP1999
	,PARTITION INDXGLP2000 TABLESPACE INDXGLP2000
	,PARTITION INDXGLP2001 TABLESPACE INDXGLP2001
	,PARTITION INDXGLP2002 TABLESPACE INDXGLP2002
);

--CREATE INDEX GK_GLP_1 ON GLP (dte) GLOBAL
--PARTITION BY RANGE (dte)
--(
--	 PARTITION INDXGLP1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) TABLESPACE INDXGLP1999
--	,PARTITION INDXGLP2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')) TABLESPACE INDXGLP2000
--	,PARTITION INDXGLP2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) TABLESPACE INDXGLP2001
--	,PARTITION INDXGLP2002 VALUES LESS THAN (MAXVALUE) TABLESPACE INDXGLP2002
--);

set transaction use rollback segment rb00;
insert into gpl select * from generalledger;
commit;

INSERT INTO glp(generalledger_id,coa#,dr,cr,dte) SELECT generalledger_id,coa#,dr,cr,dte FROM generalledger;
COMMIT;

--
--range-hash composite partition
--
create tablespace sp1 logging datafile 'c:\oracle\oradata\dual\sp1.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace sp2 logging datafile 'd:\oracle\oradata\dual\sp2.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
create tablespace sp3 logging datafile 'e:\oracle\oradata\dual\sp3.dbf' size 2M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
alter user accounts quota unlimited on sp1 quota unlimited on sp2 quota unlimited on sp3;

CREATE TABLE glprh(
	 generalledger_id NUMBER NOT NULL
	,coa# CHAR(5) NOT NULL
	,dr NUMBER(10,2) NOT NULL
	,cr NUMBER(10,2) NOT NULL
	,dte DATE NOT NULL)
PARTITION BY RANGE(dte) SUBPARTITION BY HASH(generalledger_id)
	SUBPARTITIONS 4 STORE IN (sp1, sp2, sp3)
	(PARTITION DATAGLP1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP1999
	,PARTITION DATAGLP2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2000
	,PARTITION DATAGLP2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2001
	,PARTITION DATAGLP2002 VALUES LESS THAN (MAXVALUE) TABLESPACE DATAGLP2002);

--
--range-list composite partition
--
CREATE TABLE glprl(
	 generalledger_id NUMBER NOT NULL
	,coa# CHAR(5) NOT NULL
	,dr NUMBER(10,2) NOT NULL
	,cr NUMBER(10,2) NOT NULL
	,dte DATE NOT NULL)
PARTITION BY RANGE(dte) SUBPARTITION BY LIST(coa#)
(PARTITION DATAGLP1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP1999
	(SUBPARTITION assets1999 VALUES('20001','20002')
	,SUBPARTITION liabilities1999 VALUES('10001','10002')
	,SUBPARTITION expenses1999 VALUES('50001','50002')
	,SUBPARTITION incomes1999 VALUES('60001','60002'))
,PARTITION DATAGLP2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2000
	(SUBPARTITION assets2000 VALUES('20001','20002')
	,SUBPARTITION liabilities2000 VALUES('10001','10002')
	,SUBPARTITION expenses2000 VALUES('50001','50002')
	,SUBPARTITION incomes2000 VALUES('60001','60002'))
,PARTITION DATAGLP2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) TABLESPACE DATAGLP2001
	(SUBPARTITION assets2001 VALUES('20001','20002')
	,SUBPARTITION liabilities2001 VALUES('10001','10002')
	,SUBPARTITION expenses2001 VALUES('50001','50002')
	,SUBPARTITION incomes2001 VALUES('60001','60002'))
,PARTITION DATAGLP2002 VALUES LESS THAN (MAXVALUE) TABLESPACE DATAGLP2002
	(SUBPARTITION assets2002 VALUES('20001','20002')
	,SUBPARTITION liabilities2002 VALUES('10001','10002')
	,SUBPARTITION expenses2002 VALUES('50001','50002')
	,SUBPARTITION incomes2002 VALUES('60001','60002')));

spool off;
exit;

Utilities

Employees Schema
Accounts Schema

count.sql

set sqlp '' termout off echo off feed off trimspool on head off pages 0;
spool C:\temp\counts.log;
set column table_name format a16;
select 'SELECT '''||table_name||','', TO_CHAR(count(*),''999,999,990'') from '||table_name||';' from   user_tables;
spool off;
set termout on;
@@C:\temp\counts.log;
set termout on echo on feed on trimspool off head on pages 40;

Database Size

SELECT	 'Database Size' "*****"
	,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free"
	,round(sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Used"
        ,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free"
        ,sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used"
        ,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM   dba_free_space fs,
       dba_data_files df
WHERE  fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
ORDER BY df.file_id;

Tablespace Size

COL Tablespace FORMAT a16;
SELECT   df.tablespace_name "Tablespace"
        ,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
        ,round(((df.bytes - sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used"
        ,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
        ,round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
FROM   dba_free_space fs,
       dba_data_files df
WHERE  fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.bytes, df.autoextensible
ORDER BY df.file_id;

Data File Size

COL Tablespace FORMAT a16;
SELECT   df.tablespace_name "Tablespace"
        ,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
        ,round(((df.bytes - sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used"
        ,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
        ,round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
        ,df.autoextensible "AutoExtensible"
FROM   dba_free_space fs,
       dba_data_files df
WHERE  fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
ORDER BY df.file_id;

tbfExtSize.sql

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
, ROUND(SUM(bytes)/1024/1024) "Mb"
, ROUND(SUM(bytes)/1024) "Kb"
, SUM(bytes) "Bytes"
, SUM(blocks) "Blocks"
FROM dba_extents
WHERE owner = 'ACCOUNTS'
AND segment_type IN ('TABLE','INDEX')
GROUP BY segment_name, segment_type
ORDER BY segment_name, segment_type DESC;

tbfSegSize.sql

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
, ROUND(bytes/1024/1024) "Mb"
, ROUND(bytes/1024) "Kb"
, bytes "Bytes"
, blocks "Blocks"
FROM dba_segments
WHERE owner = 'ACCOUNTS'
AND segment_type IN ('TABLE','INDEX')
ORDER BY segment_name, segment_type DESC;

statistics.sql

set termout off echo off feed off trimspool on head off pages 0 timing off;
spool c:\temp\statistics.sql;
select 'analyze '||object_type||' '||object_name||' compute statistics;' from user_objects where object_type in ('TABLE','INDEX') order by object_type,object_name;
spool off;
set termout on timing off;
@@log\statistics.sql;
select table_name,num_rows from user_tables where num_rows = (select max(num_rows) from user_tables);
exit;

showTables.sql

set wrap off linesize 132 pages 80

column tab format a20
column col format a15
column pos format 990
column typ format a10
column tbs format a25
BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES 

select	 t.table_name "Tab"
	,c.column_name "Col"
	,c.column_id "Pos"
	,c.data_type "Typ"
	,DECODE(c.nullable,'N','NOT NULL',NULL) "Null"
	,t.tablespace_name "Tbs"
from user_tables t, user_tab_columns c
where t.table_name = c.table_name
order by t.table_name, c.column_id;

showIndexes.sql

set wrap off linesize 132 pages 80
column tab format a25
column typ format a5
column ind format a25
column col format a20
column pos format 990
column tbs format a25
BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES 
select	 t.table_name "Tab"
	,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap','FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Typ"
	,t.index_name "Ind"
	,c.column_name "Col"
	,c.column_position "Pos"
	,t.tablespace_name "Tbs"
from user_indexes t, user_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
and t.index_type not in ('IOT - TOP','LOB')
order by t.table_name, t.index_name, c.column_position;

showConstraints.sql

set wrap off linesize 132 pages 80
column tab format a20
column key format a10
column cons format a20
column col format a10
column pos format 990
BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES 
select	 t.table_name "Tab"
	,decode(t.constraint_type,'P','Primary','R','Foreign','U','Alternate','Unknown') "Key"
	,t.constraint_name "Cons"
	,c.column_name "Col"
	,c.position "Pos"
from user_constraints t, user_cons_columns c
where t.constraint_type in ('P','R','U')
and t.table_name = c.table_name
and t.constraint_name = c.constraint_name
order by t.table_name, t.constraint_type, c.position;

EXPLAIN PLAN Command

expSimple.sql

COL Cost FORMAT 9990;
COL Rows FORMAT 999990;
COL Bytes FORMAT 99999990;
COL Query FORMAT a64;

SELECT	 cost "Cost"
	,cardinality "Rows"
	,bytes "Bytes"
	,operation||' '||options||' on '||object_name "Query"
FROM	plan_table
ORDER BY id;

explain.sql

--
--execute as follows: EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR 
--

COL Query FORMAT a44;
COL Pos FORMAT 990;
COL Cost FORMAT 999990;
COL Rows FORMAT 9999990;
COL Bytes FORMAT 99999990;
COL Sort FORMAT 99999990;
COL IO FORMAT 9999990;
COL CPU FORMAT 99999990;

SELECT	TRIM(LEVEL)||'. '||LPAD (' ', LEVEL - 1)||operation||' '||options||' on '||object_name "Query"
	,cost "Cost"
	,cardinality "Rows"
	,bytes "Bytes"
	,search_columns "SC"
	,decode(level,1,0,position) "Pos"
	,temp_space "Sort"
	,io_cost "IO"
	,cpu_cost "CPU"
FROM	plan_table
WHERE	statement_id = 'TEST'
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'TEST'
ORDER BY id;

delete from plan_table where statement_id='TEST';
commit;

explainp.sql

--
--execute as follows: EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR 
--

COL Query FORMAT a57;
COL Pos FORMAT 990;
COL Cost FORMAT 990;
COL Rows FORMAT 999990;
COL Bytes FORMAT 99999990;
COL Sort FORMAT 99999990;
COL IO FORMAT 9999990;
COL CPU FORMAT 99999990;

SELECT	 TRIM(LEVEL)||'. '||LPAD (' ', LEVEL - 1)||operation||' '||options||' on '||object_name||' '||other_tag "Query"
	,cost "Cost"
	,cardinality "Rows"
	,bytes "Bytes"
	,decode(level,1,0,position) "Pos"
	,temp_space "Sort"
	,io_cost "IO"
	,cpu_cost "CPU"
FROM	plan_table
WHERE	statement_id = 'TEST'
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'TEST'
ORDER BY id;

delete from plan_table where statement_id='TEST';
commit;

Using DBMS_XPLAN

--serial
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'serial'));

--parallel
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

TKPROF Trace Files Interpretation Scripts

A Perl Wrapper Script

$source = shift; #a trace path and file name

open(trace,$source) || die "Cannot open file $source\n"; @lines = ; close (trace); $len = @lines;
@p = split("\\.",$source); $output = $p[0].".out"; open(output,">$output");

$x = 0; $i = 0; while ($i < $len)
{
	if (($lines[$i] =~ /^.*SELECT.*$/i)
|| ($lines[$i] =~ /^.*INSERT.*$/i)
|| ($lines[$i] =~ /^.*UPDATE.*$/i)
|| ($lines[$i] =~ /^.*DELETE.*$/i))
{ $x = $i; }
	
if ($lines[$i] =~ /^.*TABLE ACCESS FULL DUAL.*$/i)
{ $i++; next; }
	
if (($lines[$i] =~ /^.*SORT MERGE.*$/i)
|| ($lines[$i] =~ /^.*TABLE ACCESS FULL.*$/i))
	{
		$y = $i;
		while ($lines[$i] ne "\n") { $y = $i; $i++; }
		print output "------------------------------------------------------------------------------\n";
		for ($j = $x; $j <= $y; $j++)
		{
			if ($lines[$j] ne "\n")
			{
				if (
					   ($lines[$j] =~ /^call.*count.*$/)
					|| ($lines[$j] =~ /^Parse.*$/)
					|| ($lines[$j] =~ /^Execute .*$/)
					|| ($lines[$j] =~ /^Fetch.*$/)
					|| ($lines[$j] =~ /^total.*$/)
					|| ($lines[$j] =~ /^---.*$/)
					|| ($lines[$j] =~ /^Misses.*$/)
					|| ($lines[$j] =~ /^Optimizer.*$/)
					|| ($lines[$j] =~ /^Parsing.*$/)
				) { next; }
				print output "$lines[$j]";
			}
		}
	}
	$i++;
}

close(output);

The TKPROF Interpretation Script

#!/bin/ksh

#execute profile - for example .cshrc

explain=$1 #set the explain variable to a non-null value if required.

if [ -d /tmp/tkprof ]; then sleep 0; else mkdir /tmp/tkprof; fi

if [ -z "$explain" ]; then

ls -lat $ORACLE_TRACE/$ORACLE_SID*.trc\
| awk '{print $9}'\
| awk -F/ '{print $8}'\
| awk -F. '{\
printf("tkprof %s/%s.trc\ /tmp/tkprof/%s.prf\n",oracle_trace,$1,$1)\
}' oracle_trace=$ORACLE_TRACE

else

ls -lat $ORACLE_TRACE/$ORACLE_SID*.trc\
| awk '{print $9}'\
| awk -F/ '{print $8}'\
| awk -F. '{\
printf("tkprof %s/%s.trc /tmp/tkprof/%s.prf\
explain=/ table=.plan_table\
sys=no record=/tmp/tkprof/%s.rec\n",oracle_trace,$1,$1,$1)\
}' oracle_trace=$ORACLE_TRACE

fi

ls -lat /tmp/tkprof/$ORACLE_SID*.prf\
| awk '{ printf("perl tkprof.pl %s\n",$9) }'

exit 0