Oracle schema objects

Learning objective

After completing this topic, you should be able to identify the basic features of objects in a database schema.

1. Schema objects

A schema is a collection of database objects belonging to a specific user, and is named after that user.

Schema objects are the logical structures that refer to the data in the database. They include structures such as tables, views, and indexes.

Schemas and tablespaces have no direct relationship - tablespaces can contain objects from the same or different schemas.

To create and manipulate schema objects, you can use SQL or Enterprise Manager.

The advantage of Enterprise Manager is that it creates the underlying SQL for you.

After creating an Oracle database, your system will contain the following schemas:

SYS schema

SYSTEM schema

sample schemas

SYS schema: The SYS schema contains the base tables and views that make up the database's data dictionary.

The data dictionary is a collection of tables that describe the Oracle database. It contains information about users, schema objects, and storage structures.

The data dictionary is created in the SYSTEM tablespace when the database is created. The Oracle database server updates the data dictionary when a data definition language (DDL) statement executes.
SYSTEM schema: The SYSTEM schema contains extra tables and views that store administrative data, and internal tables and views for use by various Oracle options and tools.

You must not create any additional objects in the SYSTEM schema.
sample schemas: Sample schemas provide a common platform for examples in Oracle documentation and curricula.

This collection of interlinked schemas offers a layered approach to complexity.

During a complete installation of an Oracle database, the seed database installs the sample schemas automatically.

Sample schemas include thefollowing:

HR (Human Resources)

OE (Order Entry)

PM (Product Media)

QS (Queued Shipping)

SH (Sales History)

HR (Human Resources): You use the Human Resources (HR) schema for introducing basic topics. An extension to the HR schema supports demonstrations for the Oracle Internet Directory.
OE (Order Entry): You use the Order Entry (OE) schema for issues of intermediate complexity. The OE schema provides many data types. It contains the built-in OC (Online Catalog) subschema, which is a set of object-relational database objects.
PM (Product Media): You use the PM (Product Media) schema for multimedia data types.
QS (Queued Shipping): You use the Queued Shipping (QS) schema to demonstrate Oracle Advanced Queuing.
SH (Sales History): You use the Sales History (SH) schema for demonstrations with large amounts of data. An extension to this schema supports advanced analytic processing.

Question

Match each schema to the correct characteristics.

Options:

SYS schema

SYSTEM schema

Sample schema

Targets:

Contains all of the base tables and views that constitute the database's data dictionary

Contains additional tables and views that store administrative information

Provides a common platform for examples in Oracle documentation and curriculum

Answer

The SYS schema contains all of the base tables and views that constitute the database's data dictionary. The SYSTEM schema contains additional tables and views that store administrative information. Sample schemas provide a common platform for examples in Oracle documentation and curriculum.

The data dictionary contains information about users, schema objects, and storage structures.

The SYSTEM schema also contains internal tables and views for use by various Oracle options and tools.

Sample schemas offer a layered approach to complexity.

2. Database naming conventions

When naming database objects, you must observe rules pertaining to the following:

case

characters

size

word usage

case: Database objects names are stored in uppercase, although they are not case-sensitive.

When the database processes a SQL statement, the database object name is converted to all uppercase.
characters: You must name database objects using only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#).
size: You must keep most database object names to a length of no more than 30 bytes.

The exceptions are database names, which are limited to 8 bytes, and database link names, which can be up to 128 bytes long.
word usage: You cannot name database objects using Oracle reserved words.

