Data Dictionary Views and Time Zones practice questions

Worked examples for Data Dictionary Views and Time Zones

1. Question 1

   The EMPLOYEES table in your own schema was created exactly as shown, and no constraint has been added or dropped since: ```sql CREATE TABLE employees ( emp_id NUMBER(6) PRIMARY KEY, first_name VARCHAR2(30) NOT NULL, last_name VARCHAR2(30) NOT NULL, salary NUMBER(8, 2), commission NUMBER(4, 2), manager_id NUMBER(6), dept_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ); ``` How many rows does the following query return? ```sql SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEES' ```

   1. A. 2

      Treats NOT NULL as a column attribute rather than a constraint, counting only the primary key and the foreign key. Oracle implements a NOT NULL column as a check constraint, so each one is its own row with CONSTRAINT_TYPE = 'C'.

   2. B. 3

      Assumes a referential constraint is recorded under the parent table it points at, so EMP_DEPT_FK would appear under DEPARTMENTS. A type 'R' constraint is stored under the child table that declares it — the parent is identified by R_CONSTRAINT_NAME.

   3. C. 4Correct answer

      One 'P' row for the system-named primary key, one 'R' row for EMP_DEPT_FK, and one 'C' row for each of the two NOT NULL columns FIRST_NAME and LAST_NAME — four rows in all (Oracle Database Reference, ALL_CONSTRAINTS: CONSTRAINT_TYPE).

   4. D. 5

      Assumes a PRIMARY KEY also generates a separate NOT NULL check constraint for EMP_ID on top of its 'P' row. The primary key enforces mandatory values itself: the column shows NULLABLE = 'N' in USER_TAB_COLUMNS, but no extra 'C' row is created.

   Explanation

   USER_CONSTRAINTS holds one row per constraint defined on a table owned by the current user, and CONSTRAINT_TYPE distinguishes them: 'P' for a primary key, 'U' for unique, 'R' for referential integrity, and 'C' for a check — a category that includes every NOT NULL column, since Oracle implements NOT NULL as a system-named check constraint. A referential constraint is recorded against the child table that declares it, not the parent it references, and a primary key produces a single 'P' row rather than an additional NOT NULL check on its column.

2. Question 2

   A session has already run `ALTER SESSION SET TIME_ZONE = 'America/New_York'`, while the database server's operating system runs in UTC. Which query returns the **session** time zone's UTC offset as a NUMBER of hours (for example, -5) rather than as character data or an error?

   1. A. SELECT SESSIONTIMEZONE FROM dual

      Right source, wrong datatype: SESSIONTIMEZONE returns the session time zone as character data — the region name 'America/New_York' here, or a '±TZH:TZM' string when the zone was set as an offset — never a NUMBER of hours.

   2. B. SELECT EXTRACT(TIMEZONE_HOUR FROM LOCALTIMESTAMP) FROM dual

      Treats LOCALTIMESTAMP as if it carried a time zone. LOCALTIMESTAMP returns datatype TIMESTAMP — session-local wall clock with no time zone stored — so TIMEZONE_HOUR is not a valid extract field for that source and Oracle raises ORA-30076.

   3. C. SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) FROM dualCorrect answer

      CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE expressed in the session time zone, and TIMEZONE_HOUR is a valid extract field only for a source that carries a time zone; extracting it yields the signed offset hours as a NUMBER.

   4. D. SELECT TO_NUMBER(SESSIONTIMEZONE) FROM dual

      Assumes the session time zone is always a numeric-looking offset that TO_NUMBER can convert. SESSIONTIMEZONE yields a region name or a '±TZH:TZM' string — neither is a valid number literal — so TO_NUMBER raises ORA-01722.

   Explanation

   TIMEZONE_HOUR and TIMEZONE_MINUTE can be extracted only from a value whose datatype actually carries a time zone: TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE. CURRENT_TIMESTAMP qualifies and is rendered in the session time zone, so extracting TIMEZONE_HOUR from it gives the session offset as a NUMBER. LOCALTIMESTAMP is a plain TIMESTAMP with the time zone stripped, so the same extraction fails, and SESSIONTIMEZONE — although it does report the session zone — hands back character data (a region name or offset string), not a number.

