Oracle Database Administration I 1Z0-082 exam - Notes vu vơ những thứ đã học

Exam 1Z0-082 Oracle Database Administration I

Oracle Database Administration I 1z0-082

Number of Questions: 72
Passing Score: 60%

1 / 72

1.

Which three files are used by conventional path SQL*Loader when the TABLE option is not specified? (Choose three.)

1. bad files

2. control files

3. password files

4. dump files

5. input files

2 / 72

2.

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?

1. The command succeeds and HR will be connected to the orcl database instance, and the abc.sqlscript will be executed

2. The command fails and reports an error because @ is used twice

3. The command fails because the script must refer to the full path name

4. The command succeeds and HR will be connected to the orcl and abc.sql databases

3 / 72

3.

Which two queries execute successfully?

1. SELECT NULLIF(NULL, 100) FROM DUAL;

2. SELECT 2TOLLIF(100, 'A') FROM DUAL;

3. SELECT COALESCE(100, NULL, 200) FROM DUAL;

4. SELECT COALESCE (100, 'AM FROM DUAL;

5. SELECT NULLIF(100,100) FROM DUAL;

4 / 72

4.

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

1. Duplicates are eliminated automatically by the UNION ALL operator

2. The number of columns selected in each SELECT statement must be identical

3. The names of columns selected in each SELECT statement must be identical

4. NULLS are not ignored during duplicate checking

5. The output is sorted by the UNION ALL operator

5 / 72

5.

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a
promotion time interval.
Examine this SQL statement:

What will be the result?

1. It gives an error because the GROUP BY clause is not valid

2. It executes successfully and gives the required result

3. It gives an error because the ALL keyword is not valid

4. It executes successfully but does not give the required result

6 / 72

6.

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

1. It executes successfully but does not return any result

2. It returns the date for the first Monday of the next month

3. It generates an error

4. It returns the date for the last Monday of the current month

7 / 72

7.

Which two statements are true about the SET VERIFY ON command? (Choose two.)

1. It displays values for variables prefixed with &&

2. It displays values for variables used only in the WHERE clause of a query

3. It can be used in SQL Developer and SQL*Plus

4. It displays values for variables created by the DEFINE command

5. It can be used only in SQL*Plus

8 / 72

8.

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/3/6 14:45:17

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.)

1. Drop other U1 objects in DATA

2. Increase U1's quota sufficiently in DATA

3. Add a data file to DATA

4. Set DEFERRED_SEGMENT_CREATION to TRUE

5. Grant UNLIMITED TABLESPACE to U1

6. Set AUTOEXTEND ON for data files in DATA

9 / 72

9.

In one of your databases, the user HR has the password HRMGR. You want to connect to a database instance whose listener listens on port 1531 by using this statement:

CONNECT HR/HRMGR@orcl

No name server is used.

Which statement is true about ORCL?

1. It must be the name of the database to whose instance HR wishes to connect

2. It must be the name of the server running the database to whose instance HR wishes to connect

3. It must be the value of the SERVICE_NAMES parameter on the client side

4. It must resolve to a valid connect descriptor in the client's tnsnames.ora file

5. It must resolve to a valid connect descriptor in the server's tnsnames.ora file

10 / 72

10.

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)

1. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

2. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

3. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;

4. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;

5. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;

11 / 72

11.

You have been tasked to create a table for a banking application. One of the columns must meet three requirements: Be stored in a format supporting date arithmetic without using conversion functions Store a loan period of up to 10 years Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

1. TIMESTAMP WITH LOCAL TIMEZONE

2. INTERVAL YEAR TO MONTH

3. TIMESTAMP

4. INTERVAL DAY TO SECOND

5. TIMESTAMP WITH TIMEZONE

12 / 72

12.

View the Exhibit and examine the description of the tables. You execute this SQL statement:

Which three statements are true?

1. A customer can exist in many countries.

2. The statement will fall if a row already exists in the sales table for product 23.

3. A product can have a different unit price at different times.

4. The statement will execute successfully and a new row will be inserted into the sales table.

5. The sales table has five foreign keys.

6. The statement will fail because a subquery may not be contained in a values clause.

13 / 72

13.

What is true about non-equijoin statement performance? (Choose two.)

1. The BETWEEN condition always performs better than using the >= and <= conditions

2. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

