Oracle Database Administration – Lab #5
Tables, Indexes, Integrity Constraints,
Clusters, and Undo Tablespaces
Situation: In this laboratory you will continue to develop the prototype database for the BOCK Company. You may elect to complete the laboratory individually, or you may team up with 1 to 2 laboratory partners. If you team up with partners, you will select your own partners and you will be graded as a group and you will submit a single document as evidence of your team's work. The tables and data you enter into your database during completion of this laboratory will support your work on future laboratories, so it is essential that you complete this laboratory successfully.
Tasks for this Lab
You should create one or more SQL script files that contain the commands needed to create the tables, cluster, and indexes described below. You may then execute the script file(s) once you have checked it for errors by using the start create_tables.sql command from within SQLPLUS. Additionally, if you ever need to rebuild the database, having the script available will reduce the rebuild time required.
IMPORTANT NOTE: During the creation of tables, indexes, and/or clusters you may find that you run out of data storage space in one or more tablespaces. In this case, you need to increase the amount of space allocated to the tablespace by altering the datafile associated with that specific tablespace. Additionally, you must name all constraints created as part of this assignment.
1. Startup your instance. Connect as the DBA user for your database – not as SYS.
2. Create Tables, Indexes, and Constraints As a DBA, you will be responsible for creating tables. This ensures that the DBA user is the owner of the tables.
Create a regular table named Vendor with columns as specified below.
Set PCTFREE=30, and PCTUSED=50 for this table.
Store the table in the DATA01 tablespace.
Ensure that your CREATE TABLE command creates the primary key index for the table.
Create meaningful names for ALL constraints for the table. Indexes created as part of constraint specifications should be stored to the INDEX01 tablespace—remember this is a locally managed tablespace.
VENDOR Table
Column Name Data type Characteristics Description
Vendor_Number CHAR(8) Primary Key Vendor identifying number
Vendor_Name VARCHAR2(100) Not Null Vendor Name
Street VARCHAR2(100) Not Null Vendor Street Address
City VARCHAR2(50) Not Null Vendor City
State CHAR(2) Not Null Vendor state abbreviation
Zip_Code CHAR(9) Vendor Zip Code
Account_Balance NUMBER(8,2) >= 0 Vendor Account Balance
Comments VARCHAR2(200) Comments about the Vendor
b. Create an index-organized table named Product with column as specified below.
· - Store the table in the DATA01 tablespace.
· - Do not use PCTFREE or PCTUSED for the table.
· -Specify use of the INDEX01 tablespace for overflow and allow the Oracle RDBMS to decide how to handle when to overflow.
· -Ensure that your CREATE TABLE command creates the primary key index for the table.
· -Create meaningful names for ALL constraints for the table.
PRODUCT Table
Column Name Data type Characteristics Description
Product_Number CHAR(10) Primary Key Product identifying number
Description VARCHAR2(20) Not Null Product text description
Retail_Price NUMBER(8,2) >= 0 Desired retail price
Wholesale_Price NUMBER(8,2) >=0 Desired wholesale price
3. Create an Indexed Cluster and a Cluster Index
Create an indexed cluster named Invoice_And_Details that will cluster rows for two tables named Invoice and Invoice_Details tables. The tables will be created in Task #4 below.
· Use a SIZE parameter of 150 for the cluster.
· Set PCTFREE=10, and PCTUSED=80 for the cluster.
· Store the cluster in the DATA01 tablespace
Create aCluster Index to be stored in the INDEX01 tablespace.
4. Create Cluster Tables and Additional Indexes
a. Create the Invoice and Invoice_Details tables that comprise the Invoice_And_Details cluster. Your Create Table command should also create primary key indexes for the Invoice and Invoice_Details tables to enforce primary key integrity. Your code must ensure the tables are stored in the cluster, and that the tables created enforce referential integrity for the following relationships:
· The 1:M relationship from Vendor to Invoice. If a Vendor row is deleted, cascade the deletion to the Invoice table. An Invoice row cannot be inserted without a valid associated Vendor row.
· The 1:M relationship from Product to Invoice_Details. Do not allow a deletion of a Product row if there exists an associated Invoice_Details row (do not cascade deletions). An Invoice_Details row cannot be inserted without a valid associated Product row.
· The 1:M relationship from Invoice to Invoice_Details. If an Invoice row is deleted, cascade the deletion to the Invoice_Details table. An Invoice_Details row cannot be inserted without a valid associated Invoice row.
· Give constraints meaningful names and ensure any indexes created are stored to the INDEX01 tablespace.
INVOICE Table
Column Name Data type Characteristics Description
Invoice_Number CHAR(6) Primary Key Invoice identifying number
Invoice_Date DATE Not Null Date Invoice is placed
Invoice_Amount NUMBER(10,2) >= 0 Total amount of the Invoice
Promise_Date DATE Default to TODAY + 14 days from today. Promised Invoice delivery date with a default constraint specification.
Vendor_Number CHAR(8) Not Null Vendor identifying number (Foreign Key)
INVOICE_DETAILS Table
Column Name Data type Characteristics Description
Invoice_Number CHAR(6) Primary Key Invoice identifying number
Product_Number CHAR(10) Primary Key Product identifying number
Quantity_Ordered NUMBER(8,2) >= 0 Quantity on invoice in decimal units
Actual_Price NUMBER(8,2) >= 0 Selling price for this order
5. Load Vendor and Product test data . Load valid rows. Create your own test data. You must also load rows to test the various uniqueness, integrity, and reasonableness constraints you defined for the tables. You will be evaluated on the extent to which your test data actually tests the referential integrity constraints specified for the tables, and by how clearly you demonstrate that the tests you conduct are comprehensive.
Load the Vendor table with 2 good Vendor rows.
Load the Product Table with 3 good rows.
Attempt one row insertion in Vendor that violates the check constraint for the AccountBalance.
Attempt one row insertion in Vendor that violates the Primary Key integrity constraint. Show the resulting error message.
Attempt row insertions in Vendor that violate the various NOT NULL constraints. Show the resulting error messages.
Attempt one row in Product that violates the check constraint for the RetailPrice. Show the resulting error message.
Attempt one row in Product that violates the check constraint for the WholesalePrice. Show the resulting error message.
Attempt one row insertion in Product that violates the Primary Key integrity constraint. Show the resulting error message.
6. Load Invoice and Invoice_Details test data
Design test data for the Invoice and Invoice_Details tables to test referential integrity constraints. Your test data should represent Invoice where the total value of the Invoice_Details rows for a specific invoice equals the Invoice_Amount in the corresponding Invoice row (do not write any triggers or procedures -- just select data where the total of the Invoice_Details rows matches the amount stored in the associated Invoice row). The total of all Invoice_Amount values for Invoice rows for a given Vendor should equal the Account_Balance of the corresponding Vendor row (again select the data to accomplish this).
Load two good Invoice rows with two good Invoice_Details rows for each order.
Attempt one row insertion in Invoice that violates the check constraint for the OrderAmount. Show the resulting error message.
Attempt one row insertion in Invoice that violates the Primary Key integrity constraint. Show the resulting error message.
Attempt one row insertion in Invoice that violates the referential integrity constraint between the Invoice and Vendor tables. Show the resulting error messages.
Attempt one row insertion in Invoice that violates the NOT NULL constraint for the OrderDate field. Show the resulting error messages.
Attempt one row insertion in Invoice_Details that violates the Primary Key integrity constraint. Show the resulting error message.
Attempt one row insertion in Invoice_Details that violates the referential integrity constraint between the Invoice and Invoice_Details tables. Show the resulting error messages.
Attempt one row insertion in Invoice_Details that violates the referential integrity constraint between the Product and Invoice_Details tables. Show the resulting error messages.
Attempt two row insertions in Invoice_Details that violate the check constraint for the Quantity_Ordered and Actual_Price fields. Show the resulting error messages.
7. UNDO tablespace management
Connect as the user SYS and list the undo segments in the UNDO1 tablespace.
Create a second Undo tablespace named UNDO2 of size 5MB in the same location as your current UNDO1 tablespace. List the undo segments and their status in this tablespace.
Start a second SQLPLUS session (leave the first one open) and connect to the system as the DBA user. Insert a new row in the Vendor table, but do not commit the transaction. Also, do not rollback the transaction or exit the session.
Switch to the first SQLPLUS session and use the ALTER SYSTEM command to switch the active Undo tablespace from UNDO1 to UNDO2. Now, as the user SYS, drop the UNDO1 tablespace. What happened? List the undo segments and their status. Compare this to the list you developed earlier in this task.
In the second SQLPLUS session as the user DBA, rollback the transaction and exit the session.
In the first SQLPLUS session as the user SYS, drop the tablespace named UNDO1. What happened?
NOTE: Remember to update the init.ora file for your database so that on the next startup the correct UNDO tablespace is used.
8. Create a Temporary Table
a. Create a temporary table named Temp_Vendor.
· Use a SELECT clause to create the table and to load all Vendor table data into the Temp_Vendor table.
· Specify to preserve rows on a COMMIT.
· Insert a new row into the Temp_Vendor table.
b. Execute a SELECT vendor_number, vendor_name FROM temp_vendor and display the output to your lab report.
9. Create a Hash Cluster
a. Create a single table hash cluster named States_Cluster with a single table as specified below that is capability of storing approximately 70 hash keys. Do NOT specify a HASH IS clause so that Oracle will use its internal has function.
b. Create a table named States that is stored to the States_Cluster cluster. The table should have the following columns:
· State_Code CHAR(2) column value for hashing.
· State_Name Varchar2(40).
· Population Number(8).
c. Store data to the States table for two states and then execute: SELECT * FROM States;
· Illinois – IL -- population 10,848,287
· Missouri – MO – population 8,481,994
10. Alter a Table
a. Alter the Product table to add a column named Sale_Price with data type Number(8,2).
b. Write SQL to update all rows in the table to store a value to Sale_Price that is the value of the Wholesale_Price column plus 15%. Example a wholesale price of $2.00 would yield a Sale_Price of $2.30.
c. Show the output from the following command: SELECT * FROM Product;
11. Create a Bitmap Index
a. Create a Bitmap index on the Vendor table Zip_Code column with PCTFRE=10-% and with LOGGING on. Store the index to the INDEX01 tablespace.
12. Check the system data dictionary . Execute the queries shown below. Ensure you are connected as the DBA user for your database. The output from these queries will assist me in determining whether or not you have met the assignment requirements. Ensure that you are connected to your database as the DBA user for your database. Format the output so that it is easy to read (use the Courier or Courier New font).
SELECT Table_Name, Tablespace_Name, Pct_Free, Pct_Used FROM User_Tables;
SELECT Index_Name, Table_Name, Tablespace_Name, Uniqueness, Status FROM User_Indexes;
SELECT Index_Name, Table_Name, Column_Name FROM User_Ind_Columns;
SELECT Table_Name, Constraint_Name, Constraint_Type, Search_Condition, Delete_Rule FROM User_Constraints;
SELECT Cluster_Name, Tablespace_name, Function, Hashkeys, Cluster_Type FROM User_Clusters;
13. Shut down the database
At this point you should make a new cold backup of your database.
