分享
 
 
 

chapter8: Managing Schema Objects

王朝other·作者佚名  2006-01-30
窄屏简体版  字體: |||超大  

Chapter 8: Managing Schema Objects Purpose 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 This following topics are discussed in this chapter:

Accessing Schema Objects

Managing Tables

Managing Indexes

Managing Views

Managing Database Resident Program Units

Loading Data into Tables

Viewing Screenshots

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

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 Administration and select the Tables link.

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.

Managing Tables 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

Viewing the Attributes of a Table Back to Topic List

Follow the steps below to view the attributes of the HR.EMPLOYEES table:

1.

Select the EMPLOYEES table and click View.

2.

The Table View 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.

Viewing the Contents of a Table Back to Topic List

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 drop-down menu. Click Go.

2.

The View Data For Table page appears showing the row data in the Results section.

Creating a New Table Back to Topic List

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.

EMPLOYEE_ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME VARCHAR2(25)

EMAIL_ADDRESS VARCHAR2(25)

PHONE_NUMBER VARCHAR2(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 menu 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 OK.

7.

The Create Table page appears. Click OK.

8.

The Table 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.

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)

Modifying a Table Back to Topic List

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.

Dropping a Table Back to Topic List

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 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.

Select Create Like from the Actions drop-down menu. 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 to the delete the JOBS_HIST table.

9.

Click Yes to confirm the deletion of the table.

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).

Managing Indexes 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.

In this section, you will perform the following tasks:

Viewing the Attributes of an Index

Creating a New Index

Viewing the Attributes of an Index Back to Topic List

Perform the steps listed below to view the attributes of an index in your FSOWNER schema:

1.

Click the Indexes link in the Schema section on the Administration page to access the Indexes property page.

2.

The Indexes page appears. Enter FSOWNER in the Schema Name 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.

Select the Indexes link to return to the Indexes property page.

Creating a New Index Back to Topic List

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 Tables for the Object Type. 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 drop-down menu. 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_ID column by entering 1 in the Order column. Accept ASC as the Sorting Order. Click OK to create the index.

Managing Views Back to Topic List

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

Creating a New View

Accessing Views Back to Topic List

Perform the steps listed below to access views:

1.

Select Views in the Schema region of the Administration page. Enter HR in the Schema Name field and click Go.

2.

The views defined on tables in the HR schema are displayed. Select the EMP_DETAILS_VIEW and click View to view its definition.

3.

The View page is displayed showing the definition of the view.

Select the Views link.

Creating a New View Back to Topic List

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.

Select the Database link.

Managing Database Resident Program Units Back to Topic List

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 Schema section on the Administration page.

2.

Enter hr in the Schema field click Go.

3.

Select the ADD_JOB_HISTORY procedure. Select Grant Privileges from the drop-down menu and click Go.

4.

Select EXECUTE as the privilege and FSOWNER as the user. Click OK.

Loading Data Into Tables 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 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有