ORACLE : Table Space Tricks!

tablespace First a brief introduction :

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.
Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.
Lets get a bit Techincal – What is a DB_File :  DB_FILE is the physical file that is created to store the data in Oracle.  The db_files parameter is a “soft limit ” parameter that controls the maximum number of physical OS files that can map to an Oracle instance. Historically (before Oracle8i) you need to be careful not to set db_files too high, else you would have DBWR (database writer) issues. The maxdatafiles parameter is a different “hard limit” parameter.  When you issue a “create database” command, the value you specify for maxdatafiles is stored in your Oracle control files.  The default value of 32 is usually sufficient, but after Oracle8i there is no downside to using a larger value.
Issues – How to incerease table space when you run out :
1. Check for current db_file size –
Show parameter db_files;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_files                             integer     100

2. Check if you have spfile by using the SQL command – show parameter spfile;
if you dont have spfile create one using the SQL command – create spfile from pfile;
3. Shutdown immidate; and startup mount;
4. alter system set db_files=750 scope=spfile; ( to incerease the db_file size )
5. Shutdown immidate; and startup mount;

Leave a comment

Filed under ORACLE

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s