3. Table aliases can improve performance

4. The BETWEEN condition always performs less well than using the >= and <= conditions

5. The join syntax used makes no difference to performance

14 / 72

14.

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed. Rows exist for employees 100 and 200.

Examine this statement:

Which two statements are true? (Choose two.)

1. Employee 100 will have SALARY set to the same value as the SALARY of employee 200

2. Employees 100 and 200 will have the same JOB_ID as before the update command

3. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

4. Employees 100 and 200 will have the same SALARY as before the update command

5. Employee 200 will have SALARY set to the same value as the SALARY of employee 100

6. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

15 / 72

15.

A database is configured to use automatic undo management with temporary undo enabled.
An UPDATE is executed on a temporary table.

Where is the UNDO stored?

1. in the PGA

2. in the SYSAUX tablespace

3. in the undo tablespace

4. in the temporary tablespace

5. in the SGA

16 / 72

16.

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

1. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

2. The Oracle join syntax supports creation of a Cartesian product of two tables

3. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

4. The SQL:1999 compliant ANSI join syntax supports natural joins

5. The Oracle join syntax only supports right outer joins

6. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables

7. The Oracle join syntax supports natural joins

17 / 72

17.

Which two statements are true about single row functions? (Choose two.)

1. CONCAT : can be used to combine any number of values

2. CEIL : can be used for positive and negative numbers

3. FLOOR : returns the smallest integer greater than or equal to a specified number

4. MOD : returns the quotient of a division operation

5. TRUNC : can be used with NUMBER and DATE values

18 / 72

18.

Which three statements are true about roles?

1. Roles may be granted to roles.

2. The SET ROLE statement can enable one or more roles for a session.

3. The SET ROLE statement can disable one or more roles for a session.

4. Roles must be password protected.

5. Object privileges may not be granted to roles.

6. All roles granted to a user are set on by default when the user logs in,

19 / 72

19.

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.)

1. The sales user must have been granted the CREATE TABLE privilege

2. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of alltables they plan to create in their schema

3. The sales user must have their quota on the users tablespace removed

4. The sales user must have been granted the CREATE SESSION privilege

5. The sales user must have a quota on the TEMP tablespace

6. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be insertedinto any table in their schema

20 / 72

20.

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

1. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

2. A TIMESTAMP data type column contains information about year, month, and day

3. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the timezone of the session that inserted the row

4. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

5. The CURRENT_TIMESTAMP function returns data without time zone information

21 / 72

21.

Examine these commands:

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

1. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations

2. It uses the database buffer cache to load data

3. It generates a sql script that it uses to load data from EMP.DAT to EMP

4. It appends data from EMP.DAT to EMP

5. It overwrites data in EMP with data in EMP.DAT

22 / 72

22.

Which three statements are true about advanced connection options supported by Oracle
Net for connection to Oracle Database instances? (Choose three.)

1. Connect Time Failover requires the connect string to have two or more listener addresses configured

2. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall

3. Source Routing requires the use of a name server

4. Load Balancing requires the use of a name server

5. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration

6. Connect Time Failover requires the use of Transparent Application Failover (TAF)

23 / 72

23.

Evaluate these commands which execute successfully:

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

1. Any user inserting rows into table ORD_ITEMS must have been granted access to sequenceORD_SEQ

2. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted intoORD_ITEMS and no explicit value is given for ORD_NO

3. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

4. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers

5. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL forrows inserted into ORD_ITEMS

24 / 72

24.

Which two statements are true about Enterprise Manager (EM) Express?

1. By default, EM express is available for a database after database creation using DBCA.

2. You can shut down a database instance using EM Express.

3. You cannot start up a database instance using EM Express.

4. EM Express uses a separate repository database to store target database metadata.

5. You can use a single instance of EM Express to manage multiple database running on the same server

25 / 72

25.

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

1. A left or right outer join returns only unmatched rows

2. Outer joins can be used when there are multiple join conditions on two tables

3. An inner join returns matched rows

4. A full outer join must use Oracle syntax

5. A full outer join returns matched and unmatched rows

6. Outer joins can only be used between two tables per query

26 / 72

26.

Which three statements are true about single-row functions? (Choose three.)

1. They can be used only in the WHERE clause of a SELECT statement

2. The argument can be a column name, variable, literal or an expression

