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.
Topics
The following topics are discussed in this chapter:
Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
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:
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.
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.
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.
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 Administration 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 Administration page.
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:
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 Administration 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 EMP_ID_PK
index for the EMPLOYEES
table is displayed.
Click the Indexes link to return to the Indexes
property page.
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 fsindex
in the Tablespace field. Select Standard - B-tree as the index type.
Select the CUSTOMER_IDcolumn 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 Administration page.
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:
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 Administration page.
2.
Enter hr
in the Schema field click Go.
3.
Select the
ADD_JOB_HISTORYprocedure. 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 Database to return to the Database Home page.
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 Maintenance 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.
3.
The Load Data: Data File page appears. Select The data file
is specified in the control 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.
Move your mouse over this icon to hide all screenshot
Bookmark
