The Following Tablespaces exists in Oracle 10g Database: § SYSTEM Tablespace § SYSAUX Tablespace § TEMPORARY Tablespace (TEMP) § UNDO Tablespace (UNDOTBS1) § USERS Tablespace (default users tablespace created)
SYSTEM Tablespace: All major database systems include something called a Data Dictionary. A Data Dictionary describes the contents of the database. Which tables do I own? What columns are in those tables? Do I have permissions to view other tables? Do these tables have indexes? Where these tables are physically located? What code runs when I execute a stored procedure? The Data Dictionary contains the answer to all of these questions and more. System Tablespace contain these data dictionary.
SYSAUX Tablespace: SYSAUX is the name of the compulsory tablespace, introduced in Oracle 10g, to support optional database components (called occupants) like AWR, Statspack, Oracle Streams, etc.
TEMPORARY Tablespace: Temporary Tablespace are used to store short life span data. For example: sort result.
UNDO Tablespace: UNDO Tablespace used for capturing “old imeage” date for rollback.
USER Tablespace: A default user tablespace allows defining a tablespace that will be the default tablespaces for users created objects.
Creating and Managing Tablespace: In oracle 10g big file tablespaces can be created. Bigfile tablespaces are built on a single datafile (or temp file), which can be as many as 2^32data blocks in size. So, a bigfile tablespace that uses 8KB data blocks can be as much as 32TB in size.
It is usually use in very large database. When there is very large database with thousand of datafile, then it will take long time for updating datafile header, such as checkpoint.
SQL> create bigfile tablespace users2 datafile 'C:\oracle\product\10.2.0\oradata\dba12\USERS02.DBF' size 25G;
In smallfile tablespace Each datafile can be as many as 2^22 data blocks in size. So datafiles in a smallfile tablespace that uses 8KB data blocks are limited to 32GB. The smallfile tablespace can have as many as 1,022 datafiles Tablespace created.
SQL> create tablespace users1 datafile 'C:\oracle\product\10.2.0\oradata\dba12\USERS03.DBF' size 25M;
Tablespace created.
Locally Managed Tablespace: Using Locally Managed Tablespace, each tablespace manages its own free and used space within a bitmap structure stored in one of the tablespace's data files.
When creating a
locally managed tablespace, you can specify the extent allocation method to be
used.
SQL> CREATE TABLESPACE test 2 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST01.DBF' SIZE 2M 3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE test1 2 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST02.DBF' SIZE 2M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;
Locally managed tablespaces have the following advantages over dictionary-managed tablespaces: · Local management of extents tracks adjacent free space, eliminating coalescing free extents. · Reliance on data dictionary is reduced. This minimizes access to the data dictionary, potentially, improving performance and availability.
Dictionary Managed Tablespace: For a tablespace that uses the data dictionary to manage its extents, the Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or dealloated.
CREATE TABLESPACE USERDATA DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST03.DBF' SIZE 100M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT 1M);
Segment in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient,
Changing the storage settings: The storage setting for dictionary managed tablespaces can be altered but locally managed tablespace can not be altered.
ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
ALTER TABLESPACE userdata DEFAULT STORAGE ( INITIAL 2M NEXT 2M MAXEXTENT 999);
Creating UNDO Tablespace: CREATE UNDO TABLESPACE undo01 DATAFILE ‘/u01/oradata/undo101.dbf’ SIZE 50M;
Creating TEMPORARY Tablespace: SQL> create temporary tablespace temp2 2 tempfile 'C:\oracle\product\10.2.0\oradata\dba12\TEMP02.DBF' SIZE 5M;
SQL> select username,temporary_tablespace from dba_users where username='SYS';
USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYS TEMP
After database creation, a default temporary tablespace can be set by creating a temporary tablespace and then altering the database
ALTER DATABSE DEFAULT TEMPORARY TABLESPACE temp2;
Restrictions of temporary tablespace:
Taking Tablespace ONLINE/OFFLINE: Taking a Tablespace OFFLINE: ALTER TABLESPACE userdata OFFLINE;
Taking a Tablespace ONLINE: ALTER TABLESPACE userdata ONLINE;
Some Tablespace can not be taken OFFLINE:
Taking a Tablespace READ ONLY: SQL>ALTER TABLESPACE userdata READ ONLY;
Taking a Tablespace READ WRITE: SQL>ALTER TABLESPACE userdata READ WRITE;
Dropping Tablespaces: SQL>DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILE;
Changing the size of the Datafile SQL> ALTER DATABASE DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\USERS02.DBF' RESIZE 15M;
Enable Automatic Extension SQL> CREATE TABLESPACE test3 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST03.DBF' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 30M;
Adding DATA Files to a Tablespace SQL> ALTER tablespace test ADD DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\USERS02B.DBF' SIZE 10M;
Changing Location of Data Files
ALTER TABLESPACE userdata RENAME DATAFILE ‘/u01/oradata/userdata01.dbf’ TO ‘/u01/oradata/userdata/userdata01.dbf’;
Or
ALTER DATABSE RENAME FILE ‘/u01/oradata/system01.dbf’ TO ‘/u03/oradata/system01.dbf’;
Convert Dictionary Managed Tablespace to Locally Managed Tablespace: SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
Obtaining Tablespace Information
Ø DBA_TABLESPACES Ø V$TABLESPACES Ø DBA_USERS
Ø DBA_DATA_FILES Ø V$DATAFILE
Ø DBA_TEMP_FILES Ø V$TEMPFILE |