3. Question 3

   What is the result of executing the following statement? ```sql SELECT DATE '2025-01-31' + INTERVAL '1' MONTH AS result FROM dual; ```

   1. A. The statement returns 28-FEB-2025

      Assumes INTERVAL YEAR TO MONTH arithmetic clamps an overflowing day to the last day of the target month. That rounding is a property of ADD_MONTHS(DATE '2025-01-31', 1), not of + INTERVAL '1' MONTH — interval addition keeps the day-of-month component unchanged and lets the result be validated.

   2. B. The statement returns 02-MAR-2025

      Treats INTERVAL '1' MONTH as a fixed 30 days (31-JAN-2025 + 30 = 02-MAR-2025). Only INTERVAL DAY TO SECOND measures a fixed span; a YEAR TO MONTH interval advances the calendar month field, so month length is never assumed to be 30.

   3. C. The statement fails with ORA-01839: date not valid for month specifiedCorrect answer

      Adding an INTERVAL YEAR TO MONTH increments the month field and leaves the day field at 31, producing 31-FEB-2025. Oracle validates that result, finds no such calendar date, and raises ORA-01839 (SQL Language Reference, Datetime/Interval Arithmetic).

   4. D. The statement fails with ORA-01847: day of month must be between 1 and last day of month

      Confuses the interval-arithmetic error with the conversion error. ORA-01847 is raised when a character string being converted names an impossible day, e.g. TO_DATE('2025-02-31','YYYY-MM-DD'); an invalid date produced by month-interval addition raises ORA-01839 instead.

   Explanation

   Adding an INTERVAL YEAR TO MONTH value to a DATE advances only the year and month fields; the day-of-month is carried over untouched, and the resulting date is then validated. Because the source day is 31 and February 2025 has 28 days, the computed date does not exist and Oracle raises an error rather than adjusting it. ADD_MONTHS behaves differently — it deliberately rounds a last-day-of-month or overflowing day down to the last day of the target month — which is why the two techniques are not interchangeable at month boundaries.

4. Question 4

   An application needs the current date and time **as the session's wall clock**, in a value that carries **no** time zone element — the returned value must show neither a UTC offset nor a region name, and it must shift if the session issues `ALTER SESSION SET TIME_ZONE`. Which query returns exactly that?

   1. A. SELECT SYSTIMESTAMP FROM dual

      SYSTIMESTAMP reads the database server's operating-system clock and returns TIMESTAMP WITH TIME ZONE, so it both displays an offset and ignores ALTER SESSION SET TIME_ZONE — the classic 'SYS* functions follow the session' misconception.

   2. B. SELECT LOCALTIMESTAMP FROM dualCorrect answer

      LOCALTIMESTAMP is evaluated in the session time zone and returns data type TIMESTAMP, which has no time zone element, so the value is the session wall clock with no offset or region shown.

   3. C. SELECT CURRENT_TIMESTAMP FROM dual

      Represents treating CURRENT_TIMESTAMP and LOCALTIMESTAMP as interchangeable. It is evaluated in the session time zone, but its data type is TIMESTAMP WITH TIME ZONE, so the returned value carries and displays the session's offset or region.

   4. D. SELECT SYSDATE FROM dual

      Represents the belief that SYSDATE tracks the session time zone. It returns a DATE from the server's clock: it has no time zone element, but it is the server's wall clock and has only second granularity, so it does not change with ALTER SESSION SET TIME_ZONE.

   Explanation

   Two independent axes separate these functions: which clock they read (server versus session time zone) and whether the returned data type keeps a time zone element. SYSDATE and SYSTIMESTAMP read the database server's OS clock and are unaffected by ALTER SESSION SET TIME_ZONE, while CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP all follow the session time zone. Of the session-based functions, only LOCALTIMESTAMP returns a bare TIMESTAMP; CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE, whose value displays the offset.

