Oracle Database Administration I (1Z0-082) practice questions

Exam overview 1Z0-082

Domain coverage 8 domains

• Oracle Database Architecture (15%)
• Managing Database Instances (15%)
• Managing Storage Structures (15%)
• Managing Users, Roles and Privileges (15%)
• Moving Data (10%)
• Managing Undo Data (10%)
• SQL Fundamentals for DBA (10%)
• Configuring Oracle Net Services (10%)

Sample questions 14 of 135 shown

1. Q1

   In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1. The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains an entry for LISTENER_1 that resolves to (ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)). Which statement is true?

   1. Dynamic service registration cannot be used for this database instance
   2. The LREG process registers services dynamically with the LISTENER_1 listener
   3. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
   4. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
   5. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration

   The LOCAL_LISTENER parameter tells the instance's LREG (Listener Registration) process which listener to register the instance's services with. LREG resolves the name LISTENER_1 through a naming method such as tnsnames.ora, then contacts that address and registers the services dynamically — no listener.ora entry and no CONNECT_DATA section are required (C and E are wrong). Dynamic registration works fine (A is wrong), and nothing in the scenario implies a second listener running (D is wrong).
2. Q2

   Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

   1. A single database instance can support connections for multiple service names.
   2. A service name is created by a listener.
   3. A single connection string can refer to multiple database instances.
   4. A connection string including a service name must be defined in the tnsnames.ora file.
   5. A connection string must include the SID of a database instance.
   6. Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.

   An instance can register several service names with the listener (SERVICE_NAMES), so A is true. A connect descriptor can contain multiple ADDRESS entries pointing at different instances for failover/load balancing, so C is true. Net service name aliases in tnsnames.ora may resolve to identical host/port/service details, so F is true. A service name is an attribute of the database service registered with the listener, not something created by the listener (B wrong). Easy Connect syntax does not require tnsnames.ora (D wrong), and connections can use SERVICE_NAME instead of SID (E wrong).
3. Q3

   In one of your databases, the user HR has the password HRMGR. You want to connect to a database instance whose listener listens on port 1531 by using this statement: CONNECT HR/HRMGR@orcl. No name server is used. Which statement is true about ORCL?

   1. It must be the value of the SERVICE_NAMES parameter on the client side
   2. It must resolve to a valid connect descriptor in the server's tnsnames.ora file
   3. It must resolve to a valid connect descriptor in the client's tnsnames.ora file
   4. It must be the name of the database to whose instance HR wishes to connect
   5. It must be the name of the server running the database to whose instance HR wishes to connect

   With local naming, the connect identifier ORCL after the @ sign is resolved as a net service name in the client's tnsnames.ora file, which must map it to a connect descriptor containing host, port 1531 and service/SID. It has nothing to do with SERVICE_NAMES on the client (A wrong), server-side tnsnames.ora (B wrong), or the database/server name itself (D and E wrong).
4. Q4

   The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week. Which query can be used?

   1. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
   2. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
   3. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
   4. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

   ADD_MONTHS(hire_date, 6) computes the six-month anniversary, and NEXT_DAY(date, 'MONDAY') returns the first Monday strictly after that date — exactly the requirement. A fails because NEXT_DAY requires two arguments (a date and a day name). C misuses MONTHS_BETWEEN (it returns a number, not a date) and swaps the argument order. D passes 1 as the day value; the numeric form of NEXT_DAY is not reliable/portable, and with NLS_TERRITORY=AMERICA, 1 means Sunday, not Monday.
5. Q5

   Which three statements are true concerning logical and physical database structures? (Choose three.)

   1. All tablespaces may have one or more data files
   2. The extents of a segment must always reside in the same datafile
   3. A smallfile tablespace might be bigger than a bigfile tablespace
   4. A segment can span multiple data files in some tablespaces
   5. A segment's blocks can be of different sizes
   6. A segment might have only one extent

   A smallfile tablespace can hold up to 1022 data files, so its combined size can exceed the single (albeit very large) data file of a bigfile tablespace — C is true. In smallfile tablespaces a segment's extents can be allocated from several data files, so D is true. A freshly created object gets one initial extent, so a segment can consist of exactly one extent — F is true. Temp/unlimited-size cases aside, B is false because extents of one segment may be spread across the files of a smallfile tablespace; E is false because all blocks of a tablespace (and therefore of a segment) share the same block size; G is false because a segment lives entirely within one tablespace.