3. The data type returned can be different from the data type of the argument

4. They can be nested to any level

5. They return a single result row per table

6. They can accept only one argument

27 / 72

27.

Which three statements are true about using SQL*Plus?

1. It can run Recovery Manager (RMAN) commands.

2. It has both command-line and graphical user interfaces (GUI).

3. It can run scripts entered at the SQL prompt.

4. It can run scripts passed to it by a shell script.

5. It must be downloaded from the Oracle Technology Network (OTN).

6. It has its own commands that are separate from any SQL statements.

28 / 72

28.

Which two statements are true about User Authentication in an Oracle Database?

1. REKOTE_LOGIN_PASSMOREFiLE must be set to exclusive to permit password changes for system- privileged administrative users.

2. Operating System authentication may be used for system-privileged administrative users.

3. Password File authentication must be used for system-privileged administrative users.

4. Password authentication must be used for system-privileged administrative users.

5. Password File authentication is supported for any type of database user.

29 / 72

29.

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?

1. The value is changed only in the PFILE and takes effect at the next instance startup

2. It fails because the SCOPE clause is missing

3. Change is applied to the current instance, but does not persist after instance restart

4. The value is changed for the current instance and in the PFILE

30 / 72

30.

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?

1. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level NULLAND due_amount NULL;

2. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level IS NOT NULLAND due_amount IS NOT NULL;

3. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

4. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level != NULLAND due_amount != NULL;

5. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREIT Certification Guaranteed, The Easy Way!6cust_income_level != NULLAND cust_credit_level !=NULL;

31 / 72

31.

Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

1. A table that is truncated will always have its segment removed

2. An index that is altered to be UNUSABLE will retain its segment

3. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement

4. Private Temporary Tables (PTTS) store metadata in memory only

5. An index created with the UNUSABLE attribute has no segment

32 / 72

32.

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

1. invoice_date = '15-march-2019' : uses implicit conversion

2. CONCAT (qty_sold, invoice_date) : requires explicit conversion

3. qty_sold BETWEEN '101' AND '110' : uses implicit conversion

4. qty_sold = '0554982' uses implicit conversion

5. invoie_date > '01-02-2019' : uses implicit conversion

33 / 72

33.

Which three statements are true about the Automatic Diagnostic Repository (ADR)?

1. It Is held Inside an Oracle database schema.

2. The ADR base is specified In the diagnostic_dest database parameter.

3. It is only used for Oracle Database diagnostic information.

4. It can be used for problem diagnosis of a database when that database's instance is down.

5. It is a file-based repository held outside any database.

34 / 72

34.

Which three functions are performed by dispatchers in a shared server configuration?
(Choose three.)

1. sending each connection input request to the appropriate shared server input queue

2. writing inbound request to the common request queue from all shared server connections

3. checking for outbound shared server responses on the common outbound response queue

4. broadcasting shared server session responses back to requesters on all connections

5. receiving inbound requests from processes using shared server connections

6. sending shared server session responses back to requesters on the appropriate connection

35 / 72

35.

An Oracle database session has an uncommitted transaction in progress which updated 5000
rows in a table.
Which three situations does the transaction complete thereby committing the updates?

1. when the session logs out successfully

2. when a COMMIT statement is issued by the same user from another session in the same database instance

3. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

4. when a CREATE INDEX statement is executed successfully in the same session

5. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

6. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

36 / 72

36.

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

1. It always eliminates all migrated rows if any exist in the table

2. It is not possible to shrink either indexes or Index Organized Tables (IOTs)

3. To shrink a table it must have row movement enabled

4. To shrink a table it must have a PRIMARY KEY constraint

5. To shrink a table it must have a UNIQUE KEY constraint

6. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)

37 / 72

37.

Which two statements are true about the DUAL table? (Choose two.)

1. It can be accessed by any user who has the SELECT privilege in any schema

2. It can be used to display only constants or pseudo columns

3. It can display multiple rows and columns

4. It consists of a single row and single column of VARCHAR2 data type

5. It can be accessed only by the SYS user

6. It can display multiple rows but only a single column

38 / 72

38.

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.)

1. Column positions must be used in the ORDER BY clause

2. Each SELECT statement in the compound query must have its own ORDER BY clause

3. Each SELECT statement in the compound query can have its own ORDER BY clause

