Oracle 1Z0-071: Indexes and Sequences

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Manage Indexes, Synonyms, and Sequences

User APP already has SELECT privilege on HR.EMPLOYEES. The DBA must let only APP reference that table as EMP without changing who can access the data or how the table is queried. Which action correctly applies the Oracle SQL rule?

Options:

• A. CREATE SYNONYM app.emp FOR hr.employees

• B. CREATE PUBLIC SYNONYM emp FOR hr.employees

• C. GRANT SELECT ON hr.employees TO PUBLIC

• D. CREATE INDEX hr.emp_idx ON hr.employees(employee_id)

Best answer: A

Explanation: A private synonym is just an alternate object name within one schema. Because APP already has SELECT on HR.EMPLOYEES, creating APP.EMP meets the requirement without changing data access semantics.

In Oracle, a synonym affects name resolution, not data access rights. A private synonym such as APP.EMP lets only the APP schema use that shorter name for HR.EMPLOYEES. Oracle still checks privileges on the underlying table, so existing access rules remain the same.

An index is different: it may affect how Oracle accesses rows during execution, but it does not create a new object name and does not grant or restrict access. A public synonym also changes only the name, but it makes that alias visible to all users, which violates the requirement that only APP use it. The key rule is that synonyms rename object references, while grants change access semantics.

• Public visibility the option creating a public synonym makes the alias visible database-wide, not only to APP.
• Performance only the option creating an index may affect an execution path, but it does not provide a new object name.
• Access changed the option granting SELECT to PUBLIC broadens who can read the table, which the stem forbids.

Question 2

Topic: Manage Indexes, Synonyms, and Sequences

Which statement correctly describes the use of NEXTVAL and CURRVAL for an Oracle sequence?

Options:

• A. CURRVAL can be referenced only after NEXTVAL has been used for that sequence in the current session.

• B. NEXTVAL returns the most recently generated value without changing the sequence.

• C. CURRVAL advances the sequence and returns the next available value.

• D. CURRVAL always returns the latest value generated by any session.

Best answer: A

Explanation: In Oracle, NEXTVAL increments a sequence and returns the new value. CURRVAL returns the current value for that same sequence in your session, but only after your session has already called NEXTVAL at least once.

The core rule is session scope. NEXTVAL generates the next sequence number and establishes the current sequence value for that sequence in your session. After that, CURRVAL can return that current session value without incrementing the sequence again.

If a session tries to reference CURRVAL before it has used NEXTVAL for that sequence, Oracle raises an error because no current value exists yet in that session. Sequence values are not tracked as a single shared “current value” for all sessions, and CURRVAL does not advance the sequence.

The key takeaway is that NEXTVAL initializes and increments; CURRVAL only reuses the already obtained value in the same session.

• Advances the sequence fails because incrementing is done by NEXTVAL, not CURRVAL.
• Returns the last value fails because that behavior describes CURRVAL, not NEXTVAL.
• Shared across sessions fails because CURRVAL is tied to the current session’s prior use of the sequence.

Question 3

Topic: Manage Indexes, Synonyms, and Sequences

Assume each snippet runs successfully and creates only one new row in orders. Which SQL snippet advances order_seq by two values because NEXTVAL is referenced in too many separate SQL statements?

Options:

• A. SELECT order_seq.NEXTVAL FROM dual; INSERT INTO orders (order_id, status) VALUES (order_seq.NEXTVAL, 'NEW')

• B. INSERT INTO orders (order_id, status) VALUES (order_seq.NEXTVAL, 'NEW')

• C. INSERT INTO orders (order_id, audit_id) VALUES (order_seq.NEXTVAL, order_seq.NEXTVAL)

• D. INSERT ALL INTO orders (order_id) VALUES (order_seq.NEXTVAL) INTO order_log (log_id) VALUES (order_seq.NEXTVAL) SELECT 1 FROM dual

Best answer: A

Explanation: Oracle increments a sequence once per row for a single SQL statement, even if NEXTVAL appears more than once in that statement. The snippet that first selects NEXTVAL and then uses NEXTVAL again in a separate INSERT consumes two sequence numbers.

The key rule is that NEXTVAL advances a sequence once per row generated by a single SQL statement, not once for every textual appearance of NEXTVAL inside that statement. So repeating NEXTVAL in one VALUES clause or in a multitable insert driven by one source row does not consume extra numbers for that same row.

