Managing Tablespaces and Data Files

 Tablespace and Data Files : 

Oracle Stores Data logically in tablespaces and Physically in Data files.

• Tablespaces :

- Can belong to only one databses at a time.

- Consist of one or more data files

- Are further devided into logical units of stoarge.

• Data Files :

- Can belong to only one tablespace and one databases

- Are a repository for Schema object data.

Databases, Tablespaces, and Data files are closely related, but they have important differences :

• An Oracle Database consists of one or more logical storage units called tablespaces,  which collectively store all of the database's data.
• Each tablespace in an Oracle Database consists of one or more files called data files, which are physical structures that conform with the operating system in which Oracle is running.
• A database's data is collectively stored in the data files that constitute each tablespace of the database. 

For example, the simplest Oracle Database would have one tablespace and one data file. Another database can have more tablespaces with more data files.

Types of tablespaces :

• SYSTEM  Tablespace

- Created with the database

- Required in all databases  

- Contains the data Dictionary, Including stored program units

- Contains the SYSTEM undo segments

- Should not contain user data, although it is allowed. 

• Non-SYSTEM tablespace

- Separate Segments

- Separate undo temporary, application data, and application index segments 

- Separate Data by backup requirements

- Separate dynamic and static data 

- Eases Space adminstration

- Controls amount of space allocation to a user

The DBA creates tablespace for increased control and ease of maintenance.  

Creating Tablespaces:

A tablespace is created using the command:  CREATE TABLESPACE:

Example:

CREATE TABLEPSACE userdata  DATAFILE 

'/u01/oradata/userdata01.dbf' size 100m;

Tablespace Creation syntax with more option Details :

Syntax: 

CREATE TABLESPACE tablespace name

[DATAFILE  clause] [MINIMUM EXTENT integer [K|M]]

[BLOCKSIZE integer [K]] [LOGGING | NOLOGGING]

[DEFAULT storage_clause] [ONLINE | OFFLINE ]

[PERMANENT | TEMPORARY] 

[extent_management_clause] [ segment_management_clause]

Where :

TABLESPACE NAME: This is the name of the tablespace to be created.

DATAFILE: This specifies the data file Or Data files name that uses to make up tablespace.

MINIMUM EXTENT: This ensures that every used extent size in the tablespace is a multiple of the integer. Use K or M to specify this size in Kilobytes or Megabytes.

BLOCKSIZE: Specifies a nonstandard block size for the tablespace. In order to specify this clause, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

LOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace have all changes written to online redo log files. LOGGING is the by default.

NOLOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace do not have all changes written to the online redo log file. NOLOGGING affects only some DML and DDL commands, For Example, Direct loads.

DEFAULT: DEFAULT clause specifies the default storage parameter for all objects created in the tablespace creation. 

OFFLINE: This clause makes the tablespace unavailable immediately after creation.

PERMANENT: This specifies that the tablespace can be used to hold permanent objects.

TEMPORARY: This specifies that the tablespace can be used to hold temporary objects. For example, segments used by implicit sorts caused by an ORDER BY clause. It cannot specify EXTENT MANAGEMENT LOCAL or the BLOCKSIZE clause.

extent_management_clause: This clause specifies how the extents of the tablespace are managed.

segment_management_caluse: This is relevant only for permanent, Locally managed tablespaces. It lets you specify whether Oracle should track the used and free space in the segments in the tablespace using free lists or bitmaps.

  Review below syntax:

datafile_clause:= file_name [SIZE integer (K|M)] [REUSE] [autoextend_caluse] [NEXT]

Filename : this is the name of a data file in the tablespace.

SIZE : This specifies the size if the file. Use K or M to specify the size in Kilobytes or Megabytes 

REUSE: This allows the Oracle server to reuse and existing file.

autoextend_clause : This Clause enables or disables automatic extention of the data file.

NEXT: This Specifies the size in bytes of the next increment of disk space to be allocated automatically when more extents are required.

Where:

MAXSIZE : this specifies the maximum disk space allowed for automatic extention of the datafile.

UNLIMITED :  This specifies the disk space that can be allocated to the data file, or that the tempfile is not limited. 

Thank you !!