5. Question 5

   Which query returns the character string `2024-02-29 08:00:00`?

   1. A. SELECT TO_CHAR(TIMESTAMP '2024-01-31 08:00:00' + INTERVAL '30' DAY, 'YYYY-MM-DD HH24:MI:SS') FROM dual

      Treats one calendar month as a fixed 30 days. INTERVAL '30' DAY adds exactly 30 twenty-four-hour periods, which from 31-JAN-2024 lands on 2024-03-01 08:00:00 in a 29-day February; a day interval never honours month length.

   2. B. SELECT TO_CHAR(ADD_MONTHS(TIMESTAMP '2024-01-31 08:00:00', 1), 'YYYY-MM-DD HH24:MI:SS') FROM dualCorrect answer

      ADD_MONTHS implicitly converts the TIMESTAMP to a DATE, keeping 08:00:00, and — because 31-JAN is the last day of its month and February 2024 is shorter — returns the last day of the target month, giving 2024-02-29 08:00:00.

   3. C. SELECT TO_CHAR(TIMESTAMP '2024-01-31 08:00:00' + INTERVAL '1' MONTH, 'YYYY-MM-DD HH24:MI:SS') FROM dual

      Assumes datetime + INTERVAL YEAR TO MONTH clamps to the last day of the target month the way ADD_MONTHS does. Interval month arithmetic preserves the day-of-month, so it asks for 31-FEB-2024 and the statement fails with ORA-01839 (date not valid for month specified).

   4. D. SELECT TO_CHAR(ADD_MONTHS(DATE '2024-01-31', 1), 'YYYY-MM-DD HH24:MI:SS') FROM dual

      Overlooks that an ANSI DATE literal has no time-of-day component — DATE '2024-01-31' is midnight. ADD_MONTHS clamps the date correctly to 29-FEB-2024 but the result is 2024-02-29 00:00:00, losing the 08:00 the required value carries.

   Explanation

   Adding an INTERVAL YEAR TO MONTH value to a datetime keeps the day-of-month unchanged, so a month-end date such as 31-JAN can produce a day number that does not exist in the target month and the statement raises an error instead of adjusting. ADD_MONTHS applies a different rule: it clamps the result to the last day of the target month while preserving the time of day of its argument. Adding a plain day interval is a third rule again — it advances a fixed number of 24-hour periods and is blind to month length — and an ANSI DATE literal contributes a midnight time component that a TIMESTAMP literal does not.

6. Question 6

   The session has issued `ALTER SESSION SET TIME_ZONE = 'Europe/Paris'`, so `SESSIONTIMEZONE` returns `Europe/Paris` (UTC+1 in January). Note that `America/New_York` observes UTC-5 in January and UTC-4 in July. What single value does the following query return? ```sql SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2024-01-15 09:30:00', 'America/New_York') AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') AS utc_time FROM dual; ```

   1. A. 2024-01-15 14:30Correct answer

      FROM_TZ stamps 09:30 as New York local time, which is UTC-5 on 15 January; AT TIME ZONE 'UTC' then re-expresses that same instant in UTC by adding five hours, giving 14:30. Both endpoints are explicit, so SESSIONTIMEZONE never enters the calculation.

   2. B. 2024-01-15 04:30

      Represents applying the UTC-5 offset in the wrong direction — subtracting it when converting local time to UTC. Going from a zone behind UTC to UTC moves the clock forward, not backward.

   3. C. 2024-01-15 09:30

      Represents the belief that AT TIME ZONE only relabels the time zone element while leaving the date and time fields untouched. AT TIME ZONE preserves the instant and shifts the fields; it is FROM_TZ that attaches a zone without shifting anything.

   4. D. 2024-01-15 13:30

      Represents using the daylight-saving offset UTC-4 for a region that is on standard time. Oracle resolves 'America/New_York' against the date in the value, and 15 January falls in Eastern Standard Time, so the offset is UTC-5.

   Explanation

   FROM_TZ takes a TIMESTAMP and a time zone and produces a TIMESTAMP WITH TIME ZONE whose date and time fields are unchanged — it declares which zone the wall-clock reading belongs to. AT TIME ZONE then converts that value to another zone, preserving the instant and shifting the fields by the difference between the two offsets, and Oracle picks the region's offset according to the date in the value, so a mid-January New York timestamp uses standard time. Because both the source and target zones are named explicitly, the session time zone reported by SESSIONTIMEZONE has no effect on the result; it would matter only if the value were produced by CURRENT_DATE, CURRENT_TIMESTAMP or LOCALTIMESTAMP, or cast without an explicit zone.

