Inside OCP: New Features for OCPs

TECHNOLOGY: Inside OCP

New Features for OCPs
By Aradhana Puri

Concepts and sample questions for OCP upgrade certification

The Oracle Database 10g: New Features for Administrators exam enables Oracle Certified Professionals (OCPs) certified on Oracle9i Database to upgrade their certifications to Oracle Database 10g. This is the only exam that OCPs certified on Oracle9i Database need to take to upgrade their certification credentials to Oracle Database 10g.

This column focuses on new tablespace management features in Oracle Database 10g and presents sample questions of the type you may encounter when taking the Oracle Database 10g: New Features for Administrators exam.

Default Permanent Tablespace

Oracle Database 10g allows you to define a default permanent tablespace and have it assigned automatically whenever you create a new user without specifying a permanent tablespace. Note that if you do not specify a default permanent tablespace, then the SYSTEM tablespace is also the default permanent tablespace for non- SYSTEM users. Oracle strongly recommends that you create a default permanent tablespace.

When you create a database by using one of the default templates of Database Configuration Assistant (DBCA), the DBCA creates the USERS tablespace as the default permanent tablespace. If you create a database using the CREATE DATABASE SQL statement, the DEFAULT TABLESPACE clause of the statement specifies a default permanent tablespace for the database.

USERS is the default permanent tablespace for your database. Which statements are correct in this scenario? (Choose two.)

A. USERS is the default permanent tablespace for non- SYSTEM users only.
B. The default permanent tablespace of the database can be changed.
C. The USERS tablespace can be dropped.
D. Multiple tablespaces can be designated as default permanent tablespaces.

The correct answers are A and B.

The default-tablespace concept applies to non- SYSTEM users only. The users SYS and SYSTEM continue to have SYSTEM as their default tablespace. You can change the default permanent tablespace of your database.

You cannot drop a default permanent tablespace. You must first reassign another tablespace as the default tablespace and then drop the old default permanent tablespace. At any point, your database can have only one default permanent tablespace for non- SYSTEM users.

ABC Corporation has hired Tom as its new DBA. Tom needs to identify the default permanent tablespace for the SALES database. Which data dictionary view should he query?

A. USER_TS_QUOTAS
B. DBA_TABLESPACES
C. DBA_TABLESPACE_GROUPS
D. DATABASE_PROPERTIES

The correct answer is D.

You can use the following query to identify the default permanent tablespace for your database:

SELECT property_value 
FROM database_properties
WHERE property_name =
'DEFAULT_PERMANENT_TABLESPACE';

Alternatively, you can use Oracle Enterprise Manager (OEM) to identify the default permanent tablespace for your database, as shown in Figure 1 .

Figure 1: Oracle Enterprise Manager's Edit Tablespace page, showing default permanent tablespace

Renaming a Tablespace

Before Oracle Database 10g, tablespaces were named at creation time and could not be renamed. In Oracle Database 10g, you can rename a tablespace, making it easier, for example, to migrate a dictionary-managed tablespace to be locally managed or to transport a tablespace to a database that already contains a tablespace of the same name.

You can rename an existing tablespace by using OEM. Alternatively, you can use the RENAME TO clause of the ALTER TABLESPACE command to rename a tablespace, as follows:

ALTER TABLESPACE <old_tablespace_name> RENAME TO <new_tablespace_name>;

Which statements are correct regarding the renaming of a tablespace in Oracle Database 10g? (Choose three.)

A. You cannot rename a temporary tablespace.
B. You can rename the SYSTEM tablespace.
C. You can rename a tablespace, provided that the COMPATIBLE initialization parameter is set to at least 10.0.0.
D. When you rename a read-write tablespace, Oracle Database 10g updates all references to the tablespace name in the data dictionary as well as in the control file.
E. When you rename a tablespace, all data files are also renamed, provided that you created data files for the tablespaces by using Oracle-managed files.
F. You cannot rename a tablespace if any data file in the tablespace to be renamed is offline or if the entire tablespace is offline.

The correct answers are C, D, and F.

The tablespace and all its data files must be online, and the database must have a COMPATIBLE setting of 10.0.0 or greater. When you rename a tablespace, the Oracle database updates all references to the tablespace name in the data dictionary, control file, and online data file headers. If the tablespace is read-only, data file headers are not updated and a message is written to the alert log, indicating that data file headers have not been renamed, but the data dictionary and the control file are updated.

You can rename temporary as well as permanent tablespaces, but not the SYSTEM and SYSAUX tablespaces. When you rename a tablespace, the Oracle database does not rename the actual data files.

USER_TBS is the default permanent tablespace for your database. You issue the following SQL statement to rename the USER_TBS tablespace:

ALTER TABLESPACE user_tbs 
RENAME TO user_default_tbs;

What will be the outcome of the SQL statement above?

A. The tablespace will be renamed, provided that it does not contain any user data.
B. The tablespace will be renamed, but the users continue to see USER_TBS as their default permanent tablespace.
C. The tablespace will be renamed, and a message will be recorded in the alert log file, indicating that the data file headers are not updated.
D. The statement will fail, because you cannot rename a default permanent tablespace.
E. The tablespace will be renamed, and the DATABASE_PROPERTIES view will show USER_DEFAULT_TBS as the default permanent tablespace.

The correct answer is E.

The tablespace will be renamed regardless of whether it contains any user data, and users will see USER_DEFAULT_TBS as their new default permanent tablespace. When the tablespace is renamed, the data dictionary and the control file are updated. The DATABASE_PROPERTIES view will show USER_DEFAULT_TBS as the default permanent tablespace.

SYSAUX Tablespace

Next Steps

GET certified
Visit oracle.com/education/certification for more information about the Oracle Certification program.

In earlier database releases some schemas, such as the intelligent agent user DBSNMP and the data mining user ODM , had SYSTEM as their default tablespace or they required their own tablespaces. Oracle Database 10g introduces a new tablespace, called SYSAUX , that contains the objects of these schemas. The SYSAUX tablespace reduces the number of tablespaces required by the database and serves as an auxiliary tablespace to the SYSTEM tablespace. It is automatically created when you create a database by using DBCA with one of the default templates.

You can specify only data file attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include PERMANENT, READ WRITE, EXTENT MANAGEMENT LOCAL , and SEGMENT SPACE MANAGEMENT AUTO . You cannot alter these attributes with an ALTER TABLESPACE statement. Also, you cannot drop or rename the SYSAUX tablespace.

Which statements are correct regarding the SYSAUX occupants? (Choose two.)

A. No SYSAUX occupant can be relocated to another tablespace.
B. Only some of the SYSAUX occupants can be relocated to another tablespace.
C. The SYSAUX occupants can be relocated to the default permanent tablespace only.
D. The SYSAUX occupants can be relocated to a big-file tablespace only.
E. The V$SYSAUX_OCCUPANTS dynamic performance view provides the procedure name that can be used to move an occupant to another tablespace.

The correct answers are B and E.

If you detect that an occupant is taking too much space in the SYSAUX tablespace or if you anticipate that it will, you can move the occupant to a different tablespace by using OEM. However, not all of the SYSAUX occupants can be relocated. The V$SYSAUX_OCCUPANTS dynamic performance view displays SYSAUX tablespace occupant information. The view includes a MOVE_PROCEDURE column to specify the procedure name, which can be used to move the components for that occupant to another tablespace. The value in this column is null if the occupant cannot be relocated. SYSAUX occupants that are relocatable can be relocated to any permanent tablespace, regardless of whether the tablespace is a big-file or small-file tablespace.