[Jan-2022] Oracle 1Z0-082 Exam Practice Test Questions - Actual4Exams
Updated Certification Exam 1Z0-082 Dumps - Practice Test Questions
NEW QUESTION 15
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)
- A. A table can have only one primary key but multiple foreign keys
- B. The foreign key columns and parent table primary key columns must have the same names
- C. 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. 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. A table can have only one primary key and one foreign key
- F. Only the primary key can be defined at the column and table level
- G. Primary key and foreign key constraints can be defined at both the column and table level
Answer: A,B,D,G
NEW QUESTION 16
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)
- A. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
- B. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
- C. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
- D. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
- E. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
Answer: A,E
NEW QUESTION 17
Which two are true about shrinking a segment online? (Choose two.)
- A. It is not possible to shrink either indexes or Index Organized Tables (IOTs)
- B. To shrink a table it must have a PRIMARY KEY constraint
- C. To shrink a table it must have row movement enabled
- D. To shrink a table it must have a UNIQUE KEY constraint
- E. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)
- F. It always eliminates all migrated rows if any exist in the table
Answer: B,C
Explanation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm
NEW QUESTION 18
In one of your databases, you create a user, HR, and then execute this command:
GRANT CREATE SESSION TO hr WITH ADMIN OPTION;
Which three actions can HR perform? (Choose three.)
- A. Revoke the CREATE SESSION privilege from user HR
- B. Log in to the database instance
- C. Revoke the CREATE SESSION privilege from other users
- D. Execute DDL statements in the HR schema
- E. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
- F. Execute DML statements in the HR schema
Answer: C,D,E
Explanation:
https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224
NEW QUESTION 19
The ORCL database has RESUMABLE__TIMEOUT = 7200 and
DEFERRED_SEGMENT_CREATION = FALSE
User U1 has a 1 MB quota in tablespace DATA.
U1 executes this command:
SQL> CREATE TABLE t1 AS
(SELECT object_name, sharing, created
FROM dba_objects);
U1 complains that the command is taking too long to execute.
In the alert log, the database administrator (DBA) finds this:
2017-03-06T12:15:17.183438+05:30
statement in resumable session 'User U1(136), Session 1, Instance 1'
was suspended due to ORA-01536: space quota exceeded for tablespace
'DATA'
Which are three actions any one of which the DBA could take to resume the session? (Choose three.)
- A. Drop other U1 objects in DATA
- B. Grant UNLIMITED TABLESPACE to U1
- C. Increase U1's quota sufficiently in DATA
- D. Set AUTOEXTEND ON for data files in DATA
- E. Set DEFERRED_SEGMENT_CREATION to TRUE
- F. Add a data file to DATA
Answer: C,D,E
NEW QUESTION 20
Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.
Which query can be used?
- A. SELECT * FROM customers WHERE city LIKE 'D_%';
- B. SELECT * FROM customers WHERE city LIKE 'D_';
- C. SELECT * FROM customers WHERE city = '%D_';
- D. SELECT * FROM customers WHERE city = 'D_%';
Answer: A
NEW QUESTION 21
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)
- A. Only column names from the first SELECT statement in the compound query are recognized
- B. Each SELECT statement in the compound query can have its own ORDER BY clause
- C. Each SELECT statement in the compound query must have its own ORDER BY clause
- D. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
- E. Column positions must be used in the ORDER BY clause
Answer: A,B
NEW QUESTION 22
Which two statements are true about the SET VERIFY ON command? (Choose two.)
- A. It displays values for variables used only in the WHERE clause of a query
- B. It can be used only in SQL*Plus
- C. It can be used in SQL Developer and SQL*Plus
- D. It displays values for variables created by the DEFINE command
- E. It displays values for variables prefixed with &&
Answer: C,E
NEW QUESTION 23
Which two are true about RETENTION GUARANTEE? (Choose two.)
- A. It prevents out-of-space errors.
- B. It is a static parameter.
- C. It prevents "Snapshot too old" errors.
- D. It is a tablespace attribute.
- E. It prevents FLASHBACK DATABASE operation failure.
Answer: D,E
NEW QUESTION 24
You want to use table compression suitable for OLTP that will:
* Compress rows for all DML statements on that table
* Minimize the overheads associated with compression
Which compression option is best suited for this?
- A. COLUMN STORE COMPRESS FOR QUERY LOW
- B. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
- C. ROW STORE COMPRESS BASIC
- D. ROW STORE COMPRESS ADVANCED
- E. COLUMN STORE COMPRESS FOR ARCHIVE LOW
Answer: D
NEW QUESTION 25
Which two are true about conventional path SQL load? (Choose two.)
- A. It locks objects being processed.
- B. It always generates redo.
- C. It enforces referential integrity constraints.
- D. It cannot load data into clustered tables.
- E. It does not generate UNDO.
Answer: C,E
NEW QUESTION 26
Which three statements are true about single-row functions? (Choose three.)
- A. They return a single result row per table
- B. The argument can be a column name, variable, literal or an expression
- C. They can accept only one argument
- D. They can be nested to any level
- E. The data type returned can be different from the data type of the argument
- F. They can be used only in the WHERE clause of a SELECT statement
Answer: B,D,E
Explanation:
https://www.tutorialspoint.com/sql_certificate/using_single_row_functions_questions.htm
NEW QUESTION 27
In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1.
The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:
Which statement is true?
- A. The LREG process registers services dynamically with the LISTENER_1 listener
- B. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
- C. Dynamic service registration cannot be used for this database instance
- D. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port
1521 on the same host as the database instances - E. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration
Answer: B
Explanation:
https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG292
NEW QUESTION 28
Examine this command:
CREATE UNDO TABLESPACE undotbs01
DATAFILE `undotbs_01.dbf'
SIZE 100M
AUTOEXTEND ON;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)
- A. Add the ONLINE clause
- B. Set UNDO_TABLESPACE to UNDOTBS01
- C. Add the SEGMENT SPACE MANAGEMENT AUTO clause
- D. Make certain that the database operates in automatic undo management mode
- E. Add the NOLOGGING clause
Answer: B,D
NEW QUESTION 29
In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1.
The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:
Which statement is true?
- A. The LREG process registers services dynamically with the LISTENER_1 listener
- B. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
- C. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
- D. Dynamic service registration cannot be used for this database instance
- E. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration
Answer: B
NEW QUESTION 30
Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)
- A. The lsnrctl utility requires a listener.ora file to exist before it is started
- B. Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server
- C. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target
- D. Oracle Net Manager can be used to centrally configure listeners on any database server target
- E. Oracle Net Manager can be used to locally configure naming methods on a database server
- F. The Oracle Net Configuration Assistant is only used when running the Oracle installer
Answer: B,D,E
NEW QUESTION 31
Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
SELECT MAX(unit_price * quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price * quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which two statements are true?
- A. Statement 2 returns only one row of output.
- B. Both the statements give the same output.
- C. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
- D. Statement 1 returns only one row of output.
- E. Statement 2 may return multiple rows of output.
Answer: D,E
Explanation:
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj27781.html
The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in data type. You can therefore call methods that evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.
NEW QUESTION 32
Which three statements are true about inner and outer joins? (Choose three.)
- A. Outer joins can only be used between two tables per query
- B. An inner join returns matched rows
- C. A full outer join must use Oracle syntax
- D. Outer joins can be used when there are multiple join conditions on two tables
- E. A full outer join returns matched and unmatched rows
- F. A left or right outer join returns only unmatched rows
Answer: B,E,F
Explanation:
https://www.studytonight.com/dbms/joining-in-sql.php
NEW QUESTION 33
Your database instance is started with a PFILE.
Examine these parameters:
You want to increase the size of the buffer cache.
Free memory is available to increase the size of the buffer cache.
You execute the command:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;
What is the outcome?
- A. The value is changed only in the PFILE and takes effect at the next instance startup
- B. Change is applied to the current instance, but does not persist after instance restart
- C. The value is changed for the current instance and in the PFILE
- D. It fails because the SCOPE clause is missing
Answer: B
Explanation:
Explanation/Reference: https://docs.oracle.com/database/121/SQLRF/statements_2017.htm#SQLRF00902
NEW QUESTION 34
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINT can undo a DELETE statement
- B. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
- C. A SAVEPOINT does not issue a COMMIT
- D. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
- E. Only one SAVEPOINT may be issued in a transaction
Answer: A,C
NEW QUESTION 35
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day on the week.
Which query can be used?
- A. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(`MONDAY') FROM employees;
- B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), `MONDAY') FROM employees;
- C. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
- D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
Answer: B
NEW QUESTION 36
In which three situations does a new transaction always start? (Choose three.)
- A. when issuing a DML statement after a DML statement failed in the same session
- B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
- C. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session
- D. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
- E. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
- F. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
Answer: C,D,F
Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F-8293-47B9-ADC1-905BD9C3AF57
NEW QUESTION 37
Examine the description of the CUSTOMERS table:
For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
- A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
- B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level !=NULL;
- C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;
- D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
- E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
Answer: D
NEW QUESTION 38
......
Updated Verified 1Z0-082 dumps Q&As - Pass Guarantee or Full Refund: https://braindumps.actual4exams.com/1Z0-082-real-braindumps.html