oracle 1z0-082 practice test

Oracle Database Administration I Exam

Last exam update: Jun 14 ,2024
Page 1 out of 6. Viewing questions 1-15 out of 96

Question 1

You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), MON), dd Monday for fmMonth rrrr)
What is the result?

  • A. Data block headers contain their own Data Block Address (DBA)
  • A. It executes successfully but does not return any result
  • B. A table row piece can be chained across several database blocks
  • B. It returns the date for the first Monday of the next month
  • C. Multiple row pieces from the same row may be stored in different database blocks
  • C. It generates an error
  • D. Multiple row pieces from the same row may be stored in the same block
  • D. It returns the date for the last Monday of the current month
  • E. Data block free space is always contiguous in the middle of the block
  • F. Index block free space is always contiguous in the middle of the block
Answer:

B

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 2

Which two tasks can you perform using DBCA for databases? (Choose two.)

  • A. CONCAT (qty_sold, invoice_date) : requires explicit conversion
  • A. Configure a nonstandard block size for a new database
  • B. invoice_date = ’15-march-2019’ : uses implicit conversion
  • B. Register a new database with an available Enterprise Manager Management server
  • C. invoie_date > ’01-02-2019’ : uses implicit conversion
  • C. Change the standard block size of an existing database
  • D. qty_sold BETWEEN ‘101’ AND ’110’ : uses implicit conversion
  • D. Configure incremental backups for a new database
  • E. qty_sold = ‘0554982’ uses implicit conversion
  • E. Enable flashback database for an existing database
Answer:

AB

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%

Explanation:
Reference:
https://docs.oracle.com/cd/B16254_01/doc/server.102/b14196/install003.htm

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 3

Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

  • A. The sales user must have a quota on the TEMP tablespace
  • A. The Oracle join syntax supports creation of a Cartesian product of two tables
  • B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
  • B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
  • C. The sales user must have been granted the CREATE SESSION privilege
  • C. The SQL:1999 compliant ANSI join syntax supports natural joins
  • D. The sales user must have their quota on the users tablespace removed
  • D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
  • E. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema
  • E. The Oracle join syntax only supports right outer joins
  • F. The sales user must have been granted the CREATE TABLE privilege
  • F. The Oracle join syntax supports natural joins
  • G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax
Answer:

ACD

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
F
50%
G
50%

Explanation:
https://oracle-base.com/articles/9i/ansi-iso-sql-support

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
F
G
0 / 1000

Question 4

Which three statements are true about external tables in Oracle 18c and later releases? (Choose
three.)

  • A. A table can have only one primary key but multiple foreign keys
  • A. External table files can be used for other external tables in a different database
  • B. A table can have only one primary key and one foreign key
  • B. The ORACLE_LOADER access driver can be used to unload data from a database into an external table
  • C. The foreign key columns and parent table primary key columns must have the same names
  • C. The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table
  • D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
  • D. They cannot be partitioned
  • E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
  • E. The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table
  • F. Only the primary key can be defined at the column and table level
  • F. They support UPDATEs but not INSERTs and DELETEs
  • G. Primary key and foreign key constraints can be defined at both the column and table level
Answer:

A,B,C

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
F
50%
G
50%

Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-F6948F0E-0557-4C42-9145-1897DE974CC3

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
F
G
0 / 1000

Question 5

Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session:
INSERT INTO books VALUES (ADV112, Adventures of Tom Sawyer, NULL, NULL);
SAVEPOINT a;
DELETE FROM books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true? (Choose two.)

  • A. It is not possible to shrink either indexes or Index Organized Tables (IOTs)
  • A. The second ROLLBACK command does nothing
  • B. It always eliminates all migrated rows if any exist in the table
  • B. The second ROLLBACK command replays the delete
  • C. To shrink a table it must have a PRIMARY KEY constraint
  • C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
  • D. To shrink a table it must have a UNIQUE KEY constraint
  • D. The second ROLLBACK command undoes the insert
  • E. To shrink a table it must have row movement enabled
  • E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row
  • F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)
Answer:

CD

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 6

Which three statements are true about table data storage in an Oracle Database? (Choose three.)

  • A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE
  • A. Data block headers contain their own Data Block Address (DBA)
  • B. Display suppliers whose PROD_LIST_PRICE is less than 1000
  • B. A table row piece can be chained across several database blocks
  • C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
  • C. Multiple row pieces from the same row may be stored in different database blocks
  • D. Display the total number of products supplied by supplier 102 which have a product status of obsolete
  • D. Multiple row pieces from the same row may be stored in the same block
  • E. Display the minimum PROD_LIST_PRICE for each product status
  • E. Data block free space is always contiguous in the middle of the block
  • F. Index block free space is always contiguous in the middle of the block
Answer:

ABC

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%

Explanation:
Reference:
https://antognini.ch/2016/10/whats-the-difference-between-row-migration-and-row-chaining/

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 7

The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary
tablespace.
Segment creation is not deferred.
You execute this command:

Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose
three.)

  • A. Add a data file to DATA
  • A. The sales user must have a quota on the TEMP tablespace
  • B. Drop other U1 objects in DATA
  • B. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
  • C. Increase U1’s quota sufficiently in DATA
  • C. The sales user must have been granted the CREATE SESSION privilege
  • D. Set DEFERRED_SEGMENT_CREATION to TRUE
  • D. The sales user must have their quota on the users tablespace removed
  • E. Grant UNLIMITED TABLESPACE to U1
  • E. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema
  • F. Set AUTOEXTEND ON for data files in DATA
  • F. The sales user must have been granted the CREATE TABLE privilege
