Oracle Online Exam 2024, Oracle Mock Test with MCQ

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions, RANK for example, can be reset based on the groupings provided by a CUBE, ROLLUP, or GROUPING SETS operator. It is useful to assign ranks to the groups created by CUBE, ROLLUP, and GROUPING SETS queries.

A LOB is a Large Object. LOBs are used to store large, unstructured data, such as video, audio, photo images etc. With a LOB you can store up to 4 Gigabytes of data. They are similar to a LONG or LONG RAW but differ from them in quite a few ways. Internal LOBs are stored inside database tablespaces in a way that optimizes space and enables efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. You can recover internal LOBs in the event of transaction or media failure, and any changes to an internal LOB value can be committed or rolled back.

No, it is not possible to apply external LOBs to update operation. External LOBs (BFILES) are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROM, PhotoCDs and DVDs. But note that you cannot locate a single BFILE on more than one device, for instance, striped across a disk array. External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.

FOR UPDATE clause is used to lock a row containing a LOB, while the row is locked and other users cannot able to unlock or update the LOB, until you end your transaction. The FOR UPDATE clause is an optional part of a SELECT statement. Cursors are read-only by default. The FOR UPDATE clause specifies that the cursor should be updatable, and enforces a check during compilation that the SELECT statement meets the requirements for an updatable cursor.

The V$TEMPORARY_LOBS displays temporary LOBs. Your DBA can combine information from V$TEMPORARY_LOBS and the DBA_SEGMENTS data dictionary view to see how much space a session is using for temporary LOBs.

You use the CREATE_JOB procedure to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. You can create multiple jobs in a single transaction using the CREATE_JOBS procedure.

The above statement is false. You drop one or more jobs using the DROP_JOB procedure or Enterprise Manager. DROP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all jobs in the job class are dropped, although the job class itself is not dropped.

Detached jobs allow you to run jobs in a separate process, independent of the scheduler. If it is an external job, it is also independent of the database state, meaning the job continues to run after the database is shut down. Once a detached job is initiated, the scheduler makes the job as running, then ceases to track its progress. It is up to the detached job to signal its completion using the END_DETACHED_JOB_RUN procedure. To perform cold backup is the role of detached job.

No, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views. The attributes of a job are available in the *_SCHEDULER_JOBS views. It is valid for running jobs to alter their own job attributes. However, these changes do not take effect until the next scheduled run of the job.

The above statement is true. You run a job asynchronously with RUN_JOB by setting the use_current_session argument to FALSE. In this case, the job runs as if it were started according to its schedule or by an event. That is, it runs under the control of a job slave and runs as the job owner. The session that calls RUN_JOB returns immediately; it does not block waiting for the job to complete. To find out whether the job succeeded, you must query the job views or the job log.

You can use the RUN_JOB procedure to test a job or to run it outside of its specified schedule. Running a job with RUN_JOB with the use_current_session argument set to TRUE does not change the count for failure_count and run_count for the job. The job run will, however, be reflected in the job log. Runtime errors generated by the job are passed back to the invoker of RUN_JOB.

A scheduler program object is a collection of metadata about what will be run by the Scheduler. It includes information such as the name of the program object, program action (for example, a procedure or executable name), program type (for example, PL/SQL and Java stored procedures or PL/SQL anonymous blocks) and the number of arguments required for the program.

The DBMS_SCHEDULER.LOGGING_FAILED_RUNS logging level is used when a log entry is made only if the job fails.

You disable one or more jobs using the DISABLE procedure or Enterprise Manager. Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state in the job table is changed to disabled.

There are two kinds of events in the scheduler one is events raised by the scheduler and another is events raised by the application. An application can raise an event to be consumed by the scheduler. The scheduler reacts to the event by starting a job. You can create a schedule that references an event instead of containing date, time, and recurrence information. If a job is given such a schedule (an event schedule), the job runs when the event is raised.

The DBMS_SCHEDULER is used to replace and extend the functionality provided by the DBMS_JOB package.

The remote server doesn’t have an Oracle client or database installation, but it must have an Oracle Scheduler Agent installation. Remote database jobs must be run through an Oracle Scheduler agent. Oracle recommends that an agent be installed on the same host as the remote database. If you intend to run remote database jobs, the Scheduler agent must be release 11.2 or later.

Transparent data encryption is a solution for encrypting sensitive data in the Oracle database when it requires no changes to applications, encrypting data before it is written to storage and automatically decrypting when reading from storage. Transparent Data Encryption(TDE) enables you to encrypt sensitive data, such as credit card numbers, stored in tables and tablespaces. Encrypted data is transparently decrypted for a database user or application that has access to data. TDE helps protect data stored on media in the event that the storage media or data file gets stolen.

The dequeue mode REMOVE_NODATA can be used to remove a message from a queue without retrieving the payload. Use this mode to delete a message with a large payload whose content is irrelevant.

The DBA_QUEUE_SCHEDULES view is used to gather and store detailed run-time information about propagation for each propagation schedule. Detailed run-time information about propagation is gathered and stored in the DBA_QUEUE_SCHEDULES view for each propagation schedule. This information can be used by queue designers and administrators to fix problems or tune performance.

An agent is a queue user. This can be an end user or an application. There are two types of agents:
1. Producers who place messages in a queue (enqueuing)
2. Consumers who retrieve messages (dequeuing)
Any number of producers and consumers may be accessing the queue at a given time. Agents insert messages into a queue and retrieve messages from the queue by using the Oracle AQ operational interfaces.

The Java package oracle.jms have classes and interfaces to implement Oracle extensions to the public JMS standard. The Oracle JMS interfaces extend the standard JMS interfaces to support the Oracle9i Advanced Queuing (AQ) administrative operations and other AQ features that are not included in the public standard contained in the javax.jms package.

DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed. REMOVE procedure enables a session that is no longer interested in an alert to remove that alert from its registration list. Removing an alert reduces the amount of work done by signalers of the alert.

The CONVERTTOBLOB procedure reads character data from a source CLOB or NCLOB instance, converts the character data to the character set you specify, writes the converted data to a destination BLOB instance in binary format, and returns the new offsets. You can use this interface with any combination of persistent or temporary LOB instances as the source or destination.

The above statement is true. The REGISTER procedure does not raise any package exceptions. It will raise an ORA-20000 exception for specific error conditions, with message text indicating the error as follows:
– ORU-10021: Lock request error.
– ORU-10025: Lock request error.

ALL_SCHEDULER_RUNNING_JOBS displays information about the running Scheduler jobs accessible to the current user.

The Scheduler supports two kinds of logs one is the job log and the window log. In window log, the Scheduler makes an entry in the window log each time where you can create or drop a window, open a window, close a window, overlap a window, you can enable or disable a window.
There are no logging levels for window activity logging.

The SET_ATTRIBUTE procedure modifies an attribute of a Scheduler object. It is overloaded to accept values of various types. To set an attribute to NULL, use the SET_ATTRIBUTE_NULL procedure. The attributes that can be set depend on the object being altered. All object attributes can be changed, except the object name.