Separate SQL statements are different. If you run a SELECT ... NEXTVAL first, the sequence advances immediately. A later INSERT ... NEXTVAL is a new statement, so it advances again. That is why the snippet with the standalone SELECT followed by the INSERT uses two sequence values for one inserted order row. The common trap is assuming every repeated NEXTVAL reference within one statement always burns another value.

• Single insert uses NEXTVAL once, so it advances the sequence once.
• Two NEXTVAL references in one VALUES clause still use the same generated value for that row.
• Multitable insert driven by one row from dual increments once for that source row, even though NEXTVAL appears in two branches.

Question 4

Topic: Manage Indexes, Synonyms, and Sequences

A developer needs to create one row in orders and one row in order_audit for customer 101. Both rows must use the same generated order_id, and ORDER_SEQ must advance only once for that business event.

Which option should be rejected because it advances the sequence unexpectedly?

Options:

• A. INSERT INTO orders (order_id, customer_id) VALUES (order_seq.NEXTVAL, 101); INSERT INTO order_audit (order_id, note) VALUES (order_seq.CURRVAL, 'Created');

• B. INSERT INTO orders (order_id, customer_id) SELECT order_seq.NEXTVAL, 101 FROM dual; INSERT INTO order_audit (order_id, note) VALUES (order_seq.CURRVAL, 'Created');

• C. INSERT ALL INTO orders (order_id, customer_id) VALUES (order_seq.NEXTVAL, 101) INTO order_audit (order_id, note) VALUES (order_seq.CURRVAL, 'Created') SELECT * FROM dual;

• D. INSERT INTO orders (order_id, customer_id) VALUES (order_seq.NEXTVAL, 101); INSERT INTO order_audit (order_id, note) VALUES (order_seq.NEXTVAL, 'Created');

Best answer: D

Explanation: A sequence advances each time NEXTVAL is requested in a separate SQL statement or row-producing context. Using CURRVAL after the first NEXTVAL reuses the same generated value in the session, but calling NEXTVAL again gets a new number.

The key concept is how Oracle sequences behave across statements. NEXTVAL generates the next sequence number, while CURRVAL returns the most recently generated sequence value in the current session. If a script must use the same generated key in multiple places, it should request NEXTVAL once and then reuse that value with CURRVAL.

In the rejected script, the first INSERT gets one order_id, and the second INSERT requests NEXTVAL again, so the audit row receives a different sequence value. That breaks the requirement that both rows share the same generated key.

A common trap is thinking that any repeated reference to a sequence always increments it again. In Oracle, the real issue here is the second separate call to NEXTVAL, not simply using the sequence in more than one place.

• The script using NEXTVAL first and CURRVAL second reuses the same generated value without another increment.
• The INSERT ALL statement advances the sequence once for the single row from DUAL, and CURRVAL reuses that value.
• The INSERT ... SELECT followed by CURRVAL still advances the sequence only once, because the second statement does not request a new value.

Question 5

Topic: Manage Indexes, Synonyms, and Sequences

User SALESAPP already has SELECT on HR.EMPLOYEES and wants to query that table without typing the schema name each time.

Exhibit:

SELECT employee_id, last_name
FROM hr.employees;

Which statement is the best next step to let SALESAPP reference the object with a shorter name in future queries?

Options:

• A. CREATE INDEX emp ON hr.employees(employee_id);

• B. CREATE SEQUENCE emp START WITH 1;

• C. CREATE VIEW emp AS SELECT * FROM hr.employees;

• D. CREATE SYNONYM emp FOR hr.employees;

Best answer: D

Explanation: A synonym is the Oracle object used to provide an alternate name for a table or view. Because SALESAPP already has object access, creating a synonym lets future queries use the shorter name instead of HR.EMPLOYEES.

The core concept is that a synonym is an alias for another schema object, such as a table or view. In this case, SALESAPP wants to stop repeating the fully qualified name HR.EMPLOYEES, so the correct action is to create a synonym that points to that table.

After that, queries can use the synonym name directly, for example FROM emp, as long as the underlying privilege on HR.EMPLOYEES exists. A view would create a separate schema object based on a query, which is not necessary when the only goal is a shorter object reference.

