:

Create Tablespace Oracle

create tablespace oracle

The Oracle create tablespace statement can be quite complex if you look at it in the Oracle documentation. In this article I would like to try and break down the create tablespace command in to simpler terminology.

There are three main types of Oracle tablespace:

  1. Normal – CREATE TABLESPACE
  2. TEMPORARY – CREATE TEMPORARY TABLESPACE
  3. UNDO – CREATE UNDO TABLESPACE

We will be looking at all three types of Oracle tablespace later on in this article, but first let’s cover some basics.

What is a Tablespace?

  • A tablespace within the Oracle database is a logical container which is used to group physical segments together, where some of their characteristics are the same
  • You can (optionally) have many tablespaces in one Oracle database, but you must have the SYSTEM and SYSAUX (10g onwards) tablespaces
  • A tablespace contains one or more datafiles, which are physical files on disk
  • Tablespaces are usually created to house segments with similar storage requirements and to logically separate objects within the database
  • Datafiles in one tablespace can be put on different physical disks
  • Do not put any of your application objects inside the SYSTEM tablespace
  • A TEMPORARY tablespace is different to a normal tablespace and must be created using different syntax
  • An UNDO tablespace is another special kind of tablespace and therefore different syntax is required

Top Tip: If you are going to have a large tablespace in your database try to have fewer physical datafiles which make the tablespace because when Oracle does a checkpoint, among other operations, it has to update each datafile header. If you have too many datafiles this can slowdown the checkpointing process.

How to Create a Tablespace

This is covering the “normal” tablespace, out of the three types outlined above. There are a lot of options you have to consider when creating an Oracle tablespace, but let’s just use an example of how I would normally create one and then look at the statements in a little more detail.

CREATE TABLESPACE tablespace1
 DATAFILE
'<path>\<name>.dbf' SIZE 1024M AUTOEXTEND ON NEXT 2G MAXSIZE 24g,
'<path>\<name>.dbf' SIZE 2014M AUTOEXTEND ON NEXT 2G MAXSIZE 24g
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;

Note: I’m not going to discuss dictionary managed tablespaces because they are usually only found in older database, and you should be looking to convert them to locally managed now.

Oracle create tablespace command

The create tablespace statement

The above SQL is the syntax that I would use to create 99% of the tablespaces in my own database, which is Oracle 11.2.0.3. I would have also used this same syntax on 10g. Let’s take a look at the details of what it means:

CREATE TABLESPACE – The first variable is the name of the tablespace. Name it something meaningful. I’d recommend something like using the database name, the user who will have objects in it and the extent size. For example, DB1_TEST_X1M. This will allow it to be easily identified on disk, too.

DATAFILE – This next section lists out all of the physical datafiles you will be creating. You should specify the full path and then name it something meaningful. Usually I will call it the same as the tablespace name but with a number on the end. For example, DB1_TEST_X1M_001.dbf. This will mean that each datafile will be listed on disk in order for any datafile from 001 to 999.

SIZE – Hopefully the size one is obvious; just specify how large you want it to be in Megabytes and put ‘M’ after it. You can use G for Gigabytes, and B for bytes.

AUTOEXTEND, MAXSIZE – This parameter is either set to ON or OFF. When it is on you specify a size for it to grow by up to MAXSIZE. For example, I may create my datafile as 1GB and allow it to expand up to 24GB. This is ideal for allowing the database objects to grow without having to always manually add space. I use it all the time, just making sure that I set sensible MAXSIZE amounts.

Note: Autoextend can actually cause tablespace to grow by up to 10% of the total size of the tablespace! This was added functionality in 11g.

 

Note: Historically, DBAs would have “index” and “data” tablespaces so that they could spread out the I/O across physically separate drives.The idea was to increase response times by having one set of disk heads read the index entry at the same time as the other disk heads were reading the data. This is no longer required with modern day SAN technologies, such as RAID10.

I would like to go into a little more detail for the extent management, uniform size and segment space management clauses which I will do in the next sections.

Extent Management

Let’s just make sure we are all on the same page. An extent is used to give more space to a segment, like an index or table. Segments are made up of 1 or more extents. Each extent can vary in size or can be of uniform size, depending on how you have created the tablespace.

Extents are created as the segment grows and can span multiple datafiles but all extents for the same segment must be in the same tablespace.

Hopefully this diagram will help to explain it a little better – click it to enlarge

create tablespace oracle

This diagram shows how extents are managed within an Oracle tablespace

In a locally managed tablespace, free and used blocks are managed by something called a bitmap in the datafile header. This is like a map of all blocks in the datafile, conceptually, it has a value of 1 if the block is used and a 0 if it is empty. This is one of the advantages of using locally managed tablespaces; the block management is done efficiently, and all blocks which are next to one another and free can be used.

So, let’s say that your table needs to increase by 1 extent which is 1M in size. The datafile bitmap will be scanned to see if there is a large enough chunk of disk space to create the new extent. When it finds which blocks to use for the new extent, the bitmap is updated to indicate that the blocks are now used and the segment will have some free space to use.

