- Chapter 6: The Oracle Data Dictionary 93 What Is the Data Dictionary? 93 Structure of the Oracle Data Dictionary 94. - The Purpose of the Sample Procedure 341. - A brief introduction to the architecture and components of the Oracle database. - An overview of the Oracle data dictionary, which contains information about the objects and operations of a specific Oracle database. - This final section of the book introduces you to the concepts behind optimization in the Oracle database.. - This first chapter introduces you to a lot of the behind-the-scenes underpinnings of the Oracle database — the architecture of the database and how the data is stored within it. - This chapter covers the architecture of the Oracle database as well as the storage options for your data. - A whole set of processes run in the background on an Oracle server, supporting the actions of the Oracle instance. - The previous section focused on the organization of the Oracle instance. - The data type dictates some of the characteristics of the data. - instead, the column holds a pointer to the location of the data. - The BFILE data type allows you to have a column that points to a storage location outside of the Oracle database’s internal storage. - However, with the exception of the BINARY data types listed later on, the Oracle database stores all numbers in the same internal format. - Understanding the architecture of the Oracle database is important in order to interact with the database in the most appropriate manner. - When you create a column in an Oracle database, you assign a data type to the column, which controls the basic representation and storage of the data in the column. - The first step in the process is to check the syntax of the SQL statement. - After an SQL statement has been checked, the Oracle database computes a hash algorithm to check whether a version of the statement exists in the shared SQL area. - If an SQL statement is a query, the Oracle database has to do some additional work to prepare for the return of the result set. - Retrieving those rows is the subject of the next section.. - In response to a query, the Oracle database prepares a result set that satisfies the conditions of the query. - As developers, we tend to think of the end results of an action. - If you have to reoptimize every statement, each statement adds to the overall workload of the target Oracle database. - The more the built-in features of the Oracle database can help you accomplish this task, the better. - Serialization also helps to sort out some of the multiuser data integrity issues discussed later in the chapter.. - A ROLLBACK either rolls back all the work for the entire transaction or just to the point of the last checkpoint. - Whenever any transaction changes data in the database, Oracle creates a new version of the changed row. - The Oracle database keeps track of the SCN of the oldest active transaction. - With Oracle, you are always getting a snapshot view of the database — the exact state of the database at one particular point in time.. - Instead, the Oracle database keeps track of row locks in the header of the physical page that contains the row. - This isolation level guaratees transactional integrity across an entire transaction, regardless of the number of statements in the transaction.. - All of the discussion in this chapter has centered around the use of transactions to protect data integrity.. - “how” issues are the province of the process designers and programmers. - Typically the design of the database goes through multiple high-level phases. - The logical database design phase delivered a consistent structure of the data without regard to the eventual deployment within a specific database environment. - It is usually appropriate, though, to take advantage of the specific capabilities (and limitations) of the selected relational database management system (RDBMS).. - tool that is capable of fully exploiting all of the capabilities of the RDBMS. - In the ERD, a broken line indicates that end of the line to indicate that the relationship from that direction is optional.. - The written description of the relationship uses the. - All of the columns (attributes, data elements) that form a table should have something in common. - Instead the relationship of each element that properly belongs in the table should be between the element and the key of the table.. - No two rows may have the same values of the key column(s). - in front of the attribute name.. - The point of the last discussion is that you may identify several candidate keys. - That will provide the majority of the data in the database. - Possible solution: Introduce redundancy by storing a prejoined, aggregated version of the data using a materialized view.. - Some of the additions to the Oracle physical database design include indexes, partitioning, views, materialized views, sequences, and so on. - All of the Oracle features described throughout the rest of this book will either influence the database design or be impacted by it.. - This chapter focuses primarily on the security capabilities of the Oracle database. - You can also document various business rules that govern the integrity of the data and implement them within the logic of your programs and directly in the database. - Imagine the royal family of the Duchy of Datastein. - Following is a demonstration of some of the basic capabilities for defining users to the database:. - Some shops may choose to do as much of their security administration outside of the database. - Global roles apply only within the context of the database in which they are defined. - The procedure executes, by default, using the privileges of the procedure’s owner. - This can be accomplished by adding the AUTHID CURRENT_USER clause to the definition of the procedure.. - Any breach at any of these layers can compromise the rest of the layers.. - Oracle database 10g release 2 has been announced but not delivered as of the writing of this section. - Oracle’s LDAP product is called Oracle Internet Directory (OID), part of the Oracle Identity Management solution. - Developers, administrators, and operators all need to be aware of database exposures that can occur outside of the database itself.. - Originally introduced in Oracle8 (known then as the Advanced Networking Option) ASO is one of the least known but most useful options to the Oracle Enterprise Edition database. - All of the security features provided by Oracle can be used to create a highly secure database environment.. - Str ucture of the Oracle Data Dictionar y. - Historically all of the data dictionary views and their underlying tables were owned by the SYS account. - USER_USERS , for example, gives you some basic information about other users of the database.. - Another set of views provide a way of seeing information about all of the objects in the database, regardless of owner. - The ALL_TABLES view shows all of the tables that the current user is allowed to access. - Part of this is just due to the complex- ity of the dictionary. - One of the ways of protecting the integrity of the database is to isolate accesses to the database objects from the objects themselves. - Now it’s time to install the Oracle software so you can do the hands-on work in the rest of the book.. - This file should be located in the Disk 1 subdirectory of the directory holding the unzipped files. - This action calls up the first page of the Oracle Database 10g Installer, as shown in Figure 7-4.. - The next step is to click on the Install button at the bottom of the page. - The progress of the Database Configuration Assistant is shown in Figure 7-6.. - We use iSQL*Plus to demonstrate many of the examples in this book.. - Oracle client software is released with each version of the database. - With this software installed, you can move forward fearlessly through the examples in the rest of the book.. - You should be aware that there are features of the Oracle Database that differ from the published standards. - clauses further define the attributes of the table — such as columns. - The TABLESPACE clause identifies a tablespace called TESTSPACE as the physical location of the table.. - Another version of the CREATE statement is used to create views. - Refer to the Oracle documentation for complete descriptions of the capabilities of the ALTER statement.. - SQL takes care of the whole set of appropriate rows.. - Once you specify a table alias, you can use it instead of the table name as a qualifier, as in the following:. - The WHERE clause of the SELECT statement restricts the number of rows that are returned in the result set. - The following table contains a list of the common comparison operators.. - You can use numbers in an ORDER BY clause that reflect the position of an entry in the column list of the SELECT statement. - One of the natural outcomes of an inner join is the elimination of the return of some of the rows in the EMP table. - You can also have a query whose results are used to limit the content of the result set. - The first part of the SQL is simple:. - Of course, using a column list does not subvert any of the requirements on the columns. - The data type of each of these values must match or can be implicitly convertible to the data type of the column.. - To populate the NO_COMM table with some of the rows in the EMP table, you would use the following syntax:. - This syntax is appropriate since none of the rows in the original. - The table_name is the name of the table that will be updated. - where savepoint_name is the name of the savepoint previously set. - “FG,” followed by the two-digit representation of the chapter. - The syntax for the use of the EXISTS clause is as follows:. - You can see that you no longer are comparing a column to the results of the EXISTS clause. - This syntactical difference points to the difference in operation of the EXISTS clause. - The EXISTS clause does not call for a join with the table in the main part of the query
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt