Managing Schema Objects and Access practice questions

Worked examples for Managing Schema Objects and Access

1. Question 1

   Every Oracle database contains the public synonym `DUAL`, which points at the table `SYS.DUAL`. Working in a session that owns the HR schema, you want a single query that returns exactly one row whose single value is the name of the schema that owns the *base object* behind that public synonym — that is, the value `SYS`. Which query does that?

   1. A. SELECT table_name FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DUAL'

      Confuses TABLE_NAME with TABLE_OWNER. In ALL_SYNONYMS the base object is described by two separate columns — TABLE_OWNER is its schema, TABLE_NAME is its name — so this returns the object name DUAL, not the owning schema.

   2. B. SELECT table_owner FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DUAL'Correct answer

      A public synonym is owned by the user group PUBLIC, so it is visible in ALL_SYNONYMS with OWNER = 'PUBLIC'; TABLE_OWNER holds the schema of the object the synonym points to, giving the single value SYS.

   3. C. SELECT table_owner FROM user_synonyms WHERE synonym_name = 'DUAL'

      Assumes a public synonym is visible to the current user through USER_SYNONYMS. USER_SYNONYMS lists only private synonyms owned by the current user; a public synonym belongs to PUBLIC, not to you, so this returns no rows.

   4. D. SELECT owner FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DUAL'

      Confuses the owner of the synonym with the owner of the object it names. OWNER describes the synonym itself, so for any public synonym this always returns the literal PUBLIC rather than SYS.

   Explanation

   CREATE PUBLIC SYNONYM creates a synonym in the user group PUBLIC rather than in any user's own schema, so the dictionary records it with OWNER = 'PUBLIC' and it never appears in USER_SYNONYMS, which is restricted to synonyms the current user owns. Within a synonym row, the synonym and its target are described by different column pairs: OWNER/SYNONYM_NAME identify the synonym, while TABLE_OWNER/TABLE_NAME identify the object it resolves to. Selecting the target schema therefore means reading TABLE_OWNER from ALL_SYNONYMS (or DBA_SYNONYMS), not OWNER and not TABLE_NAME.

2. Question 2

   The sequence `emp_id_seq` was created earlier as `CREATE SEQUENCE emp_id_seq START WITH 200 INCREMENT BY 1 NOCACHE;` and several other sessions have already drawn values from it. You then log in, opening a brand-new session, and the statement below is the first reference to `emp_id_seq` that your session makes. What is the result? ```sql SELECT emp_id_seq.CURRVAL FROM dual ```

   1. A. It returns 200, the START WITH value

      Assumes CURRVAL is seeded with START WITH when the sequence is created. START WITH only defines what the first NEXTVAL will return; it never initialises CURRVAL for a session.

   2. B. It returns the highest value generated so far by any session

      Treats CURRVAL as a global 'current value of the sequence'. CURRVAL is session-scoped: it reports the last value that this session obtained from NEXTVAL, and is blind to values other sessions have drawn.

   3. C. ORA-02289

      Reads an unusable CURRVAL as proof the object is missing. ORA-02289 (sequence does not exist) is raised only when the name cannot be resolved; here emp_id_seq exists and is visible, so a different error applies.

   4. D. ORA-08002Correct answer

      CURRVAL is undefined in a session until that session has referenced NEXTVAL at least once, so the statement fails with ORA-08002 (sequence CURRVAL is not yet defined in this session).

   Explanation

   CURRVAL is a per-session pseudocolumn, not a stored 'current value' of the sequence object. Until the calling session has itself evaluated NEXTVAL at least once, the session has no last-generated value to report and any reference to CURRVAL fails. Work done by other sessions, and the sequence's START WITH setting, have no effect on this — only a NEXTVAL in the same session makes CURRVAL usable.

