Tip in Font

Encode Code39 in Font Tip

The Sysaux tablespace is mandatory, whether you create a new Oracle Database or migrate from a release prior to Oracle Database 10g.

You can create the Sysaux tablespace manually at database creation time. Here is the syntax for creating the Sysaux tablespace: CREATE DATABASE mydb USER sys IDENTIFIED BY abc1def USER system IDENTIFIED BY uvw2xyz . . . SYSAUX DATAFILE '/u01/oracle/oradata/mydb/sysaux01.dbf' SIZE 500M REUSE . . . If you omit the SYSAUX creation clause from the CREATE DATABASE statement, Oracle will create both the System and Sysaux tablespaces automatically, with their datafiles being placed in systemdetermined default locations. If you are using Oracle Managed Files, the datafile location will be dependent on the OMF initialization parameters. If you include the DATAFILE clause for the System tablespace, you must use the DATAFILE clause for the Sysaux tablespace as well, unless you are using OMF. You can only set the datafile location when you create the Sysaux tablespace during database creation, as shown in the preceding example. Oracle sets all the other attributes, which are mandatory and not changeable, with the ALTER TABLESPACE command. Once you provide the datafile location and size, Oracle creates the Sysaux tablespace with the following attributes:

Permanent Read/write Locally managed Automatic segment space management You can alter the Sysaux tablespace using the same ALTER TABLESPACE command that you use for other tablespaces. Here s an example: SQL> ALTER TABLESPACE sysaux ADD DATAFILE '/u01/app/oracle/prod1/oradata/sysaux02.dbf' SIZE 500M;

Although using the ALTER TABLESPACE command to change the Sysaux tablespace may make it seem as if the Sysaux tablespace is similar to the other tablespaces in your database, several usage features set the Sysaux tablespace apart. Here are the restrictions: You can t drop the Sysaux tablespace by using the DROP TABLESPACE command during normal database operation. You can t rename the Sysaux tablespace during normal database operation. You can t transport a Sysaux tablespace like other tablespaces.

Oracle has been gradually improving its encryption capabilities over the years. In Oracle8i, Oracle introduced the DBMS_OBFUSCATION_TOOLKIT package, and in the Oracle 10.1 release, Oracle introduced the DBMS_CRYPTO package to facilitate encryption. Both the toolkit and the DBMS_CRYPTO package required the application to manage the encryption keys and call the API to perform the necessary encryption/decryption operations.

In Oracle Database, Oracle introduced the new Transparent Data Encryption (TDE) feature, which let you easily encrypt a column s data in a table. The encryption is called transparent because the Oracle database takes care of all the encryption and decryption details, with no need for you to manage any tables or triggers to decrypt data. Now, in Oracle Database 11g, you can encrypt an entire tablespace by simply using a pair of special clauses during tablespace creation. Encrypting a tablespace keeps the data in the tablespaces from being accessed by unauthorized users directly from the operating system file system. Encryption lets you safely send backup media to offsite storage or other locations. When you encrypt a column(s) for a table, there are limitations on certain queries. By encrypting the entire tablespace, some of these restrictions are removed. For example, in Oracle Database 10g, you can t encrypt a column if that column is part of a foreign key or used in another database constraint. By encrypting the tablespace, this restriction is lifted. Following are additional considerations to using tablespace-level encryption: Function-based indexes Index range scans Data type restrictions Partitioned/subpartitioned tables

Tablespace encryption depends on the transparent data encryption feature of the Oracle database, which requires you to create and maintain a secure credentials repository called an Oracle Wallet to store the master encryption key for the database.