6. Q6

   Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three.)

   1. An undo segment may be used by multiple transactions simultaneously
   2. Undo segments can wrap around to the first extent when a transaction fills the last extent of the undo segment
   3. Undo segments have a minimum of three extents
   4. Undo segments can extend when a transaction fills the last extent of the undo segment
   5. A single transaction may use multiple undo segments simultaneously
   6. Undo segments must be stored in a BIGFILE tablespace

   An undo segment can serve multiple transactions concurrently — each transaction gets its own slot in the segment's transaction table (A true). A transaction filling the current extent moves to the next extent; if the last extent is reached, the segment either wraps around to the first extent (B true) or allocates a new extent, extending the segment (D true). Undo segments are created with a minimum of two extents, not three (C wrong). A single transaction writes to only one undo segment at a time (E wrong), and undo tablespaces may be either bigfile or smallfile (F and G wrong).
7. Q7

   In your data center, Oracle Managed Files (OMF) is used for all databases. All tablespaces are smallfile tablespaces. SALES_Q1 is a permanent user-defined tablespace in the SALES database. Examine this command which is about to be issued by a DBA logged in to the SALES database: ALTER TABLESPACE sales_q1 ADD DATAFILE; Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)

   1. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.
   2. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.
   3. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
   4. Add the AUTOEXTEND ON clause with NEXT set to 100M.
   5. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

   With OMF, if no filename is given the new data file is created in DB_CREATE_FILE_DEST with a default size of 100M, so ensuring that location has at least 100M free (C) makes the command succeed. Alternatively, explicitly naming a location with enough free space (B) also succeeds. A and E wrongly involve DB_RECOVERY_FILE_DEST (the fast recovery area, not used for ADD DATAFILE without OMF storage shortcuts), and D is pointless because OMF already creates files as autoextensible with NEXT 100M, so adding that clause does not change whether the statement succeeds.
8. Q8

   Which three statements are true about the DESCRIBE command? (Choose three.)

   1. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
   2. It can be used from SQL Developer
   3. It displays the NOT NULL constraint for any columns that have that constraint
   4. It can be used to display the structure of an existing view
   5. It displays all constraints that are defined for each column
   6. It can be used only from SQL*Plus

   DESCRIBE lists column names, data types and shows NOT NULL for columns with that constraint (C true); it works on tables and views (D true) and is available in SQL Developer as well as SQL*Plus (B true, F wrong). DESCRIBE does not report PRIMARY KEY, FOREIGN KEY or CHECK constraints — only NOT NULL is shown — so A and E are wrong.
9. Q9

   Examine the description of the PROMOTIONS table: PROMO_ID (number), PROMO_NAME (varchar2), PROMO_CATEGORY (varchar2), PROMO_COST (number). You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)

   1. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
   2. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
   3. SELECT DISTINCT promo_category || ' has ' || promo_cost AS costs FROM promotions ORDER BY 1;
   4. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
   5. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

   DISTINCT applies to the whole select list, so E returns the unique combinations of category and cost, and C does the same after concatenating the two columns into one expression; both satisfy the requirement. B is invalid SQL because DISTINCT cannot appear twice in one select list, and D is invalid because DISTINCT cannot sit between the column list items. A does not remove duplicate combinations, so it shows repeated costs per category.
10. Q10

    Which two statements are true about single row functions? (Choose two.)

    1. MOD: returns the quotient of a division operation
    2. FLOOR: returns the smallest integer greater than or equal to a specified number
    3. TRUNC: can be used with NUMBER and DATE values
    4. CONCAT: can be used to combine any number of values
    5. CEIL: can be used for positive and negative numbers

    TRUNC accepts both numeric arguments and DATE arguments (it truncates a date to day/month/year granularity), so C is true, and CEIL works for negative values too (CEIL(-1.5) = -1), so E is true. MOD returns the remainder, not the quotient (A wrong). FLOOR returns the largest integer less than or equal to the value — the description given belongs to CEIL (B wrong). CONCAT takes exactly two arguments (D wrong).
