| Oracle® Database 2 Day DBA 10g Release 1 (10.1) Part Number B10742-03 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 10g Release 1 (10.1) Part Number B10742-03 |
|
|
View PDF |
This chapter discusses the management of schema objects. You are shown how to explore the schema objects in the preconfigured database. The chapter contains the following sections:
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. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
|
Note: There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas. |
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you do with the logical and physical structures of your database using Oracle Enterprise Manager. The underlying SQL is generated for you by Oracle Enterprise Manager.
This section contains the following topics:
|
See Also: Oracle Database SQL Reference for more detailed information about schema objects, object names, and data types |
To explore and manage the schema objects in your database, open the Database Administration property page (see Figure 5-2, "Database Administration Page") select one of the links under the Schema heading. Figure 8-1 shows these links.
Click each link under the Schema heading in turn to see the all of the objects in your database. You can also access any type of schema object from the Search section of any schema object home page.
When you select a schema link, a standard Object_Name page appears. In the Search section of the page, shown in Figure 8-2, you can access other schema objects, including ones with no direct link under the Schema heading. To select a specific schema object, use the drop down menu under the Object Type heading.
As you can see, you can further qualify your search for objects by specifying the Schema and Object name fields.
Some object types have many more management options than others but most have a number of similarities. Every object in the database belongs to just one schema and has a unique name within that schema. Therefore, when you create an object, you must ensure it is in the schema where you intend to store it. Generally, you place all of the objects that belong to a single application in the same schema.
A database object name must abide by certain rules including that it must be unique within its schema. For example, object names cannot be longer than 30 bytes and must begin with a letter. If you attempt to create an object with a name that violates any of these rules, Oracle raises an error.
The following sections describe how to view, create, and manage the various types of objects in your database schemas:
|
See Also: For more information about naming database objects, see Oracle Database SQL Reference |
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 an employee table, for example, there can be (vertical) columns called employee and employee number. Each (horizontal) row in the table would contain a value for employee and employee number.
To view a table, click Tables on the Administration page of Enterprise Manager. The Tables property page appears as in Figure 8-3.
Initially, you see the tables for the schema that you are logged into. To see tables in other schemas, click the flashlight adjacent to the Schema box to select another schema, then click Go to display the results. For example, you can specify HR as your schema and EMPLOYEES as the table.
To view a table definition, from the Results list, either click the link in the Table Name column or select a table, such as HR.EMPLOYEES, then click View.
To view data in a table from the Tables property page, select the table, then select View Data, from the Actions drop down menu, and click Go. A View Data For Table page appears showing the row data in the Results section.
In Figure 8–4, the Query box displays the SQL query that was executed in order to view the data for the HR.EMPLOYEES table.
To sort data in a column, you can click any column name. To change the query, click Refine Query. The Refine Query page lets you select the columns to display. It also lets you specify a WHERE clause for the SQL statement to limit the results.
You can write your own SQL query using a SELECT statement to see the contents of a table. You can execute SQL statements by starting an iSQL*Plus session from Enterprise Manager's Database Maintenance property page. A detailed description of the SELECT statement and its options is contained in the Oracle Database SQL Reference.
You can use Enterprise Manager to create tables. You can do so as a system administrator or user with the CONNECT role.
The most common type of table in an Oracle database is a relational table, structured like the employee table described at the beginning of this section, "Managing Tables". Two other types of table are supported: object tables and XMLType tables. Any of the three table types can be defined as permanent or temporary, the latter with the option of making the table structure available to multiple users or only to the user who creates the table.
Relational tables can be built in either heap or index-organized structures. In the former, the rows are not stored in any particular order but in index-organized tables, the row order is determined by the values in one of more columns which you select.
This chapter concentrates on permanent, heap-organized tables. For information about other table types and when to use them, see the Oracle Database Administrator's Guide, Oracle Database Concepts, and the Oracle Database Performance Tuning Guide. For the syntax required to create and alter tables, see the Oracle Database SQL Reference.
Each column in a table is defined with one or more characteristics as follows:
Data type: a required element which defines the nature of the data to be stored in the column and may include a value to indicate the longest value that can be placed in the column.
Constraint: one or more optional elements that determine what values are valid in the column. Some constraints can be defined at the column level or at the table level. In Enterprise Manager, the only constraint defined at the column level on the Create Table property page is the Not Null constraint, which requires that a value is included in this column for every row in the table.
Default value: A value that is automatically stored into the column whenever a new row is inserted without a value being provided for the column.
These column characteristics are described in more detail in the following sections.
When you create a table, you must specify a data type for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments.
These data types define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert 17-JAN-2004 into a date column, then Oracle treats the 17-JAN-2004 character string as a value after verifying that it translates to a valid date.
Table 8-1 is a summary of Oracle's built-in datatypes.
Table 8-1 Common Datatypes
In most tables, you should only need columns of NUMBER, VARCHAR2, and DATE datatypes. When defining numeric data, you can use the precision option to set the maximum number of digits in the number, and the scale option, to define how many of the digits are to the right of the decimal point.
For example, a field to hold monetary values might be defined as NUMBER(12,2)providing ten digits for the primary unit of currency (dollars, pounds, marks, and so on), and two digits for the secondary unit (cents, pennies, pfenigs, and so on). To define a VARCHAR2 field for character data, you must include the size value.
Set the size to the maximum number of bytes (or, optionally, characters) to be stored in the column. A column to hold postal codes for different countries, for example, might be restricted to 12 bytes by defining it as VARCHAR2(12). DATE columns are automatically formatted by Oracle to include a date and time component.
The Not Null constraint on a column requires that the column must contain a value whenever a row is inserted or updated. Unlike other constraints described in "Table Level Constraints", which may be defined as part of the column definition or part of the table definition, the Not Null constraint must be defined as part of the column definition.
Use a Not Null constraint when the data is required for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier should be defined with a Not Null constraint. On the other hand, do not define a column as Not Null if the data may be unknown or may not exist when rows are added or changed, for example, the second, optional line in a mailing address.
A primary key constraint automatically adds a Not Null constraint to the column or columns included in the primary key.
In an Oracle database, you can apply rules to preserve the integrity of your data. For example, in a table containing employee data, the Name column cannot accept NULL as a value. Similarly, in this table you cannot have two employees with the same ID.
Oracle enables you to apply data integrity rules called constraints to tables at the column level or, with the exception of the Not Null constraint discussed earlier, at the table level. Any attempt to insert or update a row that violates a constraint results in an error and the statement is rolled back. Likewise any attempt to apply a new constraint to a populated table will also result in an error if any existing row violates the new constraint.
The types of constraints you can apply at the table level are as follows:
Primary Key—Requires that a column (or combination of columns) be the unique identifier of the row. A primary key column does not allow NULLs.
Unique Key—Requires that every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns. The set of columns is said to be the unique key.
Check—Requires that a column (or combination of columns) satisfy a condition for every row in the table. A check constraint must be a boolean expression that is evaluated using the column value about to be inserted or updated to the row.
Foreign—Requires that all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or child table. The table that is referenced is called the parent table. An example of a foreign key constraint is when the department column of the employees table (child) must contain a department ID that exists in the parent department table.
Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added or modified, and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively.
|
See Also: Oracle Database Concepts for more information on constraints |
When you create a table in a locally managed tablespace, using automatic segment space management, you should allow Oracle to build the table using its own storage algorithms. If you need to store a table in a dictionary-managed tablespace, you can set the storage characteristics for the table.
However, the preconfigured database you installed (see Chapter 2, "Installing Oracle and Building the Database") and extended as described in Chapter 6, "Managing Database Storage Structures" should not contain such tablespaces. Therefore, these options are not discussed here but are described in the Oracle Database Administrator's Guide and Oracle Database Concepts manual if you need to use them.
When creating a table using the Enterprise Manager Create Table page, you can click the Show SQL button to see the SQL statement that has been constructed. This option will show the statement even if it is incomplete, so you need to complete your input to see the complete SQL statement when using this option.
You may also create a new table with the same the structure and contents as an existing table. This can be useful to build a test table that resembles a production table. To do this, select SQL in the Define Using list and complete the statement that is begun for you in the CREATE TABLE AS box. Your query can include all columns and rows from the original table or use the full power of the SQL SELECT statement to identify which rows you want, rename columns with column aliases, or exclude certain columns.
Your new table may include a column defined with an abstract data type (ADT). ADTs are created with the CREATE TYPE statement and are described in detail in the Oracle Database SQL Reference.
If you are creating a table containing one or more LOB columns, you may define different storage options for the LOB components of the table. In most cases, you should use the default settings but, if you want to override them, you click Set Default LOB Attributes and complete the entries in the Set Default LOB Attributes page. Additionally, you can set LOB attributes for a single column by selecting the column name and clicking the Advanced Attributes button.
You may also create tables with multiple partitions. Each partition is a separate database object with many of the characteristics of an individual table, although each partition contains the same column definitions as its parent table. Partitioning a very large table can simplify the management of the table because each partition can be configured independently of the other partitions. Also, some operations against a partitioned table may be more efficient than against the table if it were not partitioned. This is because the partitions may be spread across more disk drives than a non-partitioned table and because the optimizer may be able to avoid processing the contents of every partition when processing certain SQL statements.
For some applications, particularly data warehousing, with large tables that are frequently queried but very rarely updated, you may create compressed tables. These require less disk storage than uncompressed tables (which are the default). To create a compressed table, you will need to write your own CREATE TABLE statement, because the option is not available in Enterprise Manager.
|
See Also: Oracle Database Concepts and Oracle Database Administrator's Guide for design and management considerations of different table types and Oracle Database SQL Reference for the syntax to create tables. |
During table creation with Enterprise Manager, you can estimate the size of the table, allowing you to determine if you have sufficient room in your database or on the available disks, to store the table. If you do not have room, you can still create the table but not populate it until you have obtained the necessary storage.
To estimate the table size, you must first enter your column names and datatypes, then click Estimate Table Size in the Create Table page. The Estimate Table Size page then prompts you for the projected number rows, which you enter. After clicking Estimate Table Size, Enterprise Manager returns its estimate in MB.
In the following example, we create a table called purchase_orders in the MYUSER schema we created in Chapter 7, "Administering Users and Security". The table will have columns po_number, po_description, po_date, and po_item.
To create a table in MYUSER's schema, log in to Oracle Enterprise Manager as either SYS or MYUSER and follow these steps:
Under the Administration page, click Tables. The Tables page appears listing the tables in the MYUSER schema. Note if you logged in as SYS, you need to click the flashlight icon and select MYUSER to manage tables in this schema.
Click Create. The Create Table: Table Organization page appears.
Select the box marked Standard, Heap Organized. Click Continue.
The Create Table page appears. See Figure 8-5, "Create Table Page".
In the Name field, enter purchase_orders as the table name. In the Schema field, accept the MYUSER schema, and in the Tablespace field, accept Default.
In the Columns section, enter the following columns with the datatypes:
po_id as NUMBER
po_date as DATE
po_desc as VARCHAR2(200)
po_item as VARCHAR2(100)
You can leave the Scale, Not Null, and Default Value blank.
|
Note: If you want to the create table with partitions, you can do so at this step by clicking Partitions. |
Click OK. An update message appears indicating the table has been successfully created.
You can use Enterprise Manager to modify tables, such as adding and deleting columns or adding constraints. The following sections show how to add and delete a column, and how to add, change the status of, and drop a table constraint.
In this example, we modify the PURCHASE_ORDERS table by adding a new column called po_item2. To add the new column:
On the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.
In the Edit Table page, click Add 5 Table Columns. An editable columns list appears.
Enter po_item2 as VARCHAR2(100) as the new column. You can leave the Scale, Not Null, and Default Value fields blank. Click Apply.
An Update Message appears indicating that the table has been modified successfully.
This example shows you how to drop a column, in this case, the po_item2 column that was added to the PURCHASE_ORDERS table in "Adding a Column". To delete the column:
In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.
In the Columns table on the Edit Table page, select the column you want to delete, po_item2.
Click Delete.
The row that contained the information for the deleted column, po_item2, becomes blank to indicate that the table has been successfully.
You can add constraints to a table with Enterprise Manager. In our current example, we want to enforce that our purchase orders are always entered with an ID and that the purchase order ID (po_id) is the Primary Key of the table. We will add the Not Null and primary key constraints to this column.
|
Note: You can also add constraints during table creation. |
To add these constraints to the purchase_orders table, do the following:
In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.
To apply the Not Null constraint, check the Not Null box next to the po_id column. Click Apply.
To apply the primary key constraint, click the Constraints property page.
On the Constraints page, select PRIMARY from the drop-down menu, and click Add. The Add Primary Constraint page appears.
In the available columns list, select po_id and move it to the selected columns list. To keep the default settings for the constraints, do not change the check boxes in the Attributes area of the page.
Click OK. On the Edit Table page, click Apply. A confirmation message appears. You can view the new constraints by navigating to the View Table page.
You can change the status of an existing constraint, for example, from an enabled to a disabled state. Using the Primary Key constraint created in the previous section, "Creating a New Table Constraint", here is an example of how to disable a constraint:
In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.
To locate the constraint to be disabled, click the Constraints property page.
On the Constraints page, select the row containing the PRIMARY constraint from the Constraints table, and click Edit.
Under the Attributes section of Edit PRIMARY Constraint window, select the Disabled box.
Click OK. On the Edit table page, click Apply. A confirmation message appears and the Disabled column shows that the primary key constraint has been disabled.
You can drop constraints to a table with Enterprise Manager. Although you do not have to disable a constraint before dropping it, you can determine whether the constraint can be dropped by attempting to disable it first. When a constraint in a parent table is used to enforce a foreign key constraint in a child table, and if the child table contains dependent rows, the constraint cannot always be disabled or dropped.
Continuing the current example, we drop the primary key constraint that we created earlier in the section, "Creating a New Table Constraint", by completing the following steps:
In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.
To locate the constraint to be disabled, click the Constraints property page.
On the Constraints page, select the row containing the PRIMARY constraint from the Constraints table, and click Delete.
On the Edit table page, click Apply. A confirmation message appears.
If you no longer need a table or its contents, you can drop the table using Enterprise Manager. Be certain that you really do not need the data in the table before you drop it because it may be difficult and time-consuming to retrieve the records, if they can be retrieved, after you execute the drop statement.
To drop a table, follow these steps:
From the Tables page, identify the table you want to delete by selecting the required schema and the required table name.
Select the table you need to delete from the Results table and click Delete.
The Confirmation page lets you choose whether to continue the operation. If you are certain you have identified the correct table, click Yes.
The Edit table page displays a confirmation message when the table is successfully deleted.
Indexes are optional structures associated with tables. They can be created to improve query performance. Just as the index in this book helps you to quickly locate specific information, an Oracle 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 Oracle. Changes to a table's data or structure, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes with complete transparency to the user.
Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle to create a unique key index.
When you click the Indexes link under the Schema heading of the Administration page, an Indexes page appears, which is similar in content and functionality to the Tables page shown in Figure 8-3.
To find out what indexes exist in your database, use the search options on the Indexes page. The search works like the one described earlier for tables except that, when searching by object name, you can use the name of the index or the name of the table on which the index is built. Select whether the search is for an index or a table by clicking the appropriate entry in the Search By list.
To view the details of an index, click its name in the Results list on the Indexes page. You can find basic information about the index, such as its status and the column or columns on which it is built, in the General area of the main page for each individual index. Other areas include information about the space consumed by the index and the options used in its definition.
Oracle automatically creates the indexes necessary to support data integrity defined with constraints when you add or enable those constraints. For performance purposes, you might want to add an index to the columns you define in a child table when adding a foreign key constraint. Before you add additional indexes, you should examine the performance of your database. You can then compare performance after the new indexes are added.
After index creation, Oracle automatically synchronizes the index with any subsequent inserts, updates or deletes to the base table.
Indexes are generally of value to queries and to SQL statements that need to operate on a single, existing row or a small number of existing rows. However, too many indexes can cause serious problems by increasing the processing overhead for statements that add, modify, or delete rows. In some cases, a statement could use two or more indexes and the optimizer will pick just one of them. Unless other statements can take advantage of the unused indexes, they are not providing any benefit. Therefore, you might find yourself deleting indexes if you create too many.
Indexes can be categorized in a number of ways, the primary options are listed in the following section:
A standard, B-tree index contains an entry for each value in the index key along with an address to the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database. A bitmap index uses strings of bits to encapsulate values and potential row addresses. It is more compact than a B-tree and can perform some types of retrieval more efficiently. For general use, however, a bitmap index requires more overhead during row operations on the table and should be used primarily for data warehouse environments as described in the Oracle Data Warehousing Guide.
The default search through an index is from lowest to highest value, where character data is sorted by ASCII values, numeric data from smallest to largest number, and date from the earliest to the latest value. This default behavior is performed in indexes created with as ascending indexes. You can cause index searches to reverse the search order by creating the related index with the descending option.
Typically, an index entry is based on the value or values found in the table's column or columns. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data which can be in mixed case, you could use a function-based index to look for the values as if they were all in uppercase characters.
You can create an index on just one column (single column index) or on multiple columns (concatenated index). Concatenated indexes are useful when all of the columns are likely to be included in the WHERE clause of frequently-executed SQL statements. For concatenated indexes, you should define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with next fewest duplicate values is second, and so on. Columns with many duplicate values or many rows with no value (Nulls) should not be included or should be the last-named columns in the index definition.
As with tables, you can create your indexes without or with partitions. In most situations, it is useful to partition an index when the associated table is also partitioned and the index uses the same partitioning scheme as the table, known as a local index. This does not mean you must partition an index like the underlying table. You may even create a non-partitioned, or global, index on a partitioned table.
|
See Also: Oracle Database Concepts and Oracle Database Administrator's Guide for design and management considerations of different index types and Oracle Database SQL Reference for the syntax to create tables. |
You can create indexes with Enterprise Manager. To create an index, you specify one or more columns to be indexed and the type of index you want to create.
The following example creates a standard B-tree index on the SUPPLIER_ID column in the SH.PRODUCTS table, which is part of the sample schema. To create this index, do the following:
On the Administration page under Schema, click Table. The Tables page appears.
In the Search section, enter SH as the Schema and click Go. Under Results, the list of tables in the SH sample schema appears.
Select the PRODUCTS table and select Create Index from the Actions drop-down menu. Click Go.
The Create Index page appears. Name the new index PRODUCTS_SUPPLIER_IDX. For Tablespace, accept the default, and select Standard B-tree as the index type.
In the list of table columns, select the SUPPLIER_ID column by entering 1 in the Order column. Accept ASC as the Sorting Order. Click OK to create the index.
After the index is created, the Indexes page appears with a confirmation message. The new index is listed under Results.
To drop the table using Enterprise Manager, follow these steps:
From the Indexes page, identify the index you want to delete by selecting the required schema and the required index name.
Select the index you need to drop from the Results table and click Delete.
The Confirmation page lets you choose whether to continue the operation. If you are certain you have identified the correct index, click Yes.
The Edit table page displays a confirmation message if the index is successfully deleted.
|
Note: You cannot drop an index that is currently used to enforce a constraint. You must disable or drop the constraint and then, if the index is not dropped as a result of that action, drop the index. |
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.
Click the Views link under the Schema heading of the Administration page to look at the views in the database. The Views page appears and has functionality similar to the Tables page shown in Figure 8-3.
Look at the views in the SYS schema. These views enable you to look at data dictionary information or ongoing database activity. The views in the SYS schema are also the source of information for many of the Oracle Enterprise Manager pages.
The data dictionary views are called static, because they change only when a change is made to the data dictionary (for example, when a new table is created or a user is granted new privileges). The views that monitor ongoing database activity are called dynamic performance views.
Many data dictionary tables have three corresponding views:
A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators.
An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of privileges or roles.
A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
The columns of the ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are usually nearly identical.
The names of dynamic performance views, available only to administrators, start with the characters V$ and are often referred to as the V$ views.
Complete descriptions of the views in the SYS schema are contained in the Oracle Database Reference.
The following example creates a view derived from the HR.EMPLOYEES table, which is part of the sample schema. The view filters all direct reports for the manager Stephen King, whose employee_id is 100. In an application scenario, this view adds an additional level of security to the HR.EMPLOYEES table while providing a suitable presentation of relevant information for manager Stephen King.
To create this view, do the following:
On the Database Administration page, under Schema, click Views. The Views page appears.
Click Create. The Create View page appears.
Enter the following fields:
Name: SKING_VIEW
Schema: HR
Query Text:
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM HR.EMPLOYEES WHERE MANAGER_ID = 100
Click OK. The Views page appears with a confirmation message. The new view is listed under Results.
To see the new view, select it and choose View Data from the drop-down menu. Click Go. The selected data in the view appears under Result.
Oracle offers the capability to store programs in the database. This allows commonly required code to be written and tested once and then accessed by any application that requires the code. Database resident program units also ensure that the same processing is applied to the data when the code is invoked, making the development of applications easier and providing consistency between developers.
Database resident programs can be written in PL/SQL or Java, and you can use Enterprise Manager to manage the source types such as PL/SQL packages, procedures, triggers, functions, and Java sources and classes. The actions include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these source types.
This book describes the main types of PL/SQL program units. You should have experience with the PL/SQL language before attempting to create or modify these units. The following sections cover:
|
See Also: PL/SQL User's Guide and Reference for help in understanding and writing PL/SQL code and program units. |
A package is a structure that contains definitions, blocks of PL/SQL code, or both in a single unit. The contents of the package are invoked by naming the package and the element within it using a dot notation. For example, to execute the procedure called STORE_SQE in the CTX_QUERY package owned by the CTXSYS user, you would invoke it with the statement CTXSYS.CTX_QUERY.STORE_SQE(<arg1>,<arg2>) where <arg1> and <arg2> are the values for the two procedure arguments. To hide the complexity of packaged element names, you might want to use synonyms, described later in "Other Schema Objects"
You can examine packages that already exist in the database by clicking the Packages link on the Enterprise Manager Administration property page to open the Packages property page. You can then search for packages by schema, name, both schema and object name, or neither (this latter option will include every package in the result set). To see a package definition, click its name in the Results list or select the package in the Results list and click Edit. The Edit Package property page contains the package name, schema, status, and source.
The source contains the code for a package and may contain the following elements:
Authentication information: determines which security schema is used when the package contents are executed
Procedure definitions: the name and list of arguments for one or more procedures
Function definitions: the name, list of arguments, and return data type for one or more functions
Variables: definitions of variables that globally available to the user of the package
Other public elements: definitions of such structures as types, constants, and exceptions that are needed by package users
From the Edit Package property page, you can also perform the following actions:
Modify the package definition by typing new contents in the Source box and clicking Apply.
Compile the package by clicking Compile, an action that may be required if the package status has become invalid for some reason.
Modify the package body (see next section) by clicking Modify Package Body.
|
Note: You can also navigate to the Edit Package property page from the Edit Package Body property page, described in the next section, by clicking Edit Package. |
As with other schema objects, you can create a new object by clicking Create on the object page. In this case, you use Package property page. The Create Package property page provides fields to enter the package name, schema name, and the PL/SQL code for the package definition.
To delete a package, select the package from the Results list on the Packages property page and click Delete.
Package bodies contain the PL/SQL code for the procedures and functions defined in the definitions of the packages to which they belong. The same navigation paths and options for adding (creating), editing (modifying), compiling, and deleting package bodies are available in Enterprise Manager as for packages, as described in the previous section. This includes direct navigation between the Edit Package Body and the Edit Package property pages as mentioned in the previous section.
In some cases, the developer of the package may have wrapped one or more of the individual procedures or functions. This converts the code into non-readable strings which makes it difficult for other developers to misuse the application or for competitors to see the algorithms.
However, wrapped code cannot be edited and the Source box in the Edit Package Body page will contain a message such as Source code could not be displayed, because it is wrapped and will not be available for modification.
Procedures and functions do not have to belong to a package. Program units that are created outside of a package are called standalone subprograms. You can use Enterprise Manager to create, modify, compile, and delete standalone subprograms just like packaged subprograms except that they do not have a parent package (and, consequently, no navigation path in Enterprise Manager from or to the package property pages). There are separate property pages for procedures and for functions which you can find from the Enterprise Manager Administration property page.
To execute a standalone subprogram, you only need to include its schema name and object name: there is no package name. Even so, you may want to create synonyms for subprograms that are used regularly.
Note that functions return a value, whether or not they use an argument list, the datatype for which must be defined as part of the function code.
A database trigger is a stored subprogram associated with a database table, view, or event. For instance, you can have Oracle fire a trigger automatically before or after an INSERT, UPDATE, or DELETE statement affects a table. One of the many uses for database triggers is to audit data modifications. For example, the following table-level trigger fires whenever salaries in the emp table are updated:
CREATE TRIGGER audit_sal AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES... END
The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas.
|
See Also: Oracle Database Application Developer's Guide - Fundamentals and PL/SQL User's Guide and Reference for more information about the uses for and creation of triggers. |
Triggers are another type of schema object that you can manage through Enterprise Manager. They have their own property page which you can open by clicking Triggers under the Schema heading on the Administration page. From the Triggers property page, you can create new triggers, find existing triggers and either delete them or open an Edit Triggers page.
The Create Triggers and Edit Triggers property pages look very similar to the corresponding pages for packages, package bodies, and standalone subprograms. The major difference is that there are two check boxes: one to check if you want to replace an existing trigger and one to immediately enable a trigger after it is created or edited. The other difference is that the box containing the PL/SQL code is labeled Trigger Body instead of Source. Otherwise, the creation, editing, and compilation of triggers is exactly the same as for other database PL/SQL units.
Because triggers run automatically as a result of the triggering DML on the table, there is no way to run them directly.
Other schema objects are not discussed in detail in this book, but are described in Oracle Enterprise Manager online Help. As with the schema objects described in the preceding sections, they can be managed from Enterprise Manager by clicking the required link under the Schema heading on the Administration property page. The schema object pages provide:
Search capabilities to find existing objects
Links to drill down to individual objects, with further drill down to details as appropriate
Navigation to screens where you can add, edit, or delete an object
These other schema objects include:
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, fewer disk accesses may be required to retrieve records defined with a WHERE clause in a SQL statement.
Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.
A synonym is an alias for any schema object such as a table or view. Synonyms provide an easy way to hide the underlying database structure from an application or a user. For example, you can create a synonym named PERSONNEL as an alias for the EMPLOYEES table in the HR schema. With the synonym in place, the following query:
SELECT employee_id, salary FROM hr.employees ORDER BY salary
can be rewritten more simply as:
SELECT employee_id, salary FROM personnel ORDER BY salary
An additional benefit of synonyms is that you can use the same synonym in a development database as in the production database, even if the schema names are different. This allows the application code to run unmodified in both environments. For example, the second version of the preceding query would work in a development database where the EMPLOYEES table was in the DEV1 schema if the PERSONNEL synonym were defined.
Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
A sequence is a database object from which multiple users can generate unique integers. Using a sequence generator to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique.
A database link is a schema object that instructs Oracle to connect to a remote database to access an object there. They are used in distributed database environments which are described in the Oracle Database Administrator's Guide.
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.
For loading data with Enterprise Manager, one method is to use control (.ctl) and data (.dat) files. These files are formatted as standard SQL*Loader files.
The following example loads data into the PURCHASE_ORDERS table we created with MYUSER in "Managing Tables". For simplicity, this example loads only three rows. Your control and data files can be different, but the idea is the same for this method of loading.
This example requires us to create two files: load.ctl and load.dat.
The control file load.ctl looks like:
LOAD DATA INTO TABLE myuser.purchase_orders REPLACE FIELDS TERMINATED BY ';' (po_id, po_date date 'yyyymmdd', po_desc char(25), po_item char(25), po_item2 char(25))
Our data file load.dat looks like:
1; 20031025; DSL/Wireless Expense Oct; Home DSL; Wireless 2; 20031125; Office Equipment; Optical Mouse; Network Router 3; 20031225; Computer System; Dell Pentium 4 Laptop; Docking Bay
|
See Also: Oracle Database Utilities for more information about SQL*Loader control files |
To load the data file into the myuser.purchase_orders table, you must log in to Enterprise Manager as SYS or as myuser, who has administrator privileges:
From the Maintenance page, click Load Data from File under Utilities to begin a six step loading wizard. Figure 8-6, "Load Data page" shows the first step.
In the Load Data: Control File page, 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.
In the Load Data: Data File page, select Provide the full path and name on the database server machine and type in the path. Click Next.
In the Load Data: Load Method page, accept the default Conventional Path as the loading method. If you have a lot of data, you can use the Direct Path method.
In the Load Data: Options page, under Optional Files, select Generate log file where logging information is to be stored. You can accept the default file name and path or enter a different one. Note that this page gives you the option of limiting the number of rows loaded. Since we are only loading three rows in this example, we leave this un-selected. Click Next
In the Load Data: Schedule page, enter a Job Name and Description. Select Immediately to run the job now. Click Next.
The Load Data: Review page lets you review your file names and loading methods. If you want to change something, you can click the Back button. Otherwise, click Submit Job to start the loading.
The Status page appears. It should say Load Data Submit Successful. Click View Job to view the job summary. This summary page should indicate that the job has succeeded. If not, you can view the log file by clicking your job under the Logs heading or by viewing the log file directly.
You can confirm the loaded data by navigating to the Tables page, selecting the table, and choosing View Data as the action. See Figure 8-7, "View Data: MYUSER.PURCHASE_ORDERS".
By default, only the owner of an object or users with advanced system privileges can access the object. For each object you want to make available to other users, you need to include the object and the access methods you want to allow in a role. The role can then be assigned to the users who require the access as described in the "Granting Roles".
To add an object privilege to a role, first create or identify an existing role that you want to use. Find or create the role by clicking Roles, under the Security heading, on the Database Administration page. To use an existing role, click its name; to create a new role, click Create on the Roles page.
On the Edit Role or Create Role property page, depending on whether you are using an existing role or creating a new one, follow these steps to add an object privilege to the role:
Choose the object type from the Select Object Type then click Add.
Enter the schema name and object name, with a period separator, in the Select Object_Type Objects window, or click the flashlight icon to initiate a search for the required object.
Use the Move and Remove arrows to place the required privileges into the Selected Privileges window.
Click OK to add the privileges to the role.
To revoke object privileges, you can either delete the role if it contains only the object privileges you want to remove, or you can remove individual privileges from a role. To perform either of these actions, you must first find the required role as described earlier in this section. To delete the role, select it from the Results table and click Delete. To remove an individual privilege, click the role name in the Results table to navigate to the Edit Role page then click Object Privileges. Then select, in turn, each privilege you want to revoke and click Delete then Apply to remove it from the role.
Some schemas with tables of particular interest are discussed in the sections that follow.
All of the base tables and views for the database's data dictionary are stored in the schema SYS. The data dictionary is a collection of tables that describe the Oracle database. The data dictionary is created in the SYSTEM tablespace at database creation and it is updated by the database server every time that a data definition language (DDL) statement is issued. Oracle accesses the data dictionary to find information about users, schema objects, and storage structures. The data dictionary is critical for the operation of Oracle.
Oracle users can use the data dictionary as a read-only reference for information about the database. They do not access the tables directly, but rather they access a set of views built over the tables.
Objects in the SYS schema should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Special system privileges protect access to the SYS schema.
The SYSTEM schema contains additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create tables in the SYSTEM schema for use by non-administrative users.
The sample schemas provide a common platform for examples in Oracle releases. They are a set of interlinked schemas aimed at providing a layered approach to complexity:
HR (Human Resources)—This is a simple schema for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.
OE (Order Entry)—This schema is for dealing with matters of intermediate complexity. A multitude of datatypes is available in the schema.
The OC (Online Catalog) subschema is a collection of object-relational database objects built inside the OE schema.
PM (Product Media)—This schema is dedicated to multimedia datatypes.
QS (Queued Shipping)—This is a set of schemas gathered under the main schema name QS to demonstrate Oracle Advanced Queuing capabilities.
SH (Sales History)—This schema is designed to allow demos with larger amounts of data. An extension to this schema provides support for advanced analytic processing.
Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter and includes annotated screen shots.
To view the Schemas OBE, point your browser to the following location:
http://otn.oracle.com/obe/2day_dba/schema/schema.htm
