As Published In
Oracle Magazine
January/February 2006

TECHNOLOGY: Inside OCP


New Features for Oracle8i OCPs

By Aradhana Puri

Upgrade from Oracle8i OCP to Oracle Database 10g OCP in one exam.

The Oracle Database 10g DBA New Features for Oracle8i Oracle Certified Professionals (OCPs) exam (exam number: 1Z0-045) enables those certified on Oracle8i Database to upgrade their certification to Oracle Database 10g by passing only this exam.

This column focuses on some of the new features in Oracle9i Database and Oracle Database 10g that simplify database management. The column presents sample questions of the type you may encounter when taking the Oracle Database 10g DBA New Features for Oracle8i OCPs exam. Note that the sample question format has been adjusted for presentation in this article.

Server Parameter File (SPFILE)

Before Oracle9i Database, you used a text initialization parameter file called init.ora to start Oracle database instances. This file is also called the PFILE (parameter file). The binary server parameter file—SPFILE—was introduced in Oracle9i Database as the recommended method of managing initialization parameters.

If you are using Oracle9i Database, you need to create an SPFILE from the PFILE, by using the CREATE SPFILE command. If you create a new Oracle Database 10g database instance with the Database Configuration Assistant (DBCA), an SPFILE is automatically created.

If you do not use an SPFILE, changes you make by using the ALTER SYSTEM statement are in effect only for the current instance, so you must manually update the PFILE for the changes to be used the next time you start up an instance. When you use an SPFILE, however, you can automatically include changes you've made using the ALTER SYSTEM statement in your SPFILE, so that changes persist across database shutdown and startup.

Use the SCOPE clause with the ALTER SYSTEM statement, as shown below, to specify the scope of a change: 

ALTER SYSTEM SET parameter_name = value [SCOPE={SPFILE|MEMORY|BOTH}]


ALTER SYSTEM SET TIMED_STATISTICS = True;

What will be the result of this command?

A. The parameter will be modified in the current instance only.
B. The parameter will be modified for the current instance as well as in the SPFILE.
C. The parameter will be modified in the SPFILE only.
D. The statement will return an error, because the SCOPE clause is not explicitly specified.

The correct answer is B. If an SPFILE is used to start the instance, the default value for SCOPE is BOTH . As a result, the parameter value is changed in the current instance (memory) as well as in the SPFILE. Answer A is incorrect because the parameter will be modified in the current instance only if SCOPE is set to MEMORY , as in the following: 

ALTER SYSTEM SET TIMED_STATISTICS = True SCOPE = memory;


Similarly, answer C is incorrect because if SCOPE is set to SPFILE , the parameter will be modified in the SPFILE only. Answer D is incorrect because the statement will not return an error if the SCOPE clause is not explicitly specified.

Both an SPFILE and a PFILE are available at the default location in your database installation. You need to check whether an SPFILE or a PFILE was used to start your current database instance. How can you check this? (Choose all that apply.)

A. Execute the SHOW PARAMETER SPFILE command.
B. Query the DBA_CAPTURE_PARAMETERS data dictionary view.
C. Check the alert.log file.
D. View the value of SPFILE in Oracle Enterprise Manager.

The correct answers are A and D. You can execute the SHOW PARAMETER SPFILE command to check whether the SPFILE was used to start up the database instance. Alternatively, you can view the value of SPFILE in Oracle Enterprise Manager 10g, as shown in Figure 1. Answers B and C are incorrect because the DBA_CAPTURE_PARAMETERS data dictionary view and the alert.log file do not provide the desired information.

 

figure 1
Figure 1: Oracle Enterprise Manager 10g, Initialization Parameters SPFILE tab


Oracle-Managed Files

Oracle-managed files simplify the administration of an Oracle database, by eliminating the need for administrators to directly manage the operating system files that constitute an Oracle database. By using initialization parameters, you can specify the file system directory to be used for a particular type of database file. Oracle Database ensures that database files are created in the specified directories with unique names.

You created the TEST_TBS tablespace by using Oracle-managed files. You plan to drop the tablespace. Which statement is correct in this scenario?

A. You need to explicitly delete the datafiles at the operating system level after dropping the tablespace.
B. You must delete the datafiles at the operating system level before dropping the tablespace.
C. After you drop the tablespace, the associated datafiles will be deleted automatically only after a normal database shutdown.
D. The associated datafiles will be deleted automatically when you drop the tablespace.

The correct answer is D. When you drop a tablespace you created by using Oracle-managed files, all associated operating system files are automatically deleted. Answers A and B are incorrect because when you are using Oracle-managed files, you need not delete the files manually. Answer C is incorrect because a database shutdown is not required for deleting the datafiles of a dropped tablespace.

In your database, you have set the following value for the DB_CREATE_FILE_DEST initialization parameter:

DB_CREATE_FILE_DEST = '\uo1\oradata\hr'

For which file types would the database use the '\uo1\oradata\hr' directory as the default location during file creation if no file system directory name is specified? (Choose all that apply.)

A. Datafile
B. Online redo log file
C. Control file
D. Temp file
E. alert.log file
F. Password file
G. Trace file

Next Steps


LEARN more about
the Oracle Certification Program, and download a free exam guide

READ
"Inside OCP" columns

DOWNLOAD
Oracle Database 10g Release 2

The correct answers are A, B, C, and D. The DB_CREATE_FILE_DEST initialization parameter defines the location of the default file system directory where Oracle Database creates datafiles and temp files when no file specification is given in the creation operation. This initialization parameter value is also used as the default file system directory for the online redo log files and control file if DB_CREATE_ONLINE_LOG_DEST_n is not specified. Answers E, F, and G are incorrect because the Oracle-managed files feature is not supported for the alert.log file, the password file, and trace files.

Bigfile Tablespaces

In Oracle Database 10g, bigfile tablespaces were introduced to support very large databases. A bigfile tablespace is a tablespace with a single—but very large—datafile. Bigfile tablespaces can significantly enhance the storage capacity of a database and reduce the number of datafiles needed for a database. SQL syntax for the ALTER TABLESPACE statement enables you to perform operations on tablespaces rather than on the underlying individual datafiles. To create a bigfile tablespace, specify the BIGFILE keyword in the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...) .

You created an HR_TBS bigfile tablespace. You issued the following command to alter the tablespace and received the following error: 

SQL> ALTER TABLESPACE HR_TBS 
ADD DATAFILE 
'\ORACLE10\ORADATA\hr_big_data.dbf'
size 10 M;

ALTER TABLESPACE hr_tbs
      *
ERROR at line 1:
ORA-32771: cannot add file to bigfile 
tablespace


What is the reason for the error?

A. The size of the hr_big_data.dbf datafile is less than 1GB.
B. Another datafile with the same name is already added to the tablespace.
C. The datafile that is being added is not an Oracle-managed file.
D. A bigfile tablespace can have only one datafile.

The correct answer is D. A bigfile tablespace can have only one datafile. To increase the size of a bigfile tablespace, you can either resize it or make it autoextensible.

Conclusion

This column has focused on various new features introduced in Oracle9i Database and Oracle Database 10g. You can use an SPFILE and Oracle-managed files to simplify administration. You can use bigfile tablespaces to significantly enhance the storage capacity of an Oracle database.


Aradhana Puri (ocpexam_ww@oracle.com) is a principal Oracle certification exam developer. She has been at Oracle since 2000.


Send us your comments