3. Question 3

   A sequence is created as follows: ``` CREATE SEQUENCE dept_seq START WITH 5 INCREMENT BY 3 MINVALUE 2 MAXVALUE 11 CYCLE NOCACHE; ``` A brand-new session, the only session touching this sequence, then issues `SELECT dept_seq.NEXTVAL FROM dual;` four times in a row. What value does the **fourth** call return? ```sql SELECT dept_seq.NEXTVAL FROM dual ```

   1. A. 14

      Represents the belief that INCREMENT BY simply keeps adding past the declared limit, treating MAXVALUE as advisory. 11 + 3 = 14 exceeds MAXVALUE 11, so the sequence never generates it.

   2. B. 2Correct answer

      The first three calls return 5, 8 and 11. The next value would be 14, which exceeds MAXVALUE 11, and because CYCLE is specified an ascending sequence recycles to its MINVALUE — here 2, explicitly declared.

   3. C. 5

      Represents the belief that CYCLE restarts at START WITH. START WITH only seeds the first value ever generated; on wrap-around an ascending sequence restarts at MINVALUE, which is 2, not 5.

   4. D. The statement fails with ORA-08004

      Represents the belief that reaching MAXVALUE always errors. ORA-08004 (sequence exceeds MAXVALUE and cannot be instantiated) is the NOCYCLE behaviour; CYCLE was specified here, so the sequence wraps instead of failing.

   Explanation

   START WITH only supplies the first value the sequence ever generates; each subsequent NEXTVAL adds INCREMENT BY. When an ascending sequence would pass MAXVALUE, the declared cycle option decides what happens: NOCYCLE (the default) raises ORA-08004, while CYCLE makes the sequence recycle to MINVALUE and continue incrementing from there. Because MINVALUE is an independent clause, the recycled value is MINVALUE and not START WITH, so a cycling sequence can generate values it never produced on its first pass.

4. Question 4

   A developer wants to create a composite **unique** index on `employees (dept_id, manager_id)`, but the statement fails because the table already holds duplicate key combinations. Before retrying, they need a report of the offending data: each `dept_id` / `manager_id` combination that occurs in more than one row, together with how many rows it occurs in. Which query produces exactly that report?

   1. A. SELECT DISTINCT dept_id, manager_id FROM employees ORDER BY dept_id, manager_id

      Represents the belief that DISTINCT reports duplicates. DISTINCT collapses them instead: it returns one row per combination — including combinations that occur only once — and no count, so the rows that block the unique index are hidden rather than exposed.

   2. B. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id, manager_id HAVING COUNT(DISTINCT manager_id) > 1 ORDER BY dept_id, manager_id

      Represents the belief that COUNT(DISTINCT col) counts repetitions of a value. Because MANAGER_ID is itself a grouping column, it is constant within every group, so COUNT(DISTINCT manager_id) is never greater than 1 and the query returns no rows at all.

   3. C. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 1 ORDER BY dept_id

      Represents the belief that grouping by the composite key's leading column alone is enough to detect duplicate keys. MANAGER_ID is then neither a grouping expression nor an aggregate, so the statement fails with ORA-00979: not a GROUP BY expression.

   4. D. SELECT dept_id, manager_id, COUNT(*) FROM employees GROUP BY dept_id, manager_id HAVING COUNT(*) > 1 ORDER BY dept_id, manager_idCorrect answer

      Grouping on the full composite key forms one group per candidate index entry, and HAVING COUNT(*) > 1 keeps only the groups holding more than one row — precisely the key values that make CREATE UNIQUE INDEX fail with ORA-01452. (A unique index tolerates repeats only when every key column is NULL, which no group here represents.)

   Explanation

   A unique composite index requires every existing combination of all its key columns to be distinct, so the pre-check must group on the whole key, not on one column of it, and then filter the groups. HAVING is the only clause that can filter on an aggregate such as COUNT(*), because it is applied after rows are collapsed into groups; every non-aggregate expression in the select list must also appear in the GROUP BY clause or the statement will not parse.