4. Only column names from the first SELECT statement in the compound query are recognized

5. The first column in the first SELECT of the compound query with the UNION operator is used bydefault to sort output in the absence of an ORDER BY clause

39 / 72

39.

You must create a tablespace of non-standard block size in a new file system and plan to use this command:

The standard block size is 8k but other non-standard block sizes will also be used. Which two are requirements for this command to succeed?

1. db_32r_cache_si2e must be less than db_ca=he_size.

2. d3_32k_cache_size should be set to a value greater than db_cache_size.

3. db_cache_s:ze must be set to a size that is smaller than db_32k_cache_size.

4. The operating system must use a 32k block size.

5. db_32k_cache_size must be set to a value that can be accommodated in the SGA.

6. The /u02 file system must have at least lOOg space for the datafile.

40 / 72

40.

Examine these SQL statements which execute successfully:

Which two statements are true after execution?

1. The foreign key constraint will be disabled.

2. The primary key constraint will be enabled and deferred.

3. The foreign key constraint will be enabled and immediate.

4. The foreign key constraint will be enabled and deferred.

5. The primary key constraint will be enabled and immediate.

41 / 72

41.

Examine the description of the product_details table:

Which two statements are true?

1. EXPIRY_ DATE contains the sysdate by default if no date is assigned to it.

2. EXPIRY_ DATE cannot be used in arithmetic expressions.

3. PRODUCT _price can be used in an arithmetic expression even if it has no value stored in it.

4. PRODUCT _price contains the value zero by default if no value is assigned to it.

5. PRODUCT_NAKE cannot contain duplicate values.

6. PRODUCT _ic can be assigned the PRIMARY KEY constraint.

42 / 72

42.

Examine this command:

Which two statements are true?

1. Compressed objects in SALES01.DBF will be uncompressed In SALES02.DBF after the move.

2. Tables with one or more extents in this data file may be queried during the execution of thiscommand.

3. The "to" clause containing the new file name must be specified even if Oracle Managed Files(OMF) is used.

4. It overwrites any existing file with the name SALES02.DBF.dbf in /u02 by default.

5. DML may be performed on tables with one or more extents in this data file during the executionof this command.

43 / 72

43.

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?

1. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

2. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

3. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

4. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

44 / 72

44.

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)

1. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;

2. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;

3. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

4. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;

5. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

45 / 72

45.

Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

1. A datafile can be renamed when the database is in MOUNT state and the new file name isdisplayed when querying DBA_DATA_FILES after the database is opened

2. V$TABLESPACE displays information that is contained in the controlfile about tablespaces

3. Tablespace free space can be viewed in DBA_TABLESPACES

4. V$TABLESPACE displays information about tablespaces contained in the data dictionary

5. Tablespace free space can be viewed in V$TABLESPACE

46 / 72

46.

Which two statements are true about the rules of precedence for operators? (Choose two.)

1. Multiple parentheses can be used to override the default precedence of operators in anexpression

2. NULLS influence the precedence of operators in an expression

3. The concatenation operator | | is always evaluated before addition and subtraction in anexpression

4. Arithmetic operators with equal precedence are evaluated from left to right within an expression

5. The + binary operator has the highest precedence in an expression in a SQL statement

47 / 72

47.

Which two statements are true?

1. SALES1 is created with 1 row.

2. SALES1 has primary key and unique constraints on any selected columns which had those constraints in the sales table.

3. SALES1 has not NULL constraints on any selected columns which had those constraints in the sales table.

4. SALES1 is created with no rows.

5. SALES1 is created with 55,000 rows.

48 / 72

48.

Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED RETENTION? (Choose two.)

1. Unexpired UNDO is always retained.IT Certification Guaranteed, The Easy Way!5

2. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.

3. Active UNDO is always retained.

4. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

5. UNDO_RETENTION specifies how long all types of UNDO are retained.

49 / 72

49.

Which two statements are true about trace files produced by the Oracle Database server?
(Choose two.)

1. Trace files are written to the Fast Recovery Area (FRA)

2. They can be written by server processes

3. Trace file names are based on the database name concatenated with a sequential number

4. They can be written by background processes

5. All trace files contain error information that require contacting Oracle Support

50 / 72

50.

Examine the description of the employees table: Which query requires explicit data type conversion?