7. Question 7

   You are connected as the owner of the EMPLOYEES table. What is the result of executing the following statement? ```sql SELECT owner, table_name FROM user_tables WHERE table_name = 'EMPLOYEES' ```

   1. A. ORA-00942: table or view does not exist

      Assumes the USER_ dictionary views are reserved for privileged accounts and must be qualified (SYS.USER_TABLES) or granted. Every user can select from their own USER_ views by the public synonym, so the view resolves; the failure is in the select list, not the object name.

   2. B. ORA-00904: "OWNER": invalid identifierCorrect answer

      USER_TABLES describes only the tables owned by the current user, so an OWNER column would be redundant: the USER_ views are identical to the corresponding ALL_ views except that they omit OWNER. Referencing a column the view does not have raises ORA-00904 at parse time.

   3. C. ORA-01031: insufficient privileges

      Treats OWNER as a privileged column that only a DBA may project. Column-level privileges are not what fails here — the identifier simply does not exist in the view's definition, which is a parse error rather than a privilege error.

   4. D. The statement executes successfully and returns one row

      Assumes USER_TABLES carries an OWNER column that would repeat the current user's name on every row. OWNER appears in the ALL_ and DBA_ variants, which span multiple schemas; USER_ views are implicitly restricted to one owner and drop the column.

   Explanation

   The dictionary is published in three families: USER_ views describe objects owned by the session's schema, ALL_ views describe objects the session may access (owned or granted), and DBA_ views describe every object in the database. Because a USER_ view is already scoped to one schema, it omits the OWNER column that its ALL_ and DBA_ counterparts carry, so projecting OWNER from it is an unknown identifier and the statement fails to parse.

8. Question 8

   You are connected as the owner of the DEPARTMENTS and EMPLOYEES tables, and those two are the only tables your schema owns. Both were created with unquoted identifiers. Which query returns exactly two rows — DEPARTMENTS and EMPLOYEES — and nothing else, without raising an error?

   1. A. SELECT table_name FROM user_tables WHERE owner = USER

      Assumes USER_TABLES carries an OWNER column. USER_* views are already restricted to the current user's own objects and therefore omit OWNER entirely, so this fails with ORA-00904: "OWNER": invalid identifier — only the ALL_* and DBA_* families expose OWNER.

   2. B. SELECT table_name FROM user_tablesCorrect answer

      USER_TABLES describes exactly the relational tables owned by the current user, so with only DEPARTMENTS and EMPLOYEES in the schema it returns those two rows and needs no OWNER predicate (Oracle Database Reference, USER_TABLES).

   3. C. SELECT table_name FROM user_tables WHERE table_name IN ('departments', 'employees')

      Assumes the dictionary stores object names as they were typed, or that name comparison is case-insensitive. Unquoted identifiers are folded to uppercase when stored, and the comparison against the lowercase literals is case-sensitive, so this returns 0 rows.

   4. D. SELECT table_name FROM all_tables

      Reads ALL_TABLES as "all the tables I own". ALL_* views list every object the user may access — owned plus anything granted, including tables granted to PUBLIC — so this returns a superset with more than the two owned tables.

   Explanation

   The dictionary view families differ by scope, not just by size: USER_* is limited to objects owned by the session user and consequently has no OWNER column, ALL_* covers everything the user can access and does expose OWNER, and DBA_* covers the whole database. Names of unquoted identifiers are stored folded to uppercase, and dictionary string comparison is case-sensitive, so a predicate on TABLE_NAME must use the uppercase form.