Try 10 focused Oracle 1Z0-071 questions on Data Dictionary Views, with explanations, then continue with IT Mastery.
Open the matching IT Mastery practice page for timed mocks, topic drills, progress tracking, explanations, and full practice.
Try Oracle 1Z0-071 on Web View full Oracle 1Z0-071 practice page
| Field | Detail |
|---|---|
| Exam route | Oracle 1Z0-071 |
| Topic area | Manage Objects with Data Dictionary Views |
| Blueprint weight | 3% |
| Page purpose | Focused sample questions before returning to mixed practice |
Use this page to isolate Manage Objects with Data Dictionary Views for Oracle 1Z0-071. Work through the 10 questions first, then review the explanations and return to mixed practice in IT Mastery.
| Pass | What to do | What to record |
|---|---|---|
| First attempt | Answer without checking the explanation first. | The fact, rule, calculation, or judgment point that controlled your answer. |
| Review | Read the explanation even when you were correct. | Why the best answer is stronger than the closest distractor. |
| Repair | Repeat only missed or uncertain items after a short break. | The pattern behind misses, not the answer letter. |
| Transfer | Return to mixed practice once the topic feels stable. | Whether the same skill holds up when the topic is no longer obvious. |
Blueprint context: 3% of the practice outline. A focused topic score can overstate readiness if you recognize the pattern too quickly, so use it as repair work before timed mixed sets.
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.
Topic: Manage Objects with Data Dictionary Views
A developer queries the data dictionary:
SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = 'SALES_DATA';
The query returns:
OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
HR SALES_DATA TABLE
APP SALES_DATA SYNONYM
Which statement is correct?
Options:
A. Both rows represent objects owned by the current user.
B. SALES_DATA is a table owned by HR and a synonym owned by APP.
C. SALES_DATA must be a view because synonyms can reference only views.
D. ALL_OBJECTS shows duplicate rows because HR granted privileges on SALES_DATA.
Best answer: B
Explanation: The output shows two separate entries for the same object name in ALL_OBJECTS. The OWNER column identifies who owns each object, and OBJECT_TYPE shows that one is a table and the other is a synonym.
ALL_OBJECTS contains one row per accessible object, not one row per privilege. In the result, HR owns an object named SALES_DATA of type TABLE, and APP owns a different object named SALES_DATA of type SYNONYM. This means the same object name can appear more than once in the data dictionary when the owner or object type differs.
A synonym is its own schema object, so an APP synonym and an HR table can legitimately share the same name. The key columns for this interpretation are OWNER and OBJECT_TYPE.
The main takeaway is to read both columns together before deciding whether rows describe the same kind of object.
ALL_OBJECTS; separate objects do.ALL_OBJECTS includes objects accessible to the user, not only objects the user owns.Topic: Manage Objects with Data Dictionary Views
You are logged in as APPUSER. You need one query that returns OBJECT_NAME and OBJECT_TYPE for all tables, views, sequences, and synonyms in schema HR that APPUSER can access. The query must not assume DBA privileges and must sort by object type, then object name. Which query best meets the requirement?
Options:
A. SELECT table_name, object_type FROM all_tables WHERE owner = 'HR' AND object_type IN ('TABLE','VIEW','SEQUENCE','SYNONYM') ORDER BY object_type, table_name
B. SELECT object_name, object_type FROM user_objects WHERE owner = 'HR' AND object_type IN ('TABLE','VIEW','SEQUENCE','SYNONYM') ORDER BY object_type, object_name
C. SELECT object_name, object_type FROM all_objects WHERE owner = 'HR' AND object_type IN ('TABLE','VIEW','SEQUENCE','SYNONYM') ORDER BY object_type, object_name
D. SELECT object_name, object_type FROM dba_objects WHERE owner = 'HR' AND object_type IN ('TABLE','VIEW','SEQUENCE','SYNONYM') ORDER BY object_type, object_name
Best answer: C
Explanation: ALL_OBJECTS is the correct data dictionary view when you need objects in another schema that are accessible to the current user. It includes both OWNER and generic object metadata such as OBJECT_NAME and OBJECT_TYPE, so it can return the requested object categories in one query.
The key concept is choosing the right data dictionary scope. ALL_OBJECTS lists objects that the current user can access across schemas, so it fits a requirement to inspect schema HR while logged in as APPUSER. It also includes the generic columns needed for mixed object lookup: OWNER, OBJECT_NAME, and OBJECT_TYPE.
USER_OBJECTS is limited to the current user’s own schema and does not let you filter another schema by OWNER. DBA_OBJECTS can also show the data, but it requires DBA-level dictionary access, which the stem explicitly rules out. ALL_TABLES is only for tables, so it cannot serve as a single-source query for views, sequences, and synonyms.
When you need accessible objects in another schema without DBA privileges, ALL_OBJECTS is usually the right choice.
USER_OBJECTS fails because it is scoped to the current user’s objects and is not the right view for querying another schema’s available objects.DBA_OBJECTS is unnecessary here and violates the requirement not to assume DBA privileges.ALL_TABLES fails because it only describes tables and does not provide one unified object list for the four requested object types.Topic: Manage Objects with Data Dictionary Views
A developer gets ORA-00942: table or view does not exist when running SELECT * FROM sales_summary;. The object name is believed to be correct, but the developer needs to find which schema owns SALES_SUMMARY and whether it is a table, view, or synonym. The developer does not have DBA privileges.
Which query is the best next step?
Options:
A. SELECT owner, object_type FROM all_objects WHERE object_name = 'SALES_SUMMARY';
B. SELECT owner, object_type FROM dba_objects WHERE object_name = 'SALES_SUMMARY';
C. SELECT object_type FROM user_objects WHERE object_name = 'SALES_SUMMARY';
D. SELECT owner FROM user_tables WHERE table_name = 'SALES_SUMMARY';
Best answer: A
Explanation: The right next step is to query ALL_OBJECTS because it lists objects the user can access and includes both the owning schema and the object type. That makes it the appropriate troubleshooting view when the object might exist outside the current schema and DBA views are unavailable.
This question tests how to use Oracle data dictionary views for object lookup. When you need to identify both who owns an object and what kind of object it is, the view must include OWNER and OBJECT_TYPE. In this case, ALL_OBJECTS is the best fit because it shows objects accessible to the current user across schemas.
USER_OBJECTS is limited to objects in the current schema, so it cannot help if SALES_SUMMARY belongs to another user. USER_TABLES is even narrower because it only describes tables and does not solve the broader “table, view, or synonym” question. DBA_OBJECTS also contains the needed metadata, but it is intended for users with DBA-level dictionary access, which the stem explicitly rules out.
For cross-schema object troubleshooting at SQL Associate depth, ALL_OBJECTS is usually the correct starting point.
USER_OBJECTS can show type, but it cannot identify another schema’s owner.USER_TABLES assumes the object is a table and does not handle views or synonyms.DBA_OBJECTS could work only with broader dictionary privileges, which the scenario says are unavailable.Topic: Manage Objects with Data Dictionary Views
You need to find each column name, data type, data length, and nullability for tables owned by the current user. Which data dictionary view should you query?
Options:
A. USER_TABLES
B. USER_OBJECTS
C. USER_CONSTRAINTS
D. USER_TAB_COLUMNS
Best answer: D
Explanation: USER_TAB_COLUMNS is the correct view for column definitions. It provides column-level metadata for the current user’s objects, including column names, datatypes, lengths, and nullable status.
Oracle data dictionary views are grouped by what kind of metadata they expose. When the requirement is about column definitions, use a column-oriented view. USER_TAB_COLUMNS returns one row per column for tables, views, and clusters owned by the current user, and includes details such as COLUMN_NAME, DATA_TYPE, DATA_LENGTH, and NULLABLE.
By contrast, USER_OBJECTS is for object-level entries, USER_TABLES is for table-level properties, and USER_CONSTRAINTS is for constraint definitions. The key is to match the metadata level in the requirement: object, table, column, or constraint.
USER_OBJECTS helps identify objects and object types, not detailed column definitions.USER_TABLES describes tables themselves, not each column in those tables.USER_CONSTRAINTS is for rules such as primary keys, foreign keys, and check constraints, not datatype and length details.Topic: Manage Objects with Data Dictionary Views
Which Oracle data dictionary view should you query to see the column names, data types, and nullability for tables owned by the current user?
Options:
A. USER_TABLES
B. USER_OBJECTS
C. USER_CONSTRAINTS
D. USER_TAB_COLUMNS
Best answer: D
Explanation: USER_TAB_COLUMNS is the column-level data dictionary view for objects in the current schema. It includes metadata such as COLUMN_NAME, DATA_TYPE, and NULLABLE, which directly matches the requirement in the question.
Oracle data dictionary views are organized by both scope and purpose. For column inspection, you need a view that stores metadata at the column level, not just table or object level. USER_TAB_COLUMNS is the correct choice when you want details about columns in tables owned by the current user, including names, data types, lengths, defaults, and whether a column can contain NULL values.
If you needed the same metadata for accessible tables in other schemas, you would typically use ALL_TAB_COLUMNS; for database-wide access, DBA_TAB_COLUMNS is the broader version. The key distinction here is that the requirement is limited to tables owned by the current user, so the USER_ view is the correct fit.
The main takeaway is to match the metadata level: column details require a *_TAB_COLUMNS view.
USER_TABLES describes tables themselves, not each column within those tables.USER_OBJECTS lists owned objects such as tables and views, but not detailed column definitions.USER_CONSTRAINTS stores constraint information, such as primary key or check constraints, rather than general column data type and nullability details.Topic: Manage Objects with Data Dictionary Views
Which data dictionary view should you query to find both the owner and the object type for database objects that are accessible to your account, without requiring DBA privileges?
Options:
A. ALL_OBJECTS
B. DBA_OBJECTS
C. USER_OBJECTS
D. ALL_TAB_COLUMNS
Best answer: A
Explanation: ALL_OBJECTS is the correct view because it shows objects the current user can access and includes both ownership and object-type metadata. It is the standard choice when you need OWNER and OBJECT_TYPE without relying on DBA-only views.
In Oracle, object-lookup data dictionary views differ mainly by scope. USER_ views show only objects owned by the current user, so owner information is not needed there and USER_OBJECTS does not help identify different owners. DBA_ views show all objects in the database, but they typically require elevated privileges. ALL_OBJECTS sits between those two: it shows objects accessible to the current user and includes metadata such as OWNER, OBJECT_NAME, and OBJECT_TYPE.
So when the requirement is to determine who owns an accessible object and what kind of object it is, without DBA privileges, ALL_OBJECTS is the appropriate view. The closest distractor is USER_OBJECTS, but it is limited to the current user’s own objects.
USER_OBJECTS scope is too narrow because it covers only objects owned by the current user and is not meant for identifying other owners.DBA_OBJECTS privilege is the wrong fit because it can show all database objects, but the stem excludes requiring DBA privileges.ALL_TAB_COLUMNS purpose is different because it describes table and view columns, not general object ownership and object type.Topic: Manage Objects with Data Dictionary Views
A developer is logged in as APPUSER. An accessible object named SALES_SUMMARY might be a table, view, or synonym, and the developer must find which schema owns it and what type of object it is. APPUSER does not have DBA privileges. Which query correctly applies Oracle’s data dictionary rules?
Options:
A. SELECT owner, object_type FROM dba_objects WHERE object_name = 'SALES_SUMMARY';
B. SELECT owner, object_type FROM user_objects WHERE object_name = 'SALES_SUMMARY';
C. SELECT owner, object_type FROM all_objects WHERE object_name = 'SALES_SUMMARY';
D. SELECT owner, table_name FROM all_tables WHERE table_name = 'SALES_SUMMARY';
Best answer: C
Explanation: Use ALL_OBJECTS when you need metadata for objects the current user can access across schemas. It includes both the owning schema in OWNER and the object category in OBJECT_TYPE, which matches the requirement exactly.
Oracle data dictionary views differ by scope. USER_OBJECTS shows only objects owned by the current user and does not provide an OWNER column, so it cannot identify another schema’s ownership. ALL_OBJECTS shows objects accessible to the current user, including objects in other schemas, and includes both OWNER and OBJECT_TYPE. DBA_OBJECTS also includes that metadata, but it is intended for users with broader dictionary access and is excluded by the stated privilege limit.
Because the object might be a table, view, or synonym, a tables-only view is not enough. The correct rule is to use the accessible-objects view that exposes both ownership and type metadata. The closest distractor is the database-wide view, but it depends on privileges the developer does not have.
USER_OBJECTS choice fails because it is limited to the current schema and does not expose an OWNER column.ALL_TABLES choice fails because it only covers tables, so it cannot identify a view or synonym.DBA_OBJECTS choice has the right columns, but it conflicts with the stated lack of DBA privileges.Topic: Manage Objects with Data Dictionary Views
A developer runs the statement below and gets ORA-00942: table or view does not exist.
SELECT COUNT(*) FROM sales_current;
The application guide says SALES_CURRENT should be a synonym, but the developer needs to find the referenced base object and its owner using dictionary views available to non-DBA users. Which approach is the best next step?
Options:
A. Query USER_TABLES for SALES_CURRENT to find the base table owner.
B. Query ALL_SYNONYMS for SALES_CURRENT to see TABLE_OWNER and TABLE_NAME.
C. Query ALL_OBJECTS for SALES_CURRENT to get the synonym target directly.
D. Query USER_OBJECTS for SALES_CURRENT to resolve the referenced object.
Best answer: B
Explanation: Use ALL_SYNONYMS when the troubleshooting task is to resolve a synonym name to the underlying object. It shows the synonym name plus the referenced TABLE_OWNER and TABLE_NAME, which is exactly what the developer needs without requiring DBA-only views.
This symptom points to synonym resolution, not just general object lookup. When you need to discover what a synonym points to, the most direct non-DBA metadata source is ALL_SYNONYMS. It lists synonyms accessible to the current user and includes the target owner and target object name.
For this task, the useful columns are:
OWNERSYNONYM_NAMETABLE_OWNERTABLE_NAMEUSER_TABLES only shows tables owned by the current user, so it cannot resolve a synonym target in another schema. USER_OBJECTS is limited to objects in the current schema, and ALL_OBJECTS can show that a synonym exists but does not directly give the referenced base object mapping. The key takeaway is: use the *_SYNONYMS views when the problem is finding what a synonym references.
USER_TABLES idea fails because it lists only tables owned by the current user, not synonym targets in other schemas.ALL_OBJECTS idea can help confirm object type and visibility, but it does not directly show the synonym’s referenced base object.USER_OBJECTS idea is limited to objects owned by the current schema and does not resolve another schema’s target object through the synonym.Topic: Manage Objects with Data Dictionary Views
You want to verify, before referencing it in a SQL statement, whether the current user owns an object named EMP_VW and that the object is a view. Which query is the most appropriate for a normal user?
Options:
A. SELECT table_name FROM user_tables WHERE table_name = 'EMP_VW'
B. SELECT object_name FROM all_objects WHERE object_name = 'EMP_VW'
C. SELECT object_name FROM user_objects WHERE object_name = 'EMP_VW' AND object_type = 'VIEW'
D. SELECT view_name FROM dba_views WHERE view_name = 'EMP_VW'
Best answer: C
Explanation: USER_OBJECTS is the correct data dictionary view when you need to check objects owned by the current user. It also exposes OBJECT_TYPE, so you can confirm that EMP_VW is specifically a view before referencing it.
The core rule is to choose the data dictionary view that matches both the scope and the metadata you need. USER_OBJECTS contains objects owned by the current user, and it includes columns such as OBJECT_NAME and OBJECT_TYPE. That makes it the most direct way to confirm that an object exists in your schema and that it is the expected object type.
For an unquoted Oracle object name, the dictionary stores the name in uppercase, so checking for 'EMP_VW' is appropriate. A broader view like ALL_OBJECTS can show accessible objects in other schemas, while DBA_* views are not the normal first choice for a standard user.
The key takeaway is to use USER_OBJECTS for current-schema object existence checks when object type also matters.
USER_TABLES fails because the requirement is to confirm a view, not a table.ALL_OBJECTS without filtering by owner or type does not specifically confirm ownership by the current user or that the object is a view.DBA_VIEWS is not the normal choice for a standard user and is broader than needed for a current-schema check.Topic: Manage Objects with Data Dictionary Views
Examine the table definition:
CREATE TABLE projects (
project_id NUMBER(6) CONSTRAINT projects_pk PRIMARY KEY,
project_name VARCHAR2(40) NOT NULL,
dept_id NUMBER(4),
CONSTRAINT projects_dept_fk FOREIGN KEY (dept_id)
REFERENCES departments(department_id)
);
You want to list the constraint names and constraint types defined on PROJECTS in your own schema. Which data dictionary view is the best choice?
Options:
A. USER_TAB_COLUMNS
B. USER_VIEWS
C. USER_CONSTRAINTS
D. USER_OBJECTS
Best answer: C
Explanation: USER_CONSTRAINTS is the correct view because it contains metadata about constraints owned by the current user, including names and types such as primary key and foreign key. The requirement is about constraints, not object lists, column definitions, or view text.
The key concept is matching the metadata requirement to the correct data dictionary view. Here, the requirement is to see constraint information for the PROJECTS table, specifically constraint names and constraint types. Oracle stores that metadata in USER_CONSTRAINTS for objects in the current user’s schema.
Typical matching is:
USER_OBJECTSUSER_TAB_COLUMNSUSER_CONSTRAINTSIf you also needed the columns used by each constraint, you would typically look at USER_CONS_COLUMNS as well. For this requirement, though, the best single view is USER_CONSTRAINTS.
USER_OBJECTS helps identify objects such as tables or indexes, but not detailed constraint types.USER_TAB_COLUMNS is for column names, data types, lengths, and nullability, not constraint definitions.USER_VIEWS applies to view definitions, which is unrelated to table constraint inspection here.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
Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.