Oracle Practice Test - 23

A tablespace is a logical storage container in Oracle databases that consists of one or more physical data files on disk. It provides a layer of abstraction between database objects (tables, indexes) and physical storage, allowing administrators to manage disk space efficiently. Tables, indexes, and other schema objects are stored within tablespaces.

The FBEAN package is Oracle Forms' built-in interface for communicating with JavaBeans. It provides PL/SQL built-ins to register JavaBeans, set/get properties, and invoke methods. Using FBEAN eliminates the need to write custom Java wrapper code or implement custom integration layers - it's the standard, supported mechanism for Forms-JavaBean interaction.

The REFERENCES privilege (and the corresponding REFERENCES object privilege) is unique in Oracle - it can only be granted to specific users, not to roles. This security restriction prevents roles from being used as a backdoor to grant foreign key constraints. All other object privileges like SELECT, INSERT, UPDATE, DELETE, ALTER, and EXECUTE can be granted to either users or roles.

Oracle automatically creates indexes to enforce UNIQUE and PRIMARY KEY constraints. These indexes support the constraint's requirement that values be distinct across rows (or non-null and distinct for PK). CHECK, NOT NULL, and FOREIGN KEY constraints do not automatically create indexes - though you can and often should manually create indexes on foreign key columns for join performance.

The BACKUP CONTROL FILE TO trace creates a binary copy of the control file at the specified location. This is different from the TO TRACE option which creates a text file with SQL commands. The binary backup preserves the exact state of the control file including data file info, checkpoint details, and recovery metadata - essential for disaster recovery scenarios.

A message with a level greater than 25

DBMS_RESOURCE_MANAGER_PRIVS is the specific package used to grant resource group privileges and administer the Resource Manager. It provides procedures like GRANT_SYSTEM_PRIVILEGE and GRANT_SWITCH_CONSUMER_GROUP to authorize users for resource management tasks. The other packages listed serve different purposes - DBMS_REPCAT packages handle replication, while DBMS_RESOURCE_MANAGER creates and configures plans but doesn't handle privilege grants.

Horizontal partitioning divides table rows based on a key or range - different subsets of rows go into different tables. This differs from vertical partitioning (splitting columns), range partitioning (a specific horizontal method), and hash partitioning (using a hash function). The question asks about the general concept of putting different rows into different tables, which is horizontal partitioning.

Option D correctly uses substitution variables: &1 for first column, '&2' as a literal string (single quotes preserved), EMP as hardcoded table, and &4 for the WHERE condition value. Options A and C incorrectly try to substitute the table name. Option B has malformed quoting with incorrect quote placement that would cause a syntax error.

DBA_TAB_PRIVS

To replace the default Forms message with specific counts for insert/update/delete operations, you need Post-Insert, Post-Update, and Post-Delete triggers to capture counts after each operation type, plus an On-Message trigger to intercept and replace the FRM-40400 message. Post-Database-Commit fires too late (after transaction completes), and Post-Commit doesn't distinguish between operation types.

To connect to RMAN without a recovery catalog, use 'rman target nocatalog'. This connects to the target database using the control file for metadata instead of a catalog database. Options B, C, and D use incorrect syntax - 'catalog=no', 'without catalog', and 'rcvcat no' are not valid RMAN connection parameters.

A profile in Oracle is a database-level resource limit that can only be assigned to users, not to schema objects like tables or views. Profiles control password settings (expiration, complexity, failed login attempts) and resource limits (CPU time, sessions, idle time). They are assigned with the CREATE USER or ALTER USER commands using the PROFILE clause.

Oracle9i introduced automatic latch calculation based on initialization parameters and OS configuration. Manual latch tuning formulas are obsolete in Oracle9i. The database now determines optimal latch counts dynamically using db_cache_size, db_block_size, CPU count, and other parameters.

The ALTER SYSTEM SWITCH LOGFILE command forces an immediate log switch, causing LGWR to stop writing to the current online redo log group and switch to the next group. This is useful for forcing checkpoints or manually managing log file rotation.

&

With LOG_ARCHIVE_MIN_SUCCEED_DEST=2, Oracle requires successful archival to at least 2 destinations before overwriting redo logs. Since there are 2 local destinations (1 mandatory + 1 optional), once both local destinations succeed, the requirement is met even if the remote destination hasn't finished.

VARRAYs (variable-size arrays) require a maximum size to be specified at declaration time. Unlike nested tables which are unbounded, VARRAYs have a fixed upper bound. Arrays and indexes are different concepts, and synonyms are database objects unrelated to size constraints.

The ALL_OBJECTS data dictionary view shows all objects accessible to the current user, including objects owned by the user plus those on which privileges have been granted. By filtering WHERE object_type='TABLE', only table names are returned. USER_OBJECTS would show only owned objects.

Option B meets all requirements: UNIQUE constraint ensures uniqueness, NOLOGGING suppresses redo generation, PCTFREE 20 reserves 20% of each block, STORAGE clause sets 1M extents, and the index is created in INDEX01 tablespace. Option A uses 'lm' instead of '1m' (typos), C incorrectly uses PCTUSED, and D lacks NOLOGGING.

The SHOW_ALERT function in Oracle Forms returns named constants ALERT_BUTTON1, ALERT_BUTTON2, or ALERT_BUTTON3 to indicate which button the user pressed. These constants, not numeric values 1/2/3, are the proper way to programmatically determine the user's response.

A Top-n query uses ROWNUM with ORDER BY in a subquery to limit results to the top n rows after sorting. The construct involves nesting a query with ORDER BY inside another that filters on ROWNUM to get the first n records. This is different from complex views, materialized views, or hierarchical reports.

The error occurs because John is storing full state names in the list elements, but the database column is VARCHAR2(2) expecting only abbreviations. The solution is to use state names as display labels (what users see) while mapping them to 2-character abbreviations as actual values stored in the database.