Note: In general, the only time that a used extent will be given back to the tablespace for use by another segment will be if the original segment is dropped. Just deleting all rows in a segment will not release the extent back for use by another segment.

Uniform Size

There are two options for extent allocation when you specify the extent management as local:

  1. Uniform size
  2. Auto allocate

Uniform size means that each extent will be the same size, like when building a house each brick will be the same size so will fit together nicely with no wasted space.

Auto allocated extents, on the other hand, will usually start small and an internal algorithm will determine how large to make the next extent, and the one after that, etc. They will most likely be larger each time if the segment is growing rapidly and smaller if it is not.

This type of extent is like a house made of odd shaped bricks – there will be gaps and finding a space to fit an odd shaped brick will be difficult!

Segment Space Management

In a locally managed tablespace there are two options for which type of segment space management you use:

  1. Automatic
  2. Manual

With automatic segment space management (the default for locally managed tablespace) free space in each segment is managed very much in the same way that the extent management is; using bitmaps, but for the segment this information is kept in the first block allocated to the segment at creation time.

When using manual segment space management the free and used blocks are managed using free lists, which require more management when inserts, updates and deletes are run against the segment. It is also a single point of contention, so automatic management is generally accepted as the more efficient method to use.

Note: When using automatic segment space management, only the PCTFREE storage parameter is valid when creating a segment. This is what percentage of free space should be left in each block for subsequent updates to existing rows.

Bigfile and Smallfile Tablespaces

Traditionally, Oracle only had one type of tablespace, which is now what is called a smallfile tablespace. This means that the tablespace is made up of many datafiles. With the increasing size of modern day databases, Oracle decided to add a bigfile tablespace to the mix. The bigfile tablespace has the following advantages:

  • Only contains one large datafile per tablespace
  • Removes some overhead in resource when compared to management of hundreds of datafiles
  • Increases potential total storage size of the Oracle database

Note: Unless you have changed the database default all of your tablespaces will be created as smallfile ones, but you can explicitly create bigfile tablespaces.

Key Views for Tablespace Information

dba_data_files
dba_tablespaces
dba_free_space
v$datafile

Create TEMP Tablespace

The TEMPORARY tablespace in Oracle is a special kind of tablespace so let’s look at the key points:

  • Used for sorting data which does not fit into the Program Global Area (PGA)
  • Segments created in this area are managed by Oracle and no normal objects should be created in it (you can’t anyway, apart from with actual temporary tables)
  • Requires a different create tablespace syntax, as shown below
  • You can have multiple temporary tablespaces in one database and users can be assigned to different ones
  • It’s important to monitor TEMP space usage
  • No REDO data is generated for segments in the TEMP tablespace

Create TEMP Tablespace Syntax

CREATE TEMPORARY TABLESPACE temp
 TEMPFILE
'<path>/<name>.dbf' SIZE XXM,
'<path>/<name>.dbf' SIZE XXM
EXTENT MANAGEMENT LOCAL UNIFORM SIZE XXM;

Temporary tablespaces are very important for ensuring that sorts, like index creations and SQL queries, can complete if they run out of PGA to sort the results in. In an ideal world all the sorting would be done in memory because it’s much more efficient but the TEMP tablespace acts as an overspill area. Have a read about it in my article on monitoring TEMP space as it will give you some useful queries and tips.

Views for Temporary Tablespace Information

dba_temp_files
dba_temp_free_space
v$tempfile

The UNDO Tablespace

I have written another article about UNDO, what it is, how to monitor it, etc so have a read of that first if you are not familiar with the basic concepts. Here I just want to cover ways to create the UNDO tablespace and a few other bits.

If you used the DBCA to create your database it should have automatically created an UNDO tablespace for you, if you created an 11g database.

If you didn’t use the DBCA and ran the CREATE DATABASE statement manually, the UNDO tablespace would have been created automatically if the UNDO_MANAGEMENT = AUTO parameter was configured.

You can, however, create an UNDO tablespace manually. Maybe you want to create a new one and drop the original. This is the statement you would use:

CREATE UNDO TABLESPACE UNDOTBS01
 DATAFILE
'<path>/<name>.dbf' SIZE XXM AUTOEXTEND ON NEXT XXM MAXSIZE XXM,
'<path>/<name>.dbf' SIZE XXM AUTOEXTEND ON NEXT XXM MAXSIZE XXM;

Generally speaking, you should not need to do much with the UNDO tablespace. Maybe add extra datafiles to it, which can be done with the ALTER TABLESPACE command like so:

ALTER TABLESPACE UNDOTBS01 ADD DATAFILE
 '<path>/<name>.dbf' SIZE XXM AUTOEXTEND ON NEXT XXM MAXSIZE XXM;

Another key aspect of database administration is to monitor the UNDO tablespace. The UNDO tablespace is so fundamental to the smooth operation of the database that it should definitely not be overlooked.

I hope that the information I have provided in the article has been of use to you, and as always please leave a comment if you have anything to say. Good, bad or ugly!

Cheers,

Rob

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *