Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Improved VLDB Support in Oracle Database 10g

Oracle 10g includes improved support for Very Large Databases (VLDBs) including:

Bigfile Tablespaces

Bigfile tablespaces are tablespaces with a single large datafile. In contrast normal (smallfile) tablespaces can have several datafiles, but each is limited in size. The benefits of bigfile tablespaces are as follows.

Typically bigfile tablespaces must be locally managed with automatic segment-space management. Exceptions to this rule include temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces which are all allowed to have manual segment-space management.

Bigfile tablespaces are create using the CREATE BIGFILE TABLESPACE command. The first two of the following statements have the same affect as the EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO clauses are the default actions, while the last statement would error.

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G;

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
  
-- invalid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
  EXTENT MANAGEMENT DICTIONARY
  SEGMENT SPACE MANAGEMENT MANUAL;

The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

The default tablespace type is SMALLFILE unless specified otherwise in the CREATE DATABASE command. Once the database is created the ALTER DATABASE command can be used to dynamically alter this setting.

CREATE DATABASE mydb
  USER SYS IDENTIFIED BY password
  USER SYSTEM IDENTIFIED BY password
  SET DEFAULT BIGFILE TABLESPACE
  UNDO TABLESPACE undotbs
  DEFAULT TEMPORARY TABLESPACE temp;

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
ALTER DATABASE SET DEFAULT SMALLFILE TABLESPACE;

When the default tablespace type is set to bigfile the BIGFILE keyword is no longer needed in the CREATE TABLESPACE statement, but creation of of a smallfile tablespace requires the SMALLFILE keyword.

The ALTER TABLESPACE command can be used to modify the size and autoextend functionality of bigfile tablespaces.

ALTER TABLESPACE mybigtbs RESIZE 100G;
ALTER TABLESPACE mybigtbs AUTOEXTEND ON NEXT 10G;

A BIGFILE column (YES or NO) has been added to the DBA_TABLESPACES, USER_TABLESPACES and V$TABLESPACE views to indicate the tablespace type.

Some additional factors to consider before using bigfile tablespaces include:

Temporary Tablespace Groups

Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it.

-- Create group by adding existing tablespace. 
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
  TABLESPACE GROUP temp_ts_group;

The tablespaces assigned to a group can be viewed using the following.

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP
TEMP_TS_GROUP                  TEMP2

2 rows selected.

Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace.

-- Assign group as the temporary tablespace for a user.  
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace.  
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;

A tablespace can also be removed from a group.

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP

1 row selected.

There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.

-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected

Tablespace groups share the same namespace as tablespaces so a group and tablespace cannot share the same name.

Skip Unusable Indexes

In Oracle 10g the SKIP_UNUSABLE_INDEXES parameter can be set at instance level as well as session level and now defaults to TRUE. When set to TRUE Oracle will not attempt to use or report errors when an index is marked as unusable.

The parameter can be reset using the ALTER SYSTEM and ALTER SESSION as shown below.

ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = FALSE;

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = FALSE;

This setting does not affect how indexes that support unique constraints are used as ignoring them may lead to constraint violation.

Hash-Partitioned Global Indexes

Global indexes can now be hash partitioned using the syntax shown below.

CREATE TABLE mytable (
  column_1  NUMBER(10),
  column_2  VARCHAR2(10),
  column_3  NUMBER(10),
  column_4  VARCHAR2(50)
);

CREATE INDEX myindex ON mytable (column_1, column_2, column_3) GLOBAL
  PARTITION BY HASH (column_1, column_2)
    (PARTITION myindex_part_1 TABLESPACE users,
     PARTITION myindex_part_2 TABLESPACE users,
     PARTITION myindex_part_3 TABLESPACE users,
     PARTITION myindex_part_4 TABLESPACE users);

Maintenance of hash partitioned global indexes can be performed using the following statements.

-- Add a new partition.
ALTER INDEX myindex ADD PARTITION mytable_part_5;

-- Reduce the number of partitions by 1.
ALTER INDEX myindex COALESCE PARTITION;

-- Rebuild partition.
ALTER INDEX myindex REBUILD PARTITION mytable_part_1 TABLESPACE users;

-- Rename partition.
ALTER INDEX myindex RENAME PARTITION mytable_part_1 TO mytable_part_a;

-- Clean up.
DROP INDEX myindex;
DROP TABLE mytable;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.