chapter8: Managing Schema Objects
chapter8: Managing Schema Objects
chapter8: Managing Schema Objects Purpose
Topics
Accessing Schema Objects
Managing Tables
Managing Indexes
Managing Views
Managing Database Resident Program Units
Loading Data into Tables
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.
Accessing Schema Objects Back to Topic List
1.
SYS username and password.
Login.
2.
Administration and select the Tables link.
3.
flashlight icon to select a particular schema.
4.
HR and click Select.
5.
emp in the Object Name field and click Go.
6.
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
Viewing the Contents of a Table
Creating a New Table
Modifying a Table
Dropping a Table
Back to Topic List
HR.EMPLOYEES table:
1.
EMPLOYEES table and click View.
2.
page appears displaying the attributes of the table including columns, constraints, and storage options.
Tables link to return to the Tables property page.
Back to Topic List
HR.EMPLOYEES table:
1.
EMPLOYEES table and select View Data from the Actions drop-down menu. Click Go.
2.
View Data For Table page appears showing the row data in the Results section.
Back to Topic List
EMPLOYEES, CUSTOMERS, and ORDERS.
1.
Create on the Tables property page.
2.
Create Table: Table Organization page appears. Select Standard, Heap Organized and click Continue.
3.
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.
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL_ADDRESS VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
4.
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8)
MANAGER_ID NUMBER(6)
Constraints.
5.
Constraints page appears. Select Primary from the drop-down menu and click Add.
6.
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 OK.
7.
Create Table page appears. Click OK.
8.
Table page appears with an Update message indicating your table has been created.
9.
CUSTOMERS and ORDERS tables in the FSOWNER schema as defined below.
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 List
EMPLOYEES table you created. You will ensure that the EMAIL_ADDRESS column contains a value by adding a NOT NULL constraint.
NOT NULL constraint to your EMPLOYEES table:
1.
EMPLOYEES table and click Edit.
2.
Not NULL column for the EMAIL_ADDRESS column. Click Apply.
3.
Edit Table page is displayed with a message confirming your update.
Back to Topic List
1.
Tables link in the Schema section on the Administration page to access the Schema objects property page. Select Table in the Object Type drop-down menu. Enter HR in the Schema Name field and JOBS in the Object Name field. Click Go.
2.
Create Like from the Actions drop-down menu. Click Go.
3.
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.
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.
OK to create the JOBS_HIST table.
6.
7.
JOBS_HIST in the Object Name field and click Go.
8.
Tables page is displayed with the JOBS_HIST table in the Results section. Click Delete to the delete the JOBS_HIST table.
9.
Yes to confirm the deletion of the table.
10.
Go to attempt to retrieve the table.
11.
is displayed in the results section indicating your table has been dropped (deleted).
Back to Topic List
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.
Viewing the Attributes of an Index
Creating a New Index
Back to Topic List
FSOWNER schema:
1.
Indexes link in the Schema section on the Administration page to access the Indexes property page.
2.
Indexes page appears. Enter FSOWNER in the Schema Name field and click Go.
3.
EMP_ID_PK index defined on the EMPLOYEES table by clicking the index name link.
4.
EMP_ID_PK index for the EMPLOYEES table is displayed.
Indexes link to return to the Indexes property page.
Back to Topic List
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.
ORD_CUSTID_IDX index:
1.
Tables for the Object Type. Enter FSOWNER in the Schema field and click Go
2.
Tables page appears. Select the ORDERS table and select Create Index from the Actions drop-down menu. Click Go.
3.
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_ID column by entering 1 in the Order column. Accept ASC as the Sorting Order. Click OK to create the index.
Back to Topic List
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
Creating a New View
Back to Topic List
1.
HR in the Schema Name field and click Go.
2.
HR schema are displayed. Select the EMP_DETAILS_VIEW and click View to view its definition.
3.
View page is displayed showing the definition of the view.
Views link.
Back to Topic List
1.
Create on the Views property page.
2.
OK.
CLERK10_ORDS
Schema: FSOWNER
Query text:
SELECT order_id, customer_id, order_total FROM orders WHERE sales_clerk_id = 10
3.
Views page is displayed confirming the creation of your view.
Database link.
Back to Topic List
1.
Procedures link in the Schema section on the Administration page.
2.
hr in the Schema field click Go.
3.
ADD_JOB_HISTORY procedure. Select Grant Privileges from the drop-down menu and click Go.
4.
EXECUTE as the privilege and FSOWNER as the user. Click OK.
Back to Topic List
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.
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 File link in the Utilities section on the Maintenance page.
2.
The Load Data: Control File page appears. Enter the full path of your control file on the database server machine. Also enter the username and password for the host machine. 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 Load Data Submit Successful. Click on View
Job 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, selecting the table, and selecting View Data as the action. 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.
Move your mouse over this icon to hide all screenshot
