How to Create Oracle 12c Tablespaces

By Chris Ruel, Michael Wessler

Database objects are logically contained within tablespaces. In Oracle 12c tablespace is a logical storage container that houses physical data files in which database tables and indexes are stored.

In a database, tablespaces are created in two ways:

  • By default for internal database structures

  • By the database administrator (DBA) to store user objects

For example, a data tablespace has one or more database files on the OS’s file system. Within that tablespace, one or more data table is created, and the data is stored in the tablespace’s corresponding data files. You can see a graphical example of a tablespace and its contents which includes:

  • Logical tablespace: This stores data tables for the user.

  • Physical data file: You can add data files as necessary.

  • Database objects: Here you can see objects from different users.

    image0.jpg

Multiple users can store their objects in the same tablespace. Tablespaces are available to any user with objects in the database although organizing different users in different tablespaces is better for performance and manageability. Also, try to separate data and index objects into separate tablespaces (and thus database files) to reduce disk contention as index and table segments for the same object are accessed.

As you add objects and tables grow, Oracle manages the size of these things:

  • Segments are any objects requiring storage.

  • Extents are the unit of storage Oracle uses to allocate space for segments.

Oracle tracks the growth of segments and extents and knows where each object is stored.

These standard tablespaces are listed with their corresponding data files:

SYS@dev12c> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES
 2 ORDER BY TABLESPACE_NAME;
TABLESPACE_NAME FILE_NAME
---------------- ---------------------------------------------
EXAMPLE     /u01/app/oracle/oradata/dev12c/example01.dbf
MY_DATA     /u01/app/oracle/oradata/dev12c/my_data01.dbf
SYSAUX      /u01/app/oracle/oradata/dev12c/sysaux01.dbf
SYSTEM      /u01/app/oracle/oradata/dev12c/system01.dbf
UNDOTBS1     /u01/app/oracle/oradata/dev12c/undotbs01.dbf
USERS      /u01/app/oracle/oradata/dev12c/users01.dbf
6 rows selected.

The EXAMPLE tablespace is for Oracle demo objects, and MY_DATA contains a demo table. SYSAUX and SYSTEM are for internal database objects. UNDOTBS1 is for undo (rollback) objects. USERS is the default tablespace for objects created by users who didn’t specify a tablespace when they created objects.

To see each tablespace, space available, type, and extent management, go to Enterprise Manager Database Express and choose Storage→Tablespaces, shown below.

image1.jpg

From the Tablespaces management screen, you can choose Actions→Create to create a tablespace for storing application data tables.

image2.jpg

Then follow these steps:

  1. On the General options screen, type a tablespace name.

    In this example, the tablespace name is MY_DATA.

  2. Select the tablespace type:

    • Permanent for normal objects, such as tables and indexes.

    • Temporary for temp storage typically used for processing data. Data disappears from objects either after a commit or a session logs out.

    • Undo for storing undo segments.

    Do not select the Set as Default check box for this example unless you want this tablespace to be the default tablespace for all users.

  3. Select the Smallfile radio button (for data files less than 32GB).

    These files are easier to manage than bigfiles.

  4. Select the Online radio button so the tablespace is available immediately.

  5. Click the right arrow to go to the next screen.

  6. On the Add Datafiles option screen, enter this name for your file:

    /u01/app/oracle/oradata/dev12c/my_data01.dbf

    Set the remaining options as follows:

    • File Size: Leave File Size set at its default 100M.

    • Reuse Existing File: You should only check this box, for example, if you are re-creating the tablespace over an old one that was dropped.

    • Auto Extend: Allows the data file to grow if more space is needed. Leave this check box selected.

    • Increment: Tells Auto Extend how much to grow at a time. Leave this set at default 100M.

    • Maximum File Size: This allows the data file to grow to a specified size up to 32GB. Change that to 8G.

    This is what an added data file and filename for my_data01.dbf looks like.

    image3.jpg

  7. Click the right arrow to go to the next screen.

  8. On the Space options screen, follow these two quick steps:

    It’s painless:

    1. Leave Block Size set as Database Default.

    2. For Extent Allocation, select the Automatic radio button. This is best for databases that have normal, regular growth.

      image4.jpg

  9. Click the right arrow to go to the next screen.

    From the Logging options screen, you choose whether you want operations logged on the tablespace. You almost always want to choose logging. The only time you may not want logging is if this tablespace is going to store objects that are part of a load process, where every night they are batch-loaded, and then the data is moved to other tablespaces for long-term storage.

    Without logging, you compromise recoverability. The Force Logging check box sets that even if someone tries to skipping logging on an operation in this tablespace, Oracle will not allow the logging to be skipped.

  10. Leave Logging selected and Force Logging unselected.

  11. Click the right arrow to go to the next screen.

  12. On the Segments option screen, choose a Segment Space Management and Compression option:

    • Automatic: For ease of management, let Oracle manage the extent and segment growth.

    • Manual: Manually specify the size of each unit of allocation.

    • Compression: Choose from None (no compression), Basic (SELECT friendly compression, no so DML friendly), and OLTP (DML friendly, extra, licensed feature).

  13. Click the Show SQL button to see the actual Structured Query Language (SQL) being executed:

    CREATE SMALLFILE TABLESPACE "MY_DATA" DATAFILE
    '/u01/app/oracle/oradata/dev12c/my_data01.dbf'
    SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 8G
    LOGGING DEFAULT NOCOMPRESS ONLINE
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO;
  14. When you’re satisfied with your options, click OK.

    The tablespace is created.

  15. Repeat Steps 1 through 14 to create the index tablespace.

    If you name the second tablespace ACME_INDEX, you have these options when it’s created:

TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------
SYSTEM     /u01/app/oracle/oradata/dev12c/system01.dbf
SYSAUX     /u01/app/oracle/oradata/dev12c/sysaux01.dbf
UNDOTBS1    /u01/app/oracle/oradata/dev12c/undotbs01.dbf
USERS      /u02/app/oracle/oradata/dev12c/users01.dbf
MY_DATA     /u01/app/oracle/oradata/dev12c/my_data01.dbf
MY_INDEX    /u01/app/oracle/oradata/dev12c/my_index01.dbf
6 rows selected.

Now you have tablespaces.