11. Q11

    Examine the description of the CUSTOMERS table: CUST_FIRST_NAME, CUST_LAST_NAME, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT. For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used?

    1. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
    2. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level != NULL AND cust_credit_limit != NULL;
    3. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;
    4. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
    5. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

    Nulls can only be tested with IS NULL / IS NOT NULL — any comparison with NULL ( !=, <> ) is unknown, so B, C and D can never return rows. A fails because an alias (due_amount) cannot be referenced in the WHERE clause of the same query block; the filter must be written against the underlying column cust_credit_limit. E correctly filters cust_income_level IS NOT NULL and cust_credit_limit IS NOT NULL, which guarantees the computed due amount is not null.
12. Q12

    The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations. Each database has a tnsnames.ora file defining DALLAS_DB as a service name. Examine this command: CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'dallas_db'; How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

    1. as SCOTT in DALLAS_DB
    2. as SCOTT in BOSTON_DB
    3. as SCOTT in BOSTON_DB and SYS in DALLAS_DB
    4. as SYS in both the databases
    5. as SCOTT in both the databases

    The CREATE DATABASE LINK statement must be executed in the local database (BOSTON_DB) by the user who will own and use the link — SCOTT. A private database link created by SCOTT in BOSTON_DB is visible only to SCOTT and stores the fixed credentials SCOTT/TIGER for the connection to DALLAS_DB via the 'dallas_db' connect string. Creating it elsewhere (SYS or DALLAS_DB side) would place the link in the wrong database or in the wrong schema.
13. Q13

    Which four account management capabilities can be configured using Oracle profiles? (Choose four.)

    1. The number of hours for which an account is locked after the configured number of login attempts has been reached
    2. The number of days for which an account may be inactive before it is locked
    3. The maximum amount of CPU time allowed for a user's sessions before their account is locked
    4. The ability to prevent a password from ever being reused
    5. The number of password changes required within a period of time before a password can be reused
    6. The number of days for which an account is locked after the configured number of login attempts has been reached

    Profiles control password and lockout behavior: FAILED_LOGIN_ATTEMPTS plus PASSWORD_LOCK_TIME define lockout count and duration in days (A and F true), PASSWORD_REUSE_MAX/PASSWORD_REUSE_TIME prevent or condition password reuse (D and E true). Profiles never lock an account for inactivity (B wrong) or for CPU usage — CPU_PER_SESSION/SESSIONS_PER_USER cause the session or connection attempt to be refused, not the account to be locked (C and G wrong).
14. Q14

    Examine this command executed while OMF is configured: ALTER DATABASE MOVE DATAFILE 'sales1.dbf'; Which two statements are true? (Choose two.)

    1. DML may be performed on tables with one or more extents in this data file during the execution of this command.
    2. The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.
    3. The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.
    4. If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
    5. The file is renamed and stored in the same location

    ALTER DATABASE MOVE DATAFILE is an online operation: the tablespace stays open for queries and DML while the file is copied and synchronized, so A is true and B and C are false. When no TO clause is supplied and OMF is in use, Oracle generates an OMF-style name and relocates the file under DB_CREATE_FILE_DEST (D true, E false).

Answer all 135 questions with instant feedback →

Reader reviews 26 ratings

• I recommend this to everyone preparing for 1Z0-082. The lifetime access is great — I keep coming back for reference.

  ★★★★★★ Robert C.

• The question explanations for 1Z0-082 really dig into the AWS (or relevant) concepts. Helped me understand things at a deeper level.

  ★★★★★★ Hazel C.

• Just passed the 1Z0-082 certification. These questions were more challenging than the real exam, which is exactly how prep should be.

  ★★★★★ Liam H.

• Really well-structured 1Z0-082 practice set. I like that you can attempt questions multiple times and it tracks your progress.

  ★★★★★★ Lucas W.

• Good investment for the 1Z0-082 exam. My only regret is not buying it sooner — would have saved me a lot of study time.

  ★★★★★★ Henry C.

• Best investment for Oracle certification prep. The question bank for 1Z0-082 is comprehensive and mirrors the real exam perfectly.

  ★★★★★ Sarah M.

Frequently asked questions

More Oracle exams