What Happens When the System Tablespace Is Full?

Save

Database administration requires managing and organizing files, even though the database itself exists to perform this task. While the database can manage files and relationships within its own context, the database administrator must manage the implementation, migration and physical constraints of files in the database. For example, the database uses tablespaces to organize files, but the administrator must monitor these tablespaces to ensure that they continue to function properly.

Tablespaces

  • Data in a database is stored in files which are organized into "tablespaces." A tablespace is the logical unit of storage used in database management. Each tablespace contains files that exist on a computer hard drive and conform to the computer's file system. The files hold data that is used to populate tables in the database. Each tablespace represents a particular aspect or collection of data from a certain part of the associated database.

SYSTEM Tablespace

  • By default, certain tablespaces are built by the database software when a new database is created by an administrator. One tablespace in particular – the SYSTEM tablespace – contains files that store the "data dictionary" of the database. The data dictionary contains metadata about the data in the database. This metadata is essentially data about the data, such as time of creation, format and relationships to other data. The data dictionary is crucial to the operation of the database.

Filling Up SYSTEM Tablespace

  • Theoretically, the SYSTEM tablespace can fill up its allocated memory if it is not monitored. While the SYSTEM tablespace represents a special set of data and requires special privileges to access and manage, the actual management of the tablespace is the same as with any other tablespace. This means that the tablespace can fill with data and cause a lock of the system. Its space can be extended, however, with customary tablespace management commands.

Extending the Tablespace

  • To extend the SYSTEM tablespace, the administrator uses the "alter" command, which permits direct access to the tablespace. Then, the "autoextend" keyword allows the administrator to extend the tablespace to a certain size. For example, the following command will extend the "example" table size by 100 and ensure that the tablespace auto-extends when it is running out of space:

    ALTER TABLESPACE example

    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

References

Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!