Hi frnds, these are some of the questions from the Oracle dumps jst check your Knowledge
1) In your organization, you have a single functional database and you are setting up a network connection between the Oracle database server and the Oracle clients. Because there are only a few Oracle clients, you want the clients to use the host naming method to connect to the database server. Which option must you specify in the listener.ora file to enable the listener process to receive connection requests from clients?
A. DB_NAME
B. DB_DOMAIN
C. GLOBAL_DBNAME
D. SERVICE_NAMES
E. LOCAL_LISTENER
A. DB_NAME
B. DB_DOMAIN
C. GLOBAL_DBNAME
D. SERVICE_NAMES
E. LOCAL_LISTENER
2) Identify two features of an offline tablespace. (Choose two.)
A. It automatically becomes online when the database is started.
B. The tablespace becomes online when the database is closed.
C. Users trying to access objects in the tablespace receive an error.
D. The event of bringing back offline tablespace to online is recorded in the control file.
E. The status of an offline tablespace is checked only when the database is mounted or opened.
A. It automatically becomes online when the database is started.
B. The tablespace becomes online when the database is closed.
C. Users trying to access objects in the tablespace receive an error.
D. The event of bringing back offline tablespace to online is recorded in the control file.
E. The status of an offline tablespace is checked only when the database is mounted or opened.
3) In which two scenarios does a checkpoint occur? (Choose two.)
A. when a log switch occurs
B. when a segment is dropped
C. when a tablespace is dropped
D. when a tablespace is taken offline in NORMAL mode
A. when a log switch occurs
B. when a segment is dropped
C. when a tablespace is dropped
D. when a tablespace is taken offline in NORMAL mode
4) You need to change the name of a net service name without changing any domains. Which net configuration file would you edit?
A. names.ora only
B. sqlnet.ora only
C. tnsnames.ora only
D. names.ora and sqlnet.ora
E. tnsnames.ora and sqlnet.ora
A. names.ora only
B. sqlnet.ora only
C. tnsnames.ora only
D. names.ora and sqlnet.ora
E. tnsnames.ora and sqlnet.ora
5) The maximum number of online redo log groups that you can have in a database is dependent on _____.
A. the number of data files
B. the number of control files
C. the MAXLOGFILES clause in the CREATE DATABASE command
D. the MAXLOGMEMBERS clause in the CREATE DATABASE command
A. the number of data files
B. the number of control files
C. the MAXLOGFILES clause in the CREATE DATABASE command
D. the MAXLOGMEMBERS clause in the CREATE DATABASE command
6) You executed the following command to create a database user:
SQL> CREATE USER appsuser IDENTIFIED externally TEMPORARY TABLESPACE tempPROFILE user_profile;
Then, you execute the following command to grant privileges to the user:
SQL> GRANT CREATE SESSION TO appsuser;
Which three statements are true for the user? (Choose three.)
A. The user cannot query any database tables.
B. The user has the SYSTEM tablespace as the default tablespace.
C. The user is authenticated by the operating system or a network service.
D. The user has unlimited quota on all tablespaces including the SYSTEM tablespace.
E. The user is external to the database and can never log in and is only defined to be a schema
owner.
SQL> CREATE USER appsuser IDENTIFIED externally TEMPORARY TABLESPACE tempPROFILE user_profile;
Then, you execute the following command to grant privileges to the user:
SQL> GRANT CREATE SESSION TO appsuser;
Which three statements are true for the user? (Choose three.)
A. The user cannot query any database tables.
B. The user has the SYSTEM tablespace as the default tablespace.
C. The user is authenticated by the operating system or a network service.
D. The user has unlimited quota on all tablespaces including the SYSTEM tablespace.
E. The user is external to the database and can never log in and is only defined to be a schema
owner.
7) SALES is a locally managed, read-only tablespace in your database containing the CUSTOMER table. You decide to drop the CUSTOMER table by using the following command:
DROP TABLE CUSTOMER;
Which statement is true?
A. The command fails because the Oracle server cannot write to the data dictionary.
B. The command fails as the extents cannot be deallocated from a read-only tablespace.
C. The command fails because this DROP TABLE command is a write operation on a read-only
tablespace.
D. The command succeeds and the dropped table segment is changed to a temporary segment.
DROP TABLE CUSTOMER;
Which statement is true?
A. The command fails because the Oracle server cannot write to the data dictionary.
B. The command fails as the extents cannot be deallocated from a read-only tablespace.
C. The command fails because this DROP TABLE command is a write operation on a read-only
tablespace.
D. The command succeeds and the dropped table segment is changed to a temporary segment.
8)In your Oracle9i database, INSTANCE_NAME and SERVICE_NAMES parameters are set. Which statement is correct regarding the service registration with the default local listener?
A. You must manually configure the listener.ora file to enable PMON to register service
information with the listener automatically.
B. By default, the PMON process automatically registers service information with its local listener on the default local address of TCP/IP, port 1521.
C. You must configure the LOCAL_LISTENER parameter in the initialization parameter file to
enable PMON to register service information with the listener automatically.
D. You must set the NAMES.DIRECTORY_PATH = (TNSNAMES,HOSTNAME) parameter in the sqlnet.ora file to enable PMON to register service information with the listener automatically.
A. You must manually configure the listener.ora file to enable PMON to register service
information with the listener automatically.
B. By default, the PMON process automatically registers service information with its local listener on the default local address of TCP/IP, port 1521.
C. You must configure the LOCAL_LISTENER parameter in the initialization parameter file to
enable PMON to register service information with the listener automatically.
D. You must set the NAMES.DIRECTORY_PATH = (TNSNAMES,HOSTNAME) parameter in the sqlnet.ora file to enable PMON to register service information with the listener automatically.
9) You execute the following command to create an index on the EMPLOYEE_NAME column of the EMPLOYEES table:
SQL> CREATE UNIQUE INDEX hr.employee_nm_index
ON hr.employees (employee_name)
NOLOGGING
NOSORT;
Which two statements are true regarding the index? (Choose three.)
A. The index creation does not update the data dictionary.
B. The Oracle server does not sort the rows while creating the index.
C. The statement fails if the values in the EMPLOYEE_NAME column are not sorted.
D. The modifications of the index branch and leaf blocks are not logged in the online redo log file.
E. Unique values are stored in the index although the EMPLOYEE_NAME column can contain
duplicate values.
SQL> CREATE UNIQUE INDEX hr.employee_nm_index
ON hr.employees (employee_name)
NOLOGGING
NOSORT;
Which two statements are true regarding the index? (Choose three.)
A. The index creation does not update the data dictionary.
B. The Oracle server does not sort the rows while creating the index.
C. The statement fails if the values in the EMPLOYEE_NAME column are not sorted.
D. The modifications of the index branch and leaf blocks are not logged in the online redo log file.
E. Unique values are stored in the index although the EMPLOYEE_NAME column can contain
duplicate values.
10) What are the two effects of the following command to drop a table? (Choose two.)
SQL> DROP TABLE oe.orders_history CASCADE CONSTRAINTS;
A. The dependent tables are truncated.
B. The dependent tables are dropped along with the ORDERS_HISTORY table.
C. The extents are released and can be used by other segments in the tablespace.
D. The contents are moved to the undo segments, thus enabling the user to recover the table.
E. The referential integrity constraints that refer to primary and unique keys in the
ORDERS_HISTORY table are dropped.
SQL> DROP TABLE oe.orders_history CASCADE CONSTRAINTS;
A. The dependent tables are truncated.
B. The dependent tables are dropped along with the ORDERS_HISTORY table.
C. The extents are released and can be used by other segments in the tablespace.
D. The contents are moved to the undo segments, thus enabling the user to recover the table.
E. The referential integrity constraints that refer to primary and unique keys in the
ORDERS_HISTORY table are dropped.
11) Which three statements are true while querying DBA_TABLES, ALL_TABLES, and
USER_TABLES views? (Choose three.)
A. The USER_TABLES view lists the tables that belong to the current user.
B. The DBA_TABLES view lists the tables that belong to all schemas in the database.
C. The ALL_TABLES view lists the tables that belong to and are accessible to the current user.
D. The USER_TABLES view lists the tables that belong to and are accessible to the current user. E. The DBA_TABLES view lists the fixed tables (X$ tables) along with the tables that belong to all schemas in the database.
F. The ALL_TABLES view lists the tables that can be accessed by the current user but not the
tables owned by the user.
USER_TABLES views? (Choose three.)
A. The USER_TABLES view lists the tables that belong to the current user.
B. The DBA_TABLES view lists the tables that belong to all schemas in the database.
C. The ALL_TABLES view lists the tables that belong to and are accessible to the current user.
D. The USER_TABLES view lists the tables that belong to and are accessible to the current user. E. The DBA_TABLES view lists the fixed tables (X$ tables) along with the tables that belong to all schemas in the database.
F. The ALL_TABLES view lists the tables that can be accessed by the current user but not the
tables owned by the user.
12) Which two statements are correct regarding the listener process? (Choose two.)
A. Multiple listeners can listen on behalf of a single database.
B. The name of the listener should be unique per listener.ora file.
C. A listener can listen for a single database only and not multiple databases.
D. A listener can be configured to listen for a single protocol only and not multiple protocols.
E. The listener transfers the connection request to the Connection Manager in a Shared Server
configuration.
A. Multiple listeners can listen on behalf of a single database.
B. The name of the listener should be unique per listener.ora file.
C. A listener can listen for a single database only and not multiple databases.
D. A listener can be configured to listen for a single protocol only and not multiple protocols.
E. The listener transfers the connection request to the Connection Manager in a Shared Server
configuration.
13) Which two statements regarding a server process are true? (Choose two.)
A. It is one of the mandatory background processes in an Oracle instance.
B. It starts on the client system when a user establishes a connection to the database instance.
C. It can be used by different user sessions one at a time if the database is in shared server
mode.
D. It starts on the server when a user establishes a dedicated connection to the database
instance.
E. It starts on the client system when the user starts a SQL*Plus session to interact with the
database.
A. It is one of the mandatory background processes in an Oracle instance.
B. It starts on the client system when a user establishes a connection to the database instance.
C. It can be used by different user sessions one at a time if the database is in shared server
mode.
D. It starts on the server when a user establishes a dedicated connection to the database
instance.
E. It starts on the client system when the user starts a SQL*Plus session to interact with the
database.
14) You created a table using the following command:
SQL> CREATE TABLE oe.orders_history
(ord_id NUMBER(4), ord_date DATE, cust_id NUMBER(4), ord_tot NUMBER(11,2)) PCTUSED 40 PCTFREE 30 INITRANS 5 MAXTRANS 10 STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 50 MINEXTENTS 5 MAXEXTENTS 100) TABLESPACE users;
After a month, you realize that the rows in the table are no longer required and you decide to truncate the table by using the following command:
SQL> TRUNCATE TABLE oe.orders_history REUSE STORAGE;
What are the two effects of this command? (Choose two.)
A. No undo data is generated.
B. The delete trigger on the table, if any, is executed.
C. All rows are removed and only one extent is retained.
D. All rows are removed but no extents are deallocated.
E. All rows are removed and five extents are retained due to MINEXTENTS.
(ord_id NUMBER(4), ord_date DATE, cust_id NUMBER(4), ord_tot NUMBER(11,2)) PCTUSED 40 PCTFREE 30 INITRANS 5 MAXTRANS 10 STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 50 MINEXTENTS 5 MAXEXTENTS 100) TABLESPACE users;
After a month, you realize that the rows in the table are no longer required and you decide to truncate the table by using the following command:
SQL> TRUNCATE TABLE oe.orders_history REUSE STORAGE;
What are the two effects of this command? (Choose two.)
A. No undo data is generated.
B. The delete trigger on the table, if any, is executed.
C. All rows are removed and only one extent is retained.
D. All rows are removed but no extents are deallocated.
E. All rows are removed and five extents are retained due to MINEXTENTS.
15) You want to store data in a table that should provide fast key-based access for queries involving exact matches and range searches. Which type of table would you use?
A. clustered table
B. index-organized table
C. regular partitioned table
D. regular table with referential integrity
16) You receive the following error while connecting to an Oracle9i database instance: ORA-12523 TNS:listener could not find instance appropriate for the client connection Which action would be appropriate in the context of investigating the cause of error?
A. checking the listener.ora file to verify that the protocol specified in ADDRESS is supported by the database server
B. checking the sqlnet.ora file to verify that the NAMES.DIRECTORY_PATH =
(TNSNAMES,HOSTNAME)parameter is set
C. executing the lsnrctl services command to verify that the instances are registered with the
listener, and have the READY status.
D. checking the tnsnames.ora file to verify that the net service name specified in your connect
string is mapped to a connect descriptor
A. clustered table
B. index-organized table
C. regular partitioned table
D. regular table with referential integrity
16) You receive the following error while connecting to an Oracle9i database instance: ORA-12523 TNS:listener could not find instance appropriate for the client connection Which action would be appropriate in the context of investigating the cause of error?
A. checking the listener.ora file to verify that the protocol specified in ADDRESS is supported by the database server
B. checking the sqlnet.ora file to verify that the NAMES.DIRECTORY_PATH =
(TNSNAMES,HOSTNAME)parameter is set
C. executing the lsnrctl services command to verify that the instances are registered with the
listener, and have the READY status.
D. checking the tnsnames.ora file to verify that the net service name specified in your connect
string is mapped to a connect descriptor
17) You are using rollback segments in your database. To ease transaction management, you want to implement automatic undo management by using the following steps:
1. creating an undo tablespace
2. setting the UNDO_MANAGEMENT parameter to AUTO in the parameter file
3. changing the tablespace that has the rollback segments to an undo tablespace by using the
ALTER TABLESPACE command
4. setting the UNDO_TABLESPACE parameter to the tablespace that has the rollback segments
5. dropping the rollback segments and create undo segments in the same tablespace that has the
rollback segments
Which option would you use?
A. 3 only
B. 1 and 2 only
C. 2 and 3 only
D. 3 and 5 only
E. 2 and 5 only
1. creating an undo tablespace
2. setting the UNDO_MANAGEMENT parameter to AUTO in the parameter file
3. changing the tablespace that has the rollback segments to an undo tablespace by using the
ALTER TABLESPACE command
4. setting the UNDO_TABLESPACE parameter to the tablespace that has the rollback segments
5. dropping the rollback segments and create undo segments in the same tablespace that has the
rollback segments
Which option would you use?
A. 3 only
B. 1 and 2 only
C. 2 and 3 only
D. 3 and 5 only
E. 2 and 5 only
18) You specified extent management as local for a tablespace. How will this affect space
management in the tablespace?
A. Bitmap will be used to record free and allocated extents.
B. Free extents will be managed by the data dictionary tables.
C. The tablespace can have extents containing blocks of different sizes.
D. The tablespace will be system managed and the users cannot specify the extent size.
19) Which Oracle data type should you use to store data in a database that uses multiple languages to store data?
A. LONG
B. CHAR
C. VARCHAR
D. VARCHAR2
E. NVARCHAR2
management in the tablespace?
A. Bitmap will be used to record free and allocated extents.
B. Free extents will be managed by the data dictionary tables.
C. The tablespace can have extents containing blocks of different sizes.
D. The tablespace will be system managed and the users cannot specify the extent size.
19) Which Oracle data type should you use to store data in a database that uses multiple languages to store data?
A. LONG
B. CHAR
C. VARCHAR
D. VARCHAR2
E. NVARCHAR2
20) Which two statements are true regarding object privileges? (Choose two.)
A. The privileges cannot be granted to a user in the form of a role.
B. The object owner can grant the privileges to any other user in the database.
C. Any user that has the GRANT ANY OBJECT PRIVILEGE can grant the privileges on behalf of the object owner.
D. The grantee of the privileges with WITH GRANT OPTION can revoke the same privileges from any other user irrespective of its grantor.
A. The privileges cannot be granted to a user in the form of a role.
B. The object owner can grant the privileges to any other user in the database.
C. Any user that has the GRANT ANY OBJECT PRIVILEGE can grant the privileges on behalf of the object owner.
D. The grantee of the privileges with WITH GRANT OPTION can revoke the same privileges from any other user irrespective of its grantor.
21) You executed the following command to grant the CONNECT role to the USER01 user:
SQL> GRANT connect to USER01 WITH ADMIN OPTION;Which statement is true?
A. The user can grant the role to any database user but cannot revoke it.
B. The user can grant or revoke the role to or from any database user.
C. The user can revoke the role from any database user but cannot grant it.
D. The statement fails because the CONNECT role cannot be granted with WITH ADMIN
OPTION.
E. The user can grant or revoke the role to or from any database user except the SYS and
SYSTEM users.
SQL> GRANT connect to USER01 WITH ADMIN OPTION;Which statement is true?
A. The user can grant the role to any database user but cannot revoke it.
B. The user can grant or revoke the role to or from any database user.
C. The user can revoke the role from any database user but cannot grant it.
D. The statement fails because the CONNECT role cannot be granted with WITH ADMIN
OPTION.
E. The user can grant or revoke the role to or from any database user except the SYS and
SYSTEM users.
22) Which two statements are true regarding the temporary tablespace? (Choose two.)
A. Used for sort operations
B. Can contain permanent objects
C. Cannot be shared by multiple users
D. Cannot be created with nonstandard block size specification
A. Used for sort operations
B. Can contain permanent objects
C. Cannot be shared by multiple users
D. Cannot be created with nonstandard block size specification
23) You executed the following command to create an index on CUSTOMER_ID and ITEMS_ID
columns in the ORDERS table:
SQL> CREATE INDEX orders_prim_indx
ON orders (customer_id, items_id);
In which two conditions would a query use this index? (Choose two.)
A. when both the columns are used in the WHERE clause of the query
B. when both the columns are used in the SELECT clause of the query
C. when the CUSTOMER_ID column is used in the WHERE clause of the query
D. when the ITEMS_ID column is used with a single row function in the WHERE clause of the
query
columns in the ORDERS table:
SQL> CREATE INDEX orders_prim_indx
ON orders (customer_id, items_id);
In which two conditions would a query use this index? (Choose two.)
A. when both the columns are used in the WHERE clause of the query
B. when both the columns are used in the SELECT clause of the query
C. when the CUSTOMER_ID column is used in the WHERE clause of the query
D. when the ITEMS_ID column is used with a single row function in the WHERE clause of the
query
24) A session in the database should not have access to the uncommitted changes made by other sessions. How does the Oracle server ensure this?
A. by using the redo log buffer
B. by using the undo segment
C. by using the temporary tables
D. by using the online redo log files
E. by using the temporary segment
A. by using the redo log buffer
B. by using the undo segment
C. by using the temporary tables
D. by using the online redo log files
E. by using the temporary segment
25) Identify the metadata that you find in the control file. (Choose three.)
A. table record
B. schema record
C. redo thread record
D. tablespace record
E. archived log record
A. table record
B. schema record
C. redo thread record
D. tablespace record
E. archived log record
26) Your database operates in NOARCHIVELOG mode. What should happen before the log writer (LGWR) can reuse a filled online redo log file?
A. The alert log file should be updated.
B. The control file header should be frozen.
C. The manual checkpoint should be issued.
D. All transactions with entries in the redo log file must complete.
E. The changes recorded in the redo log file must be written to the data files.
A. The alert log file should be updated.
B. The control file header should be frozen.
C. The manual checkpoint should be issued.
D. All transactions with entries in the redo log file must complete.
E. The changes recorded in the redo log file must be written to the data files.
27) Which statement is true regarding a tablespace?
A. The tablespace can contain blocks of different sizes.
B. The tablespace can spread across multiple databases.
C. The tablespace can contain segments of different sizes.
D. The tablespace can contain a part of the non-partitioned segment.
A. The tablespace can contain blocks of different sizes.
B. The tablespace can spread across multiple databases.
C. The tablespace can contain segments of different sizes.
D. The tablespace can contain a part of the non-partitioned segment.
28) In which situation should the control file be backed up?
A. when a checkpoint occurs
B. when you create a new schema
C. when you add a data file to a tablespace
D. when you execute any transactional control command
A. when a checkpoint occurs
B. when you create a new schema
C. when you add a data file to a tablespace
D. when you execute any transactional control command
29) You execute the following command on an index, SALES_INDX:
SQL> ANALYZE INDEX sales_indx VALIDATE STRUCTURE;
Which two statements are true regarding this command? (Choose two.)
A. It displays whether the index is valid.
B. It checks all index blocks for block corruption.
C. It organizes the index to reduce fragmentation.
D. It populates index statistics in the INDEX_STATS view.
E. It verifies whether index entries correspond to the data in the table.
A. It displays whether the index is valid.
B. It checks all index blocks for block corruption.
C. It organizes the index to reduce fragmentation.
D. It populates index statistics in the INDEX_STATS view.
E. It verifies whether index entries correspond to the data in the table.
30) Which three pieces of information are stored in online redo log files that enable an efficient recovery mechanism in the event of an instance failure? (Choose three.)
A. committed transactions
B. changes made to the undo segments
C. structures of the tables being accessed
D. schema and object management commands
E. privileges that users have on the database tables
Answers
1. Answer: C
2. Answer: C, D
3. Answer: A, D
4. Answer: C
5. Answer: C
6. Answer: A, B, C
7. Answer: D
8. Answer: B
9. Answer: B, C, D
10. Answer: C, E
11. Answer: A, B, C
12. Answer: A, B
13. Answer: C, D
14. Answer: A, D
15. Answer: B
16. Answer: C
17. Answer: B
18. Answer: A
19. Answer: E
20. Answer: B, C
21. Answer: B
22. Answer: A, D
23. Answer: A, C
24. Answer: B
25. Answer: C, D, E
26. Answer: E
27. Answer: C
28. Answer: C
29. Answer: B, D
30. Answer: A, B, D
