Chapter 8: Managing Schema Objects
Chapter 8: Managing Schema Objects
A schema is a collection of database objects. A schema is
owned by a database user and has the same name as that user. Schema objects
are logical structures created by users to contain, or reference, their data.
Schema objects include structures like tables, views, and indexes. You can create
and manipulate schema objects using Oracle Enterprise Manager.
Approximately 1 hour
This tutorial covers the following topics:
Place
the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: This action loads all
screenshots simultaneously, so response time may be slow depending on your Internet
connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
Before you perform this tutorial, you should:
Back to Topic List
Follow the steps below to access schema objects using Enterprise
Manager:
| 1. |
Log in to Enterprise Manager Database Console by opening
your browser and specifying the SYS
username and password.
Click Login.
|
| 2. |
Click Schema and select the Tables link in the
Database Objects section.
|
| 3. |
Select the flashlight icon to select a particular
schema.
|
| 4. |
Select HR
and click Select.
|
| 5. |
Enter emp
in the Object Name field and click Go.
|
| 6. |
The objects that match your search criteria are displayed.
|
Back to Topic List
Tables are the basic unit of data storage in an Oracle database.
They hold all user-accessible data. Each table has columns and rows. In this
section, you will perform the following tasks:
Viewing
the Attributes of a Table
Follow the steps below to view the attributes of the HR.EMPLOYEES
table:
| 1. |
Click on the EMPLOYEES
table.
|
| 2. |
The View Table page appears displaying the attributes
of the table including columns, constraints, and storage options.
Click the Tables link to return to the Tables
property page.
|
Back to Topic
Viewing
the Contents of a Table
Perform the steps listed below to view the contents of the
HR.EMPLOYEES table:
| 1. |
Select the EMPLOYEES
table and select View Data from the Actions list. Click
Go.
|
| 2. |
The View Data for Table page appears showing the row
data in the Result section. Click the Tables link to return to
the Tables property page.
|
Back to Topic
Creating
a New Table
In this section you will create three new tables for your
database: EMPLOYEES, CUSTOMERS,
and ORDERS.
| 1. |
Click Create on the Tables property page.
|
| 2. |
The Create Table: Table Organization page appears. Select
Standard, Heap Organized and click Continue.
|
| 3. |
The Create Table page appears. Enter employees
in the Name field. Enter fsowner
in the Schema field. Enter fsdata
in the Tablespace field. Define the columns as follows and click Add
5 Table Columns.
Name: EMPLOYEE_ID,
Data Type: NUMBER, Size: 6
Name: FIRST_NAME, Data Type: VARCHAR2, Size: 20
Name: LAST_NAME, Data Type: VARCHAR2, Size: 25
Name: EMAIL_ADDRESS, Data Type: VARCHAR2, Size: 25
Name: PHONE_NUMBER, Data Type: VARCHAR2, Size: 20
|
| 4. |
Enter the remaining columns as follows:
HIRE_DATE
DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8)
MANAGER_ID NUMBER(6)
Click Constraints.
|
| 5. |
The Constraints page appears. Select PRIMARY
from the drop-down list and click Add.
|
| 6. |
The Add PRIMARY Constraint page appears. Enter
emp_id_pk in the Name field. Select EMPLOYEE_ID
in the Available Columns list. Click Move to select it. Click Continue.
|
| 7. |
The Create Table page appears. Click OK.
|
| 8. |
The Tables page appears with an Update message indicating
your table has been created.
|
| 9. |
Repeat steps 2 through 8 to create the CUSTOMERS
and ORDERS tables
in the FSOWNER schema
as defined below. Name the primary key for the Customers table, CUST_ID_PK,
and for the Orders table, ORD_PK_ID.
| CUSTOMERS |
|
|
| Column |
Type |
PK? |
| CUSTOMER_ID |
NUMBER(6) |
Y
|
| CUST_FIRST_NAME |
VARCHAR2(20) |
|
| CUST_LAST_NAME |
VARCHAR2(20) |
|
| STREET_ADDRESS |
VARCHAR2(40) |
|
| CITY |
VARCHAR2(30) |
|
| STATE |
VARCHAR2(10) |
|
| POSTAL_CODE |
VARCHAR2(10) |
|
| PHONE_NUMBER |
VARCHAR2(20) |
|
| ORDERS |
|
|
| Column |
Type |
PK? |
| ORDER_ID |
NUMBER(12) |
Y
|
| ORDER_DATE |
DATE |
|
| ORDER_MODE |
VARCHAR2(8) |
|
| CUSTOMER_ID |
NUMBER(6) |
|
| DELIVERY_MODE |
VARCHAR2(8) |
|
| ORDER_STATUS |
NUMBER(2) |
|
| SALES_CLERK_ID |
NUMBER(6) |
|
| ORDER_TOTAL |
NUMBER(8,2) |
|
|
Back to Topic
Modifying
a Table
You can use Enterprise Manager to modify tables, such as adding
and deleting columns or adding constraints. In this section you will add a constraint
to the EMPLOYEES table you created.
You will ensure that the EMAIL_ADDRESS
column contains a value by adding a NOT
NULL constraint.
Follow the steps below to add a NOT
NULL constraint to your EMPLOYEES
table:
| 1. |
Select the EMPLOYEES
table and click Edit.
|
| 2. |
Check the Not NULL column for the EMAIL_ADDRESS
column. Click Apply.
|
| 3. |
The Edit Table page is displayed with a message confirming
your update.
Click the Tables link to return to the Tables page.
|
Back to Topic
Dropping
a Table
You can drop (delete) a table by using Enterprise Manager
as follows. For the purposes of this exercise, you will create a new table and
then drop the table.
| 1. |
Click the Tables link in the Database Objects
section on the Schema page to access the Tables property page. Enter HR
in the Schema Name field and JOBS
in the Object Name field. Click Go.
|
| 2. |
Select Create Like from the Actions list. Click
Go.
|
| 3. |
The Create Table page appears. Enter JOBS_HIST
in the Name field. Deselect Not Null for the JOB_ID
and JOB_TITLE columns.
Click Constraints.
|
| 4. |
The Constraints page appears. Delete the constraints
on the table by selecting each and clicking Delete. They are not
needed for this exercise. Click General to return to the General
page.
|
| 5. |
Click OK to create the JOBS_HIST
table.
|
| 6. |
A message is displayed indicating that the table has
been created.
|
| 7. |
Enter JOBS_HIST
in the Object Name field and click Go.
|
| 8. |
The Tables page is displayed with the JOBS_HIST
table in the Results section. Click Delete With Options to the
delete the JOBS_HIST
table.
|
| 9. |
Select Delete the table definition, all its data,
and dependent objects. Click Yes.
|
| 10. |
A message is displayed indicating the table has been
deleted. Click Go to attempt to retrieve the table.
|
| 11. |
No object found is displayed in the results section
indicating your table has been dropped (deleted). Click the database
link to return to the Schema page.
|
Back to Topic
Indexes are optional structures associated with
tables and can be used to improve query performance. An index provides a quick
access path to table data. Indexes can be created on one or more columns of
a table. After an index is created, it is automatically maintained and used
by the Oracle Database server. Changes to a table’s data or structure are
automatically incorporated into all relevant indexes with complete transparency
to the user.
In this section, you will perform the following tasks:
Viewing the Attributes of
an Index
Perform the steps listed below to view the attributes of an
index in your FSOWNER schema:
| 1. |
Click the Indexes link in the Database Objects
section on the Schema page to access the Indexes property page.
|
| 2. |
The Indexes page appears. Enter FSOWNER
in the Schema field and click Go.
|
| 3. |
The indexes created when you defined primary keys are
displayed. Select the EMP_ID_PK
index defined on the EMPLOYEES
table by clicking the index name link.
|
| 4. |
The View Index page for EMP_ID_PK
index in the FSOWNER
schema is displayed.
Click the Indexes link to return to the Indexes
property page.
|
Back to Topic
Creating
a New Index
Create an index on the CUSTOMER_ID
column in the ORDERS table so
that you can quickly access all orders for a specified customer. The index should
be stored in the FSINDEX tablespace.
Perform the steps listed below to create the ORD_CUSTID_IDX
index:
| 1. |
Select Table from the Object Type list. The page
is refreshed. Enter FSOWNER
in the Schema field and click Go.
|
| 2. |
The Tables page appears. Select the ORDERS
table and select Create Index from the Actions list. Click
Go.
|
| 3. |
The Create Index page appears. Enter ORD_CUSTID_INDX
in the Name field. Enter fsdata
in the Tablespace field. Select Standard - B-tree as the index
type. Select the CUSTOMER_ID
column by entering 1 in the Order column. Accept ASC
as the Sorting Order. Click OK to create the index.
You receive a message that the index was created. Click
the database instance link to return to the Schema page.
|
Back to Topic
Views are customized presentations of data in
one or more tables or other views. They can be thought of as stored queries.
Views do not actually contain data, but instead they derive their data from
the tables upon which they are based. These tables are referred to as the base
tables of the view. Like tables, views can be queried, updated, inserted into,
and deleted from, with some restrictions. All operations performed on a view
actually affect the base tables of the view. Views provide an additional level
of security by restricting access to a predetermined set of rows and columns
of a table. They also hide data complexity and store complex queries.
In this section, you will perform the following
tasks:
Accessing Views
Perform the steps listed below to access views:
| 1. |
Select Views in the Database Objects section
of the Schema page.
|
| 2. |
On the Views property page, enter HR
in the Schema field and click Go.
|
| 3. |
The views defined on tables in the HR
schema are displayed. Select the EMP_DETAILS_VIEW
and click View to view its definition.
|
| 4. |
The View page is displayed showing the definition of
the view.
Click the Views link to return to the Views page.
|
Back to Topic
Creating
a New View
Perform the steps listed below to create a new view:
| 1. |
Click Create on the Views property page.
|
| 2. |
Enter the following information and click OK.
View name: CLERK10_ORDS
Schema: FSOWNER
Query text:
SELECT order_id,
customer_id, order_total FROM orders WHERE sales_clerk_id = 10
|
| 3. |
The Views page is displayed confirming the creation
of your view.
Click the Database Instance link to return to
the Schema page.
|
Back to Topic
You can use Enterprise Manager to manage database resident
program units such as PL/SQL packages, procedures, triggers, and functions and
Java sources and classes. The actions include creating and compiling the database
resident program units, creating synonyms for the database resident program
units, granting privileges to use the database resident program units, and showing
dependencies for the database resident program units.
| 1. |
Click the Procedures link in the Programs section
on the Schema page.
|
| 2. |
Enter hr
in the Schema field. Click Go.
|
| 3. |
Select the ADD_JOB_HISTORY
procedure. Select Object Privileges from the Actions list and
click Go.
|
| 4. |
Click Add.
|
| 5. |
Select EXECUTE
as the privilege and FSOWNER
as the user. Click OK.
|
| 6. |
Click Apply.
|
| 7. |
You receive a confirmation message. Click the Database
tab to return to the Database Home page.
|
Back to Topic List
You can use Enterprise Manager to load data into tables in
batch. Batch loading is useful when you have a lot of data. You can load data
from operating system files or from other databases. You can also export data
into files. One method of loading is to use control (.ctl)
and data (.dat) files. These
files are formatted as standard SQL*Loader files. SQL*Loader is a utility that
you can use to load data from external files into tables of an Oracle database.
In this section you will load customer information into your
FSOWNER.CUSTOMERS table using
the load_cust.ctl file. Create
a directory named $HOME/labs.
Download the load_cust.zip
file and unzip the load_cust.ctl
and load_cust.dat files into
$HOME/labs for use in this section.
| 1. |
Click the Load Data from User Files link in the
Move Row Data section on the Data Movement page.
|
| 2. |
The Load Data: Generate or Use Existing Control File
page appears. Select Use Existing Control File. Also enter the
username and password for the host machine. Click Continue.
Enter the full path of your control file on the database
server machine and click Next. (Note: For this example, you will
use the control file included with the load_cust.zip file).
|
| 3. |
The Load Data: Data File page appears. Select Provide
the full path and name on the database server machine. Input the full
path of your dat file on the database server machine. (Note: For this
example, you will use the .dat file included with the load_cust.zip file).Click
Next.
|
| 4. |
The Load Data: Load Method page appears. Accept the
default of Conventional Path as the loading method. Click Next.
|
| 5. |
The Load Data: Options page appears. Select Generate
log file in the Optional Files region. You can accept the default
file name and path or enter a different one. Click Next.
|
| 6. |
The Load Data: Schedule page appears. Enter a name
in the Job Name field and description in the Description
field. Select Immediately to run the job now. Click Next.
|
| 7. |
The Load Data: Review page appears. Review your file
names and loading methods. If you want to change something, you can click
on the Back button. Otherwise, click Submit Job
to start the loading.
|
| 8. |
The Status page appears with a message indicating the
job was created. Click the job name link to view the
job summary.
|
| 9. |
This summary page should indicate that the job has succeeded.
If not, you can view the log file by clicking on your job under the Logs
heading or by viewing the log file directly.
|
| 10. |
You can confirm the data load by navigating to the Tables
page, entering FSOWNER in the Schema field,
clicking Go, selecting the table, and selecting View
Data from the Actions list. Click Go.
|
| 11. |
The rows you loaded are displayed on the View Data for
Table: FSOWNER.CUSTOMERS page. Click OK to return to the Tables
property page. Click Database to return to the Database
Home page.
Click the Database tab to return to the Database
Home page.
|
Back to Topic List
In this tutorial, you learned how to:
 |
Query for tables in a specific
schema. |
|
Create an table and view the
attributes and contents of the new table. |
|
Create a view. |
|
Manage database resident program
units. |
|
Load data into an existing table. |
Back to Topic List
Place the cursor over this icon to hide all screenshots.
| 
Bookmark