1. SELECT join_date I I ' ' II salary FROM employees;

2. SELECT join_date + '20' FROM employees; SELECT salary * '120.50' FROM employees;

3. SELECT SUBSTR<join_date, 1, 2) - 10 FROM employees;

4. SELECT join_date FROM employees WHERE join_date > '10-02-2018';

51 / 72

51.

In your data center, Oracle Managed Files (OMF) is used for all databases.
All tablespaces are smallfile tablespaces.
SALES_Q1 is a permanent user-defined tablespace in the SALES database.
Examine this command which is about to be issued by a DBA logged in to the SALES database:
ALTER TABLESPACE sales_q1 ADD DATAFILE;
Which are two actions, either one of which you could take to ensure that the command executes
successfully? (Choose two.)

1. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

2. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

3. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

4. Add the AUTOEXTEND ON clause with NEXT set to 100M.

5. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

52 / 72

52.

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

1. A DROP COLUMN command can be rolled back

2. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

3. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

4. Partition key columns cannot be dropped.

5. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

6. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

53 / 72

53.

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

1. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE

2. Display the minimum PROD_LIST_PRICE for each product status

3. Display suppliers whose PROD_LIST_PRICE is less than 1000

4. Display the total number of products supplied by supplier 102 which have a product status of obsolete

5. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

54 / 72

54.

Which three statements are true about the Automatic Diagnostic Repository (ADR)?

1. It is a file-based repository held outside any database.

2. It Is held Inside an Oracle database schema.

3. It is only used for Oracle Database diagnostic information.

4. The ADR base is specified In the diagnostic_dest database parameter.

5. It can be used for problem diagnosis of a database when that database's instance is down.

55 / 72

55.

Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

1. Different connection strings in the same tnsnames.ora file can contain the same service name,host and port parameters.

2. A connection string must include the SID of a database Instance.

3. A connection string including a service name must be defined in the cnsnames.ora file.

4. A single connection string can refer to multiple database instances.

5. A single database instance can support connections for multiple service names.

6. A service name is created by a listener.

56 / 72

56.

Which compression method is recommended for Direct-Path Insert operations?

1. RON STORE COMPRESS BASIC

2. ROW STORE COMPRESS ADVANCED

3. COLUMN STORE COMPRESS ADVANCED

4. COLUMN STORE COMPRESS BASIC

57 / 72

57.

In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE. Examine this command: SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB); Which segment or segments, if any, are created as a result of executing the command?

1. T1, an index segment for the primary key, a LOB segment, and a lobindex segment

2. T1, an index segment for the primary key, and a LOB segment only

3. T1 only

4. T1 and an index segment created for the primary key only

5. no segments are created

58 / 72

58.

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?

1. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.

2. Statement 2 returns only one row of output.

3. Statement 1 returns only one row of output.

4. Both the statements give the same output.

5. Statement 2 may return multiple rows of output.

59 / 72

59.

Which three statements are true about undo segments and the use of undo by transactions
in an Oracle database instance? (Choose three.)

1. Undo segments must be stored in a SMALLFILE tablespace

2. Undo segments have a minimum of three extents

3. Undo segments must be stored in a BIGFILE tablespace

4. A single transaction may use multiple undo segments simultaneously

5. Undo segments can extend when a transaction fills the last extent of the undo segment

6. An undo segment may be used by multiple transactions simultaneously

7. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment

60 / 72

60.

Which two statements are true about interval data types?

1. INTERVAL year to month columns only support monthly intervals within a range of years.

2. The value in an interval day to SECOND column can be copied into an interval year to month column.

3. INTERVAL year TO month columns support yearly intervals.

4. INTERVAL day to second columns support fractions of seconds.

5. The year field in an interval year to month column must be a positive value.

6. INTERVAL year to month columns only support monthly Intervals within a single year.

61 / 72

61.

Which two statements are true about Oracle synonyms?

1. A synonym has an object number.

2. Any user can create a PUBLIC synonym.

3. A synonym can be created on an object in a package.

4. All private synonym names must be unique in the database.

5. A synonym can have a synonym.

62 / 72

62.

Your database instance is started with an SPFILE. A PFILE is also available. You execute this command: ALTER SYSTEM SET DB_CACHE_SIZE=100K; Where Is the value changed?

1. only in memory