5. Question 5

   A synonym only resolves a name — it never confers any privilege on the object behind it. Your session issues the statement below against the name `DEPT`, for which the session can reach no accessible object: either no private synonym, public synonym, or local object of that name resolves at all, or a public synonym `DEPT` exists but the session holds no object privilege on the table it points to. Which ORA- error does the statement raise? ```sql SELECT dept_name FROM dept ```

   1. A. ORA-00904

      Assumes an unresolvable object name is reported the way an unknown column is. ORA-00904 ("invalid identifier") is raised for a column name that does not exist in an object that *did* resolve; name resolution of the object itself fails earlier.

   2. B. ORA-01031

      Assumes that reaching a public synonym whose base object you lack privileges on reports a privilege failure. Oracle deliberately hides the object's existence in that case and reports it as if the object did not exist, so no insufficient-privileges error is raised for a SELECT.

   3. C. ORA-00942Correct answer

      "table or view does not exist" is what Oracle raises both when no synonym or object of that name resolves and when a synonym resolves to an object on which the session has no privilege — a public synonym makes a name referenceable, not readable.

   4. D. ORA-04043

      Assumes a query reports a missing object the same way DESCRIBE does. ORA-04043 ("object does not exist") comes from DESCRIBE and other object-inspection commands, not from name resolution inside a SELECT.

   Explanation

   CREATE PUBLIC SYNONYM affects only name resolution: it lets any user reference an object without a schema qualifier, but access still depends on an object privilege granted on the underlying object, so the synonym alone is never enough. When a name cannot be resolved to an object the session is permitted to see, Oracle does not distinguish "absent" from "present but not granted" — revealing the difference would leak the existence of objects — and reports both as table or view does not exist. Granting SELECT on the base object, not creating another synonym, is what makes such a reference succeed.

6. Question 6

   A composite B-tree index has been created on the EMPLOYEES table: ```sql CREATE INDEX emp_dept_sal_ix ON employees (dept_id, salary); ``` Which query returns exactly the `dept_id` and `salary` of every employee in department 20, ordered by salary, and can be satisfied by `emp_dept_sal_ix` alone — that is, the optimizer can drive an index range scan and needs no access to the table itself?

   1. A. SELECT dept_id, salary, last_name FROM employees WHERE dept_id = 20 ORDER BY salary

      Represents the belief that any query with an indexed predicate can be answered from the index. LAST_NAME is not part of the index key, so the row must be fetched from the table by ROWID; the projection also carries an extra column, so this is not the requested two-column result.

   2. B. SELECT dept_id, salary FROM employees WHERE salary = 6000 ORDER BY salary

      Represents the belief that any key column of a composite index can drive a range scan. A predicate on SALARY alone does not constrain the leading column DEPT_ID, so the index cannot be range-scanned from a start key — and the predicate selects by salary, not by department, so the rows are wrong.

   3. C. SELECT dept_id, salary FROM employees WHERE dept_id = 20 ORDER BY salaryCorrect answer

      The predicate constrains the leading key column, so the optimizer can range-scan the index; every referenced column (DEPT_ID, SALARY) is in the index key, so no table access is needed, and within DEPT_ID = 20 the index entries are already in SALARY order, satisfying the ORDER BY without a sort.

   4. D. SELECT dept_id, salary FROM employees WHERE dept_id = 20 AND salary > 5000 ORDER BY salary

      Represents the belief that a composite index is usable only when every one of its key columns is constrained. The extra SALARY predicate is not required for index access and it discards department 20 employees earning 5000 or less, so the result is narrower than the one asked for.

   Explanation

   A composite B-tree index is stored in the order of its key columns, so the optimizer can range-scan it whenever the leading portion of the key is constrained — a predicate on a trailing column alone gives no start key. When every column a query references appears in the index key, the answer can be read from the index entries and the table is never visited; adding a column that is not in the key reintroduces the table lookup. Because entries within one leading-column value are already sorted by the next key column, such a scan can also satisfy an ORDER BY on that column without a separate sort.

