The statement will work without error. None of these statements could use the index. NULL Which of the following statements are true? Choose two. Primary key constraints allow NULL values in the columns. Unique key constraints allow NULL values in the columns. Primary key constraints do not allow NULL values in the columns. A non-unique index cannot be used to enforce primary key constraints.
A user from Dubai is connected to a session in the data- base located on a server in Dallas. What will be the result of his query? None of the above At a minimum, how many join conditions should there be to avoid a Cartesian join if there are three tables in the FROM clause?
There is no minimum. Why does the following statement fail? The table should have more than one column in its definition. NAME is a reserved word, which cannot be used as a column name. The table name is invalid. Column length cannot exceed 30 characters. Which two statements are true about NULL values? In an ascending order sort, NULL values appear at the bottom of the result set. Which of the fol- lowing functions include the NULL rows in its result?
Which of the following statements will achieve the desired results? A, B, and C will achieve the desired results. None of these statements will achieve the desired results.
Which option is not available in Oracle when modifying tables? Add new columns B. Rename existing column C. Drop existing column D. Choose all that apply. Years B. Quarters C. Months D. Days E. Hours F. Minutes G. Seconds H. Fractional seconds Which components are not part of the easy connect connection string? Line 2 B. Line 3 C. Line 4 D. There will be no error. Choose the best answer. All of the above What will be result of trunc How many indexes will be created automatically when this table is created?
Which line of the following code has an error? FROM emp C. There is no error in this statement. How do you represent the following business rule in an ER diagram? Single solid line. Single line that is solid at one end and dotted at another end. Single solid line with a crowfoot at one end. The business rule cannot be represented in the ER diagram. What order does Oracle use in resolving a table or view referenced in a SQL statement?
All locks created by DML statements are released in the session. All savepoints created are erased in the session. All undo information written from the DML statements are erased.
Which two operators are used to add more joining conditions in a multiple-table query? NOT B. AND D. Comma , What is wrong with the following SQL?
Aggregate functions cannot be nested. Which types of constraints can be created on a view? Primary key, foreign key, unique key C. No constraints can be created on a view. Which two declarations define the maximum length of a CHAR datatype column in bytes? CHAR 20 B. SJC C. What happens when you issue the following command? Choose all correct answers. The indexes on the table are dropped. You cannot truncate a table if triggers are defined on the table.
Which two statements will drop the primary key defined on table EMP. Which database version cannot be upgraded directly to Oracle Database 12c? All of the above 2. Which of the following is not considered part of an Oracle database?
Data files B. Redo logs C. The pfile and spfile D. Control files 4. Schema B. Table C. Row D. Block 5. Which query can be used to find and categorize all chained jobs only chained jobs , and includes any remote steps as applied to those chained jobs, as executed from the current database? Choose the best answers, more than one if appropriate. None of the above 7. MMNL B. QMN1 C. MMON D. MMAN 8. Which other statements are the minimum required to enable a resumable session?
Which utility is used to install Oracle Database 12c? DBCA B. OUI C. Oracle Database 12c is installed using the URL to www. You have just made changes to the listener. Which of the following commands or combinations of commands would you use to put the changes into effect with the least amount of client disruption?
An error will not occur. The job will stop running. The job will be dropped altogether. All of the above. None of the above. Where does Oracle Database 12c record all the changes made to the database that can be used for recovery operations? Control files B. Redo log files C.
Alert log file D. Parameter file What is accomplished when you issue the following statement? John is assigned all the roles created in the database. Any existing roles remain the same, but any future roles created will be enabled. Which activity is a must do before upgrading a database to 12c using the manual upgrade method?
Run preupgrd. What would you do to reduce the time required to start the instance after a data- base crash? Multiplex the redo log files B. Increase the size of the redo log files C. All of the above E. When you are configuring Oracle Shared Server, which initialization parameter would you likely need to modify? Which options are available in DBCA to configure recovery-related operations?
Data Guard B. Standby Database C. Fast Recovery Area D. Archiving When you started the Oracle Database 12c database, you got an ORA cannot identify data file … error. Which of the following commands is most likely to generate an error message? Startup will fail, because you have not completed the instance recovery. Oracle automatically performs recovery. All committed changes are written to data files. During instance recovery, you have the option to selectively commit uncommitted transactions.
After the database starts, you have to manually clean out uncommitted transactions from the transaction table. Where does the following procedure allow jobs to be created? On a remote server as a database destination B. On a remote server using a hostname and IP address C.
On a local database server only D. On a local server using a hostname and an IP address E. Which of the following statements is not always true? Every database should have at least two tablespaces. Every database should have at least two data files. Every database should have at least three multiplexed redo logs. Every database should have at least three control files. How can you prevent someone from using an all-alphabet password?
There is no mechanism that lets you prevent an all-alphabet password. Which metadata view can be used to find Oracle Scheduler priorities? Which component of the SGA has the dictionary cache? Buffer cache B. Library cache C. Program global area E. Large pool F. Result cache Which of the following advisors is used to determine if the database read-consistency mechanisms are properly configured?
Undo Management Advisor B. Memory Advisor Which storage parameter is used to make sure that each extent is a multiple of the value specified? What is are the name of the audit trail view s where audit records from Unified Auditing is are stored, when database auditing, RMAN, and Data Pump auditing are enabled? Undo data in an undo tablespace is not used for which of the following purposes?
Providing users with read-consistent queries B. Rolling forward after an instance failure C. Flashback queries D. Recovering from a failed transaction E. Log file B. Parfile C. Bad file D. Data file Which of the following is false about shared servers? Shared servers can process requests from many users.
Shared servers receive their requests directly from dispatchers. Shared servers place completed requests on a dispatcher response queue. Identify the statement that is not true about checkpoints. Instance recovery is complete when the data from the last checkpoint up to the latest SCN in the control file has been written to the data files. A checkpoint keeps track of what has already been written to the data files. The redo log group writes must occur before a commit complete is returned to the user.
The distance between the checkpoint position in the redo log file and the end of the redo log group can never be more than 90 percent of the size of the largest redo log group.
Which parameter is used to enable the Automatic Memory Management feature of the Oracle database? SKIP B. Two tablespaces are critical to the database. The loss of a data file in these tablespaces requires an instance shutdown to recover. Which are these tablespaces? TEMP B. UNDO D. Which initialization parameter determines the location of the alert log file?
Which statement regarding a global temporary table and redo generation is true? Global temporary tables generate the same amount of redo as persistent tables. Global temporary tables generate redo from undo when a DML operation is per- formed on them. All of the above are true. Which database management tools are automatically installed and configured when Oracle Database 12c database is configured?
SQL Developer In Oracle Database 12c, which script is recommended to run for upgrading a database from Using listener. Using Oracle Net Manager configuration. Depends on the application. Which product do you install to configure an Oracle Automatic Storage Management instance? Oracle Database 12c database B.
Oracle Database 12c Grid Infrastructure C. In the arithmetic operators, unary operators are evaluated first, then multiplication and division, and finally addition and subtraction.
The expression is evaluated from left to right. See Chapter 2 for more information. Except for option C, all other SQL statements are not valid syntactically. The MERGE state- ment performs kind of an upsert—it inserts if the row does not exist, and updates if the row exists. For more information, read Chapter 6. See Chapter 3 to learn more.
Read Chapter 4 to learn more about group functions. See Chapter 6 for more information. For more information, see Chapter 4. The BFILE datatype stores only the locator to an external file in the database; the actual data is stored as an operating sys- tem file.
Read Chapter 7 for more information. See Chapter 5 for more information about subqueries. The join condition can be specified by using the ON clause to specify the columns explicitly or using the USING clause to specify columns with common column names. Options B and D would result in errors.
See Chapter 5 for more information. The index contains all the information needed to satisfy the query in option A, and a full-index scan would be faster than a full-table scan. Read Chapter 7 to learn more about using an index. A rollback will never stuff values back into a sequence. Primary key and unique key constraints can be enforced using non-unique indexes. Unique constraints allow NULL values in the columns, but primary keys do not. See Chapter 7 for more information. See Chapter 3 for more information.
For more infroma- tion, see Chapter 6. There should be at least n-1 join conditions when joining n tables to avoid a Cartesian join.
No other characters are allowed in the table name. You can have letters and numbers in the table name. Since NULLs are sorted higher, they appear at the bottom of the result set in an ascending order sort. No other aggregate function takes NULL into consideration.
See Chapter 4 for more information. Read Chapter 3 for more information. To learn more about managing tables, read chapter 7. Read Chapter 2 for more information. D, E, F, G. Answers to Assessment Test lvii For databases where the sid is the same as the database unique name, the service name is the same as the sid; thus you can say option C is also part of the easy connect string.
However, in reality it is the service name. Easy connect only supports TCP protocol. See Chapter 1 for more information. In the example, there are two rows with NULL values. Since a self-join is getting data from the same table, you must include alias names and qualify column names. Oracle creates unique indexes for each unique key and primary key defined in the table.
Although there is no error in this statement, the statement will not return the desired result. The solid line represents that the order must belong to a customer, and the crow- foot represents that a customer can have more than one order. Private synonyms override public synonyms, and tables or views owned by the user always resolve first.
NOT is a negation operator, and a comma is used to separate column names and table names. For more information, see Chapter 5. You can create primary key, foreign key, and unique key constraints on a view.
The constraints on views are not enforced by Oracle. To enforce a constraint, it must be defined on a table. So an AND operator should be used. In the second part, you have two options: the salary can be either more than or less than , so the second part should be enclosed in parentheses; use an OR operator. Option B is similar to option C except for the parentheses, but the difference changes the meaning completely. Option B would select the employees who were hired before Jan or have a salary above 5, or have a salary below 1, None of the indexes, constraints, or triggers on the table are dropped or disabled.
If there are valid foreign key constraints defined to this table, you must disable all of them before truncating the table. Read Chapter 6 for more information. Since there can be only one primary key per table, the syntax in option A works.
Exam 1Z Installation and Administration 1. Database Upgrade Assistant and a manual upgrade both support an upgrade from For See Chapter 17 for more information.
To rename or relocate a data file in Oracle Database 12c, you need to use only the statement specified in option B. To rename a data file in prec databases, you need to take the tablespace offline so that Oracle does not try to update the data file while you are renaming. To rename the file in the data- base, the new file should exist. Bring the tablespace online for normal database opera- tion. For more information, read Chapter Although pfiles and spfiles are physical files used to configure the Oracle instance, they are not considered part of the database.
For more information, see Chapter 8. Answers to Assessment Test lix 4. The highest level at which a user can request a lock is the table level; the only other lock level available to a user is a row-level lock. Users cannot lock at the block or schema level. Read Chapter 11 for more information. Read Chapter 13 for more information. B and C are both the best answers. They are identical because queries in Oracle are case-insensitive. QMN1 is the process that monitors Oracle advanced queuing fea- tures.
See Chapter 14 for more information. The resumable session must be enabled using option B. Option C disables the resumable session. Option D is invalid. Read Chapter 10 for more information. The OUI is a Java-based application that provides the same installa- tion look and feel no matter which operating system is being used for the installation.
See Chapter 9 for more information. Although you can use choice A to stop and start the listener, doing so temporarily disrupts clients attempting to connect to the database. Therefore, the best method is C, to use the lsnrctl reload listener1 command to load the new set of values for the listener without disrupting connection service to the databases the listener is servicing.
For more information, see Chapter An error will occur because the job is not running, given that a job that is not execut- ing cannot be stopped.
See Chapter 16 for more information. Redo log files record all the changes made to the Oracle database, whether the change is committed or not. See Chapter 15 for more information. Default roles are enabled when a user connects to the database, even if the roles are password protected. Although options A and B are recommended practices for upgrade performance, option D is a must.
The Pre-Upgrade Information tool must be run, and all errors must be taken care of before upgrading the database. Option C is not required for the upgrade process. The default is seconds. A lower value will reduce the instance recovery time, but may cause frequent checkpoints. A value of 0 turns off MTTR tuning.
Read Chapter 15 for more information. A large pool is configured and is responsible for most of the SGA space allocation. The cache size and block buffers settings do not affect Oracle Shared Server.
See Chapter 12 for more information. Recovery options are optional when you create a database. Read Chapter 9 for more information. Option A is invalid. See Chapter 11 for more information. All uncommitted changes are rolled back, and committed changes are written to data files during instance recovery. LOCAL implies the current machine only. Every database must have at least two redo log files, which may or may not be multiplexed.
Every database must have one control file. It is a good idea to have more than one control file for redundancy. Read Chapter 8 for more information. There are no standard password complexity settings in either the initialization parameters or profiles.
Answers to Assessment Test lxi The shared pool has three components: library cache, result cache, and dictionary cache. You can use the Undo Management Advisor to monitor and manage the undo segments to ensure maximum levels of read consistency and minimize occurrences of ORA Snapshot Too Old error messages. This parameter is useful to reduce fragmentation in the tablespace. See Chapter 13 for more information. Protocols come with tools that allow you to test network connectivity.
The user supplies either an IP address or a hostname to the ping utility. It then searches the network for this address. If it finds one, it displays information on data that is sent and received and on how quickly it found this address.
The other choices are Oracle-supplied utilities. Read Chapter 12 for more information. The online redo log files are used to roll forward after an instance failure; undo data is used to roll back any uncommitted transactions.
See Chapter 18 for more information. However, shared servers do not receive requests directly from dis- patchers. The requests are taken from the request queue. The distance between the checkpoint position in the redo log file and the end of the redo log group can never be more than 90 percent of the size of the smallest redo log group. REPLACE is the valid value; it drops the existing table and creates the table using the definition from the dump file.
SKIP leaves the table untouched. Read Chapter 18 to learn more. Oracle Database 12c uses the Automatic Diagnostic Repository to maintain the alert log and other diagnostic information. Option A is not correct because operations on temporary tablespaces do not gen- erate redo, and a temporary table is created in a temporary tablespace.
When DML is performed on a temporary table, the undo is written by default to the active undo tablespace, which generates redo. Option C is wrong. See Chapter 10 for more information. In the previous database releases, the upgrade script was named catupgrd. In Oracle Database 12c, the upgrade script is also catupgrd. This script runs the upgrade in parallel by default, thereby reducing the upgrade time. The utlui. The utlus. The ASM instance does not have any data files or database associated to it; it has only the memory structures and processes.
Organizations and individuals collect and use a variety of infor- mation data. A database collects data, stores and organizes data, and retrieves related data used by a business.
With the release of its Database 12c, Oracle has enhanced the capabilities of its feature-rich database to include cloud architecture. The c in 12c stands for cloud computing. From Oracle version 8 onward, Oracle includes the core emphasis of the release along with the version number in its name.
Versions 8 and 9 are called i to indicate Internet computing; versions 10 and 11 are called g for grid computing. With the cloud enablement, Oracle Database 12c lets you manage many databases as one, thereby reducing overhead and valuable resource consumption. This chapter will introduce you to the Oracle Database 12c high-level components and how the Oracle database is organized.
You will also learn about the relational and object capabilities of the database, and the tools available for database administrators DBAs to retrieve information and manage the database. In a DBMS, the kernel code is the software piece that manages the storage and memory component of the database.
There is metadata in the DBMS that keeps track of all the com- ponents of the database, also known as the dictionary. The code or language used to retrieve data from the database is known as SQL, which stands for Structured Query Language. Over the years, database management systems have evolved from hierarchical to network to relational database management systems RDBMS.
A relational database management system is an organized model of subjects and characteristics that have relationships among the subjects. A well-designed relational database provides volumes of information about a business or process. We see relationships everywhere in our daily lives: parents and chil- dren, team and players, doctor and patient, to name a few.
The main advantages of RDBMS include the way it stores and retrieves information and how the data integrity is maintained.
RDBMS structures are easy to understand and build. These structures are logically repre- sented using the entity-relationship ER model. Tables, views, and indexes are examples of structures in Oracle. These rules protect the data and the structure of the database.
The primary keys and foreign keys are examples of integrity rules in Oracle. Logical Model In the design phase of the system development cycle, a logical model of the database is created. An ER model has entity, relationship, and attributes.
An ER model is visual, showing the structure, characteristics, and interactions within and around the data being modeled. The characteristics of an entity are known as its attributes. Attributes are detailed information about an entity that serves to qualify, identify, classify, or quantify it. For example: ABC Inc. Placing the organization of ABC Inc. Each entity will also have its own characteristics. See Figure 1.
There are optional and mandatory attributes. In Figure 1. An asterisk along with the attribute name indicates that it is mandatory.
The optional attribute may be indicated with a small o. So it is mandatory to have at least one occurrence of department for each location. There may be many departments in one location. Optional occurrence is represented by a dotted line. You should be able to identify each occurrence of an entity uniquely. Now what happens if there are two employees with the same name? How do you distinguish them? For office location, the city and state uniquely identify each office; for department, the department name identifies it uniquely.
For employee, you can introduce a unique identifier UID called employee number. Figure 1. UID is represented in the diagram using a pound symbol. F igur e 1. For example, product and patent—one product might have a patent, and one patent corresponds to only one product. Many-to-many relationships should not exist in RDBMS because they cannot be properly joined to represent a single row correctly. To solve this, create another entity that has an one-to-many relationship with the first entity and an one-to-many relationship with the second entity.
For example, doctor and patient—a patient can visit many doctors, and a doctor can have many patients. They are the common ways you usually query the database in order to retreive information.
Think of the access paths as an index to the data; they help us locate data just as the index of a book helps us quickly find the information we need. Normalization is the process of eliminating redundant information from the entities until you can uniquely identify each occurrence of the entity. This may not always be practical due to performance and implementation issues. In such cases, you can denor- malize to some extent.
Physical Model The physical model is created by taking the logical model and creating a database and data- base objects to represent the entities and relationships. In the physical model, each entity becomes a table and attributes of the entity become columns of the table. The relationship between the entities is part of one or more constraints between the tables.
Physical implemen- tations might force you to combine, separate, or create completely new entities in order to best realize the logical model. The unique identifiers of an entity become the primary key of the table. Stored procedures, functions, and triggers may be created to enforce business rules. An Oracle database satisfies all three major characteristics of the relational model.
Oracle lets you define tables, columns, column characteristics such as datatype, length, whether the values are mandatory, and default values. Defining foreign key ensures the referential integrity of the data. You can define primary keys and indexes on the data. The primary key of a relational table uniquely identifies each record in the table; it may consist of a single attribute column or multiple attributes in combination.
A foreign key is a column or collection of columns in one table that uniquely identifies a row of another table, defining the relationship between the tables. Records in a database table can be seen as instances of the entity. Each occurrence of an entity is differentiated by the values of the attributes. Oracle stores these records as rows of the table and the attributes as columns in each row.
In the most generic form, a database table can be seen as a single spreadsheet with unlimited numbers of columns and rows. The columns are not defined until the user names them and gives them a datatype. Oracle extends the concept of spreadsheets by defining relationships between multiple spreadsheets, constraints on columns, and providing mechanisms for multiple users to access the same database table s at the same time.
The data access path is implemented in Oracle using indexes. Indexing allows us to predefine to the relational database system the most common access paths that will be used. These indexes decrease the time required to search for data in a table using a number of algorithms such as B-tree, bitmap, etc.
It lets you create user-defined object types in the relational database system. Object types are structures that consist of built-in or user-defined data types. For example, Address can be defined as an object type and can be referenced in tables. This is a small example to show you how objects can be reused and how the functionality of the RDBMS can be extended to include built-in complex business rules.
In this section, we will discuss the capabilities and features of Oracle Database 12c. Detailed architecture and components are discussed in various chapters in Part II of this book. The physical structure of an Oracle Database 12c server consists of two major compo- nents: the database and the instance.
The database is a set of physical files saved on the disk that store information. The instance is a set of memory structures and processes that uses the physical components to manipulate and retrieve data. The host machine is where the Oracle instance is running. It has the memory structures and processes. The storage array, or disk, is where the database resides. The host machine is where users and applications connect and interact. If the machine goes down for some reason, the database will be unavailable.
In this architecture, more than one instance com- municates to a single database. Oracle RAC takes reliability a step further by removing the database server as a single point of failure.
If an instance fails, the remaining instances in the RAC pool remain open and active; and connections from failed instances can be failed-over to active instances.
The RAC load balancer directs the user connection request to the appro- priate instance. Oracle manages the connection load balancing and failover automatically. Many organizations have several hundreds or thousands of Oracle databases. Imagine if the policy were to have one instance per server, then you would have as many servers as the number of instances to manage. If you have a high-capacity server or if the database resource requirements are minimal, you can have more than one instance on the same host machine.
The Oracle Database 12c 11 F igur e 1. You need instance memory structures, processes, and management activities such as backup for each instance or database. With Oracle 12c, a new architecture feature is introduced known as the multitenant archi- tecture. The multitenant architecture enables an Oracle database to function as a multitenant container database CDB that includes zero, one, or many pluggable databases PDBs. All databases created prior to Oracle Database 12c are non-CDB; a pluggable database appears as a non-CDB to the application, so existing code and application need not be changed when you move to Oracle Database 12c.
Oracle RDBMS is responsible for keeping the pluggable databases separate, private for the application, and secure. The instance and SGA are assigned to a container database. The databases that are part of the CDB are known as pluggable databases. There is no need to change application code or architecture to start using Oracle Database 12c. This drastically reduces the administrative time required.
With container and pluggable databases, you only need to back up one multitenant container database. This is especially useful if you plan to upgrade all but a few data- bases to the next release. Before upgrading the container database, you may move a few pluggable databases to another container database, or move pluggable databases to another CDB of higher release. In the next section, you will learn about users and schema in the database, which is the basis for connecting to the database.
Connecting to Oracle Database Before you can connect to an Oracle database, you must create a user. Future sections will treat psalm verses , troparia , and kanons. Includes audio demonstrations. In , the Orthodox Liturgical Music Chatroom was inaugurated and was open on the 1st and 3rd Tuesdays of each month throughout and In , Dr. Vladimir Morosan, a member of the OCA Department of Liturgical Music and noted Orthodox composer, editor, publisher, and choir director, offered a series of four webinars dealing with the services of Great Lent and Holy Pascha.
Each session addresses a particular subtopic. Learn about the work and efforts of the Department of Liturgical Music and Translations and offer your support!
0コメント