Tablespace

What is a Tablespace? 

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.