You can break database naming rules by enclosing names in double quotation marks (").

However, if you name an object this way, you must always refer to it with the quotes around the name and use the same case,or you will generate an error message.

An example of a name enclosed in double quotation marks (") is the table "Local Temp".

SQL> select * from "Local Temp";
TEMP_DATE LO_TEMP HI_TEMP
--------- ---------- ----------
01-DEC-03 30 41

If you mistype the case as "local temp", you will receive an error.

SQL> select * from "local temp";
select * from "local temp"
*
ERROR at line 1:
ORA-00942: table or view does not exist

You can name quoted identifiers using any characters and punctuation marks, as well as spaces.

However, you cannot use double quotation marks within quoted or nonquoted identifiers.

Also, you cannot give the same name to two objects within the same namespace.

Question

Which of these are database naming rules in Oracle 10g?

Options:

You can name database objects using Oracle reserved words

You can name database objects using the underscore (_), dollar sign ($), and pound sign (#)

Apart from database names and link names, database object names should be no longer than 30 bytes

Database object names are stored in uppercase

Answer

You can name database objects using the underscore (_), dollar sign ($), and pound sign (#). With two exceptions, you must keep database object names to between 1 and 30 bytes long. Database object names are stored in uppercase.

Option 1 is incorrect. You cannot name database objects using Oracle reserved words.

Option 2 is correct. In addition, you must use only alphanumeric characters from your database character set.

Option 3 is correct. The exceptions are database names, which are limited to 8 bytes, and database link names, which can be 128 bytes long.

Option 4 is correct. In addition, database names are not case-sensitive.

The Oracle database resolves schema object references by using namespaces.

When you refer to an object in a SQL statement, Oracle considers the context of the SQL statement and locates the object in the appropriate namespace.

After finding the object, Oracle performs the operation specified by the statement on the object. If the named object cannot be found, Oracle returns an error.

Database schema objects are organized into

schema objects with their own namespaces

schema objects in the same namespace

schema objects with their own namespaces: Schema objects with their own namespaces include clusters, constraints, database triggers, dimensions, indexes, and private database links.
schema objects in the same namespace: Schema objects in the same namespace include materialized views, packages, private synonyms, sequences, standalone procedures, standalone stored functions, tables, user-defined types, and views.

Because tables and views are in the same namespace, a table and a view in the same schema cannot share the same name.

However, tables and indexes are in different namespaces. This means a table and an index in the same schema can have the same name.

A schema contains namespaces for each of the objects it holds.

Therefore, two tables in different schemas can have the same name, because they are in different namespaces.

Question

Which of these schema objects are in the same namespace?

Options:

Clusters

Packages

Tables

Views

Answer

Schema objects in the same namespace include packages, tables, and views.

Option 1 is incorrect. Clusters have their own namespace.

Option 2 is correct. When you refer to an object in a SQL statement, such as a package, Oracle considers the context of the SQL statement and locates the object in the appropriate namespace.

Option 3 is correct. For example, because tables and indexes are in different namespaces, a table and an index in the same schema can have the same name.

Option 4 is correct. For example, because views and tables are in the same namespace, a view and a table in the same schema cannot share the same name.

3. Data types

When creating a table, you have to specify a data type for each of its columns. Similarly, when creating a procedure or function, you must define a data type for each of its arguments.

These data types specify the domain of values that each column can contain or each argument can have.

Common database built-in data types include the following:

CHAR

VARCHAR2

DATE

NUMBER

CHAR: The CHAR data type is a fixed-length character data type. You must limit its length to between 1 and 2000 bytes, with 1 being the default length.
VARCHAR2: The VARCHAR2 data type is a variable-length character string with a maximum length of 4000 bytes. You must specify the size for VARCHAR2.
DATE: The DATE data type is the valid date range from January 1, 4712 BC to December 31, 9999 AD. You can also use this data type to store the time down to the second.
NUMBER: The NUMBER data type has precision p and scale s. The precision ranges from 1 to 38. The scale ranges from s -84 to 127.

Other useful data types include:

FLOAT(p)

INTEGER

NCHAR(length)

NVARCHAR2(length)

LONG

RAW(size)

TIMESTAMP(fractional_seconds_precision)

FLOAT(p): The FLOAT(p) data type is a floating-point number with a binary precision p. It is an ANSI data type that has a default precision of 126 binary, or 38 decimal.
INTEGER: The INTEGER data type is equivalent to NUMBER(p,0).
NCHAR(length): The NCHAR data type is a Unicode-only data type. When creating a table with an NCHAR column, you specify the column length in characters. You define the national character set after you create your database.

The national character set definition determines the maximum length of a column. The maximum column size is 2000 bytes.
NVARCHAR2(length): The NVARCHAR2(length) data type is a Unicode-only data type like NCHAR. Its maximum length is 4000 bytes and it is not blank-padded to the length specified.
LONG: The LONG data type is a character data type of variable length up to 2 gigabytes.
RAW(size): The RAW(size) data type is used for raw binary data of length size bytes. The maximum size is 2000 bytes. You must define a size for a RAW value.
TIMESTAMP(fractional_seconds_precision): The TIMESTAMP(fractional_seconds_precision) data type contains the year, month, and day values of the date. It also holds the hour, minute, and second values of time. The fractional_seconds_precision element refers to the number of digits in the fractional part of the SECOND date time field, that is, the fractions of a second. Valid values of fractional_seconds_precision are 0 to 9. The default is 6.

Question

Match each data type to the correct description.

Options:

CHAR

VARCHAR2

INTEGER

NCHAR(length)

Targets:

A fixed-length character data type of length size bytes

A variable-length character string with a maximum length of 4000 bytes

Equivalent to NUMBER(p,0)

A Unicode-only data type

Answer

CHAR contains fixed-length character data of length size bytes. VARCHAR2 is a variable-length character string with a maximum length of 4000 bytes. INTEGER is equivalent to NUMBER(p,0). NCHAR(length) is a Unicode-only data type.

You must limit this data type to between 1 and 2000 bytes, with 1 being the default length.

You must specify the size for VARCHAR2.

The NUMBER data type is a number that has precision p and scale s.

When creating a table with an NCHAR column, you specify the column length in characters.

Summary

A schema is a set of database objects used by a particular user. Schema objects are the logical structures that refer directly to the data in a database, and include structures such as indexes, tables, and views.

You must observe several rules when naming database objects, including not using Oracle reserved words. You can bypass many of the rules by enclosing database names in double quotation marks (").

When creating a procedure or function, you must specify a data type for each of its arguments. These data types define the domain of values that each column can contain or each argument can have.

Oracle 10g

Wednesday, September 5, 2007