The key takeaway is that synonyms simplify object naming; they do not replace object privileges.

• View vs alias A view can expose query results, but it is not the simplest tool when the requirement is only a shorter reference name.
• Index purpose An index improves access paths for data retrieval; it does not give an alternate name for a table.
• Sequence purpose A sequence generates numeric values and has nothing to do with referencing an existing table or view.

Question 6

Topic: Manage Indexes, Synonyms, and Sequences

A developer runs this statement:

CREATE INDEX emp_last_name_ix
ON employees(last_name);

What is the best interpretation of this Oracle SQL statement?

Options:

• A. It creates a separate schema object to speed access to employees rows by last_name.

• B. It guarantees that every last_name value in employees is unique.

• C. It moves last_name values out of the table into a new storage structure.

• D. It creates another name for the employees table based on last_name.

Best answer: A

Explanation: CREATE INDEX creates an index object on one or more table columns. Its basic purpose is to help Oracle find rows more efficiently, while the table data itself remains in the table.

An index is a schema object associated with a table, but it is managed separately from the table’s actual row data. In this example, Oracle creates an index on employees(last_name) so queries that search, join, or sometimes sort by last_name can use a faster access path.

The key idea is that creating or dropping a normal index does not move table rows into another table-like structure and does not change the table’s logical data. It also does not imply uniqueness unless the index is explicitly created as UNIQUE or backed by a unique or primary key constraint. A synonym is a different object type used only as an alternate name.

• Data moved out is incorrect because table rows stay in employees; the index is an additional access structure.
• Uniqueness enforced is incorrect because a plain CREATE INDEX statement does not make column values unique.
• Alternate name is incorrect because that describes a synonym, not an index.

Question 7

Topic: Manage Indexes, Synonyms, and Sequences

A developer starts a new session and wants to reuse the current value of orders_seq later with orders_seq.CURRVAL. The first statement in the session must make CURRVAL available and must obtain the next sequence number from the sequence itself.

Which SQL statement is the best choice?

Options:

• A. SELECT orders_seq.NEXTVAL FROM dual;

• B. SELECT MAX(order_id) FROM orders;

• C. SELECT orders_seq.CURRVAL FROM dual;

• D. ALTER SEQUENCE orders_seq INCREMENT BY 1;

Best answer: A

Explanation: In Oracle, CURRVAL is session-specific and cannot be referenced until NEXTVAL has been used at least once in that session. Selecting orders_seq.NEXTVAL from dual both generates the next sequence value and enables later use of orders_seq.CURRVAL.

The core rule is that sequence_name.CURRVAL is undefined in a session until that session has already referenced sequence_name.NEXTVAL. Oracle tracks the current sequence value per session, so a brand-new session has no current value yet.

For this requirement, the correct first step is to call orders_seq.NEXTVAL, typically with:

SELECT orders_seq.NEXTVAL FROM dual;

After that statement runs, the same session can use orders_seq.CURRVAL in later SELECT, INSERT, or other SQL statements to reuse that generated value. Looking up a table maximum or altering the sequence does not establish a session current value. The key takeaway is simple: NEXTVAL must come before CURRVAL in each session.

• Using CURRVAL first fails because a new session has no current sequence value yet.
• Reading MAX(order_id) looks up table data, not the sequence state for the session.
• Altering the sequence changes sequence properties but does not initialize CURRVAL for the session.

Question 8

Topic: Manage Indexes, Synonyms, and Sequences

A sequence named ORD_SEQ already exists. In a newly opened session, which statement about ORD_SEQ.CURRVAL is correct?

Options:

• A. It raises an error until ORD_SEQ.NEXTVAL is referenced once in that session.

• B. It can be referenced only after a COMMIT follows ORD_SEQ.NEXTVAL.

• C. It automatically returns the sequence START WITH value on first reference.

• D. It returns the most recent sequence value generated by any session.

Best answer: A

Explanation: CURRVAL does not have a value in a session until that same session first calls NEXTVAL for the sequence. Oracle tracks the current sequence value per session, so a new session cannot use CURRVAL immediately.

The core rule is that CURRVAL depends on prior use of NEXTVAL in the same session. When a session references NEXTVAL, Oracle increments the sequence and makes that generated value available as CURRVAL for later use in that session. If the session has never called NEXTVAL for that sequence, CURRVAL is undefined and Oracle raises an error.

