OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P10

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

Thêm vào BST

Báo xấu

118
lượt xem 27
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P10: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P10

Review Questions 381 5. You create a view based on the EMPLOYEES table using the following SQL. CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES; You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view? A. Nothing. Since the view definition is selecting all columns, the new column will appear in the view automatically. B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE. C. Re-create the view using CREATE OR REPLACE VIEW. D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN. 6. Which is a valid status of a constraint created on a view? A. DISABLE VALIDATE B. DISABLE NOVALIDATE C. ENABLE NOVALIDATE D. All of the above 7. The SALARY column of the EMPLOYEE table is defined as NUMBER(8,2), and the COMMIS- SION_PCT column is defined as NUMBER(2,2). A view is created with the following code: CREATE VIEW EMP_COMM AS SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) Commission FROM EMPLOYEES; What is the datatype of the COMMISSION column in the view? A. NUMBER (8,2) B. NUMBER (10,2) C. NUMBER D. FLOAT 8. Which clause in the SELECT statement is not supported in a view definition subquery? A. GROUP BY B. HAVING C. CUBE D. FOR UPDATE OF E. ORDER BY
382 Chapter 7 N Creating Schema Objects 9. The EMPLOYEE table has the following columns: EMP_ID NUMBER (4) EMP_NAME VARCHAR2 (30) SALARY NUMBER (6,2) DEPT_ID VARCHAR2 (2) Which query will show the top five highest-paid employees? A. SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY ASC) WHERE ROWNUM
Review Questions 383 10. The EMPLOYEE table has the following columns: EMP_ID NUMBER (4) PRIMARY KEY EMP_NAME VARCHAR2 (30) SALARY NUMBER (6,2) DEPT_ID VARCHAR2 (2) A view is defined using the following SQL: CREATE VIEW EMP_IN_DEPT10 AS SELECT * FROM EMPLOYEE WHERE DEPT_ID = ‘HR’; Which INSERT statement will succeed through the view? A. INSERT INTO EMP_IN_DEPT10 VALUES (1000, ‘JOHN’,1500,’HR’); B. INSERT INTO EMP_IN_DEPT10 VALUES (1001, NULL,1700,’AM’); C. INSERT INTO EMP_IN_DEPT10 VALUES (1002, ‘BILL’,2500,’AC’); D. All of the above 11. To be able to modify a join view, the view definition should not contain which of the fol- lowing in the top-level query? (Choose all that apply.) A. A DISTINCT operator B. An ORDER BY clause C. Aggregate functions such as SUM, AVG, and COUNT D. A WHERE clause E. A GROUP BY clause F. A ROWNUM pseudocolumn 12. Which statement will create a sequence that starts with 0 and gets smaller one whole num- ber at a time? A. create sequence desc_seq start with 0 increment by -1 maxvalue 1; B. create sequence desc_seq increment by -1; C. create sequence desc_seq start with 0 increment by -1; D. Sequences can only increase. 13. Which statement is most correct in describing what happens to a synonym when the under- lying object is dropped? A. The synonym’s status is changed to INVALID. B. You can’t drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement. C. The synonym is automatically dropped with the underlying object. D. Nothing happens to the synonym.
384 Chapter 7 N Creating Schema Objects 14. There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the fol- lowing statements will remove this public synonym from the database? A. drop table system.plan_table; B. drop synonym plan_table; C. drop table system.plan_table cascade; D. drop public synonym plan_table; 15. A developer reports that she is receiving the following error: SELECT key_seq.currval FROM dual; ERROR at line 1: ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined Which of the following statements does the developer need to run to fix this condition? A. create sequence key_seq; B. create synonym key_seq; C. select key_seq.nextval from dual; D. grant create sequence to public; 16. Bitmapped indexes are best suited to which type of environment? A. High-cardinality columns B. Online transaction processing (OLTP) applications C. Full-table scan access D. Low- to medium-cardinality columns 17. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.) A. SELECT B. FROM C. WHERE D. HAVING
Review Questions 385 18. You need to generate artificial keys for each row inserted into the PRODUCTS table. You want the first row to use a sequence value of 1000, and you want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements? A. CREATE SEQUENCE product_key2 START WITH 1000 INCREMENT BY 1 NOCACHE; B. CREATE SEQUENCE product_key2 START WITH 1000 NOCACHE; C. CREATE SEQUENCE product_key2 START WITH 1000 NEXTVAL 1 NOCACHE; D. Options A and B meet the requirements. E. None of the above statements meet all the requirements. 19. Which statement will display the last number generated from the EMP_SEQ sequence? A. select emp_seq.curr_val from dual; B. select emp_seq.currval from dual; C. select emp_seq.lastval from dual; D. select last_number from all_sequences where sequence_name =’EMP_SEQ’; E. You cannot get the last sequence number generated. 20. Which statement will create a sequence that will rotate through 100 values in a round-robin manner? A. create sequence roundrobin cycle maxvalue 100; B. create sequence roundrobin cycle to 100; C. create sequence max_value 100 roundrobin cycle; D. create rotating sequence roundrobin min 1 max 100;
386 Chapter 7 N Creating Schema Objects Answers to Review Questions 1. B. A view is dropped using the DROP VIEW view_name; command. 2. A. You can perform an INSERT, UPDATE, or DELETE operation on the columns involving only one base table at a time. There are also some restrictions on the DML operations you perform on a join view. 3. D. Since the view definition includes a DISTINCT clause, only queries are allowed on the view. 4. B, E. The OR REPLACE option in the CREATE VIEW statement is used to modify the defini- tion of the view. The FORCE option can be used to create the view with errors. The ALTER VIEW statement is used to compile a view or to add or modify constraints on the view. 5. C. When you modify the base table, the view becomes invalid. Oracle will recompile the view the first time it is accessed. Recompiling the view will make it valid, but the new col- umn will not be available in the view. This is because when you create the view using *, Oracle expands the column names and stores the column names in the dictionary. 6. B. Since the constraints on the view are not enforced by Oracle, the only valid status of a constraint can be DISABLE NOVALIDATE. You must specify this status when creating con- straints on a view. 7. C. When numeric operations are performed using numeric datatypes in the view definition, the resulting column will be a floating datatype, which is NUMBER without any precision or scale. 8. D. The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed. 9. C. You can find the top five salaries using an inline view with the ORDER BY clause. The Oracle 11g Optimizer understands the top-n rows query. Option B would have been correct if you had ROWNUM
Answers to Review Questions 387 14. D. To remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database but will not drop any synonyms on the table. The synonym will become invalid. 15. C. A sequence is not yet initialized if NEXTVAL has not yet been selected from it within the current session. It has nothing to do with creating a sequence, creating a synonym, or grant- ing privileges. 16. D. Bitmapped indexes are not suited for high-cardinality columns (those with highly selec- tive data). OLTP applications tend to need row-level locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited to multiple combinations of low- to medium-cardinality columns. 17. A, C. The obvious answer is C, but an index also can be used for the SELECT clause. If an index contains all the columns needed to satisfy the query, the table does not need to be accessed. 18. D. Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement. 19. B. Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself, using the pseudocolumn CURRVAL. 20. A. The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle to. The name roundrobin is there to confuse to you.
Oracle Part Database 11g: Administration I II
Chapter Introducing Oracle 8 Database 11g Components and Architecture OrACle DAtAbAse 11g: ADmInIstrAtIOn I exAm ObjeCtIves COvereD In thIs ChApter: Exploring the Oracle Database Architecture ÛÛ NÛ Explain the Memory Structures NÛ Describe the Process Structures NÛ Overview of Storage Structures Preparing the Database Environment ÛÛ NÛ Identify the tools for Administering an Oracle Database NÛ Plan an Oracle Database installation NÛ Install the Oracle software by using Oracle Universal Installer (OUI)
With this chapter, you’ll start learning Oracle Database 11g (Oracle 11g) database administration. This chapter and the remaining chapters of the book will discuss the objectives for the Oracle 11g Administration I OCA certification exam. With the release of Oracle 11g, Oracle Corporation has delivered a powerful and feature- rich database that can meet the performance, availability, recoverability, application-testing, and security requirements of any mission-critical application. As the Oracle DBA, you are responsible for managing and maintaining the Oracle Database 11g throughout its life cycle, from initial installation, creation, and configuration to final deployment. Perform- ing these tasks requires a solid understanding of Oracle’s product offerings so that you can apply the proper tools and features to the application. You must also use relational database concepts to design, implement, and maintain the tables that store the application data. At the heart of these activities is the need for a thorough understanding of the Oracle architecture and the tools and techniques used to monitor and manage the components of this architecture. I will begin the chapter by reviewing the Oracle Database basics. You will learn what con- stitutes the Oracle Database 11g—an overview of the memory structures, the processes that manage the database, and how data is stored in the database. I will also discuss the tools used to administer the Oracle Database 11g and how to install the Oracle 11g software. Exam objectives are subject to change at any time without prior notice and at Oracle’s sole discretion. Please visit Oracle’s Training and Certifica- tion website at http://education.oracle.com/pls/web_prod-plq-dad/ db_pages.getpage?page_id=41&p_exam_id=1Z0_052 for the most current exam-objectives listing. Oracle Database Fundamentals Databases store data. The data itself is composed of related logical units of information. The database management system (DBMS) facilitates the storage, modification, and retrieval of this data. Some early database technologies used flat files or hierarchical file structures to store application data. Others used networks of connections between sets of data to store and locate information. The early DBMS architecture mixed the physical manipulation of data with its logical manipulation. When the location of data changed, the
Oracle Database Fundamentals 393 application referencing the data had to be updated. Relational databases brought a revolu- tionary change to this architecture. Relational DBMS introduced data independence, which separated the physical model of the data from its logical model. Oracle is a relational DBMS. All releases of Oracle’s database products have used a relational DBMS model to store data in the database. This relational model is based on the groundbreaking work of Dr. Edgar Codd, which was first published in 1970 in his paper “A Relational Model of Data for Large Shared Data Banks.” IBM Corporation, which was then an early adopter of Dr. Codd’s model, helped develop the computer language that is used to access all relational databases today—Structured Query Language (SQL). The great thing about SQL is that you can use it to easily interact with relational databases without having to write complex computer programs and without needing to know where or how the data is physically stored on disk. You saw several SQL statements in the previous chapters. Relational Databases The concept of a relational database management system (RDBMS) is that the data con- sists of a set of relational objects. The basic storage of data in a database is a table. The relations are implemented in tables, where data is stored in rows and columns. Figure 8.1 shows such a relationship. F I g u r e 8 .1 Relational tables EMP (Employee Table) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC -8 0800 20 7499 ALLEN SALESMAN 7698 20-FEB-8 11600 300 30 7521 WARD SALESMAN 7698 22-FEB-8 11250 500 30 7566 JONES MANAGER 7839 02-APR-8 12975 20 7654 MARTIN SALESMAN 7698 28-SEP-8 11250 1400 30 7698 BLAKE MANAGER 7839 07-MAY-8 12850 30 7844 URNER SALESMAN 7698 08-SEP-8 11500 30 Primary Key Foreign Key Column Column DEPT (Department Table) DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Primary Key Column
394 Chapter 8 N Introducing Oracle Database 11g Components and Architecture The DEPT table in the lower part of the figure stores information about departments in the company. Each department is identified by the department ID. Along with the ID, the name and location of the department are also stored in the table. The EMP table stores information about the employees in the company. Each employee is identified by a unique employee ID. This table includes employee information such as hire date, salary, manager, and so on. The DEPTNO column in both tables then provides a relationship between the tables. A department may have many employees, but an employee can work for only one department. Since the user accessing this data doesn’t need to know how or where the row is stored in the database, there must be a way to uniquely identify the rows in the tables. In our example, the department is uniquely identified by department number, and an employee is identified by an employee ID. The column (or set of columns) that uniquely identifies a row is known as the primary key. According to relational theory, each table in a relational data- base must have a primary key. When relating tables together, the primary key of one table is placed in another table. For example, the primary key of the DEPT table is a column in the EMP table. In RDBMS terminology, this is known as a foreign key. A foreign key states that the data value in the column exists in another table and should continue to exist in the other table to keep the relationship between tables. The table where the column is a primary key is known as the parent table, and the table where the foreign key column exists is known as the child table. Oracle enforces the parent-child relationship between tables using constraints. Oracle Database 11g Objects Every RDBMS supports a variety of database objects. Oracle 11g supports the entire set of database objects required for a relational database, such as tables, views, constraints, and so on. It also supports a wide range of objects specific to the Oracle Database 11g, such as packages, sequences, materialized views, and so on. Table 8.1 lists the objects available in Oracle 11g. I also discussed many of these in Chapter 6, “Creating Tables and Constraints,” and Chapter 7, “Creating Schema Objects.” tA b l e 8 .1 Oracle Database 11g Objects Object Type Description Table A table is the basic form of data storage. A table has columns and stores rows of data. View A view is a stored query. No data-storage space is occupied for view data. Index An index is an optional structure that is useful for locating data faster. Materialized view Materialized views are used to summarize and store data. They are similar to views but take up storage space to store data.
Oracle Database Fundamentals 395 tA b l e 8 .1 Oracle Database 11g Objects (continued) Object Type Description Index-organized An index-organized table use a primary key and stores the table data table in the index segment. Cluster A cluster is a group of tables that share the same storage blocks. Constraint A constraint is a stored rule to enforce data integrity. Sequence A sequence provides a mechanism for the continuous generation of numbers. Synonym A synonym is an alias for a database schema object. Triggers A trigger is a PL/SQL program unit that gets executed when an event occurs. Stored function Stored functions are PL/SQL programs that can be used to create user-defined functions to return a value. Stored procedure Stored procedures are PL/SQL programs to define a business process. Package A package is a collection of procedures, functions, and other program constructs. Java Stored Java procedures can be created in Oracle to define business processes. Database link Database links are used to communicate between databases to share data. You use SQL to create database objects and to interact with application data. In the next section, I will discuss the tools available to access and administer Oracle 11g database. Interacting with Oracle 11g SQL is the language used to interact with the Oracle 11g database. Many tools are available for the DBA to administer an Oracle 11g database. The common tools are as follows: NÛ SQL*Plus, which is a command-line interface utility NÛ SQL Developer, a GUI tool NÛ Oracle Enterprise Manager Database Control, a GUI tool
396 Chapter 8 N Introducing Oracle Database 11g Components and Architecture Using SQL*Plus and SQL Developer, you interact directly with the Oracle 11g database using SQL statements and a superset of commands such as STARTUP, SHUTDOWN, and so on. Using Enterprise Manager, you interact indirectly with the Oracle 11g database. SQL*Plus SQL*Plus is the primary tool for an Oracle DBA to administer the database using SQL commands. Before you can run SQL statements, you must connect to the Oracle 11g data- base. You can start SQL*Plus from a Windows command prompt using the SQLPLUS.EXE executable or using the $ORACLE_HOME/bin/sqlplus executable on the Unix/Linux plat- form. Figure 8.2 shows connecting to SQL*Plus from a Linux workstation. FIgure 8.2 SQL*Plus login in Linux To get an overview of SQL*Plus and how to connect to the database using SQL*Plus, please refer to Chapter 1, “Introducing SQL.” SQL Developer SQL Developer is a free GUI database-development tool. With SQL Developer, you can create and view the database objects, make changes to the objects, run SQL statements, run PL/SQL programs, create and edit PL/SQL programs, and perform PL/SQL debugging.
Oracle Database Fundamentals 397 SQL Developer also includes a migration utility to migrate Microsoft Access and Microsoft SQL Server databases to Oracle 11g. Figure 8.3 shows the object browser screen of SQL Developer. FIgure 8.3 SQL Developer screen You can download and learn more about SQL Developer on the OTN website (http://www.oracle.com/technology/products/database/sql_ developer/index.html). Enterprise Manager Database Control Oracle Enterprise Manager Database Control is a web-based database management tool that is bundled with the Oracle 11g database. This is a graphical tool specifically designed to administer the Oracle database. The Enterprise Manager Database Control is used to man- age a single database, whereas the Enterprise Manager Grid Control can manage multiple databases and other services and applications, such as OAS, and even non-Oracle applica- tions at the same time. Figure 8.4 shows the Enterprise Manager Database Control home screen, where an overview of the database is shown.
398 Chapter 8 N Introducing Oracle Database 11g Components and Architecture FIgure 8.4 Enterprise Manager home screen For all the database-administration examples in this chapter, you may use either SQL*Plus to perform the SQL command line or use the GUI tool Enterprise Manager (EM) Database Control. Before learning to administer the Oracle 11g database, let’s start with the basics. In the next section, you’ll learn about Oracle 11g architecture. Oracle 11g Architecture Each database-administration and -development tool described previously allows a user to interact with the database. Using these tools requires that user accounts be created in the database and that connectivity to the database be in place across the network. Users must also have adequate storage capacity for the data they insert, and they need recovery mechanisms for restoring the transactions they are performing in the event of a hardware
Oracle 11g Architecture 399 failure. As the DBA, you take care of each of these tasks, as well as others, which include the following: NÛ Selecting the server hardware on which the database software will run NÛ Installing and configuring the Oracle 11g software on the server hardware NÛ Creating the Oracle 11g database NÛ Creating and managing the tables and other objects used to manage the application data NÛ Creating and managing database users NÛ Establishing reliable backup and recovery processes for the database NÛ Monitoring and tuning database performance The remainder of this book is dedicated to helping you understand how to perform these and other important Oracle database-administration tasks. But first, to succeed as an Oracle DBA, you need to completely understand Oracle’s underlying architecture and its mechanisms. Understanding the relationship between Oracle’s memory structures, background processes, and I/O activities is critical before learning how to manage these areas. The Oracle server architecture can be described in three categories: NÛ User-related processes NÛ Logical memory structures that are collectively called an Oracle instance NÛ Physical file structures that are collectively called a database You will also see how the physical structures map to the logical structures of the data- base you are familiar with, such as tables and indexes. Database is a confusing term that is often used to represent different things on different platforms; the only commonality is that it is something related to storing data. In Oracle, however, the term database represents the physical files that store data. An instance is com- posed of the memory structures and background processes. Each database should have at least one instance associated with it. It is possible for multiple instances to access a single database; such a configuration is known as Real Application Clusters (RAC). In this book, however, you’ll concentrate only on single-instance databases because RAC is not part of the certification exam. Figure 8.5 shows all the parts of an Oracle instance and database. Although the architecture in Figure 8.5 may at first seem complex, each of these archi- tecture components is described in more detail in the following sections, beginning with the user-related processes, and is actually fairly simple. This figure is an important piece of fundamental information when learning about the Oracle 11g architecture. The key database components are memory structures, process structures, and storage structures. Process and memory structures together are called an instance; the storage structure is called a database. Taken together, the instance and the database are called an Oracle server.
400 Chapter 8 N Introducing Oracle Database 11g Components and Architecture FIgure 8.5 The Oracle 11g architecture Server PGA Server PGA Process 1 Process 2 Background Instance SGA Processes DBWn CKPT LGWR Archive Log Database Files Physical Password Database Data Control Redo Log Files Structure Files Files Files Parameter Files Datafile 1 Datafile 2 Datafile 3 Datafile 4 Datafile 5 Database Logical Database SYSTEM SYSAUX Tablespace Tablespace Structure Tablespace Tablespace 1 2 User Processes At the user level, two types of processes allow a user to interact with the instance and, ulti- mately, with the database: the user process and the server process. Whenever a user runs an application, such as a human-resources or order-taking applica- tion, Oracle starts a user process to support the user’s connection to the instance. Depending on the technical architecture of the application, the user process exists either on the user’s own computer or on the middle-tier application server. The user process then initiates a con- nection to the instance. Oracle calls the process of initiating and maintaining communication between the user process and the instance a connection. Once the connection is made, the user establishes a session in the instance. After establishing a session, each user starts a server process on the host server itself. It is this server process that is responsible for performing the tasks that actually allow the user to interact with the database.