Tablespaces & DataFiles
Oracle stores data logically in tablespaces and physically in data files
Tablespaces:
1.Can belong to only one dataase at a time
2. Consist of one or more data files
3. Are further divied into locgical units of starage
Data files:
1.Can belong to only one tablespace and one database
2. Are a repository for schema object data
Types of Tablespaces
1.SYSTEM tablespace
a. Created with the database
b.Contains the data dictionary
c.Contains the SYSTEM undo segment
2.Non-SYSTEM tablespace
a.Separate segmets
b.Eases space administration
c.Controls amount of space allocated to a user
also types : permanent, undo , temporary
Space Mgmt in Tablespaces
1.Locally maaged tablespace:
a.Free extents are managed in the tablespace
b.Bitmap is used to record free extents.
c. Each bit corresponds to a block or group of blocks.
d. Bit value indicates free or used.
2.Dictionary-managed tablespace:
a. Free extents are managed by the data dictionary.
b.Appropriate tables are updated when extents are allocated or dellocated
推荐使用local Managed TS
Dictionary-Managed TS
1.Extents are managed in the data dictionary
2.Each segment stored in the tablespace can have a different storage clause.
3.Coalescing is required
CREATE TABLESPACE userdata DATAFILE 'xxxx.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE(initial 1M NEXT 1M PCTINCREASE 0)
</pre><h1 class="sql" name="code">Locally Managed Tablespace</h1><span style="font-size:18px;"></span><pre class="sql" name="code">1.Reduce contention on data dictionary tables
2.No undo generated when space allocation or deallocation occurs
3.No coalescing required
<pre class="sql" name="code">CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGMENT LOCAL UNIFORM SIZE 128K
Migrating a DM SYSTEM TS
Migrate a dictionary manged SYSTEM tablespace to locally managed:
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
Undo Tablespace
1.Used to store undo segments
2.Cannot contain any other objects
3.Extents are locally namaged
4.Can only use the DATAFILE and EXTENT MANAGEMENT clauses
CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M
Temporary Tablespaces
1.Used for sort operations
2. Can be shared by multiple users
3.Cannot contain any permanent objects
4.Locally managed extents recommended
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradta/temp01.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Create temporary tablespace mytemp tempfile '/u01/oradata/wilson/mytemp01.dbf' size 100M extent management local;
Restrictions
Defualt tmporary tablespaces cannot be:
1.Dropped until after a new default is made available
2.Taken Offline
3.Altered to a permanent tablespace
Read-Only Tablespaces
Use the following command to place a tablespace in read-only mod:
ALTERTABLESPACE userdata READ ONLY
1.Causes a checkpoint
2.Data available only for read operations
3.Objects can be dropped from tablespace
Taking a Tablespace Offline
1.Not available for data access
2.Tablespaces that cannot be aken offline:
a.SYSTEM tablespace
b.Tablespaces with active undo segments
c.Default temporary tablespace
3.To take a tablespace offline:
ALTER TABLESPACE userdata OFFLINE
4.To bring a tablespace online:
ALTER TABLESPACE userdata ONLINE
Resizeing a Tablespace
A tablespace can be resized by :
1. Change the size of a data file:
a.Automatically using AUTOEXTEND
b.Manually using ALTER DATABASE
2.Adding a data file using ALTER TABLESPACE
Enable Automatic Extension
1.Can be resized automatically with the following commands:
a.CREATE DATABASE
b.CREATE TABLESPACE
c.ALTER TABLESPACE ... ADD DATAFILE
b.Example:
CREATE TABLESPACE user_data DATAFILE '/u01/oradata/userdata01.dbf' SIZE 200M AUTOEXTEND ON
NEXT 10M MAXSIZE 500M
c.Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled
Manually Resizing Date File
1.Manually increase or decrease a data file size using ALTER DATABASE
2.Resizeing a data file adds more space without adding more data files
3.Manual resizeing of a data file reclaims unused space in database
Example:
ALTER DATABASE DATAFILE '/u01/oradata/userdaa0.dbf' RESIZE 200M
Adding Data Files to a TS
Increases the space allocated to a tablespace by adding additional data files
Add DATAFILE clause is used to add a data file
Example:
ALTER TABLESPACE user_data ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M
Methods for Moving DF
1.ALTER TABLESPACE
a.TABLESPACE must be offline.
b. Target data files must exist
ALTER TABLESPACE userdate RENAME DATAFILE '/u01/oradata/allan/1.dbf' TO '/u01/radata/allan/2.dbf'
2.ALTER DATABASE
a. Database must be mountd
b.Targe datafile must exit
ALTER DATABASE RENAME FILE '/u01/oradata/system01.dbf' TO '/u01/oradata/system02.dbf'
Dropping Tablespaces
1.You cannot drop a tablespace if it:
a.Is the SYSTEM tablespace
b. Has active segments
2.INCLUDING CONTENTS drops the segments
3.INCLUDING CONTENTS AND DATAFILES deletes data files
4.CASCADE CONSTRAINTS drops all referential integrity constraints.
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;
GEt Tablespace Information
Obtaining tablespace and data file information can be obtained by querying the following:
1.Tablespace information:
a.DBA_TABLESPACES
b.v$TABLESPACE
2.Data file information:
a.DBA_DATA_FILES
b.V$DATAFILE
3.Temp file information:
a. DBA_TEMP_FILES
b.V$TEMPFILE