1 . Which two Oracle database space management features require the use of locally managed tablespaces?
A) Online segment shrink
B) Automatic data file extension (AUTOEXTEND)
C) Oracle Managed Files (OMF)
D) Free space management with bitmaps.
E) Server-generated tablespace space alerts.
2 . Examine the description of the CUSTOMERS table:
You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
SELECT cust_last_name, cust_credit_limit FROM customers
Which two WHERE conditions give the required result?
A. WHERE UPPER(cust_last_name) IN (‘AX’, ‘B%’)
AND cust_credit_limit < 1000;B. WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR UPPER(cust_last_name) LIKE ‘B%’)
AND ROUND(cust_credit_limit) < 1000;C. WHERE UPPER(cust_last_name) BETWEEN UPPER(‘A%’ AND ‘B%’)
AND ROUND(cust_credit_limit) < 1000;D. WHERE (INITCAP(cust_last_name) LIKE ‘A%’ OR INITCAP(cust_last_name) LIKE ‘B%’)
AND cust_credit_limit < 1000;E. WHERE (UPPER(cust_last_name) LIKE INITCAP(‘A’) OR UPPER(cust_last_name) LIKE INITCAP(‘B’))
AND ROUND(cust_credit_limit) < ROUND(1000);
3 . Which two statements are true about in an Oracle Database?
A. Table segments always have two or more extents.
B. Temporary segments are only stored in a temporary tablespace.
C. Undo segments are only stored in an undo tablespace.
D. Cluster segments may contain data from multiple tables.
E. Index segments always have two or more extents.
4. Examine the description of the PRODUCT_INFORMATION table:
Which query retrieves the number of products with a null list price?
A. SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;
B. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
C. SELECT COUNT(DISTINCT list price) FROM product_information WHERE list_price IS NULL;
D. SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;
5. Which two statements are true about a self join? (Choose two.)
A. It can be an inner join.
B. It can be a left outer join.
C. It must be an equijoin.
D. The join key column must have an index.
E. It must be a full outer join.
6 . Which three statements are true about a self join? (Choose three.)
A. The ON clause must be used
B. The query must use two different aliases for the table
C. It must be an equijoin
D. It must be an inner join
E. The ON clause can be used
F. It can be an outer join
7 . Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?
A) the first column in the last SELECT of the compound query.
B) the first column in the first SELECT of the compound query.
C) the first VARCHAR2 column in the first SELECT of the compound query
D) the first NUMBER OF VARCHAR2 column in the last SELECT of the compound query E) the first NUMBER column in the first SELECT of the compound query
8 . Examine this command: SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT Which two statements are true?
A) Dependent indexes become UNUSABLE
B) The high-water mark (HWM) of ORDERS is adjusted
C) The SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERS segment
D) The SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERS segment
E) Queries and DML statements are allowed on ORDERS while the SHRINK is executing
F) Only queries are allowed on ORDERS while the SHRINK is executing
9. Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
A) A table can have only one primary key but multiple foreign keys
B) A table can have only one primary key and one foreign key
C) The foreign key columns and parent table primary key columns must have the same names 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
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.
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
10 . Which three statements are true about sequences in a single instance Oracle database? (Choose three.)
A) A sequence can issue duplicate values
B) A sequence's unallocated cached value are lost if the instance shuts down
C) Sequences can always have gaps
D) Two or more tables cannot have keys generated from the same sequence
E) A sequence can only be dropped by a DBA
F) A sequence number that was allocated can be rolled back if a transaction fails
11. Which two statements are true about the configuration and use of UNDO_RETENTION with no GUATANTEED RETENTION?
A) UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.
B) UNDO_RETENTION specifies how long all types of UNDO are retained.
C) Unexpired UNDO is always retained.
D) Active UNDO is always retained.
E) UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
12 . Examine the description of the SALES1 table:
SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 and SALE2 and wish to see duplicates too Which set operator generates the required output?
(A). INTERSECT
(B). UNION ALL
(C). UNION
(D). SUBTRACT
(E). MINUS
13 . Which two statements are true about INTERVAL data types?
A) INTERVAL DAY TO SECOND columns support fractions of seconds.
B) INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
C) INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
D) INTERVAL YEAR TO MONTH columns support yearly intervals.
E) The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value. F) The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
14. Which two statements are true about the Oracle join and ANSI join syntax?
A) The Oracle join syntax lacks the ability to do outer joins.
B) The Oracle join syntax perform better than the SQL:1999 compliant ANSI join syntax.
C) The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax. D) The Oracle join syntax supports creation of a Catesian product of two tables.
E) The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian prodcut of two tables.
15. Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
A. Full outer-join with self-join
B. Subquery
C. Left outer-join with self-join
D. Self-join
E. Right outer-join with self-join
16 . Which two tasks can you perform using DBCA for databases?
A) Register a new database with an available Enterprise Manager Management server.
B) Change the standard block size of an existing database.
C) Configure incremental backups for a new database.
D) Configure a nonstandard block size for a new database
. E) Enable flashback database for an existing database.
17 . Which three statements are true about UNDO and REDO?
A . REDO is used for read consistency
B. REDO is used for instance recovery
C . Both REDO and UNDO can be multiplexed
D . UNDO is used for some flashback operations
E. UNDO is used for read consistency
F. REDO is used rollback
18 Which two statements are true about User Authentication in an Oracle Database?
A) Password File authentication must be used for system-privileged administrative users
B) Password File authentication is supported for any type of database user
C) Operation System authentication may be used for system-privileged administrative users
D) Password authentication must be used for system-privileged administrative users
E) REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users
19. which two tasks can be performed in the nomount state
A.creating a database
B.re-creating controlfile
20. Which three statements are true about the DESCRIBE command? (Choose three.)
A) It displays the PRIMARY KEY constraint for any column or columns that have that constraint
B) It can be used from SQL Developer
C) It displays the NOT NULL constraint for any columns that have that constraint
D) It can be used to display the structure of an existing view
E) It displays all constraints that are defined for each column
F) It can be used only from SQL*Plus
21. The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?
A) SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') from customers;
B) SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;
C) SELECT INITCAP(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O')) FROM customers;
D) SELECT REPLACE(TRIM(TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers;
22. Which two statements are true about the Oracle Data Dictionary? (Choose two.)
A) Data dictionary base tables can be queried directly
B) All data dictionary view join base tables to dynamic performance views
. C) It is owned by the SYSTEM user
D) It is owned by the SYS user
E) Data Dictionary Views are always created with queries that join two or more base tables.
23 . Examine the description of the EMPLOYEES table: Name Null? Type ============================ ========== ======================= EMP_ID NOT NULL NUMBER EMP_NAME VARCHAR2(10) DEPT_ID NUMBER(2) SALARY NUMBER(8,2) JOIN_DATE DATE NLS_DATE_FORMAT is set to DD-MON-YY Which query requires explicit data type conversion?
A) SELECT SUBSTR(join_date, 1, 2) – 10 FROM employees;
B) SELECT salary + ‘120.50’ FROM employees;
C) SELECT join_date || ‘ ‘ || salary FROM employees;
D) SELECT join_date + ‘20’ FROM employees;
E) SELECT join_date FROM employees WHERE join_date > ’10-02-2018’;
24. The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NfUMBER Which two queries execute successfully?
A) SELECT prod_id FROM sales WHERE quantity_sold > 55000 and COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10;
B) SELECT prod_id FROM sales WHERE quantity_sold > 55000 and COUNT(*) > 10 GROUP BY COUNT(*) >10;
C) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10;
D) SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
E) SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
25 . Which three statements are true about using SQL*Plus?
A) It can run Recovery Manager (RMAN) commands
B) It has both command-line and graphical user interfaces (GUI)
C) It can run scripts passed to it by a shell script
D) It can run scripts entered at the SQL prompt
E) It has its own commands that are separate from any SQL statements
F) It must be downloaded from the Oracle Technology Network (OTN)
26 . Which two queries execute successfully?
A) SELECT COALESCE (100, NULL, 200) FROM DUAL;
B) SELECT NULLIF(100, 100) FROM DUAL;
C) SELECT NULLIF(NULL, 100) FROM DUAL;
D) SELECT COALESCE(100, 'A') FROM DUAL;
E) SELECT NULLIF(100, 'A') FROM DUAL;
27 . Which compression method is recommended for Direct-Path Insert operations
A) ROW STORE COMPRESS ADVANCED
B) COLUMN STORE COMPRESS ADVANCED
C) ROW STORE COMPRESS BASIC
D) COLUMN STORE COMPRESS BASIC
28 . Which three statements are true about Deferred Segment Creation in Oracle databases? A) Indexes inherit the DEFERRED or IMMEDIATE segment creation attribute from their parent table
B) Sessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment creation
C) It is the default behavior for tables and indexes
D) It is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces
E) It is supported for SYS-owned tables contained in locally managed tablespaces
29 . Examine the description of the PRODUCT_DETAILS table:
A) PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
B) PRODUCT_NAME cannot contain duplicate values.
C) PRODUCT_ID can be assigned the PRIMARY KEY constraint.
D) PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
E) EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
F) EXPIRY_DATE cannot be used in arithmetic expressions.
30 . Which two statements are true about substitution variables?
A) A substitution variable prefixed with & always prompts only once for a value in a session B) A substitution variable can be used only in a SELECT statement
C) A substitution variable used to prompt for a column name must be enclosed in single quotation marks
D) A substitution variable can be used with any clause in a SELECT statement
E) A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
F) A substitution variable used to prompt for a column name must be enclosed in double quotation marks
31 . Examine this query:
Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.)
A. Use the DEFINE command before executing the query. ' with '&&1' in the query.
C. Use the UNDEFINE command before executing the query.
D. Execute the SET VERIFY OFF command before executing the query.
E. Execute the SET VERIFY ON command before executing the query.
F. Store the query in a script and pass the substitution value to the script when executing it
32 . Which two statements are true about undo and undo tablespaces?
(A). There can be only one undo tablespace created in a database.
B). An instance will crash if the active undo tablespace is lost.
(C). An undo tablespace may be owned by only one instance.
(D). undo segments are owned by SYSTEM.
(E). undo segments are owned by SYSBACKUP.
33 . Which three statements are true regarding single row subqueries?
A) A SQL statement may have multiple single row subquery blocks.
B) They must return a row to prevent errors in the SQL statement.
C) They can be used in the HAVING clause.
D) They must be placed on the right side of the comparison operator or condition.
E) They must be placed on the left side of the comparison operator or condition
F) They can be used in the WHERE clause.
34 . Which statement is true about database links?
A) Private database link creation requires the same user to exist in both the local and the remote databases.
B) A database link can be created only between two Oracle databases.
C) A public database link can be created only by SYS.
D) A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.
E) A database link created in a database allows a connection from that database’s instance to the target database’s instance, but not vice versa.
35 . In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
A) TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.
B) PROMO_BEGIN_DATE – SYSDATE will return a number.
C) PROMO_BEGIN_DATE – 5 will return a date.
D) PROMO_BEGIN_DATE – SYSDATE will return an error.
E) TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
36 . 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). Dynamic service registration cannot be used for this database instance
(B). The LREG process registers services dynamically with the LISTENER_1 listener
(C). LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
(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
37 . Which three statements are true about multiple row subqueries? •
A. They can contain GROUP BY clauses. •
B. They can return multiple columns. •
C. Two or more values are always returned from the subquery. •
D. They can contain HAVING clauses.
E. They cannot contain a subquery.
38 . 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 frees resources held by abnormally terminated processes
C) It records checkpoint information in the control file
D) It frees unused temporary segments
E) It rolls back transaction when a process fails
39 Which two statements are true about Oracle synonyms?
A) A synonym has an object number
B) Any user can create a PUBLIC synonym
C) A synonym can be created on an object in a packag
e D) All private synonym names must be unique in the database
E) A synonym can have a synonym.
40 Which two are benefits of external tables?
A) They can be queried while the database is in the MOUNT state like dynamic performance views.
B) They support DELETE which transparently deletes records in the file system as if they were table rows.
C) They can be queried, transformed, and joined with other tables without having to load the data first.
D) They support UPDATES which transparently updates records in the file system as if they were table rows.
E) The results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.
41. Which three statements are true about data block storage in an Oracle Database?
(A). A block header contains a row directory pointing to all rows in the block.
(B). An index block can contain row data.
(C). Row data is stored starting at the end of the block.
(D). A data block header is of a fixed length.
(E). A table block must always contain row data
42 . two about full outer join
A It include row that are return by an inner join
B It returned match and unmatch row from both table being join
43 . Which two statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose two.)
A) Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment
B) Undo segments can extend when a transaction fills the last extent of the undo segment
C) A single transaction may use multiple undo segments simultaneously
D) Undo segments can be stored in the SYSTEM tablespace
E) Undo segments can be stored in the SYSAUX tablespace
44 two about expdp operation
It create a master table to store detail of the export operation
It create dumpfile for entire database
45 . Examine the description of the PROMOTIONS table:
A) SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
B) SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
C) SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
D) SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2
; E) SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
46 which three statements are true about Oracle Managed Files(OMF_
If only DB_CREATE_ONLINE_LOG_DEST_1 is specified , only redo logs are control files are Oracle managed
If DB_RECOVERY_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not , the redo logs and control files are placed in DB_RECOVERY_FILE_DEST default
If only DB_CREATE_FILE_DEST is specified, only datafiles and temp files are Oracle managed
47 . 9 Which two statements are true about Enterprise Manager (EM) Express?
(A). You can use a single instance of EM Express to manage multiple database running on the same server.
(B). EM Express uses a separate repository database to store target database metadata.
(C). By default, EM express is available for a database after database creation using DBCA.
(D). You can shut down a database instance using EM Express.
(E). You cannot start up a database instance using EM Express.
48 Which two statements are true about Enterprise Manager Database Express?
A) It is available only when the database is open
B) It can be used to perform database recovery when database is mounted
C) The same port number can be used for Database Express configurations for databases on different hosts
D) It can be used to switch a database into ARCHIVELOGMODE
E) The same port number can be used for multiple Database Express configurations for multiple databases on the same host
F) It can be used to perform database recovery when database is open
49 . 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 1 returns only one row of output.
B) Statement 2 returns only one row of output.
C) Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
D) Both the statements give the same output.
E) Statement 2 may return multiple rows of output.
50 Examine this description of the TRANSACTIONS table: Which two SQL statements execute successfully? (Choose two.)
A) SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
B) SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
C) SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;
D) SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;
E) SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions;
51 . Which two statements are true about single row functions? (Choose two.)
A. MOD : returns the quotient of a division operation
B. FLOOR : returns the smallest integer greater than or equal to a specified number
C. TRUNC : can be used with NUMBER and DATE values
D. CONCAT : can be used to combine any number of values
E. CEIL : can be used for positive and negative numbers
52 . You must create a tablespace of non-standard block size in a new file system and plan to use the command:
A) DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
B) DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.
C) DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
D) DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
E) The /u02 file system must have at least 100g space for the datafile.
F) The operating system must use a 32k block size.
53 . Which three statements are true about connection strings and service names used to connect to an Oracle database instance?
A) A connection string must include the SID of a database instance.
B) A connection string including a service name must be defined in the tnsnames.ora file.
C) Different connection strings in the same tnsnames.ora file can contain the same service, host and port parameters.
D) A single database instance can support connections for multiple service names.
E) A single connection string can refer to multiple database instances.
F) A service name is created by a listener.
54 . which three statements are true about enterprise manager cloud control
It provides management for Oracle middleware
It is intergrated with My Oracle Support
It provides management for Oracle-engineered systems
55 . Which three statements are true about the Automatic Diagnostic Repository (ADR)?
A) It is only used for Oracle Database diagnostic Information.
B) It is held inside an Oracle database schema.
C) It is a file-based repository held outside any database.
D) It can be used for problem diagnosis of a database when that database’s instance is down.
E) The ADR base is specified in the DIAGNOSTIC_DEST database parameter.
56 Examine the description of the BOOKS_TRANSACTIONS table: Name Null? Type =========================================== =========== ========================== TRANSACTION_ID NOT NULL VARCHAR2(6) TRANSACTION_TYPE VARCHAR2(3) BORROWED_DATE DATE BOOK_ID VARCHAR2(6) MEMBER_ID VARCHAR2(6) Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? A) WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);
B) WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = ‘A101’ OR member_id = ‘A102’));
C) WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’,‘A102’));
D) WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’,‘A102’);
E) WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’,‘A102’);
57 . What is true about non-equijoin statement performance?
A) The BETWEEN condition used with an non-equijoin always performs better than when using the >= and <= conditions.
B) The BETWEEN condition used with an non-equijoin sometimes performs better than using the >= and <= conditions.
C) The join syntax used makes no difference to performance.
D) The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
E) The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax
58 Examine the description of the PRODUCTS table:
Which query is valid?
A. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date; Most Voted
C. SELECT prod_id, AVG(MAX(cost)) FROM products GROUP BY prod_id;
D. SELECT prod_id, MAX(AVG(cost)) FROM products GROUP BY prod_id
59 . Examine the description PRODUCTS table:
Examine the description of the NEW_PRODUCTS table:
Which two queries execute successfully?
A. SELECT prod_id FROM products -
UNION ALL -
SELECT prod_id, prod_name FROM new_products;B. SELECT prod_id, exp_date FROM products
UNION ALL -
SELECT prod_id, NULL FROM new_products; Most VotedC. SELECT * FROM products -
MINUS -
SELECT prod_id, FROM new_products;D. SELECT prod_id, prod_name FROM products
INTERSECT -
SELECT 100, prod_name FROM new_products;E. SELECT * FROM products -
UNION -
SELECT * FROM new_products; Most Voted
60 Which three actions are ways to apply the principle of least privilege?
A) setting the 07_DICTIONARY_ACCESSBILITY parameter to true
B) revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the PUBLIC user
C) revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the SYSTEM user
D) enabling Unified Auditing
E) setting the REMOTE_OS_AUTHENT parameter to true
F) using Access Control Lists (ACLs)
61 . . 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?
A) Only in memory
B) In the SPFILE and PFILE
C) Only in the SPFILE
D) In the SPFILE and in memory
E) In the SPFILE, PFILE, and memory
62 . Which three statements are true about views in an Oracle Database? (Choose three.)
A. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
B. Views have no segment. Most Voted
C. Views have no object number.
D. Views can join tables only if they belong to the same schema.
E. A view can be created that refers to a non-existent table in its defining query. Most Voted
F. Rows inserted into a table using a view are retained in the table if the view is dropped. Most Voted
63 Examine these statements executed in a single Oracle session:
Which three statements are true? (Choose three.)
A. The code for pen is 10. Most Voted
B. There is no row containing fountain pen.
C. There is no row containing pen.
D. The code for fountain pen is 3. Most Voted
E. The code for pen is l.
F. There is no row containing pencil. Most V
64 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? A) Revoke the CREATE SESSION privilege from other users
B) Revoke the CREATE SESSION privilege from user HR
C) Log in to the database instance
D) Grant the CREATE SESSION privilege with ADMIN OPTION to other users
E) Execute DDL statements in the HR schema
F) Execute DML statements in the HR schema
65 Which three are benefits of using temp UNDO when perfoming DML on global temporary tables?
A . It reduces the amout of UNDO stored in the UNDO tablespace
B . It reduces I/Os to the SYSTEM tablespace
C . It permits DML on Global temporary tables even if the database is opened read only
D . It reduces the amount of redo generated
E . It reduces I/Os to the SYSAUX tablespace
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?
A) WHERE TO_NUMBER(start_date – SYSDATE) <= 25
B) WHERE ADD_MONTHS(start_date, 25) <= SYSDATE
C) WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
D) WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
67 Which three statements are true about the naming methods and their features supported by Oracle database used to resolve connection information? (Choose three.)
A. Local Naming requires setting the TNS_ADMIN environment variable on the client side.
B. A client can connect to an Oracle database instance even if no client side network admin has been configured. Most Voted
C. Directory Naming can be used if Connect-Time Failover is required. Most Voted
D. Easy Connect supports TCP/IP and SSL.
E. Local naming can be used if Connect-Time Failover is required. Most Voted
F. Directory Naming requires setting the TNS_ADMIN environment variable on the client side.
68 . Which two statements are true about the DUAL table? (Choose two.)
A) It can be accessed only by the SYS user
B) It consists of a single row and single column of VARCHAR2 data type
C) It can display multiple rows but only a single column
D) It can be used to display only constants or pseudo columns
E) It can be accessed by any user who has the SELECT privilege in any schema
F) It can display multiple rows and columns
69 . . 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.)
A) SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') – '01-JAN-2019' FROM DUAL;
B) SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
C) SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;
D) SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
E) SELECT SYSDATE - TO_DATE ('01-JAN-2019') FROM DUAL;
70 . Which three statements are true about indexes and their adminstration in Oracle database ?
A . A descending index is a type of function-based index
B . A drop index statement always prevents updates to the table during the drop operation
C . A unique and non-unique index can be created on the same table column
D . If a query filter on and indexed column then it always be used during execution of the query
E. An Invisible index is not maintained when Data Manipulation Language(DML) is perfomed on its underlying table
F . An index can be created as part of create table statement
71 . You issued this command:
DROP TABLE hr.employees;
Which three statements are true? (Choose three.)
A. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
B. Synonyms for HR.EMPLOYEES are dropped.
C. Views referencing HR.EMPLOYEES are dropped.
D. All constraints defined on HR.EMPLOYEES are dropped. Most Voted
E. The HR.EMPLOYEES table may be moved to the recycle bin. Most Voted
F. All indexes defined on HR.EMPLOYEES are dropped Most Vo
72 Which two statements are true about constraints? (Choose two.)
A . A Check constraint can refer to values in other rows.
B . A primary key constraint can only be added to an empty table.
C . A unique constraint can use a pre-existing index on the constrained column or columns
D . A column can have only one Check constraint
E . A foreign key column can contain Null