2. in the SPFILE and SPFILEc

3. in the SPFILE and in memory

4. only In the SPFILE

5. in the SPFILE, SPFILE, and memory

63 / 72

63.

You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? (Choose two.)

1. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;

2. SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;

3. SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;

4. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;

5. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

64 / 72

64.

In the promotions table, the PROMO_BEGIN_DATE column is of data type date and the default date format Is DD-MON-RR. h two statements are true about expressions using PROMO_BEGIN_DATE contained In a query?

1. PROMO_ BEGIN_DATE - sysdate will return an error.

2. TO_date(PROMO_ BEGIN_DATE * 5| will return a date.

3. PROMO_ BEGIN_DATE - 5 will return a date.

4. PROMO_ BEGIN_DATE - sysdate will return a number.

5. to_numberipromo_begin_date) - s will return a number.

65 / 72

65.

Which three statements are true concerning logical and physical database structures? (Choose three.)

1. A segment's blocks can be of different sizes

2. A smallfile tablespace might be bigger than a bigfile tablespace

3. Segments can span multiple tablespaces

4. The extents of a segment must always reside in the same datafile

5. All tablespaces may have one or more data files

6. A segment might have only one extent

7. A segment can span multiple data files in some tablespaces

66 / 72

66.

The stores table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which where clause can be used?

1. WHERE MON'THS_BETWEEN (SYSDATE, start_date) <= 25

2. WHERE MON'THS_BETWEEN (start_date, SYSDATE) <= 25

3. WHERE TO_XUMBER(start_date - SYSDATE) <= 25

4. WHERE ADD_MONTHS<start_date, 25) <= SYSDATE

67 / 72

67.

In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:

1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.
What will happen when the next update is attempted by any transaction?

1. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

2. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

3. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".

4. It succeeds and the generated undo is stored in SYSTEM.

5. It succeeds and the generated undo is stored in SYSAUX.

68 / 72

68.

Which statement is true about aggregate functions?

1. Aggregate functions can be used in any clause of a SELECT statement

2. Aggregate functions can be nested to any number of levels

3. The AVG function implicitly converts NULLS to zero

4. The MAX and MIN functions can be used on columns with character data types

69 / 72

69.

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?

1. SELECT * FROM customers WHERE city LIKE 'D_';

2. SELECT * FROM customers WHERE city = '%D_';

3. SELECT * FROM customers WHERE city = 'D_%';

4. SELECT * FROM customers WHERE city LIKE 'D_%';

70 / 72

70.

Which two statements are true about UNDO and REDO? (Choose two.)

1. DML modifies Oracle database objects and only generates REDO

2. The generation of UNDO generates REDO

3. DML modifies Oracle database objects and generates UNDO and REDO

4. DML modifies Oracle database objects and only generates UNDO

5. The generation of REDO generates UNDO

71 / 72

71.

While one of your databases was in mount state, the datafiles were renamed because they had been moved to a new file system. The database was then opened. Which two statements are true?

1. DBA_DATA_FILES displays the original name for the data files.

2. DBA_DATA_FILES must be resynchronized manually with the control file an order to have itdisplay the new file names.

3. DBA_DATA_FILES displays the new name for the data files.

4. DBA_DATA _FILES displays both the new name and the old name for the data files.

5. V$DATAFILE displays the new names for the data files.

72 / 72

72.

The sales table has columns prod_id and quantity_sold of data type number In two queries
execute successfully?

1. SELECT COUNT|prod_id> FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

2. SELECT prod_id FROM sales WHERE quantity_scld > 55000 AND COUNT(-) > 10 GROUP BY COUNT(-) > 10;

3. SELECT prod_id FROM sales WHERE quantity_sold > 5S000 AND COUNT(-) > 10 GROUP BY prod_id HAVING COUNT(-) > 10;

4. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(-) > 10;

5. SELECT COUNTlprod_id> FROM sales GROUP BY prod_id WHERE quantity_Sold > 55000;

Your score is

0%

Please rate this quiz

Related Posts

[practice] Oracle Database Administration I 1Z0-082

10: Kiến trúc Multitenant trong Oracle

1.1: [thực hành] Cài đặt Database oracle 19c trên ec2 (aws)

9: Flashback query database

8: Undo và Redo trong Oracle

7: Tablespace trong Oracle Database