The Oracle 10g Database New Features

General Storage Enhancements in the Oracle 10g database

SYSAUX tablespace in the Oracle 10g database

The Oracle 10g database added one more tablespace (SYSAUX) to help the SYSTEM tablespace. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. It is a mandatory tablespace and should be created at database creation time. You can not drop or rename this tablespace. The SYSAUX tablespace holds the following database information:

• Text, Ultra Search

• Intermedia, Spatial

• Scheduler

• OLAP

• XML DB

• Workspace Manager

• Data Mining

• Server Manageability Components

• Recovery Catalog

• EM Repository

• Analytical Workspace Object table

• LogMinor, Log Standby, Streems

• Statspack

To monitor the space usage of each about occupant inside the SYSAUX tablespace, use the following SQL statement.

SQL> SELECT occupant_name, space_usage_kbytes

            FROM v$sysaux_occupants

            /

The Mandatory attributes of SYSAUX are:

PERMANENT

READ WRITE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

What are its benefits?

Reduces the number of tablespaces to manage. You don�t need to create the TOOLS, OEM_REPOSITROY, DRSYS, CWMLITE, ODM, etc tablespaces.

You reduce the load on the SYSTEM tablespace.

Renaming a tablespace in the Oracle 10g database

Now you can rename tablespace in the Oracle 10g database. You can not rename the SYSTEM and SYSAUX tablespaces. You can rename any permanent or temporary tablespaces. All tablespaces must have their datafiles online. Your database spfile will be updated.

To rename a tablespace do the following SQL statement.

SQL> ALTER TABLESPACE tools RENAME TO my_tools;

Default Permanent Tablespace in the Oracle 10g database

Now, you can change the default permanent tablespace for non-system users.

Check what your default permanent tablespace is.

SQL> SELECT property_value

            FROM database_properties

            WHERE property_name =

                        �DEFAULT_PERMANENT_TABLESPACE�

            /

If it is SYSTEM, then change it to USERS.

SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Now, check to see the default was changed.

SQL> SELECT property_value

            FROM database_properties

            WHERE property_name =

                        �DEFAULT_PERMANENT_TABLESPACE�

            /

Copy files without using system operating system

You can use the COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file from one place to another. You should make sure to create source and destination directories and you have a read access on the source directory and a write access on the destination directory.

Assuming we are going to copy file a1.dbf from location /u01/oradata/school to location /u02/oradata/school.

SQL> CREATE DIRECTORY my_source AS �/u01/oradata/school�;

SQL> CREATE DIRECTORY my_destination AS �/u02/oradata/school�;

SQL> BEGIN

            DBMS_FILE_TRANSFER.COPY_FILE (

            SOURCE_DIRECTORY_OBJECT => �MY_SOURCE�,

            SOURCE_FILE_NAME => �a1.dbf�,

            DESTINATION_DIRECTORY_OBJECT => �MY_DESTINATION�,

            DESTINATION _FILE_NAME => �a1.dbf�);

          END;

            /

Redo Logfile size advisor in the Oracle 10g database

It determines the optional smallest online redo log file size based on the current FAST_START_MTTR_TARGET setting and the corresponding statistics. To enable the Redo Logfile Size Advisor, you should set the FAST_START_MTTR_TARGET parameter. Note that an online redo log file size is considered optimal if it does not drive incremental check pointing more aggressively than needed by FAST_START_MTTR_TARGET.

Hands-On #1:

Connect as sysdba

SQL> CONNECT / AS SYSDBA

Control the interval and retention of snapshot. Make the retention period for one day with an interval off �0� which switches off snapshot collection.

SQ> EXEC dbms_workload_repository.modify_snapshot_settings (1440, 0);  -- 1440 minute or 1 day and �0� switches OFF snapshot collection.

Create a table with the following storage parameter.

SQL> CREATE TABLE logfile_advisor

            (c1 NUMBER, c2 CHAR(2000))

            PCTFREE 0

            STORAGE (INITIAL 4m NEXT 2m PCTINCREASE 0);

Execute the following PL/SQL block to populate the table.

SQL> BEGIN

            FOR this IN 1..10000 LOOP

                        INSERT INTO logfile_advisor VALUES (this, null);

            END LOOP;

            COMMIT;

          END;

          /

Go to the Database Control home page and click on the Administration tab. On the Administration tab, click on the Redo Log Groups link. Now, you should see the size of each group. On the same page, select Sizing Advice in the Actions drop-down list and click Go. You should see the recommended optimal redo log file size if database recommended.

Now change the STATISTICS_LEVEL parameter set to TYPICAL.

Do the following.

SQL> BEGIN

            UPDATE logfile_advisor SET

            c2 = �this is a long long long long data���.�

            WHERE mod (c1, 1) = 0;

            COMMIT;

          END;

          /

Now, go to the Database Control home page and do the same you did. Repeat the process. On the Administration tab, click on the Redo Log Groups link. Now, you should see the size of each group. On the same page, select Sizing Advice in the Actions drop-down list and click Go. You should see the recommended optimal redo log file size if database recommended.

Is there any recommendation? why?