7. Question 7

   No sequence named `EMP_SEQ` exists in the current schema. A developer runs the statement below. What is the result? ```sql CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 10 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20 ```

   1. A. The sequence is created successfully; CACHE 20 is silently reduced to the number of values available in one cycle.

      Assumes Oracle quietly clamps an oversized CACHE to a legal value. CREATE SEQUENCE performs no such adjustment — an illegal CACHE/CYCLE combination is rejected at creation time, so no sequence is created at all.

   2. B. The statement fails with ORA-00922: missing or invalid option, because CYCLE cannot be combined with CACHE.

      Reads CYCLE as requiring NOCACHE. CYCLE and CACHE are independent clauses and are legal together; the only requirement is that the cache be smaller than one cycle, so the failure is semantic, not a syntax error.

   3. C. The statement fails with ORA-04013: number to CACHE must be less than one cycle.Correct answer

      With MINVALUE 1, MAXVALUE 100 and INCREMENT BY 10, one cycle holds only the values 1, 11, 21, … 91 — ten numbers. CREATE SEQUENCE requires the CACHE value to be less than the number of values in a cycle, so CACHE 20 is rejected immediately with ORA-04013.

   4. D. The sequence is created, but the first call to EMP_SEQ.NEXTVAL fails with ORA-08004 because prefetching 20 values overshoots MAXVALUE.

      Assumes the CACHE/CYCLE conflict surfaces only when values are drawn. Oracle validates the CACHE-versus-cycle-size rule while parsing CREATE SEQUENCE, so the error is raised at creation; ORA-08004 belongs to a NOCYCLE sequence that has genuinely run past its limit.

   Explanation

   CACHE tells Oracle how many values to preallocate in memory, and a cycling sequence must be able to hold a whole cache inside one cycle. The number of values in a cycle is derived from MINVALUE, MAXVALUE and INCREMENT BY — here ten values — so any CACHE at or above that count is rejected when the sequence is defined rather than when it is used. Note that this validation is about cache sizing only; even a legal cache still permits gaps, because values sitting in a lost or flushed cache are never reissued.

8. Question 8

   Every Oracle database ships with a public synonym named `DUAL`. You want a query that returns one row giving the **schema and object name that this public synonym resolves to** — that is, the base object behind the alias. Which query returns that row?

   1. A. SELECT table_owner, table_name FROM user_synonyms WHERE synonym_name = 'DUAL'

      Assumes USER_SYNONYMS lists every synonym the session can use. USER_SYNONYMS shows only synonyms owned by the current user; a public synonym is owned by the user group PUBLIC, so this returns no rows.

   2. B. SELECT table_owner, table_name FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DUAL'Correct answer

      CREATE PUBLIC SYNONYM stores the synonym under the owner PUBLIC, and ALL_SYNONYMS.TABLE_OWNER/TABLE_NAME name the base object the synonym translates to — here SYS, DUAL.

   3. C. SELECT owner, synonym_name FROM all_synonyms WHERE table_owner = 'SYS' AND table_name = 'DUAL'

      Reverses the two column pairs: OWNER/SYNONYM_NAME describe the synonym itself, so this returns PUBLIC, DUAL — who owns the alias, not the object it points to.

   4. D. SELECT synonym_name, table_name FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DUAL'

      Confuses SYNONYM_NAME (the alias) with TABLE_OWNER (the schema holding the base object), so it returns DUAL, DUAL and never identifies the owning schema.

   Explanation

   A public synonym is not owned by the schema that owns the underlying object; it is owned by the user group PUBLIC, so the dictionary row has OWNER = 'PUBLIC'. Within a synonym row, OWNER and SYNONYM_NAME describe the alias, while TABLE_OWNER and TABLE_NAME describe the base object the alias translates to. Reading those two pairs in the right direction is what distinguishes a query that reports the target object from one that merely reports the synonym.