Answer:

B,D,F

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
F
0 / 1000

Question 8

View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks require subqueries? (Choose two.)

  • A. Oracle Managed Files (OMF)
  • A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE
  • B. Online table segment shrink
  • B. Display suppliers whose PROD_LIST_PRICE is less than 1000
  • C. Online index segment shrink
  • C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
  • D. Automatic data file extension (AUTOEXTEND)
  • D. Display the total number of products supplied by supplier 102 which have a product status of obsolete
  • E. Capacity planning growth reports based on historical data in the Automatic Workload Repository (AWR)
  • E. Display the minimum PROD_LIST_PRICE for each product status
Answer:

A,C

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 9

Which three statements are true about Oracle synonyms? (Choose three.)

  • A. Table aliases can improve performance
  • A. A synonym cannot be created for a PL/SQL package
  • B. The BETWEEN condition always performs better than using the >= and <= conditions
  • B. A synonym can be available to all users
  • C. The join syntax used makes no difference to performance
  • C. A SEQUENCE can have a synonym
  • D. The BETWEEN condition always performs less well than using the >= and <= conditions
  • D. A synonym created by one user can refer to an object belonging to another user
  • E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
  • E. Any user can drop a PUBLIC synonym
Answer:

B,C,D

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%

Explanation:
Reference:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 10

Which three statements are true regarding indexes? (Choose three.)

  • A. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;
  • A. A UNIQUE index can be altered to be non-unique
  • B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;
  • B. A SELECT statement can access one or more indices without accessing any tables
  • C. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
  • C. A table belonging to one user can have an index that belongs to a different user
  • D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
  • D. An update to a table can result in updates to any or all of the table’s indexes
  • E. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
  • F. An update to a table can result in no updates to any of the table’s indexes
Answer:

A,C,E

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%

Explanation:
https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/indexes.htm
https://docs.oracle.com/cd/B10501_01/server.920/a96521/indexes.htm

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 11

A script abc.sql must be executed to perform a job.
A database user HR, who is defined in this database, executes this command:
$ sqlplus hr/hr@orcl @abc.sql
What will happen upon execution?

  • A. Private Temporary Tables (PTTS) store metadata in memory only
  • A. The command succeeds and HR will be connected to the orcl and abc.sql databases
  • B. An index created with the UNUSABLE attribute has no segment
  • B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed
  • C. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement
  • C. The command fails because the script must refer to the full path name
  • D. An index that is altered to be UNUSABLE will retain its segment A table that is truncated will always have its segment removed
  • D. The command fails and reports an error because @ is used twice
Answer:

B

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
0 / 1000

Question 12

Examine these commands:

Which two statements are true about the sqlldr execution? (Choose two.)

  • A. Set OS_AUTHENT_PREFIX to OPS$
  • A. It overwrites data in EMP with data in EMP.DAT
  • B. Have the OS administrator add KING to the OSDBA group
  • B. It uses the database buffer cache to load data
  • C. Grant DBA to KING
  • C. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations
  • D. Unset REMOTE_LOGIN_PASSWORDFILE
  • D. It generates a sql script that it uses to load data from EMP.DAT to EMP
  • E. Alter user KING to be IDENTIFIED EXTERNALLY
  • E. It appends data from EMP.DAT to EMP
Answer:

CE

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 13

Which two are true about shrinking a segment online? (Choose two.)

  • A. Data Definition Language (DDL) statements
  • A. It is not possible to shrink either indexes or Index Organized Tables (IOTs)
  • B. non-default database parameters
  • B. It always eliminates all migrated rows if any exist in the table
  • C. block corruption errors
  • C. To shrink a table it must have a PRIMARY KEY constraint
  • D. deadlock errors
  • D. To shrink a table it must have a UNIQUE KEY constraint
  • E. session logins and logouts
  • E. To shrink a table it must have row movement enabled
  • F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)
Answer:

EF

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%

Explanation:
Reference:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 14

Your database instance was shut down normally and then started in NOMOUNT state. You then
execute this command:
ALTER DATABASE MOUNT;
Which two actions are performed? (Choose two.)

  • A. Add the SEGMENT SPACE MANAGEMENT AUTO clause
  • A. The online redo logs are opened
  • B. Set UNDO_TABLESPACE to UNDOTBS01
  • B. The online data files are opened
  • C. Add the NOLOGGING clause
  • C. The alert log records the execution details
  • D. Make certain that the database operates in automatic undo management mode
  • D. The Oracle background processes are started
  • E. Add the ONLINE clause
  • E. The initialization parameter file is read
  • F. The control file is read
Answer:

C,F

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%

Explanation:
http://facedba.blogspot.com/2014/07/oracle-database-startup-stages-and.html

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 15

Which statement is true about aggregate functions?

  • A. The online redo logs are opened
  • A. Aggregate functions can be nested to any number of levels
  • B. The online data files are opened
  • B. The AVG function implicitly converts NULLS to zero
  • C. The alert log records the execution details
  • C. Aggregate functions can be used in any clause of a SELECT statement
  • D. The Oracle background processes are started
  • D. The MAX and MIN functions can be used on columns with character data types
  • E. The initialization parameter file is read
  • F. The control file is read
Answer:

D

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000
To page 2