All locally created database objects should conform to the following naming conventions. Database objects include tables, indexes, views, sequences, synonyms, indexes, clusters, database links, procedures, and packages.
Each object should contain a five-character prefix of the format "xxxx_", where "xxxx" is a three-character sequence representing the system to which the object belongs (E.g. For BBS, objects are prefixed by "BBBS_".
If the objects being created are extensions of a third party system, use the prefix ‘yyyB_’ where yyy is the prefix used by the system being extended. Or, alternatively use the existing naming structure of the application in question, and prefix the object names with BU. (eg. For Oracle GL extensions, one could use the prefix BU_GL. )
Primary key constraint names conform to the format xxxx_constraintname_PK.
Foreign key constraint names conform to the format xxxx_constraintname_FK.
View names conform to the format xxxx_constraintname_V.
Comments that describe database objects can be stored in the database. The comment command places comments on views, tables, snapshots and columns. Comments are required on all tables, views, snapshots and their associated columns. The purpose behind this convention is to enable us to have a current online data dictionary of these objects.
Table comments can be found in the views ALL_COL_COMMENTS and
ALL_TAB_COMMENTS
Example:
-----------------------------------------------------------------
-- Create comments for graduate contacts table
-----------------------------------------------------------------
Comment on Table SMSB_MAJOR_TRANSLATIONS is
'This table contains a list of graduate majors and their
corresponding concentration and school names';
comment on column SMSB_MAJOR_TRANSLATIONS.MAJOR is
'SMS_Students, Program_Intended';
comment on column SMSB_MAJOR_TRANSLATIONS.CONCENTRATION is
'SMS_Students, Concentration1';
comment on column SMSB_MAJOR_TRANSLATIONS.SCHOOL is
'SMS_Students, School_Applied_For';
All table creation statements for production environments must include a storage clause, PCT_FREE, PCT_USED, and TABLESPACE parameters.
Create objects in the appropriate tablespaces. For example in BDEV, unless otherwise specified, tables should be created in bing_data, and your indexes in bing_index.
Generally, INITRANS and MAXTRANS should not be specified. The system default for these values will be used.
Tables of any significant size should include an initial extent size that is capable of holding the entire table, for a reasonable time. A spreadsheet is available from the DBA that assists in computing initial extent sizes. The spreadsheet can be downloaded from the Online Documentation section Administrative Computing web site.
PCTFREE specifies the space reserved in the object’s data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage. IF the row's data once entered will remain unchanged make this a small value like 5. Alternatively, IF the row's data will be dynamic and change in value and thus SIZE make it larger perhaps 45
PCTUSED specifies the limit that Oracle7 uses to determine when additional rows can be added to a cluster’s data block. If the row's data once entered will remain unchanged make this a large value like 90, on the hand If the row'sdata will be dynamic and change in value and thus SIZE make it smaller perhaps 5
Please see the Create Table documentation in the Oracle8 SQL Reference for a more complete description.
Determining the average row size is necessary to size tables and indexes. Average rowsize can determined by a formula. (Can some one write up the formula?) Or Average rowsize can be determined from sample data.
Average Row size Formula
Can someone find this?
Average Row Size from Sample Data
To determine the average row size from sample data ion a table. Use the "ALTER TABLE COMPUTE STATISTICS" command. And query the table "ALL TABLES" to view the average row size.
DROP TABLE PegIT_issue_journal;
CREATE TABLE PegIT_issue_journal
(issue_no NUMBER,
datetime_stamp DATE,
originator VARCHAR2(50) NOT NULL,
responsible_person VARCHAR2(50) NOT NULL,
title VARCHAR2(80) NOT NULL,
problem_area VARCHAR2(80) NOT NULL,
type VARCHAR2(50) NOT NULL,
status VARCHAR2(50) NOT NULL,
severity VARCHAR2(50) NOT NULL,
environment VARCHAR2(50) NOT NULL,
server VARCHAR2(50) NOT NULL,
tar_number VARCHAR2(50),
description VARCHAR2(2000) NOT NULL,
--
CONSTRAINT PegIT_Issue_Journal_PK PRIMARY KEY(issue_no,datetime_stamp)
USING INDEX STORAGE (INITIAL 64K NEXT 64K)
PCTFREE 5
TABLESPACE BING_INDEX)
--
PCTFREE 5
PCTUSED 50
TABLESPACE BING_DATA
STORAGE (INITIAL 500K NEXT 500K);
Alternate table creation sample
/*
** Create the table that contains the data for an issue
**
** Using PRIMARY KEY as below will make a unique
** index on issue_no, datetime_stamp
**
** START c:\IssuesTracker\tab_issues_journal.sql
**
** FIXED HEADER 57
** TABLE DIRECTORY 4
** BLOCK SIZE 8192
** ROW HEADER 3
**
** ROWS 2500
** COLUMNS < 250 B 11
** COLUMNS > 250 B 1
** EST AVE ROW SIZE 750
** BLOCKS 250
** TOTAL BYTES 2,048,000 (2M)
**
*/
DROP TABLE PegIT_issue_journal;
CREATE TABLE PegIT_issue_journal
(issue_no NUMBER,
datetime_stamp DATE,
originator VARCHAR2(50) NOT NULL,
responsible_person VARCHAR2(50) NOT NULL,
title VARCHAR2(80) NOT NULL,
problem_area VARCHAR2(80) NOT NULL,
type VARCHAR2(50) NOT NULL,
status VARCHAR2(50) NOT NULL,
severity VARCHAR2(50) NOT NULL,
environment VARCHAR2(50) NOT NULL,
server VARCHAR2(50) NOT NULL,
tar_number VARCHAR2(50),
description VARCHAR2(2000) NOT NULL,
--
CONSTRAINT PegIT_Issue_Journal_PK PRIMARY KEY(issue_no, datetime_stamp)
USING INDEX STORAGE (INITIAL 64K NEXT 64K)
PCTFREE 5
TABLESPACE BING_INDEX)
--
PCTFREE 5
PCTUSED 50
TABLESPACE BING_DATA
STORAGE (INITIAL 500K NEXT 500K);
/*
** COMMENTS FOR TABLE AND COLUMNS
*/
COMMENT ON TABLE PegIT_issue_journal IS
'This table contains the issues data';
COMMENT ON COLUMN PegIT_issue_journal.issue_no IS
'The number of the issue';
COMMENT ON COLUMN PegIT_issue_journal.datetime_stamp IS
'A manufactured date and time stamp used for uniqness';
COMMENT ON COLUMN PegIT_issue_journal.originator IS
'The person that posted this issue journal entry';
COMMENT ON COLUMN PegIT_issue_journal.responsible_person IS
'The person that this issue journal entry is assigned to';
COMMENT ON COLUMN PegIT_issue_journal.title IS
'A short description of the problem';
COMMENT ON COLUMN PegIT_issue_journal.problem_area IS
'The problem area this issue relates to';
COMMENT ON COLUMN PegIT_issue_journal.type IS
'The issue type';
COMMENT ON COLUMN PegIT_issue_journal.status IS
'The issue status';
COMMENT ON COLUMN PegIT_issue_journal.severity IS
'The severity of the issue';
COMMENT ON COLUMN PegIT_issue_journal.environment IS
'The environment the issue relates to';
COMMENT ON COLUMN PegIT_issue_journal.server IS
'Which server does the issue related to';
COMMENT ON COLUMN PegIT_issue_journal.tar_number IS
'Is there a TAR number for this issue';
COMMENT ON COLUMN PegIT_issue_journal.description IS
'A complete description, explanation and all information for the issue
when opened, activities and resolution and closing';
/*
** INITIAL DATA
*/
--NONE at this time
/*
** GRANT PUBLIC
*/
--GRANT SELECT ON PegIT_issue_journal TO PUBLIC;
--GRANT INSERT ON PegIT_issue_journal TO PUBLIc;
--GRANT UPDATE ON PegIT_issue_journal TO PUBLIC;
/*
** PUBLIC SYNONYM
*/
-- NONE made at this time
-- DROP PUBLIC SYNONYM PegIT_issue_journal_synonym_name
-- CREATE PUBLIC SYNONYM PegIT_issue_journal_synonym_name FOR issue_journal;
/*
** COMMIT EM
*/
COMMIT;
/*
** Lets have a look, shall we
*/
DESC PegIT_issue_journal;
Common table and index extent sizes should be used. These are 8K, 64K, 256K, 1M, 5M, 10M.
Public synonyms need to be created for all the major objects of an application. These include packages, procedures and tables. Team DBA’s will create public synonyms.
Developers are required to store files for application development in appropriate file server based directories. These directories must be backed up on a business-daily basis.
This environment still needs to be established.
The following structure is recommended for applications development directories:
|
XX_TOP (Application Base)
|
-------------------------------------------
| | | | | | | | |
bin lib sql rpt forms log srw out mesg
APPL_TOP – top level directory for the application development tree.
XX_TOP(Application Basepath) – A directory name for the top level of a particular application’s
directory tree.
bin - contains executable code of programs
lib - contains compiled object code of programs
sql - holds SQL*Plus and PL*SQL scripts
rpt – holds SQL*Reports programs
srw - holds SQL*ReportWriter or Oracle Report programs (rel. 10 and up)
forms - contains forms (.inp and .frm files)
log - contains the applications log files
out - contains the application's output files
mesg - holds the application's message files
developer’s log files/project notes/specs/sql*loader files etc.? John said he had some more directories for this one. John?
The top level directory should contain a file called README that describes all the parts of the application.
Installation script, etc. (Doug, could you take a cut at defining this. Right now, I think you’re the farthest along. )
Skeleton Directory.
We need to set up a skeleton directory of this structure on admin-server1.
All developers should have SQL*Plus and SQL*loader installed on their desk tops. (import/export?) The current installation set includes Oracle8 client software, Oracle8 Utilities and Oracle7 Utilities. Utilities include sqlldr, imp, and exp.
Applications 11 developers should have Developer 1.6.1 installed.
Developers should also have recent versions of Developer 2000 installed. (currently at release 2.1)
Tool for Oracle Applications Developers (TOAD) is available for download from www.toadsoft.com. Although this is not a "supported" tool, it’s reputed to be quite useful for some applications.
Standard Sample Header:
Suggested header/description blocks for PL/SQL modules in a format something like below:
/*
** #####################################################################
**
** DESCRIPTION:
** DATE CREATED:
**
** FILE NAME:
** COMPILE WITH:
**
** USAGE:
**
** PROCEDURE NAME:
**
** PROCEDURES CALLED:
**
** FUNCTIONS CALLED:
**
** IN:
** OUT:
** IN OUT:
**
** TABLE(S) READ:
** TABLE(S) WRITTEN:
** TABLE(S) UPDATED:
**
** OTHER INFORMATION:
**
** MODIFICATION LOG:
** DATE CHANGE
** -------- -----------------------------------------------------------------
**
** #####################################################################
*/
Similarly I suggest packages have a block at the start of the body and specification similar to a format as below:
/*
** #####################################################################
** PACKAGE <name> BODY
** ------------------------------------------------------------------
** BODY FILE NAME:
** COMPILE BODY WITH:
** ------------------------------------------------------------------
** SPECIFICATION FILE NAME:
** COMPILE SPECIFICATION WITH:
** ------------------------------------------------------------------
**
** Brief Description
**
** ------------------------------------------------------------------
**
** High Level Functionality
**
** ------------------------------------------------------------------
**
** DEVELOPER NAME:
** DATE CREATED:
**
** #####################################################################
*/
/*
** #####################################################################
** PACKAGE <name> SPECIFICATION
** ------------------------------------------------------------------
** SPECIFICATION FILE NAME:
** COMPILE SPECIFICATION WITH:
** ------------------------------------------------------------------
** BODY FILE NAME:
** COMPILE BODY WITH:
** ------------------------------------------------------------------
** DEVELOPER NAME:
** DATE CREATED:
**
** #####################################################################
*/