# OCA: Oracle Database 11g Administrator Certified Associate- P16

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
53
lượt xem
8

## OCA: Oracle Database 11g Administrator Certified Associate- P16

Mô tả tài liệu

Tham khảo tài liệu 'oca: oracle database 11g administrator certified associate- p16', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate- P16

2. 582 Chapter 10 N Allocating Database Storage and Creating Schema Objects Summary This chapter discussed the most important aspect of the Oracle Database: storing data. You learned to create both tablespaces and data files as well as to create schema objects that store the data. You found out how to create and manage tablespaces as well as how Oracle stores some schema objects as segments that are comprised of extents and data blocks. In addition, you learned how to create and modify tables, indexes, and constraints. I also cov- ered deferred constraint checking and how to configure foreign key constraints to support either deferrable or not deferrable implementations. A data file belongs to one tablespace, and a tablespace can have one or more data files. The size of the tablespace is the total size of all the data files belonging to that tablespace. The size of the database is the total size of all tablespaces in the database, which is the same as the total size of all data files in the database. Tablespaces are logical storage units used to group data depending on their type or category. Understand the relationship between data files and tablespaces because that is important information to know for the certification. Tablespaces can handle the extent management through the Oracle dictionary or locally in the data files that belong to the tablespace. Locally managed tablespaces can have uni- form extent sizes; this reduces fragmentation and wasted space. You can also make Oracle do the entire extent sizing for locally managed tablespaces. A temporary tablespace is used only for sorting; no permanent objects can be created in a temporary tablespace. Only one sort segment will be created for each instance in the tempo- rary tablespace. Multiple transactions can use the same sort segment, but one transaction can use only one extent. Although temporary files are part of the database, they do not appear in the control file, and the block changes do not generate any redo information because all the segments created on locally managed temporary tablespaces are temporary segments. You learned about tables, indexes, and constraints in this chapter. Also study Chapters 6 and 7 before taking the certification exam. Tables are created using the CREATE TABLE command. By default, the table will be created in the current schema. To create the table in another schema, you should qualify the table with the schema name. Storage parameters can be specified when creating the table. Tables can be moved or reorganized using the MOVE clause. Indexes can be created as B-tree or bitmap. Bitmap indexes save storage space for low- cardinality columns. You can create reverse key or function-based indexes. An index- organized table stores the index and row data in the B-tree structure. Tablespace and storage should be specified when creating indexes. Indexes can be created ONLINE; that is, the table will be available for insert/update/delete operations while the indexing is in progress. The REBUILD clause of the ALTER INDEX command can be used to move the index to a different tablespace or to reorganize the index. Constraints are created on the tables to enforce business rules. There are five types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY. The constraints can be created to check the conformance at each SQL statement or when committing the changes—checking for conformance at each statement is the default. You can enable and disable constraints. Constraints can be enabled with the NOVALIDATE clause to save time after large data loads.
3. Exam Essentials 583 Exam Essentials Know the relationship of data files to tablespaces. Tablespaces are built on one or more data files—bigfile tablespaces on a single data file and smallfile tablespaces on one or more data files. Understand the statements needed to create, modify, and drop tablespaces. Use a CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statement to create, modify, and drop a tablespace, respectively. Know how to take tablespaces offline and what consequences the OFFLINE IMMEDIATE option poses. Use an ALTER TABLESPACE statement to take a tablespace offline or bring it online. If you use the OFFLINE IMMEDIATE option, you must perform media recovery when you bring it back online. Understand the default tablespaces for the database. When the database is created, if you do not specify the DEFAULT TABLESPACE and DEFAULT TEMPORARY TABLESPACE clauses, the SYSTEM tablespace will be the default for user objects and temporary segments. Know how to use the EM Database Control to view tablespace information. The EM Database Control can be used to view tablespace information as well as perform various administrative tasks. A working knowledge of this tool is required. Know the difference between segment space management and extent management. Extent management deals with segment-level space allocations, and segment space management deals with data block-level space allocations. Know which initialization parameter controls OMF placement. The DB_CREATE_FILE_ DEST parameter tells the database where to place Oracle Managed Files. Know the different types of constraints and which have dependencies with others. There are the CHECK, NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. A PRIMARY KEY constraint implicitly includes NOT NULL and UNIQUE constraints. A FOREIGN KEY con- straint must refer to a PRIMARY KEY or UNIQUE constraint. Know the types of indexes and when they are appropriate. B-tree indexes are medium- to high-cardinality columns in applications in which data can change frequently. Bitmap indexes are best for low- to medium-cardinality columns in applications that control data changes, usually in batches.
4. 584 Chapter 10 N Allocating Database Storage and Creating Schema Objects Review Questions 1. Which of the following statements about tablespaces is true? A. A tablespace is the physical implementation of logical structure called a namespace. B. A tablespace can hold the objects of only one schema. C. A bigfile tablespace can have only one data file. D. The SYSAUX tablespace is an optional tablespace created only if you install certain data- base options. 2. Automatic segment space management on the tablespace causes which of the following table attributes in that tablespace to be ignored? A. The whole storage clause B. NEXT and PCTINCREASE C. BUFFERPOOL and FREEPOOL D. PCTFREE and PCTUSED 3. Which is not a type of segment that is stored in a tablespace? A. Undo B. Redo C. Permanent D. Temporary 4. Can a table name ever include the special metacharacter dollar sign ($)? A. No B. Yes C. Only if the table name is enclosed in double quotes D. Only if the table name is enclosed in single quotes 5. Which operation can you not do to a table that is created with the following SQL statement? CREATE TABLE properties (“Location” NUMBER primary key ,value NUMBER(15) ,lot varchar2(12) ,constraint positive_value check (value > 0) ); 5. Review Questions 585 A. Rename the primary key to properties. B. Insert a null into the value column. C. Add a column named owner. D. Rename the index-supporting primary key to properties. E. None of the above. 6. Which constraint-checking model is the default? A. Initially immediate and deferrable B. Initially immediate and not deferrable C. Initially deferred and not immediately D. Initially deferrable and not immediate 7. Which allocation unit is the smallest? A. Data file B. Extent C. Data block D. Segment 8. Which of the following is not a valid Oracle 11g datatype? A. TIMESTAMP WITH LOCAL TIMEZONE B. BINARY C. BLOB D. UROWID 9. How do you specify that a temporary table will be emptied at the end of a user’s session? A. Create the temporary table with the ON COMMIT PRESERVE ROWS option. B. Create the temporary table with the ON DISCONNECT PRESERVE ROWS option. C. Create the temporary table with the ON DISCONNECT PURGE ROWS option. D. Create the temporary table with the ON COMMIT DELETE ROWS option. 10. You performed the following statement in the database. What actions can you perform on the table CUST_INFO in the CUST_DATA tablespace. (Choose all that apply.) ALTER TABLESPACE CUST_DATA READ ONLY; A. ALTER TABLE CUST_INFO DROP COLUMN xx; B. TRUNCATE TABLE CUST_INFO; C. INSERT INTO CUST_INFO VALUES (…); D. DROP TABLE CUST_INFO; E. RENAME CUST_INFO TO CUSTOMER_INFO; 6. 586 Chapter 10 N Allocating Database Storage and Creating Schema Objects 11. Which statements should be executed to make the USERS tablespace read-only, if the tablespace is offline? (Choose all that apply.) A. ALTER TABLESPACE USERS READ ONLY B. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY C. ALTER TABLESPACE USERS ONLINE D. ALTER TABLESPACE USERS TEMPORARY 12. How would you add more space to a tablespace? (Choose all that apply.) A. ALTER TABLESPACE ADD DATAFILE SIZE B. ALTER DATABASE DATAFILE RESIZE C. ALTER DATAFILE RESIZE D. ALTER TABLESPACE DATAFILE RESIZE 13. The database is using automatic memory management. The standard block size for the database is 8KB. You need to create a tablespace with a block size of 16KB. Which initial- ization parameter should be set? A. DB_8K_CACHE_SIZE B. DB_16K_CACHE_SIZE C. DB_CACHE_SIZE D. None of the above 14. Which data dictionary view can be queried to obtain information about the files that belong to locally managed temporary tablespaces? A. DBA_DATA_FILES B. DBA_TABLESPACES C. DBA_TEMP_FILES D. DBA_LOCAL_FILES 15. How would you drop a tablespace if the tablespace were not empty? A. Rename all the objects in the tablespace, and then drop the tablespace. B. Remove the data files belonging to the tablespace from the disk. C. Use ALTER DATABASE DROP CASCADE. D. Use DROP TABLESPACE INCLUDING CONTENTS. 16. Which command is used to enable the autoextensible feature for a file if the file is already part of a tablespace? A. ALTER DATABASE. B. ALTER TABLESPACE. C. ALTER DATA FILE. D. You cannot change the autoextensible feature once the data file created. 7. Review Questions 587 17. Which statement is true regarding the SYSTEM tablespace? A. It can be made read-only. B. It can be offline. C. Data files can be renamed. D. Data files cannot be resized. 18. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. Which statements are true? (Choose all that apply.) ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE; A. No new rows can be added to the BONUS table. B. Existing rows of the BONUS table are validated before disabling the constraint. C. Rows can be modified, but the primary key columns cannot change. D. The unique index created when defining the constraint is dropped. 19. Which clause in the ALTER TABLE command is used to reorganize a table? A. REORGANIZE B. REBUILD C. RELOCATE D. MOVE 20. Which keyword should be used in the CREATE INDEX command to create a function-based index? A. CREATE FUNCTION INDEX B. CREATE INDEX ORGANIZATION INDEX C. CREATE INDEX FUNCTION BASED D. None of the above 8. 588 Chapter 10 N Allocating Database Storage and Creating Schema Objects Answers to Review Questions 1. C. Bigfile tablespaces can have only a single data file. The traditional or smallfile tablespace can have many data files. 2. D. Segment space management refers to free-space management, with automatic segment space management using bitmaps instead of FREELISTS, PCTFREE, and PCTUSED. 3. B. Redo information is not stored in a segment; it is stored in the redo logs. Undo segments are stored in the undo tablespace, temporary segments are in the temporary tablespace, and permanent segments go into all the other tablespaces. 4. B. Objects in an Oracle 11g database can always include letters, numbers, and the charac- ters$, _, and # (dollar sign, underscore, and number sign). Names can include any other character only if they are enclosed in double quotes. The character dollar sign is not a spe- cial metacharacter in an Oracle 11g database. 5. E. You can rename both a constraint and an index to the same name as a table—they are in separate namespaces. Columns can be added, and owner is a valid column name. If the check constraint condition evaluates to FALSE, the data value will not be allowed; if the condition evaluates to either TRUE or NULL, the value is allowed. 6. B. Constraints can be created as deferrable and initially deferred, but deferred constraint checking is not the default. 7. C. An extent is composed of two or more data blocks; a segment is composed of one or more extents, and a data file houses all these. 8. B. Although BINARY_FLOAT and BINARY_DOUBLE are valid datatypes, BINARY is not. 9. A. The options for temporary tables are either ON COMMIT DELETE ROWS, which causes the table to flush at the end of each transaction, or ON COMMIT PRESERVE ROWS, which causes the table to flush at the end of each session. 10. B, D, E. When a tablespace is read-only, DML operations and operations that affect data in the table are not allowed. Truncate and drop operations are allowed, and you can also rename the table using the RENAME statement or the ALTER TABLE statement. 11. C, A. To make a tablespace read-only, all the data files belonging to the tablespace must be online and available. So, bring the tablespace online and then make it read-only. 12. A, B. You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file. Option A does not have a file name specified; it uses the OMF feature to generate filename. 13. B. DB_CACHE_SIZE doesn’t need to be set for the standard block size since automatic mem- ory management is used. If you set DB_CACHE_SIZE, its value will be used as the minimum. DB_16K_CACHE_SIZE should be set for the nonstandard block size. You must not set the DB_8K_CACHE_SIZE parameter because the standard block size is 8KB.
9. Answers to Review Questions 589 14. C. Locally managed temporary tablespaces are created using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view. The EXTENT_MANAGEMENT column of the DBA_TABLESPACES view shows the type of the tablespace. The data files belonging to locally managed permanent tablespaces and dictionary-managed (permanent and temporary) tablespaces can be queried from DBA_DATA_FILES. Locally managed temporary tablespaces reduce contention on the data dictionary tables. 15. D. The INCLUDING CONTENTS clause is used to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace if the files are not Oracle man- aged; you need to do it manually using an OS command. Oracle updates only the control file. To remove the files, you can include the INCLUDING CONTENTS AND DATAFILES clause. 16. A. You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but all other file-management operations are done through the ALTER DATABASE command. To enable autoextension, use ALTER DATABASE DATAFILE AUTOEXTEND ON NEXT MAXSIZE . 17. C. The data files belonging to the SYSTEM tablespace can be renamed when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement. 18. A, D. DISABLE VALIDATE disables the constraint and drops the index but keeps the con- straint valid. No DML operations are allowed on the table. 19. D. The MOVE clause is used to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move. 20. D. No keyword needs to be specified to create a function-based index other than to specify the function itself. To permit the Oracle optimizer to use a function-based index, you must set the parameter QUERY_REWRITE_ENABLED to TRUE and QUERY_REWRITE_INTEGRITY to TRUSTED.
10. Chapter Understanding 11 Network Architecture OrAcle DAtAbAse 11g: ADmiNistrAtiON i exAm Objectives cOvereD iN this chApter: Configuring the Oracle Network Environment ÛÛ NÛ Configure and Manage the Oracle Network NÛ Using the Oracle Shared Server architecture
11. Networks have evolved from simple terminal-based systems to complex multi-tiered systems. Today’s networks can comprise many computers on multiple operating systems using a wide variety of protocols and communicating across wide geographic areas. Although networks have become increasingly complex, they also have become easier to use and manage. For instance, we all take advantage of the Internet without knowing or caring about the com- ponents that make this communication possible, because the complexity of this huge net- work is completely hidden from us. The experienced Oracle database administrator has seen this maturation process in the Oracle network architecture as well. From the first version of SQL*Net to the latest releases of Oracle Net, Oracle has evolved its network strategy and infrastructure to meet the demands of the rapidly changing landscape of network communications. This chapter highlights the areas you need to consider when implementing an Oracle network strategy and when managing an Oracle 11g network. I’ll also discuss the most common network configurations. The chapter introduces the features of Oracle Net—the connectivity-management software that is the backbone of the Oracle network architecture. I’ll explain how to configure the main client- and server-side components of Oracle Net, and I’ll discuss the tools you have at your disposal to perform these tasks. As the number of users connecting to Oracle Databases in the enterprise grows, the system requirements of the servers increase—particularly the memory and process require- ments. When a system starts to encounter these capacity issues, you need to know which alternatives are available within the Oracle environment that can address the problem. One configuration alternative that may help to overcome this capacity problem is Oracle Shared Server. This chapter also discusses Oracle Shared Server and its benefits. You will learn about the client connection process and how Oracle Shared Server processes user requests. You will also learn how to configure Oracle Shared Server. Introducing Network Configurations You can select from three basic types of network configurations when designing an Oracle infrastructure: NÛ Single-tier NÛ Two-tier NÛ n-tier
12. Introducing Network Configurations 593 Single-tier is the simplest type. It has been around for years and is characterized by the use of terminals for serial connections to the Oracle server. The two-tier configuration is also referred to as the client/server architecture, and more recently the n-tier architecture has been introduced. Let’s take a look at each of these configuration alternatives. Single-Tier Architecture Single-tier architecture was the standard for many years before the birth of the personal computer. Applications using single-tier architecture are sometimes referred to as green- screen applications because most of the terminals that used them, such as the IBM 3270, had green screens. Single-tier architecture is commonly associated with mainframe-type applications. This architecture is still in use today for many mission-critical applications, such as order processing and fulfillment and inventory control, because it is the simplest architec- ture to configure and administer. Because the terminals are directly connected to the host computer, the complexities of network protocols and multiple operating systems don’t exist. When single-tier architecture is used—for example, in mainframes—users interact with the database using terminals, which are nongraphical, character-based devices. In this type of architecture, client terminals are directly connected to larger server systems such as mainframes. All the intelligence exists on the mainframe, and all the processing takes place there. Simple serial connections also exist on the mainframe. Although no complex network architecture is necessary, a single-tier architecture is somewhat limiting in terms of scalability and flexibility (see Figure 11.1). F i g U r e 11 .1 Single-tier architecture Direct Connection Dumb Terminal Mainframe Two-Tier Architecture Two-tier architecture gained popularity with the introduction of the personal computer and is commonly referred to as client/server computing. In a two-tier environment, clients connect to servers over a network using a network protocol, which is the agreed-upon method for the client to communicate with the server. Transmission Control Protocol/Internet Protocol (TCP/IP) is a popular network protocol and has become the de facto standard of network computing. Whether you choose TCP/IP or some other network protocol, both the client and the server must be able to understand it. Figure 11.2 shows an example of two- tier architecture.