Lesson 13

This module described how Oracle stores data in the database, how to control the way that Oracle manages the space needed for this storage, and one of the problems that can crop up with Oracle space management.

In this module, you learned:

1. The basic concepts of Oracle storage
2. The role of database blocks, extents, and segments
3. How to set storage characteristics for database objects and tablespaces
4. How to learn about storage characteristics from the data dictionary
5. How Oracle manages free space in database objects
6. How to learn about free space from the data dictionary
7. How fragmentation can affect your Oracle database and how to avoid the problems that can come from fragmentation

In this module, you learned the following glossary terms:

1. Add-on Tuning Pack: One of any number of applets Oracle offers to extend the capabilities of the Oracle Enterprise Manager.
2. BLOB: A (BLOB) binary large object is a binary large object, stored inside the database

Oracle Database 10g introduced Transparent Data Encryption (TDE), allowing you to encrypt data within specific columns of a table. Oracle Database 11g enhances Transparent Data Encryption by providing for the encryption of all contents of a given tablespace. Any permanent tablespace can be encrypted. Undo and temporary tablespaces cannot be encrypted; however, all blocks and data created in these tablespace types will be encrypted if they originate from an encrypted tablespace. Additionally the redo log stream will be encrypted. Partitioning allows you to mix and match encryption. One partition can be encrypted, and another may not be encrypted, simply by virtue of which tablespace they are in. Most Oracle features are available with encrypted tablespaces. There are some restrictions on transporting encrypted tablespaces (cross-endianness transport is not supported, and there are wallet-related restrictions if you are using wallets in the destination database). Binary files (BFILES) and external tables are not encrypted in Oracle (which makes sense as neither of these are stored in tablespaces!). Finally, you cannot change the key of an encrypted tablespace once it has been set.

Oracle offers a number of mechanisms for securing the data within a database Transparent Data Encryption (TDE) is used to further ensure the integrity of your data. TDE encrypts the data in the

1. datafiles,
2. undo logs,
3. redo logs, and
4. the buffer cache

of the system global area (SGA) in memory. You can select which columns of which tables will be encrypted. Using TDE provides an enhanced level of security for sensitive data without you having to change anything about the application that uses the data. End users and application developers will continue to access and store rows the same as they always have. The only change will be the way those rows are physically stored at the operating system level. TDE is well suited for database applications in which sensitive data elements must be encrypted due to regulatory or business requirements.

TDE is a key-based encryption system. In order to use TDE, you will need to configure a wallet for the database to use when it is first opened. The wallet file will have a password that must be provided in order for the encrypted columns to be accessed. Thus, anyone who attempts to copy your datafiles in an effort to duplicate your database will need the 1) datafiles, 2) wallet file, and 3) wallet password in order to access those columns. The wallet file can be stored on a server apart from the database server (to further enhance security). If a user attempts to access an encrypted column with the wallet closed, the database will respond with the following error:

ORA-28365: wallet is not open

Once the database is opened with the wallet password, anything stored in the designated secured columns will be encrypted when it is written to the datafiles and the ther columns will not beaffected.
In the next module, you will learn to assign privileges to database objects to limit the ways that users can interact with them.

Click the Quiz link below to answer a few questions about tablespace fragmentation.
Tablespace fragmentation - Quiz