Chapter 12 Additional Database Objects

1
Chapter 12Additional Database Objects
2
Chapter Objectives

• Define the purpose of a sequence and state how it
  can be used by an organization
• Explain why gaps may appear in the integers
  generated by a sequence
• Correctly use the CREATE SEQUENCE command to
  create a sequence

3
Chapter Objectives

• Identify which options cannot be changed by the
  ALTER SEQUENCE command
• Use NEXTVAL and CURRVAL in an INSERT command
• Explain when Oracle9i will automatically create
  an index
• Create an index, using the CREATE INDEX command

4
Chapter Objectives

• Delete an index, using the DELETE INDEX command
• Create a PUBLIC synonym
• Delete a PUBLIC synonym
• Identify the contents of different versions of
  views used to access the data dictionary, based
  on the prefix of the view

5
Database Objects

• Anything that has a name and defined structure
• Includes
• Sequence generate sequential integers
• Index quickly locate specific records
• Synonym alias for other database objects

6
Sequences

• Used for internal control purposes by providing
  sequential integers for auditing
• Used to generate unique value for primary key
  column no correlation with actual row contents

7
CREATE SEQUENCE Command

• Various intervals allowed Default 1
• Can specify starting number Default 1

8
CREATE SEQUENCE Command

• Can specify MINVALUE for decreasing sequence,
  MAXVALUE for increasing
• Numbers can be reused if CYCLE specified
• ORDER clause for application cluster environment
• Use CACHE to pre-generate integers Default 20

9
CREATE SEQUENCE Command Example
10
Verifying Sequence Values

• Query USER_SEQUENCES data dictionary view

11
Using Sequence Values

• NEXTVAL generates integer
• CURRVAL contains last integer generated by
  NEXTVAL

12
Altering Sequence Definitions

• Use ALTER SEQUENCE command
• START WITH value cannot be altered drop
  sequence and re-create
• Changes cannot make current integers invalid

13
ALTER SEQUENCE Command Example
14
DROP SEQUENCE Command

• Previous values generated are not affected by
  removing a sequence from a database

15
Indexes

• Stores frequently referenced value and row ID
  (ROWID)
• Can be based on one column, multiple columns,
  functions, or expressions

16
Creating an Index

• Implicitly created by PRIMARY KEY and UNIQUE
  constraints
• Explicitly created by CREATE INDEX command

17
CREATE INDEX Command Example
18
Verifying an Index

• Indexes listed in USER_INDEXES view

19
Removing an Index

• Use DROP INDEX command

20
Synonyms

• Serve as permanent aliases for database objects
• Can be private or public
• Private synonyms are only available to user who
  created them
• PUBLIC synonyms are available to all database
  users

21
CREATE SYNONYM Command Syntax
22
CREATE SYNONYM Command Example
23
Deleting a SYNONYM

• A private synonym can be deleted by owner
• A PUBLIC synonym can only be deleted by a user
  with DBA privileges

24
Data Dictionary

• Stores information about database objects
• Owned by user SYS
• Cannot be directly accessed by users
• Displays contents through data dictionary views

25
View Prefixes