Oracle Online Test Series, Oracle Mock Test with Question Answers

The above statement is false. Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table). For referential integrity to hold in a relational database, any column in a base table that is declared a foreign key can contain either a null value, or only values from a parent table’s primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity.

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. It specifies positioning at specific rows in the result set. It modifies data in the rows at the current position in the result set.

Horizontal partitioning involves putting different rows into different tables. Horizontal (column) partitioning occurs when the measure data in a table is divided into two or more components by column, with each component being stored in a separate table. This architecture can arise when two different applications maintain different measures that logically belong in the same table.

CHAR are padded with space characters to match the specified length. CHAR is used to store fixed length character strings. If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values.

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes. An Oracle instance can have many background processes; not all are always present. There are numerous background processes. The background processes in an Oracle instance can include the following:

Database Writer Process (DBWn)
Log Writer Process (LGWR)
Checkpoint Process (CKPT)
System Monitor Process (SMON)
Process Monitor Process (PMON)
Recoverer Process (RECO)
Job Queue Processes
Archiver Processes (ARCn)
Queue Monitor Processes (QMNn)
Other Background Processes

On many operating systems, background processes are created automatically when an instance is started.

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.
The control file includes:
The database name
Names and locations of associated datafiles and redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

The above statement is true. Using synonyms is a good way to implement location transparency. Synonyms are useful in both distributed and non-distributed environments because they hide the identity of the underlying object, including its location in a distributed database system. Synonyms also simplify SQL statements for users in a distributed database system.

A cluster is a schema object that contains data from two or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key. To create a cluster in your own schema, you must have CREATE CLUSTER system privilege. To create a cluster in another user’s schema, you must have CREATE ANY CLUSTER system privilege.

A tablespace is an area on disk which comprises of one or more disk files. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

A Rollback Segment is a database object containing before-images of data written to the database. Rollback segments are used to:
– Undo changes when a transaction is rolled back.
– Ensure other transactions do not see uncommitted changes made to the database.
– Recover the database to a consistent state in case of failures.

Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

The level of logical database storage greater than an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace.

Process task: The resources entire lifecycle is audited. Auditing collects information at run time for performance monitoring, security etc. Different levels of auditing are available depending on the extent to which auditing is required:

– Resource Form: Audits any form of data associated with the resource – user record, group membership, provisioned resources
– Resource: user record, group membership, provisioned resources is audited.
– Membership: Audits the user record and group membership only
– Core: Audits the user record.
– At a higher level audits are at Privilege level, Object and Statement level.

The above statement is false. The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener.

Extents is a part of logical structure of oracle. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

The files that are created when an Oracle background process encounters an exception are Trace files. Trace files are also created by ORA-00600 error or due to some diagnostic dump events. The SQL Trace facility provides performance information on individual SQL statements.

Copying physical files when the database is up is called hot backup. A hot backup is taking up the backup of the database while it is still up and running. A few systems that need to support continuous operation, it is difficult to bring down the database without interrupting the service provided to the users. In such cases, hot backup approach should be used. While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup. In hot backup, the database has to run all the time due to critical requirements. Because of this there may be changes in the data files while backup is being taken.

Yes, BLOB, CLOB, NCLOB and BFILE are all LOB data types. The built-in LOB data types BLOB, CLOB and NCLOB (stored internally), and BFILE (stored externally), can store large and unstructured data such as text, images and spatial data up to 4 gigabytes in size.

BLOB stores binary large objects. BLOB can store up to 4 gigabytes of binary data.

CLOB stores character large objects. CLOB can store up to 4 gigabytes of character data.

NCLOB stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

BFILE enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column stores a locator, which serves as a pointer to a binary file on the server’s file system. The maximum file size supported is 4 gigabytes.

Using Rule Based Optimizer and Cost Based Optimizer, Oracle decide how to retrieve the necessary data whenever a valid SQL statement is processed. When a valid SQL statement is sent to the server for the first time, Oracle produces an execution plan that describes how to retrieve the necessary data.

Rule-Based Optimizer (RBO): This was the original optimization method and as the name suggests, was essentially a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced, this method was used if the server had no internal statistics relating to the objects referenced by the statement, or if explicitly requested by a hint or instance/session parameter. This optimizer was made obsolete, then deprecated in later versions of the database.

Cost-Based Optimizer (CBO): The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation.

The dynamic performance views are the views that get continuously updated even while the database is open or in use. Dynamic performance views are sometimes called fixed views because they cannot be altered or removed by a database administrator. However, database administrators can query and create views on the tables and grant access to these views to other users.

The above statement is wrong. A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. The materialized view is populated immediately. The materialized view is populated on the first requested refresh. The following refresh types are available.
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

Yes, the global temporary table is session specific. Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations, where data fetch and passage is not possible in a single stretch.

Note that only table data is session specific, but physically table is available in all sessions.

Syntax:
CREATE GLOBAL TEMPORARY TABLE
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

In the above syntax, ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

The snapshot logs that Oracle creates for a master table is a table in its own right. It is stored in the same database as the table. If a transaction modifies data in the master table, an internal trigger (kernel code in Oracle8) on the master table automatically inserts rows into it’s snapshot log. One master table can only have one snapshot log. However, the master table can have multiple snapshots each having data replicated to them from the one master table via the snapshot log.

In reactive tuning the bottom up approach is used to find and fix the bottlenecks. The goal is to make Oracle run faster. Reactive tuning involves performance tuning and troubleshooting within boundaries of existing architecture.

A correlated sub-query is dependent upon the outer query. A correlated sub-query (also known as a synchronized sub-query) is a sub-query (a query nested inside another query) that uses values from the outer query. Because the sub-query is evaluated once for each row processed by the outer query, it can be inefficient.

Dynamic data replication is the way in which updating or inserting records in remote database through database triggers. It may fail if remote database is having any problem. Dynamic replication can supported 3 changes: user requests, storage capability and information measure. Dynamic replication techniques square measure capable of creating intelligent choices to position data within the grid supported storage capability and node handiness.

The above statement is wrong. Sequence will cache values for performance. In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Yes, we can pass parameters into a cursor and use them in the query. We can only pass values to the cursor; and cannot pass values out of the cursor through parameters. Only the datatype of the parameter is defined, not its length. Optionally, we can also give a default value for the parameter, which will take effect if no value is passed to the cursor.