Tablespaces is a logical storage unit. It is used to group related logical structures together.
What are the Predefined Tablespaces in a database?
When you create a new database, Oracle server will create 4 required tablespaces for the new database:
SYSTEM Tablespace
- Every Oracle database contains a tablespace named SYSTEM
- Created automatically during the database creation
- It is always online when the database is open
SYSAUX Tablespace
- Gets installed as an auxiliary tablespace to the SYSTEM tablespace during the database creation
- Database components that otherwise create and use separate tablespaces then occupy this tablespace.
UNDO Tablespace
- UNDO tablespaces are used for the storage of undo information.
- No other segment types can be created in undo tablespaces.
- Each database contains zero or more undo tablespaces.
- Only a single undo tablespace is assigned to an Oracle instance in automatic undo management mode.
TEMP Tablespace
- At least one default temporary tablespace needs to be defined when creating a database when the SYSTEM tablespace is locally managed.
- A locally managed SYSTEM tablespace cannot be used for default temporary storage.
What is SYSTEM tablespace?
SYSTEM tablespace is automatically created when the database is created. It contains data dictionary for the entire database.
Explain how to assign a Tablespace to a user in Oracle.
The following statement assigns already created tablespace ‘TABLESPACE_01’ as the tablespace group for user sh:
ALTER USER sh
TEMPORARY TABLESPACE TABLESPACE_01;
Explain how to prevent tablespace fragmentation in oracle.
Tablespace fragmentation can be prevented by using PCTINCREASE command. PCTINCREASE is the percentage a new subsequent extent will grow. This value should be ideally set to 0 or 100 to avoid tablespace fragmentation. Alternate and strange values for PCTINCREASE results in strange sizes of extents. Same size of each extent of all segments must be used.