Oracle DBA FAQ - Managing Oracle Tablespaces and Data Files

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4

(Continued from previous part...)

How To Create a Table in a Specific Tablespace?

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space 
  2  AS SELECT * FROM employees;
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM USER_TABLES
  3  WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME                     TABLESPACE_NAME    NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM                        MY_SPACE           -  
EMPLOYEES                      USERS              107 
...

How To See Free Space of Each Tablespace?

One of the important DBA tasks is to watch the storage usage of all the tablespaces to make sure there are enough free space in each tablespace for database applications to function properly. Free space information can be monitored through the USER_FREE_SPACE view. Each record in USER_FREE_SPACE represents an extent, a contiguous area of space, of free space in a data file of a tablespace.

Here is SQL script example on how to see free space of a tablespace:

SQL> connect HR/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES 
  2  FROM USER_FREE_SPACE 
  3  WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                5   10354688
USERS                                   4  101974016
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536

This tells us that:

MY_SPACE has a single free extent of 10MB.
USERS has one big free extent of 100MB, and many small free extents of 64KB.

How To Bring a Tablespace Offline?

If you want to stop users using a tablespace, you can bring it offline using the ALTER TABLESPACE ... OFFLINE statement as shown in the following script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space OFFLINE NORMAL;
Tablespace altered.

After bringing a tablespace offline, you can backup or rename the data file safely.

(Continued on next part...)

Part: 1 2 3 4

More...