Re: CREATE TABLESPACE Help

>What I'd like to do is create a tablespace that will eventually
>contain about 8-10 tables, using about 125-150 megs of space. What
>all do I need to create a good tablespace for storing, storing,
>updating and querying these tables after joining several (if not all)
>of them together? Do I need to use the DATAFILES line?

A tablespace is the database's logical name for a given set of physical files which it will use to store data. A database can use many tablespaces, or can use one. Whenever you have to create an object in a database which requires physical space, like a table or an index, the database needs to know the TABLESPACE to put it in. If you dont specify one, the database will default one for you.

A table or index definition can include the TABLESPACE which it will use as follows:

create table a(
...)
tablespace MYTABLESPACE;

This means that table a will be physically located in the files which together comprise tablespace MYTABLESPACE. Note that the actual distribution of the data is not predictable, although you can find out what it is after the fact.

If you DONT specify a tablespace name, the tablespace used is the default defined for the user in the field DBA_USERS.DEFAULT_TABLESPACE. When you create a new user, you can specify the user's default tablespace, or you can change it using the ALTER USER command. The default is SYSTEM.

The create database statement automatically creates a tablespace called SYSTEM. Data files you specify at database CREATION are assigned to that tablespace. Generally, you want to keep your application tables out of here, and leave it for SYS and SYSTEM owned stuff. The special user SYSTEM, the dba, is intended to be the owner of ORACLE-specific stuff, like forms definitions, reports definitions, the old CRT definition stuff (for those of us with fond memories...) and basically anything else which you would consider to be ORACLE proprietory, and has a DEFAULT TABLESPACE of SYSTEM. You create new tablespaces, IN ADDITION TO SYSTEM, with the CREATE TABLESPACE command, which allows you to assign files to the new tablespace in the same way as the CREATE DATABASE command allows you to assign files to SYSTEM.

There are two other special tablespaces you may want to bear in mind, but you dont need them to get up and running (it depends on the size of your application and your immediate requirements). They are the TEMP and RBS tablespaces, for the temporary sort area and rollback areas. These are NOT created automatically for you, you need to do it yourself, and you are not restricted to these names, but it helps when you are talking to ORACLE support.

The temporary sort area is used by the database to hold data which is being sorted, eg when you specify an order by or when you create an index. The existance of a separate temp space wont automatically get the database to use it; there is another field on the dba_users table called 'temporary_tablespace' which needs to be set, again when you create the user or using 'alter user'. The default value per user is SYSTEM. The rollback area is used by the database in it's rollback strategy (when you enter rollback and commit). The default rollback space is also SYSTEM. If you have separate rollback areas, you can force the use of a specific area using SET TRANSACTION (not available from all of the oracle tools). Otherwise, the choice of area is random and fixed for the duration of a single transaction. Of course, if you dont have separate rollback areas, you always get the SYSTEM tablespace.

The other thing to know about tablespaces is that they form a layer of space management. When the database needs to physically allocate space to a table, it does so in fixed size chunks called EXTENTS. The first extent allocated to a file can be different from the ones which follow. The size of a table extent can be specified in the table definition, otherwise it defaults to that for the tablespace. This begs the question: what is the correct size? RTFM. It's enough to know that you have control over it. Extents can be changed on the fly, and take effect for new extents.

By now, you can see that the argument of how many tablespaces to use is very specific to the use of the database, which explains the absence of any clearcut instructions on the subject. However, there are lots of rules of thumb.

So, why would you want to separate tables into separate areas?

• management - separate tablespaces aid backup strategies (maybe)
• performance - separate tablespaces and careful allocation of tables can improve i/o performance by 'striping' if you have multiple disk drives and the tables are really large
• space - specific extent sizes depending on table usage can improve space usage overrall. For example, distinguish between factors such as table size, usage (select-only or update); etc

Note that the operation of allocation of a table to a tablespace has no theoretical bearing on the logical operation of performing queries and joins on it. In practice, however, you have to look at how the database performs and take into account your physical system.

Also note that you dont have to create separate tablespaces at all, but sometimes it helps.

Now, an application of 125M is not too big (he says, with no knowledge of your system) to rule out using the defaults you get when you create the database and specify all your files as part of the creation.

Last of all, if you do decide to create separate tablespaces, it helps to do it from separate command files:

• one to create the database
• one to create the tablespaces
• one to create your application tables etc

That way you can always export you specific tables, recreate the database, recreate empty tables with tablespaces pointing at where you need to, and re-import them if you find you need to change anything.

cheers,
mr Received on Sun Jan 28 1996 - 00:00:00 CET