Data Files
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.
AUTOALLOCATE - means that the extent sizes are managed by Oracle.
This might help conserve space but will lead to fragmentation. This is usually recommended for small tables or in low managed systems.
SQL> CREATE TABLESPACE test
2 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST01.DBF' SIZE 2M
3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
UNIFORM - specifies that the extent allocation in the tablespace is in a fixed uniform size. The extent size can be specified in M or K. The default size for UNIFORM extent allocation is 1M. Using uniform extents usually minimizes fragmentation and leads to better overall performance
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:
You can not drop a default temporary tablespcae.
You can not take offline default temporary tablespace.
Temporary tablespace can not be make a permanent type 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:
SYSTEM Tablespace
SYSAUX Tablespae
Default Temporary Tablespace
Tablespace with active undo segment
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
Take the tablespace offline
Copy the files or use the operating system command to move the file
Execute the following command:
ALTER TABLESPACE userdata
RENAME
DATAFILE ‘/u01/oradata/userdata01.dbf’
TO ‘/u01/oradata/userdata/userdata01.dbf’;
Bring the tablespace online
Delete the past location file if mandatory.
Or
Shut down the databse.
Copy the files or use the operating system command to move the file
Mount the Database
Execute the following command:
ALTER DATABSE RENAME
FILE ‘/u01/oradata/system01.dbf’
TO ‘/u03/oradata/system01.dbf’;
Open the database.
Convert Dictionary Managed Tablespace to Locally Managed Tablespace:
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
Obtaining Tablespace Information
Tablespace Information
Ø DBA_TABLESPACES
Ø V$TABLESPACES
Ø DBA_USERS
Data file Information
Ø DBA_DATA_FILES
Ø V$DATAFILE
Temp file Information
Ø DBA_TEMP_FILES
Ø V$TEMPFILE