This behavior is session-specific, not transaction-specific. A COMMIT is not required, and activity in other sessions does not initialize or supply a CURRVAL value for your session. The key takeaway is simple: use NEXTVAL first, then CURRVAL can be referenced in that session.

• Other-session value fails because sequence CURRVAL is not shared as the latest value from any session.
• Commit required fails because sequence pseudocolumn usage is independent of transaction commit.
• Auto-start value fails because first reference to CURRVAL does not initialize it to the START WITH value.

Question 9

Topic: Manage Indexes, Synonyms, and Sequences

Examine the exhibit:

DESC orders
Name        Null?    Type
----------- -------- ---------
ORDER_ID    NOT NULL NUMBER(8)
ORDER_DATE           DATE

SELECT MAX(order_id) FROM orders;

MAX(ORDER_ID)
-------------
         1050

You need to generate numeric values for future INSERT statements into ORDERS without reusing an existing ORDER_ID. Which statement should you execute?

Options:

• A. CREATE SEQUENCE orders_seq START WITH 1051 INCREMENT BY 1 NOCYCLE;

• B. CREATE INDEX orders_seq ON orders(order_id);

• C. CREATE SEQUENCE orders_seq START WITH 1050 INCREMENT BY 1 NOCYCLE;

• D. CREATE SYNONYM orders_seq FOR orders.order_id;

Best answer: A

Explanation: A sequence is the Oracle object used to generate numeric values for later inserts. Because the highest existing ORDER_ID is 1050, the sequence must start at 1051 to avoid duplicating an existing key.

To generate future numeric keys in Oracle SQL, you create a sequence and use its NEXTVAL in INSERT statements. The exhibit shows that ORDER_ID already contains values up to 1050, so the first generated sequence value must be higher than that existing maximum.

A suitable definition is:

• create a sequence object
• set START WITH to 1051
• use INCREMENT BY 1 for consecutive values
• keep NOCYCLE so used values are not reused automatically

After creation, inserts can use orders_seq.NEXTVAL for new rows. The closest distractor also creates a sequence, but starting at 1050 risks generating a value that already exists.

• Starting too low fails because beginning at 1050 can reproduce the current maximum existing key.
• Using a synonym fails because a synonym is only an alternate name for an object; it does not generate numbers.
• Using an index fails because an index improves access paths but does not supply new key values.

Question 10

Topic: Manage Indexes, Synonyms, and Sequences

In one session, a developer wants to test ORDER_SEQ but consume only one new sequence value. Which choice advances the sequence unexpectedly because NEXTVAL is referenced too many times?

Options:

• A. SELECT order_seq.NEXTVAL, order_seq.NEXTVAL FROM dual;

• B. SELECT order_seq.NEXTVAL FROM dual; SELECT order_seq.NEXTVAL FROM dual;

• C. SELECT order_seq.NEXTVAL, order_seq.CURRVAL FROM dual;

• D. SELECT order_seq.NEXTVAL FROM dual; SELECT order_seq.CURRVAL FROM dual;

Best answer: B

Explanation: Only the choice with two separate SELECT ... NEXTVAL statements advances the sequence twice. In Oracle, multiple NEXTVAL or CURRVAL references in the same statement and row reuse the same generated value, but a later statement using NEXTVAL gets the next value.

In Oracle, sequence advancement is based on the statement and returned row, not simply on how many times NEXTVAL appears in one row. Because DUAL returns one row, a single SELECT that includes ORDER_SEQ.NEXTVAL increments the sequence once for that row. Additional NEXTVAL or CURRVAL references in that same statement return the same sequence value for that row.

A separate statement that uses NEXTVAL is a new request for another sequence number, so it increments the sequence again. By contrast, CURRVAL reuses the most recent sequence value already obtained in the same session.

The key rule is that separate statements with NEXTVAL consume separate sequence values.

• The single SELECT with NEXTVAL twice still increments once for the one row from DUAL.
• The single SELECT with NEXTVAL and CURRVAL also increments once and returns the same value in both columns.
• The two-statement version using CURRVAL after NEXTVAL reuses the session’s current value instead of consuming another one.
• The two-statement version using NEXTVAL twice requests a new sequence value in each statement.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.