Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 111:

  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. The value is changed for the current instance and in the PFILE
  C. It fails because the SCOPE clause is missing
  D. Change is applied to the current instance, but does not persist after instance restart
  D. Change is applied to the current instance, but does not persist after instance restart

  ---

  Explanation/Reference:

  The change is applied in memory only, the change does not persist as the database can't write to the pfile

  The default SCOPE option, when you start the instance using a PFILE, is MEMORY (as well as the only scope option you can use with a pfile). If scope is omitted the default scope option is used instead. The SCOPE clause is optional and

  not mandatory when changing the value of a parameter!

  A is wrong, as the pfile is a read only file for the database so it can't write to it. Changes to the pfile have to be done manually by changing it directly using f.e. vi on unix systems.

  B is wrong, same reason as for why answer A is wrong

  C is wrong, as mentioned above, when omitted the scope clause defaults to MEMORY when using a pfile.

  Not related to that question but the more you know: When starting the database using a spfile you have three options for the scope (MEMORY, SPFILE and BOTH). Default value is BOTH.

  Reference: https://docs.oracle.com/database/121/SQLRF/statements_2017.htm#SQLRF00902

• Question 112:

  Evaluate these commands which execute successfully:

  

  Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)

  A. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS
  B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
  C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO
  D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
  E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers
  B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
  C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO

• Question 113:

  Examine the description of the product_details table:

  

  Which two statements are true?

  A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
  B. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
  C. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
  D. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
  E. PRODUCT_NAME cannot contain duplicate values.
  F. EXPIRY_DATE cannot be used in arithmetic expressions.
  A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
  C. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.

  ---

  Explanation/Reference:

• Question 114:

  Which two statements are true about the PMON background process? (Choose two.)

  A. It registers database services with all local and remote listeners known to the database instance.
  B. It rolls back transactions when a process fails.
  C. It records checkpoint information in the control file.
  D. It frees unused temporary segments.
  E. It frees resources held by abnormally terminated processes.
  B. It rolls back transactions when a process fails.
  E. It frees resources held by abnormally terminated processes.

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14220/process.htm

• Question 115:

  Examine the data in the CUST_NAME column of the CUSTOMERS table:

  

  You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? (Choose two.)

  A. WHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER ('MC%')
  B. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'
  C. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') +1) LIKE 'Mc%'
  D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) LIKE 'Mc%'
  E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) IN (MC%', 'Mc%)
  A. WHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER ('MC%')
  D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) LIKE 'Mc%'

  ---

  Explanation/Reference:

• Question 116:

  You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed.

  The variables used in your query are never undefined in your session.

  Which query can be used?

  A. SELECT andandcol1, andandcol2 FROM andtable WHERE andandcondition = andandcond;
  B. SELECT andcol1, andcol2 FROM andandtable WHERE andcondition;
  C. SELECT andcol1, andcol2 FROM "andtable" WHERE andcondition;
  D. SELECT `andandcol1', `andandcol2' FROM andtable WHERE `andandcondition' = `andcond';
  E. SELECT andandcol1, andandcol2 FROM andtable WHERE andandcondition;
  B. SELECT andcol1, andcol2 FROM andandtable WHERE andcondition;

• Question 117:

  Which three statements are true about inner and outer joins? (Choose three.)

  A. A full outer join returns matched and unmatched rows
  B. An inner join returns matched rows
  C. Outer joins can only be used between two tables per query
  D. A full outer join must use Oracle syntax
  E. Outer joins can be used when there are multiple join conditions on two tables
  F. A left or right outer join returns only unmatched rows
  A. A full outer join returns matched and unmatched rows
  B. An inner join returns matched rows
  E. Outer joins can be used when there are multiple join conditions on two tables

  ---

  Explanation/Reference:

  E. Outer joins can be used when there are multiple join conditions on two tables can be like following query:

  - select * from table a left join table1 b on a.col1 = b.col1 and a.col2=b.col2;

  Reference: https://www.studytonight.com/dbms/joining-in-sql.php

• Question 118:

  Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)

  A. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds
  B. SYSDATE can be queried only from the DUAL table
  C. CURRENT_DATE returns the current date and time as per the session time zone
  D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR
  E. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server
  F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE
  C. CURRENT_DATE returns the current date and time as per the session time zone
  F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE

  ---

  Explanation/Reference:

  https://www.w3resource.com/oracle/datetime-functions/oracle-current_timestamp-function.php

• Question 119:

  Which three activities are recorded in the database alert log? (Choose three.)

  A. Data Definition Language (DDL) statements
  B. non-default database parameters
  C. block corruption errors
  D. deadlock errors
  E. session logins and logouts
  B. non-default database parameters
  C. block corruption errors
  D. deadlock errors

  ---

  Explanation/Reference:

  All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur The values of all initialization parameters that had nondefault values at the time the database and instance start

  https://docs.oracle.com/cd/B28359_01/server.111/b28310/monitoring001.htm#ADMIN11247

• Question 120:

  Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

  A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
  B. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
  C. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
  D. Partition key columns cannot be dropped.
  E. A DROP COLUMN command can be rolled back
  F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table
  A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
  D. Partition key columns cannot be dropped.
  F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

  ---

  Explanation/Reference:

  Answer A is RIGHT: Oders is Parent table with PRIMARY KEY ord_no, Order_items is child table which ord_no is REFERENCE KEY that reference ord_no of Parent table, now drop PRIMARY KEY on Orders by command: ALTER TABLE orders DROP COLUMN ORD_NO CASCADE CONSTRAINTS; Answer D is RIGHT: ORA-12984: cannot drop partitioning column Answer F is RIGHT: Unused Columns Count against 1000-column Table Limit Causing ORA-01792 on Compressed Table (Doc ID 2259600.1) and "ORA-01792: Maximum Number Of Columns In A Table Or View Is 1000" AND HIDDEN_COLUMN name is recreated with date and timestamp (Doc ID 2624150.1)