Lab 4

Lab Assignment 4:

Each and every student in the group will do this in their respective database.

Submit outputs , logs, screen shots in the Group file locker of your Group folder with your name tag.

A) Database User Creation

Objectives:

· Create database users

· Assign privileges to users

· Creating a role and granting it to user (See end of page for instructions)

Procedure:

Create a database user for each member of the group. For usernames, you can use the same usernames as your UNIX accounts (e.g. dbst670a) or you can use whatever name you'd like. Set the default tablespace for each user to USERS and the default temporary tables to TEMP. Then, grant the CONNECT and RESOURCE roles to the user. The CONNECT role allows a user to login to the database, and the RESOURCE role allows the creation of database objects, such as tables.

StudentFirst> sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 22 19:27:19 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect / as sysdba;

Connected.

SQL> create user myuser identified by mypassword default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to myuser;

Grant succeeded.

In addition to any users you create for each group member, you will need to create an unprivileged user that will be the owner of the tables for the database lab. You can give it a name that reflects the contents of your database.

Also, create an additional user to use for DBA tasks caled dbauser. It is a good practice to create a separate privileged user for every individual who will be granted heightened privileges, rather than sharing an account with a generic name. However, for this class, creating a DBA user to be shared by the group is fine. Follow the procedure above, but instead, grant the DBA privilege to the new user.

SQL> grant dba to dbauser;

Creating a role and a user

1. Create a user named special

and grant create session to the user.

2. Create a role nustudent

3. Grant privileges - create table to role nustudent

4. Grant role nustudent to user special

5. log on as user special and create a table

6. log out from user special, drop user special

B)

Creating Tablespaces

Objectives:

Plan the size and location of new tablespaces

Add new tablespaces to your database instance

Assign default permanent and temporary tablespaces to users

Procedure:

Each team will need to add four tablespaces with their associated files. They will be named USER_DATA1, USER_DATA2, USER_INDX, and USER_TEMP. The USER_DATA1 and USER_DATA2 tablespaces will hold table data. The USER_INDX tablespace will hold indexes. The USER_TEMP tablespace will be used as the temporary tablespace for ordinary database users.

How large should the new tablespaces be? Based on the calculations you made in the capacity planning exercise, you know how much space each of your tables will take. You will create two data tablespaces, USER_DATA1 and USER_DATA2, as well as a separate tablespace for indexes, USER_INDX. Decide which tablespace you want to store each table in. Your large, 50000 row table will be partitioned, so it will be split between the two data tablespaces. Based on how you are dividing the tables between the two tablespaces, determine how much space you will allocate for each tablespace.

Example:

Your 50000 row table is expected to be 2476kB in size. The remaining 9 tables are split into two groups. The sum of the sizes of the tables in group 1 is 3048kB and the sum of the sizes of the tables in group 2 is 2896kB. Therefore, the USER_DATA1 tablespace will need to be:

USER_DATA1 = (2476 / 2) + 3048 = 4286kB

and

USER_DATA2 = (2476 / 2) + 2896 = 4134kB

The indexes for all the tables will go into the USER_INDX tablespace. As a rough estimate, simply take half of the size of the data as the initial size of the USER_INDX tablespace.

USER_INDX = (2476 + 3048 + 2896) / 2 = 4210 kB

Now we can create the tablespaces. We'll use the space estimates as the initial sizes of the tablespaces. In order to allow for growth, we'll permit the tablespaces to autoextend. However, because it would be bad for a tablespace to grow without limit, we will set a reasonable maximum size.

CREATE TABLESPACE "USER_DATA1" LOGGING DATAFILE '/u01/app/oracle/oradata/DBST670/user_data101.dbf' SIZE 4286K REUSE AUTOEXTEND ON NEXT 512K MAXSIZE 65536K EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USER_DATA2" LOGGING DATAFILE '/u01/app/oracle/oradata/DBST670/user_data201.dbf' SIZE 4134K REUSE AUTOEXTEND ON NEXT 512K MAXSIZE 65536K EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USER_INDX" LOGGING DATAFILE '/u01/app/oracle/oradata/DBST670/user_indx01.dbf' SIZE 4210K REUSE AUTOEXTEND ON NEXT 512K MAXSIZE 65536K EXTENT MANAGEMENT LOCAL;

CREATE TEMPORARY TABLESPACE "USER_TEMP" TEMPFILE '/u01/app/oracle/oradata/DBST670/user_temp01.dbf' SIZE 16384K REUSE AUTOEXTEND ON NEXT 512K MAXSIZE 65536K EXTENT MANAGEMENT LOCAL;

Pay attention to the syntax for creating a temporary tablespace. Use “CREATE TEMPORARY TABLESPACE” instead of “CREATE TABLESPACE” and use “TEMPFILE” instead of “DATAFILE”.

Now that the tablespaces have been created, let's set them as the defaults for your users. Do this for each of your unprivileged database users.

SQL> alter user myuser default tablespace user_data1 temporary tablespace user_temp;

C)

Creating Tables

Objectives:

Understand how pctfree, pctused, and storage clause parameters affect the way a table is stored in the database

Design the DDL for ordinary tables

Design the DDL for a partitioned table

Run the DDL to create your tables

