QUESTION NO: 16
What is the new Oracle Shared Server?
A. An improved version of multithreaded server configuration.
B. A connection pooling configuration where several clients are connected to the same server process.
C. Two more database servers, which share data by means of database links; the client software is unaware to which server it is connected.
D. A configuration of Real Application Cluster where the client connection is routed to the least busy instance.
Answer: A
Explanation:
Oracle Shared Server is an improved version of multithreaded server configuration.
Incorrect Answers
B: Oracle Shared Server does not provide configuration where several clients are connected to the same server process.
C: Oracle Shared Server does not include two or more database servers, which share data by means of database links.
D: Oracle Shared Server is not a configuration of Real Application Clusters where the client connection is routed to the least busy instance.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 59-60
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 17
What type of protection is implemented when the DBA issues the ALTER DATABASE SET STANDBY DATABASE PROTECTED; command?
A. The standby database is protected against write operations.
B. The primary database is protected against write operations.
C. The primary database is protected against data loss and data divergence.
D. The standby database is protected against data loss and data divergence.
Answer: C
Explanation:
The failure resolution policy specifies what should happen on the primary database if all the standby databases ion the configuration are unable to archive the redo logs. To specify guaranteed protection, you must establish the PROTECTED mode for the primary database. The PROTECTED mode will establish a zero divergence and zero data loss configuration.
Incorrect Answers
A: The standby database is not protected against write operations by this operation. The standby database can be mounted for recovery or open as read-only database.
B: The primary database is not protected against write operations.
D: The primary, not standby, database is protected against data loss and data divergence.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72
Chapter 2: Availability Enhancements
Oracle 9i New Features, Robert Freeman, p. 86-99
Chapter 3: New Oracle9i Availability and Recoverability Features
QUESTION NO: 18
You are attempting to create an Oracle-Managed Files (OMF) tablespace in a production database with the following statement and receive the following error message:
CREATE TABLESPACE tbsl;
ORA-02199: missing DATAFILE/TEMPFILE clause
oerr ora 2199 02199, 00000, “missing DATAFILE/TEMPFILE clause”
*Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause.
*Action: specify DATAFILE/TEMPFILE clause.
What is the corrective action to create the OMF based tablespace?
A. Issue the CREATE TABLESPACE tbsl DATAFILE SIZE 10M; command.
B. Issue the CREATE TABLESPACE tbsl EXTENT MANAGEMENT ORACLE; command.
C. Set the db_create_file_dest parameter using the ALTER SESSION command and reissue the statement.
D. An OMF tablespace is now allowed; only control files and/or redo log files can be created by OMF.
Answer: C
Explanation:
You can set the DB_CREATE_FILE_DEST parameter using the ALTER SESSION command and re-issue the statement. You should define directories for OMF datafiles, redo log files and control files. Oracle9i gives you two new initialization parameters, DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n, to specify the location where Oracle will create and manage OMFs; n can take a value from 1 to 5.
Incorrect Answers
A: This command will not fix the problem.
B: There is no EXTENT MANAGEMENT ORACLE option in the CREATE TABLESPACE command.
D: An OMF tablespace is allowed; datafiles, control files and redo log files can be created by OMF.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 2-12
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 19
Why can setting too low a value for the FAST_START_MTTR_TARGET parameter reduce your overall database performance?
A. Data blocks have to be written more frequently by DBWR.
B. Redo blocks have to be written more frequently by LGWR.
C. You need to substantially increase the size of your database buffer cache to hold the additional checkpoint records.
D. You need to substantially increase the size of your redo buffer cache to hold the additional checkpoint records.
Answer: A
Explanation:
If you set a value for the FAST_START_MTTR_TARGET too low DBWR will write the data blocks more frequently to reduce recovery time in accordance with the FAST_START_MTTR_TARGET parameter. It can cause the overall database performance degradation.
Incorrect Answers
B: It will not make LGWR to write redo blocks more frequently: data blocks will be written more frequently by DBWR.
C: You don’t need to substantially increase the size of your database buffer cache to hold the additional checkpoint records.
D: You don’t need to substantially increase the size of your redo buffer cache to hold the additional checkpoint records.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45
Chapter 2: Availability Enhancements
Oracle 9i New Features, Robert Freeman, p. 73-75
Chapter 3: New Oracle9i Availability and Recoverability Features
QUESTION NO: 20
What is true regarding a shared, server-side parameter file for a Real Application Cluster database?
A. It can contain parameters with distinct values for each distance.
B. It can contain only parameters with identical values for each instance.
C. It must contain an IFILE parameter for each instance’s individual parameter file.
D. It must be located in the default location for the primary instance’s parameter file.
Answer: A
Explanation:
The server-side parameter file for a Real Application Clusters database can contain parameters with distinct values for each instance.
Incorrect Answers
B: It can contain not only parameters with identical values for each instance, but distinct values for each instance.
C: It is not required that it must contain an IFILE parameter for each instance's individual parameter file. It needs to be done when the DBA stores the common parameters in one file and references that common file in each instance by using the IFILE initialization parameter.
D: It can be not located in the default location for the primary instance's parameter file.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 227-230
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 188-192
Chapter 7: Oracle9i Real Application Clusters
Aug 11, 2006
Oracle 1z0-032: Oracle 9i Database Fundamentals-II #3
QUESTION NO: 11
When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?
A. During a query’s parse time only.
B. During a query’s execution time only.
C. During any kind of DML statement execution.
D. During the execution of an INSERT statement only.
Answer: A
Explanation:
During a query's parse time Oracle9i flags an index as being used in the V$OBJECT_USAGE view. You can query the V$OBJECT_USAGE view to review the index utilization data. If the index has been used within the period of time it was monitored, the USED column within this view will contain a YES value; it will contain a NO otherwise.
Incorrect Answers
B: During a query's execution time Oracle9i does not flag an index as being used in the V$OBJECT_USAGE view.
C: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during any kind of DML statement execution.
D: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during execution of an INSERT statement.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 56-57
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 12
Using a bitmap structure to manage database object space has several benefits over using freelist structures. Which three are benefits of managing space using automaticsegment-space managed objects? (Choose three)
A. It readily responds to dynamic changes for concurrent access.
B. It improves performance and space utilization in a multiple-instance environment.
C. It requires fewer space related options then freelist managed structures.
D. It improves performance for SYSTEM tablespaces where a high degree of concurrency is required.
Answer: A, B, D
Explanation:
Managing space with automatic segment-space managed objects can respond to dynamic changes for concurrent access, improves performance and space utilization in a multi-instance environment and improves performance for SYSTEM tablespaces where a high degree of concurrency is required.
Incorrect Answers
C: It requires more space related options than freelist managed structures.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 53-54
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 13
The EMPLOYEES table has six indexes and DML operations are slow. Which command begins monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan?
A. ALTER TABLE employees monitor index employee_idx_fk;
B. ALTER INDEX employee_idx_fk monitoring on;
C. ALTER TABLE employees monitor all indexes;
D. ALTER INDEX employee_idx_fk monitoring usage;
Answer: D
Explanation:
This statement provides correct syntax to start monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan.
Incorrect Answers
A: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR INDEX clause with ALTER TABLE command.
B: This command requires MONITORING USAGE clause to be used, not MONITORING ON clause.
C: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR ALL INDEXES clause with ALTER TABLE command.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 56-59
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 14
You can control instance recover performance by setting parameters to define targets for ______.
A. The size of the redo buffer in SGA.
B. The instance that will perform recovery in Real Application Cluster database.
C. The number of blocks to be read during instance recovery, or the time needed to complete instance recovery.
D. The number of passes through the redo logs made by the recovery process to identify blocks needing recovery.
Answer: C
Explanation:
Oracle9i added the FAST_START_MTTR_TARGET parameter to define targets for the number of blocks to be read during instance recovery, or the time needed to complete instance recovery. Oracle internally uses the FAST_START_MTTR_TARGET parameter value to calculate and set the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters. If you specified the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameter values, they will override the values Oracle calculates from the FAST_START_MTTR_TARGET parameter.
Incorrect Answers
A: You cannot control instance recovery performance by setting parameters to define targets for the size of the redo buffer in the SGA
B: It’s not possible to control instance recovery performance for the instance that will perform recovery in a Real Application Clusters database.
D: The number of passes through the redo logs made by the recovery process to identify blocks needing recovery will not help to control instance recovery performance.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45
Chapter 2: Availability Enhancements
Oracle 9i New Features, Robert Freeman, p. 73-75
Chapter 3: New Oracle9i Availability and Recoverability Features
QUESTION NO: 15
Which three statements are true about the privileged connection options available in Oracle9i? (Choose three)
A. The CONNECT INTERNAL is disallowed.
B. The Server Manager tool is no longer supplied.
C. The listener must be running to make a connection.
D. The use of remote password login file (orapw) is unchanged.
Answer: A, B, C
Explanation:
The CONNECT INTERNAL is disallowed in Oracle9i. The Server Manager tool is no longer supplied also. And the listener must be running to make a connection.
Incorrect Answers
D: The use of a remote password login file (orapw) is changed in Oracle9i.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 5-10
Chapter 1: Security Enhancements
Oracle 9i New Features, Robert Freeman, p. 132-135
Chapter 5: Miscellaneous Oracle9i Features and Enhancements
When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?
A. During a query’s parse time only.
B. During a query’s execution time only.
C. During any kind of DML statement execution.
D. During the execution of an INSERT statement only.
Answer: A
Explanation:
During a query's parse time Oracle9i flags an index as being used in the V$OBJECT_USAGE view. You can query the V$OBJECT_USAGE view to review the index utilization data. If the index has been used within the period of time it was monitored, the USED column within this view will contain a YES value; it will contain a NO otherwise.
Incorrect Answers
B: During a query's execution time Oracle9i does not flag an index as being used in the V$OBJECT_USAGE view.
C: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during any kind of DML statement execution.
D: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during execution of an INSERT statement.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 56-57
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 12
Using a bitmap structure to manage database object space has several benefits over using freelist structures. Which three are benefits of managing space using automaticsegment-space managed objects? (Choose three)
A. It readily responds to dynamic changes for concurrent access.
B. It improves performance and space utilization in a multiple-instance environment.
C. It requires fewer space related options then freelist managed structures.
D. It improves performance for SYSTEM tablespaces where a high degree of concurrency is required.
Answer: A, B, D
Explanation:
Managing space with automatic segment-space managed objects can respond to dynamic changes for concurrent access, improves performance and space utilization in a multi-instance environment and improves performance for SYSTEM tablespaces where a high degree of concurrency is required.
Incorrect Answers
C: It requires more space related options than freelist managed structures.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 53-54
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 13
The EMPLOYEES table has six indexes and DML operations are slow. Which command begins monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan?
A. ALTER TABLE employees monitor index employee_idx_fk;
B. ALTER INDEX employee_idx_fk monitoring on;
C. ALTER TABLE employees monitor all indexes;
D. ALTER INDEX employee_idx_fk monitoring usage;
Answer: D
Explanation:
This statement provides correct syntax to start monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan.
Incorrect Answers
A: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR INDEX clause with ALTER TABLE command.
B: This command requires MONITORING USAGE clause to be used, not MONITORING ON clause.
C: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR ALL INDEXES clause with ALTER TABLE command.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 56-59
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 14
You can control instance recover performance by setting parameters to define targets for ______.
A. The size of the redo buffer in SGA.
B. The instance that will perform recovery in Real Application Cluster database.
C. The number of blocks to be read during instance recovery, or the time needed to complete instance recovery.
D. The number of passes through the redo logs made by the recovery process to identify blocks needing recovery.
Answer: C
Explanation:
Oracle9i added the FAST_START_MTTR_TARGET parameter to define targets for the number of blocks to be read during instance recovery, or the time needed to complete instance recovery. Oracle internally uses the FAST_START_MTTR_TARGET parameter value to calculate and set the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters. If you specified the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameter values, they will override the values Oracle calculates from the FAST_START_MTTR_TARGET parameter.
Incorrect Answers
A: You cannot control instance recovery performance by setting parameters to define targets for the size of the redo buffer in the SGA
B: It’s not possible to control instance recovery performance for the instance that will perform recovery in a Real Application Clusters database.
D: The number of passes through the redo logs made by the recovery process to identify blocks needing recovery will not help to control instance recovery performance.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45
Chapter 2: Availability Enhancements
Oracle 9i New Features, Robert Freeman, p. 73-75
Chapter 3: New Oracle9i Availability and Recoverability Features
QUESTION NO: 15
Which three statements are true about the privileged connection options available in Oracle9i? (Choose three)
A. The CONNECT INTERNAL is disallowed.
B. The Server Manager tool is no longer supplied.
C. The listener must be running to make a connection.
D. The use of remote password login file (orapw
Answer: A, B, C
Explanation:
The CONNECT INTERNAL is disallowed in Oracle9i. The Server Manager tool is no longer supplied also. And the listener must be running to make a connection.
Incorrect Answers
D: The use of a remote password login file (orapw
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 5-10
Chapter 1: Security Enhancements
Oracle 9i New Features, Robert Freeman, p. 132-135
Chapter 5: Miscellaneous Oracle9i Features and Enhancements
Aug 9, 2006
Oracle 1z0-032: Oracle 9i Database Fundamentals-II #2
QUESTION NO: 6
Which statement correctly describes the function of Oracle9i Cache Fusion feature?
A. It provides each session with its own view of the database at a different point in the past.
B. It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.
C. It lets you dynamically reassign memory in your database buffer cache to different block buffer sizes.
D. It allows you to add new sites to multimaster replication environment without quiescing the master definition site.
Answer: B
Explanation:
The Oracle9i Cache Fusion allows you to execute scalable applications on a clustered database without having to partition the users or the database tables.
Incorrect Answers
A: It does not provide each session with its own view of the database at a different point in the past.
C: The Oracle9i Cache Fusion feature does not dynamically reassign memory in your database buffer cache to different block buffer sizes.
D: It does not provide you ability to add new sites to multimaster replication environment without quiescing the master definition site.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 193-206
Chapter 7: Oracle9i Real Application Clusters
QUESTION NO: 7
What is true if you want to switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2?
A. It is NOT possible to switch unless no active transaction exist in UNDO1.
B. It is possible to switch to UNDO2; but current active transactions will abort.
C. It is possible to switch to UNDO2; current active transactions will be automatically migrated to UNDO2.
D. It is possible to switch to UNDO2; only current active transactions will continue to execute inside UNDO1.
Answer: D
Explanation:
You can switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2. Only current active transactions will continue to execute inside UNDO1, all new transactions will be assigned to the new undo tablespace.
Incorrect Answers
A: You can switch undo tablespaces while active transactions will run in the old undo tablespace. All new transactions will be assigned to the new undo tablespace.
B: Current active transactions will abort if you switched undo tablespaces.
C: Current active transactions will continue to execute inside UNDO1 till they commit or rollback. They will not be automatically migrated to UNDO2.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 19-25
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 8
Examine the list of variables and their data types:
NAME DATA Type
TS, TS1 TIMESTAMP
TSZ TIMESTAMP WITH TIME ZONE
TLZ TIMESTAMP WITH LOCAL TIME ZONE
IYM INTERVAL YEAR TO MONTH
IDS, IDSI INTERVAL YEAR To SECOND
Which three expressions using the new data and time data types are valid? (Choose three)
A. IDS* 2
B. TS + IYM
C. TS –TSI
D. IDS – TS
E. IDS + IYM
Answer: A, B, E
Explanation:
IDS*2, TS+IYM and IDS+IYM are valid new date and time data types.
Incorrect Answers
C: You cannot subtract timestamps.
D: It’s not possible to subtract timestamp from the interval day to second.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-271
Chapter 5: Language Enhancements
Oracle 9i New Features, Robert Freeman, p. 132-135
Chapter 5: Miscellaneous Oracle9i Features and Enhancements
QUESTION NO: 9
Consider the following statement:
SQL> EXECUTE DBMS_STATS.GATHER_SHEMA_STATS (-2> ownname => ‘OE’, -
3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
4> method opt => ‘for all columns size AUTO’);
What is the effect of ‘for all columns size AUTO’ of the METHOD_OPT option?
A. The Oracle server creates a new histogram based on existing histogram definitions for all table, column, and index statistics for the OE schema.
B. The Oracle server creates a histogram based on data distribution regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.
C. The Oracle server creates a histogram based on data and application usage of the column/s for all table, column, and index statistics for the OE schema.
D. The Oracle server creates a histogram based on application usage, regardless of data distribution, for all table, column, and index statistics for the OE schema.
Answer: C
Explanation:
The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.
Incorrect Answers
A: The Oracle server does not create a new histogram based on existing histogram.
B: Histogram creation is not regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.
D: The Oracle server creates a histogram not only based on application usage, but based on data distribution also.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 180-181
Chapter 6: Oracle9i SQL, PL/SQL New Features
QUESTION NO: 10
Which two are true regarding external tables? (Choose two)
A. External tables can be updated.
B. External tables are read-only tables.
C. Indexes can be created on external tables.
D. Indexes cannot be created on external tables.
Answer: B, D
Explanation:
External tables are read-only tables whose data resides in an external OS flat file, and whose definition is stored inside the database. Indexes cannot be created on external tables.
Incorrect Answers
A: External tables cannot be updated. They are read-only tables.
C: Indexes cannot be created on external tables.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 111-116
Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features
Which statement correctly describes the function of Oracle9i Cache Fusion feature?
A. It provides each session with its own view of the database at a different point in the past.
B. It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.
C. It lets you dynamically reassign memory in your database buffer cache to different block buffer sizes.
D. It allows you to add new sites to multimaster replication environment without quiescing the master definition site.
Answer: B
Explanation:
The Oracle9i Cache Fusion allows you to execute scalable applications on a clustered database without having to partition the users or the database tables.
Incorrect Answers
A: It does not provide each session with its own view of the database at a different point in the past.
C: The Oracle9i Cache Fusion feature does not dynamically reassign memory in your database buffer cache to different block buffer sizes.
D: It does not provide you ability to add new sites to multimaster replication environment without quiescing the master definition site.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 193-206
Chapter 7: Oracle9i Real Application Clusters
QUESTION NO: 7
What is true if you want to switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2?
A. It is NOT possible to switch unless no active transaction exist in UNDO1.
B. It is possible to switch to UNDO2; but current active transactions will abort.
C. It is possible to switch to UNDO2; current active transactions will be automatically migrated to UNDO2.
D. It is possible to switch to UNDO2; only current active transactions will continue to execute inside UNDO1.
Answer: D
Explanation:
You can switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2. Only current active transactions will continue to execute inside UNDO1, all new transactions will be assigned to the new undo tablespace.
Incorrect Answers
A: You can switch undo tablespaces while active transactions will run in the old undo tablespace. All new transactions will be assigned to the new undo tablespace.
B: Current active transactions will abort if you switched undo tablespaces.
C: Current active transactions will continue to execute inside UNDO1 till they commit or rollback. They will not be automatically migrated to UNDO2.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 19-25
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 8
Examine the list of variables and their data types:
NAME DATA Type
TS, TS1 TIMESTAMP
TSZ TIMESTAMP WITH TIME ZONE
TLZ TIMESTAMP WITH LOCAL TIME ZONE
IYM INTERVAL YEAR TO MONTH
IDS, IDSI INTERVAL YEAR To SECOND
Which three expressions using the new data and time data types are valid? (Choose three)
A. IDS* 2
B. TS + IYM
C. TS –TSI
D. IDS – TS
E. IDS + IYM
Answer: A, B, E
Explanation:
IDS*2, TS+IYM and IDS+IYM are valid new date and time data types.
Incorrect Answers
C: You cannot subtract timestamps.
D: It’s not possible to subtract timestamp from the interval day to second.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-271
Chapter 5: Language Enhancements
Oracle 9i New Features, Robert Freeman, p. 132-135
Chapter 5: Miscellaneous Oracle9i Features and Enhancements
QUESTION NO: 9
Consider the following statement:
SQL> EXECUTE DBMS_STATS.GATHER_SHEMA_STATS (-2> ownname => ‘OE’, -
3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
4> method opt => ‘for all columns size AUTO’);
What is the effect of ‘for all columns size AUTO’ of the METHOD_OPT option?
A. The Oracle server creates a new histogram based on existing histogram definitions for all table, column, and index statistics for the OE schema.
B. The Oracle server creates a histogram based on data distribution regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.
C. The Oracle server creates a histogram based on data and application usage of the column/s for all table, column, and index statistics for the OE schema.
D. The Oracle server creates a histogram based on application usage, regardless of data distribution, for all table, column, and index statistics for the OE schema.
Answer: C
Explanation:
The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.
Incorrect Answers
A: The Oracle server does not create a new histogram based on existing histogram.
B: Histogram creation is not regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.
D: The Oracle server creates a histogram not only based on application usage, but based on data distribution also.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 180-181
Chapter 6: Oracle9i SQL, PL/SQL New Features
QUESTION NO: 10
Which two are true regarding external tables? (Choose two)
A. External tables can be updated.
B. External tables are read-only tables.
C. Indexes can be created on external tables.
D. Indexes cannot be created on external tables.
Answer: B, D
Explanation:
External tables are read-only tables whose data resides in an external OS flat file, and whose definition is stored inside the database. Indexes cannot be created on external tables.
Incorrect Answers
A: External tables cannot be updated. They are read-only tables.
C: Indexes cannot be created on external tables.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 111-116
Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features
Aug 7, 2006
Oracle 1z0-032: Oracle 9i Database Fundamentals-II #1
QUESTION NO: 1
What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?
A. The filename format.
B. Information stored inside a data dictionary table.
C. Information stored in the ALERT.LOG file for the corresponding instance.
D. Information stored inside the corresponding initialization parameter file for the instance.
Answer: A
Explanation:
Oracle will use naming conventions when it creates the OMF files. In this naming convention, %t represents the tablespace name, %u is a unique 8-character string, and %g stands for the redo log group number.
Incorrect Answers
B: Oracle will create a locally managed tablespaces, so information about a datafile will not be stored inside a data dictionary table.
C: Oracle does not use ALERT.LOG file to store information about datafiles. It just keep log of database events and database structure changes.
D: Initialization parameter file will not be used to store this information. Oracle reads this file to set initialization parameters to start the instance. The structure of the database is stored inside control files. Oracle uses them to mount and open the database.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 2-12
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 2
Oracle9i extends the cursor sharing functionality with the new value of SIMILAR for the CURSOR_SHARING parameter. With CURSOR_SHARING = SIMILAR, cursors are shared for safe literals only. What is meant by ‘safe literals only’?
A. No literal value is substituted for a shared cursor.
B. Different execution plans are generated for substituted literal values.
C. The substitution of a literal value will produce different execution plans.
D. The substitution of any literal value will produce exactly the same execution plan.
Answer: D
Explanation:
Oracle9i has enhanced cursor sharing mode. It can use additional value, SIMILAR, in addition to the EXACT and FORCE cursor sharing modes. When you specify SIMILAR, Oracle only uses the execution plan if is certain that the execution plan does not have any association with the specific literal value. You can enable similar statements to share the same SQL execution plan by setting CURSOR_SHARING to either FORCE or SIMILAR.
Incorrect Answers
A: Literal value is substituted for a shared cursor. The substitution of any literal value will produce exactly the same execution plan.
B: Exactly the same execution plans will be generated for substituted literal values.
C: The substitution of any literal value will produce exactly the same execution plan.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 57-59
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 3
The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command. This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components.
Which three statements are true for the Dynamic SGA feature? (Choose three)
A. The maximum granule size is 4 MB.
B. The minimum SGA configuration is three granules.
C. SGA memory is based on granules by SGA components.
D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.
E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.
Answer: B, C, E
Explanation:
The minimum SGA configuration is three granules. One is for fixed SGA, one for database buffer, one for shared pool. Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation fr SGA is a granule in Oracle9i. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE. If you do not set SGA_MAX_SIZE or if you set it to a value less than initial SGA size, you cannot increase the SGA size later.
Incorrect Answers
A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB otherwise.
D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. This parameter is just maximum limit for sum of the SGA components.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 16-17
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 4
There is more than one way to set the server to detect and affect long running operations automatically. What is the best choice of you want to reduce the impact of long running operations on other users without aborting the long running operations?
A. Define user profiles and set the CPU_PER_CALL limit.
B. Define a SWITCH_TIME for a plan in the Resource Manager.
C. Create a batch job that checks V$SESSION_LONGOPS; the batch job alters the session priority of the long running operations.
D. Create a user defined event in the Oracle Enterprise Manager, which monitors V$SESSION_LONGOPS.
Answer: B
Explanation:
You can define a SWITCH_TIME for a plan in the Resource Manager to reduce the impact of long running operations on other users without aborting the long running operations.
Incorrect Answers
A: You cannot do this with CPU_PER_CALL limit.
C: You don’t need to use a batch job to check V$SESSION_LONGOPS for this purpose.
D: It can be done with a SWITCH_TIME for a plan in the Resource Manager, you don’t need to monitor V$SESSION_LONGOPS.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129
Chapter 3: Manageability Enhancements
QUESTION NO: 5
What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three)
A. Global indexes are rebuilt automatically at the end of the DDL operation thereby avoiding problems with the UNUSABLE status.
B. You do not have to search for invalid global indexes after the DDL command completes and rebuild them individually.
C. Global indexes are maintained during the operation of the DDL command and therefore can be used by any concurrent query.
D. Global indexes remain intact and available for use by data manipulation language (DML) statements even for sessions that have not enabled the skipping of unusable indexes.
Answer: A, B, D
Explanation:
Oracle9i overcomes the problem of rebuilding the global index by giving you the option to update global indexes as Oracle performs the partition DDL. This feature is not applicable to local indexes, domain indexes, index-organized tables (IOTs), or to indexes that were UNUSABLE prior to start of the partition DML. You can invoke this capability by using the optional clause UPDATE GLOBAL INDEX of the ALTER TABLE command. You can use this clause with the ADD, COALESCE, DROP, EXCHANGE, MERGE, MOVE, SPLIT, and TRUNCATE partition DDL commands.
Incorrect Answers
C: Global indexes are not maintained during the operation of the DDL command.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 102-107
Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features
*This is published for helping newbies in Oracle/MySql DBA field. Please excuse, If in any sense violating the rules/laws.
What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?
A. The filename format.
B. Information stored inside a data dictionary table.
C. Information stored in the ALERT.LOG file for the corresponding instance.
D. Information stored inside the corresponding initialization parameter file for the instance.
Answer: A
Explanation:
Oracle will use naming conventions when it creates the OMF files. In this naming convention, %t represents the tablespace name, %u is a unique 8-character string, and %g stands for the redo log group number.
Incorrect Answers
B: Oracle will create a locally managed tablespaces, so information about a datafile will not be stored inside a data dictionary table.
C: Oracle does not use ALERT.LOG file to store information about datafiles. It just keep log of database events and database structure changes.
D: Initialization parameter file will not be used to store this information. Oracle reads this file to set initialization parameters to start the instance. The structure of the database is stored inside control files. Oracle uses them to mount and open the database.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 2-12
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 2
Oracle9i extends the cursor sharing functionality with the new value of SIMILAR for the CURSOR_SHARING parameter. With CURSOR_SHARING = SIMILAR, cursors are shared for safe literals only. What is meant by ‘safe literals only’?
A. No literal value is substituted for a shared cursor.
B. Different execution plans are generated for substituted literal values.
C. The substitution of a literal value will produce different execution plans.
D. The substitution of any literal value will produce exactly the same execution plan.
Answer: D
Explanation:
Oracle9i has enhanced cursor sharing mode. It can use additional value, SIMILAR, in addition to the EXACT and FORCE cursor sharing modes. When you specify SIMILAR, Oracle only uses the execution plan if is certain that the execution plan does not have any association with the specific literal value. You can enable similar statements to share the same SQL execution plan by setting CURSOR_SHARING to either FORCE or SIMILAR.
Incorrect Answers
A: Literal value is substituted for a shared cursor. The substitution of any literal value will produce exactly the same execution plan.
B: Exactly the same execution plans will be generated for substituted literal values.
C: The substitution of any literal value will produce exactly the same execution plan.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 57-59
Chapter 2: Oracle9i Architecture Changes
QUESTION NO: 3
The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command. This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components.
Which three statements are true for the Dynamic SGA feature? (Choose three)
A. The maximum granule size is 4 MB.
B. The minimum SGA configuration is three granules.
C. SGA memory is based on granules by SGA components.
D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.
E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.
Answer: B, C, E
Explanation:
The minimum SGA configuration is three granules. One is for fixed SGA, one for database buffer, one for shared pool. Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation fr SGA is a granule in Oracle9i. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE. If you do not set SGA_MAX_SIZE or if you set it to a value less than initial SGA size, you cannot increase the SGA size later.
Incorrect Answers
A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB otherwise.
D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. This parameter is just maximum limit for sum of the SGA components.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 16-17
Chapter 1: Oracle9i Database Administration and Management Features
QUESTION NO: 4
There is more than one way to set the server to detect and affect long running operations automatically. What is the best choice of you want to reduce the impact of long running operations on other users without aborting the long running operations?
A. Define user profiles and set the CPU_PER_CALL limit.
B. Define a SWITCH_TIME for a plan in the Resource Manager.
C. Create a batch job that checks V$SESSION_LONGOPS; the batch job alters the session priority of the long running operations.
D. Create a user defined event in the Oracle Enterprise Manager, which monitors V$SESSION_LONGOPS.
Answer: B
Explanation:
You can define a SWITCH_TIME for a plan in the Resource Manager to reduce the impact of long running operations on other users without aborting the long running operations.
Incorrect Answers
A: You cannot do this with CPU_PER_CALL limit.
C: You don’t need to use a batch job to check V$SESSION_LONGOPS for this purpose.
D: It can be done with a SWITCH_TIME for a plan in the Resource Manager, you don’t need to monitor V$SESSION_LONGOPS.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129
Chapter 3: Manageability Enhancements
QUESTION NO: 5
What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three)
A. Global indexes are rebuilt automatically at the end of the DDL operation thereby avoiding problems with the UNUSABLE status.
B. You do not have to search for invalid global indexes after the DDL command completes and rebuild them individually.
C. Global indexes are maintained during the operation of the DDL command and therefore can be used by any concurrent query.
D. Global indexes remain intact and available for use by data manipulation language (DML) statements even for sessions that have not enabled the skipping of unusable indexes.
Answer: A, B, D
Explanation:
Oracle9i overcomes the problem of rebuilding the global index by giving you the option to update global indexes as Oracle performs the partition DDL. This feature is not applicable to local indexes, domain indexes, index-organized tables (IOTs), or to indexes that were UNUSABLE prior to start of the partition DML. You can invoke this capability by using the optional clause UPDATE GLOBAL INDEX of the ALTER TABLE command. You can use this clause with the ADD, COALESCE, DROP, EXCHANGE, MERGE, MOVE, SPLIT, and TRUNCATE partition DDL commands.
Incorrect Answers
C: Global indexes are not maintained during the operation of the DDL command.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 102-107
Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features
*This is published for helping newbies in Oracle/MySql DBA field. Please excuse, If in any sense violating the rules/laws.
Oracle FAQs #2
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT'S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it's synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each 'Candidate row' by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL" OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE () FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE OF [break] ON etc are SQL*PLUS REPORTING COMMANDS.
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges. Create < object >, Select, Insert, Alter etc are Object Privileges.
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT'S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it's synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each 'Candidate row' by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL" OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE () FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges. Create < object >, Select, Insert, Alter etc are Object Privileges.
Subscribe to:
Posts (Atom)