Procedure:

In this portion of the lab, you will write a script that contains the DDL for creating your tables. The tables will be created under the schema of the table owner that you created in lab 4.

In Oracle, it’s possible to write fairly simple DDL, and allow Oracle to use its defaults for where and how the table data is stored. For example:

CREATE TABLE CUSTOMER {

CUSTOMER_ID NUMBER(38) PRIMARY KEY,

CUSTOMER_NAME VARCHAR2(50),

CUSTOMER_ADDRESS VARCHAR2(100),

CUSTOMER_PHONE CHAR(10)

);

In this example, the table would be created, using storage defaults, in the default tablespace of the user. Also, a primary key index would be created, again in the user’s default tablespace, and given an Oracle-generated name (something like “SYS_C0013259”).

However, we want to make specific decisions about where and how the table data is stored. There are a number of optional parameters that can be added to the DDL to make sure that the table is created exactly how we want it.

The value of pctfree determines how much room in a block that Oracle reserves for updates to existing rows on the tables. A general principle to follow is: if updates to your rows will cause data values to get longer, then increase the pctfree. If updates to the rows will not change the width of your rows on a particular table, set pctfree low. The Oracle default is 10 (10% of the block left unused).

In Oracle, pctused is used as a low watermark. Once the block has filled up beyond pctused, only updates can be made to rows already in the block, and no new rows are inserted in that block until the percentage of space used falls below pctused. The Oracle default is 40.

Examples of good use of pctfree and pctused:

· Usual activity has updates that increase row size. Performance is important. Therefore, pctfree=20 and pctused=40

· Usual activity is INSERT and DELETE. Updates do not affect row size. Performance is important. Therefore, pctfree=5 and pctused=60

· Activity is usually read-only. Query performance is important. Table is very large. Therefore, pctfree=5 and pctused=90

Think about the contents of your tables and how their data would change (or not change) in a real-world scenario. Choose appropriate values of pctfree and pctused based on how your tables would be used.

The storage clause of the table DDL describes how space should be initially allocated for your table, and how new space should be allocated as your table grows.

The initial parameter indicates how much space should be allocated upon table creation (i.e., the size of the initial extent). The Oracle default is 5 times the db_block_size. Because you have performed the calculations for table sizing, you already know how much space the table should take.

The next parameter is the size in bytes of the next extent to be allocated to the table. The Oracle default is 5 times the db_block_size. For a table that is expected to grow quickly, a large value of next may be appropriate. Consider how you expect your tables to grow and choose a value that you think is correct.

The pctincrease parameter controls the size of the third and subsequent extents that are allocated as the table grows. The default is 50, which means that each subsequent extent is 50% large than the previous. For example, if next was set to 10k, then the third extent allocated would be 15k, then 22.5k, and so on. If pctincrease is set to 0, then all new extents would be the size specified by next.

The maxextents parameter specifies the total number of extents that can be allocated for the table. Use this to set a fixed limit to how large the table can grow, or specify unlimited to allow the table to grow without restriction.

In addition to the storage parameters, you will indicate which tablespace will hold the tables. You will also create indexes (give them descriptive names). Put tables in USER_DATA1 or USER_DATA2, and indexes in USER_INDX.

Let’s see it all put together in a complete example:

/* Drop the current table */

drop table CUSTOMER;

/* Create the table */

create table CUSTOMER (

ACCOUNT_NUMBER number(10) NOT NULL,

SSN number(9) NOT NULL,

FIRST_NAME char(10),

MIDDLE_INITL char(1),

LAST_NAME char(20) NOT NULL,

FIRST_LINE_AD char(20),

FK_ZIPCODE char(5),

constraint pk_customer primary key (ACCOUNT_NUMBER)

using index

storage (

initial 25k

next 12k

)

tablespace USER_INDX,

constraint fk_zip foreign key (FK_ZIPCODE) references

ZIP(ZIPCODE)

)

pctfree 0

pctused 90

storage (

initial 50k

next 10k

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

/* Add an additional index on the FK_ZIPCODE column */

create index cust_zip_idx

on CUSTOMER(FK_ZIPCODE)

storage(

initial 10k

next 10k

)

tablespace USER_INDX;

In addition to the above, one of your tables will be partitioned. This means that the storage of its data will be split between two tablespaces. Below is the example DDL for a partitioned table. In this example, the value of the STATE column determines in which tablespace the row will be stored. States starting with A – M will go into the USER_DATA1 tablespace, and states starting with N-Z will go into USER_DATA2.

/* Drop existing table */

drop table ZIP;

/* Create the ZIP table */

create table ZIP (

ZIPCODE char(5) NOT NULL,

CITY char(25),

STATE char(2),

constraint pk_zip primary key (ZIPCODE)

using index

storage (

initial 25k

next 12k

)

tablespace USER_INDX

)

partition by range (state) (

partition zip_part1

values less than ('N')

pctfree 5

pctused 90

tablespace USER_DATA1

storage (

initial 1500k

next 200k

pctincrease 0

maxextents 5

),

partition zip_part2

values less than (MAXVALUE)

pctfree 5

pctused 90

tablespace USER_DATA2

storage (

initial 1500k

next 200k

pctincrease 0

maxextents 5

)

);