Data, in Oracle, is logically stored in tablespaces and physically stored in datafiles. The illustration below shows the file and logical structures according to the operating system. (TableSpace can also be abbreviated to TS.)
It is necessary to understand that a TS stores schema objects that are constant/non-volatile. The Data Dictionary is kept in a special system TS. Version 10G introduced a mandatory auxilary system TS. If migrating from a version earlier than 10G, the auxiliary TS must be created before the migration process.
The structure of a TS has been designed so as to be easier for a database to control and to ease maintenance activities for administrators.
An example of this is enabling separation of a TS for every department and the data that gets generated when creating new users in the system (e.g. accounting_tbs, sales_tbs). Activities such as backups of departmental data should be scheduled for different dates so that they can be better supervised.
Such scheduling helps prevent possible errors from one departmental backup affecting other departments’ data. Additionally, the separated data allows for greater ease and flexibility when granting users privileges.
Temporary TS: A user’s session data, such as queues and sort transactions, gets stored in a temp file rather than a datafile.
Undo TS: Used to maintain consistency of data that is read. (The consistency of undo data will be explained in further sections)
TableSpace Types
1- System tablespace: Gets created along with the database and is compulsory for every database. Stores the Data Dictionary and parts of programs.
2- Non-System tablespace: Are created by database administrators (DBAs) and users.
The 2 types of Extent options when creating TableSpaces
1- Dictionary Managed TableSpace (DMT): Extent Size is managed by the Data Dictionary. This type of TS is not generally recommended.
2- Locally Managed TableSpace (LMT): A TS where where the Extend Size is managed locally (using a bitmap structure). When a TS management option hasn’t been selected while creating it, it becomes a LMT by default.
1 2 3 4 5 6 7 8 9 |
create tablespace test datafile '/u01/DataFiles/test_tbs01' size 50MB Extent management LOCAL Uniform SIZE 128K ; |
A LMT that has been created using a UNIFORM SIZE of 128K & a database block size of 2K will have a corresponding Extent block size of 64K.
An Extent size of 128K corresponds to 1 bit and a bit’s status (whether it’s empty or full) can be determined using the bitmap.
Reminder:
The empty areas in the manual segment’s space are managed using the bitmap in the Freelist Automatic Space Management.
The Segment Space Management is set to Automatic by default and is more efficient. There is no need to coalesce the free areas.
1 2 3 4 5 6 7 8 9 |
create tablespace test datafile '/u01/DataFiles/test_tbs01' size 50M Extent management LOCAL SEGMENT SPACE MANAGEMENT AUTO; |
There are two options for managing the size of an Extent.
1. To have it automatically managed by the dbms. Using the AUTOALLOCATE option, starting from 64KB Oracle can automatically adjust the values to a maximum of 64MB.
2. By entering an amount in KB or MB using the [b]UNIFORM[/b] option. Once a UNIFORM size has been set whilst creating a database it cannot later be changed. If an Extent size hasn’t manually been entered, Oracle automatically assigns a value of 1MB.
If all the extents in the database are, on average, the same size then it is recommended to manually set the extent size using the uniform option.
An example of where Extent sizes are important, in terms of performance, are situations where the data held in extents are frequently queried – having smaller extent sizes will negatively impact performance. If you plan on setting the sizes of the extents in Oracle, the values below are recommended according to database usage.
64 KB small segments
1 MB medium segments
63 MB large segments
If the average growth rate is not known while creating the database and if all segments in the database will not be the same size then it is recommended to use the AUTOALLOCATE option.
The diagram below summarizes the mentioned points:
The type of Segment Space Management is set while creating the database, it isn’t possible to later change this option and the setting applies to all the database segments.
LOCAL UNIFORM: There needs to be a minimum of 5 available database blocks to select this option.
LOCAL AUTOALLOCATE: With this option, if the database block size is 16K or more, the DBMS sets a minimum block size of approximately 64K and creates a minimum of 5 database blocks.
1 2 3 4 5 |
create tablespace sales datafile '/u1/appa/oracle/orcl/sales_tbs.dbf' size 50M ; |
1 2 3 4 5 6 7 8 9 |
create tablespace accounting datafile '/u1/appa/oracle/orcl/accounts_tbs.dbf' size 50M extent management LOCAL segment space management auto; |
The previous 2 codeblocks were used to create the 2 tablespaces using different parameters, they both utilize locally managed extents (as it is the default option.)
1 |
select * from dba_tablespaces; |
TABLESPACE_NAME | SALES | ACCOUNTING | SHIPPING |
BLOCK_SIZE | 8192 | 8192 | 8192 |
INITIAL_EXTENT | 65536 | 65536 | 65536 |
NEXT_EXTENT | |||
MIN_EXTENTS | 1 | 1 | 1 |
MAX_EXTENTS | 2147483645 | 2147483645 | 2147483645 |
PCT_INCREASE | |||
MIN_EXTLEN | 65536 | 65536 | 65536 |
STATUS | ONLINE | ONLINE | ONLINE |
CONTENTS | PERMANENT | PERMANENT | PERMANENT |
LOGGING | LOGGING | LOGGING | LOGGING |
FORCE_LOGGING | NO | NO | NO |
EXTENT_MANAGEMENT | LOCAL | LOCAL | LOCAL |
ALLOCATION_TYPE | SYSTEM | SYSTEM | SYSTEM |
PLUGGED_IN | NO | NO | NO |
SEGMENT_SPACE_MANAGEMENT | AUTO | AUTO | AUTO |
DEF_TAB_COMPRESSION | DISABLED | DISABLED | DISABLED |
RETENTION | NOT APPLY | NOT APPLY | NOT APPLY |
BIGFILE | NO | NO | NO |
1 2 3 4 5 6 7 |
create tablespace stock datafile '/u01/app/oracle/oradata/orcl/stock01_tbs.dbf' SIZE 50M extent management local uniform ; |
The previous codeblock created a tablespace using the local uniform parameter, but as a size wasn’t specified, a default amount of 1MB has been applied.
TABLESPACE_NAME | SALES | ACCOUNTING | PATIENTS |
BLOCK_SIZE | 8192 | 8192 | 8192 |
INITIAL_EXTENT | 65536 | 65536 | 1048576 |
NEXT_EXTENT | 1048576 | ||
MIN_EXTENTS | 1 | 1 | 1 |
MAX_EXTENTS | 2147483645 | 2147483645 | 2147483645 |
PCT_INCREASE | 0 | ||
MIN_EXTLEN | 65536 | 65536 | 1048576 |
STATUS | ONLINE | ONLINE | ONLINE |
CONTENTS | PERMANENT | PERMANENT | PERMANENT |
LOGGING | LOGGING | LOGGING | LOGGING |
FORCE_LOGGING | NO | NO | NO |
EXTENT_MANAGEMENT | LOCAL | LOCAL | LOCAL |
ALLOCATION_TYPE | SYSTEM | SYSTEM | UNIFORM |
PLUGGED_IN | NO | NO | NO |
SEGMENT_SPACE_MANAGEMENT | AUTO | AUTO | AUTO |
DEF_TAB_COMPRESSION | DISABLED | DISABLED | DISABLED |
RETENTION | NOT APPLY | NOT APPLY | NOT APPLY |
BIGFILE | NO | NO | NO |
BigFile TableSpace
A BigFile TableSpace is a type of TS that was introduced with Oracle 10G. It can contain a single very large datafile, a 32TB datafile using a block size of 8K and a 128TB datafile using a block size of 32K.
The following points should be considered when considering using BigFile TableSpaces:
• They were developed for use in Automatic Storage Management or ASM or other expandable disk architectures, such as RAID.
• The DBMS creates a smallfile tablespace by default.
• By using the ALTER TABLESPACE syntax users can perform operations on a whole tablespace rather than individual datafiles, this is known as datafile transparency.
It is not advisable to use BigFile TS in the following scenarios:
• Storage structures that don’t use striping, i.e. storing data sequentially on different physical devices.
• In operating systems that don’t support large file sizes.
BigFile TSs are only supported in Locally Managed TSs that use Automatic Segment Space Management, with 3 exceptions:
• Undo TS
• Temporary TS
• System TS
Table_Space | SSM |
SYSTEM | MANUAL |
UNDOTBS1 | MANUAL |
SYSAUX | AUTO |
TEMP | MANUAL |
USERS | AUTO |
UNDOTBS2 | MANUAL |
EXAMPLE | AUTO |
SALES | AUTO |
ACCOUNTING | AUTO |
TEMP02 | MANUAL |
SHIPPING | AUTO |
PATIENTS | AUTO |
Creating a Bigfile TS:
1 2 3 4 5 |
create bigfile tablespace customer datafile '/u01/app/oracle/oradata/orcl/customer_bf_tbs.dbf' SIZE 100G |
If the default TS option has been configured as bigfile then there is no need to enter a BIGFILE parameter. However if a smallfile TS is to be created then a SMALLFILE parameter should be entered.
1 |
alter tablespace customer resize 200G; |
It is possible to change the size of the TS.
1 |
alter tablespace customer autoextend on next 10G; |
Using the above statement the TS can be configured to grow automatically, depending on requirements.
1 2 3 4 5 |
DBA_TABLESPACES USER_TABLESPACES V$TABLESPACE |
The BigFile column in the TS indicates whether the TS is a BigFile or not.
Temporary TableSpace
The temporary tablespace store (temporary) session data and is used for sorting and indexing operations as well as for user queries. A temp TS is created for every user that can be configured during creation or later (CREATE USER, ALTER USER).
Permanent/non-volatile objects cannot be created in a Temp TS which is composed of temporary tables.
If a database has been created according to the recommended settings then by default a temporary TS is created for all the users, similarly in a temporary TS can be created afterwards if it hasn’t been created along with a new database.
A number of users can also use a common tablespace to share information.
With an extent size setting of “uniform” and 64K, Oracle writes data to the Program Global Area (PGA) in 64KB stacks which improves performance. In data warehouses the suggested extent size is 1MB.
Creating more than a single TS and sharing it among users is another method of increasing performance.
• V$SORT_SEGMENT
• V$TEMPSEG_USAGE
• V$TEMPFILE
• V$DATAFILE
• DBA_TEMP_FILES
• DBA_DATA_FILES
Usage information can be determined through querying from the views.
1 2 3 4 5 |
create temporary tablespace developertemp tempfile '/u01/app/oracle/oradata/orcl/developertemp01.dbf' size 50M ; |
1 |
select * from dba_tablespaces ; |
The default values in the temporary TS can be viewed using the previous line.
TABLESPACE_NAME | DEVELOPERTEMP |
BLOCK_SIZE | 8,192 |
INITIAL_EXTENT | 1,048,576 |
NEXT_EXTENT | 1,048,576 |
MIN_EXTENTS | 1 |
MAX_EXTENTS | |
PCT_INCREASE | 0 |
MIN_EXTLEN | 1,048,576 |
STATUS | ONLINE |
CONTENTS | TEMPORARY |
LOGGING | NOLOGGING |
FORCE_LOGGING | NO |
EXTENT_MANAGEMENT | LOCAL |
ALLOCATION_TYPE | UNIFORM |
PLUGGED_IN | NO |
SEGMENT_SPACE_MANAGEMENT | MANUAL |
DEF_TAB_COMPRESSION | DISABLED |
RETENTION | NOT APPLY |
BIGFILE | NO |
As can be seen in this table, all Extents are locally managed and have a uniform size of 1MB.
To change the Uniform Size:
1 2 3 4 5 6 7 |
create temporary tablespace nameOne tempfile '/datafile path/datafileName' size ; extent management local uniform size xx; |
1 |
create bigfile temporary tablespace |
This code creates a bigfile tablespace.
An ALTER TABLESPACE syntax cannot change a permanent TS to a temporary TS. It can only be used to add datafiles and to switch a tempfile offline/online.
1 2 3 4 5 |
alter tablespace developertemp add tempfile '/u01/app/oracle/oradata/orcl/developertemp02.dbf' size 50m; |
1 2 3 |
alter tablespace developertemp tempfile offline; alter tablespace developertemp tempfile offline; |
The same can be accomplished using the code below.
1 2 3 |
alter database tempfile '/u01/app/oracle/oradata/orcl/developertemp02.dbf' offline; alter database tempfile '/u01/app/oracle/oradata/orcl/developertemp02.dbf' offline; |
It should be noted that what has been made offline is the datafile and not the temporary TS in which it is held. To view the statuses the v$tempfile can be used.
1 |
alter tablespace developertemp tempfile offline; |
To change the size of the tempfile.
1 |
alter database tempfile '/u01/app/oracle/oradata/orcl/developertemp02.dbf' resize 100M; |
To cancel the tempfile and to delete the datafile.
1 |
alter database tempfile '/u01/app/oracle/oradata/orcl/developertemp02.dbf' drop including datafiles; |
Changing the name and location:
1. Take the TS offline.
2. Moved from the operating system and/or change its name.
3. Update the control file using the “alter database rename file” syntax.
Temporary TableSpace Group
Temporary TableSpace Group is a feature, introduced with 10G, that enables users in different sessions to concurrently use the temporary TS.
• Formed out of a minimum of 1 TS, no maximum amount.
• The group automatically gets deleted if all group members are deleted.
• The same name is in the group name field of the TS for all group members.
• The temporary TS name and the group name that a member belongs to can not be identical.
• Members can be assigned the group name instead of the temporary TS.
Benefits of using a Temporary TS Group:
• Stops congestion by preventing more than one TS being used simultaneously.
• Perfomance benefits from spreading TS across several physical disks.
• Increases the productivity of many temporary TS running in parallel.
• One user can access more than one TS at the same time.
Creating TableSpaces
The following code changes the default temp tablespace.
1 |
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempxx; |
Deleting the Temporary TS with the datafile.
1 |
DROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES; |
This statement cancels the datafile and erases it from the system.
1 |
alter database tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' drop including datafiles; |
The alertlog error entry (reminder : “tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log“)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Wed Feb 10 20:40:24 2010 alter database tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' drop including datafiles Wed Feb 10 20:40:25 2010 Deleted file /u01/app/oracle/oradata/orcl/temp01_01.dbf Completed: alter database tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' drop including datafiles Wed Feb 10 20:43:47 2010 alter database tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' drop including datafiles Wed Feb 10 20:43:47 2010 ORA-1516 signalled during: alter database tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' drop including datafiles ... |
When queried
1 |
select * from dba_tablespace_groups; |
the DBMS outputs the following error:
1 |
ORA-01516: nonexistent log file, datafile, or tempfile "/u01/app/oracle/oradata/orcl/temp01_01.dbf" |
First, remove temp01 from the default temporary group:
1 |
alter tablespace temp01 tablespace group ''; |
Then:
1 |
DROP TABLESPACE TEMP01 INCLUDING CONTENTS and datafiles; |
From now on running select * from dba_tablespace_groups; will not produce an error message.
1 2 3 4 5 |
create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' size 50M tablespace group tempgroup01; |
Using the previous syntax, a temporary TS called temp01 is created as well as a temporary group called tempgroup1, temp01 is included in tempgroup01.
A temporary TS must first be created before being added to a group. The code below assigns a temporary tablespace to a group:
1 |
alter tablespace temp02 tablespace group tempgroup01 ; |
Assigning a default temporary TS to a group:
1 |
alter database default temporary tablespace tempgroup01; |
Assigning a user to a default temporary group:
1 2 3 4 5 |
create user user_name identified by user_password default tablespace tablspace_name temporary tablespace tempgroup01; |
Re-assigning a user to a temporary tablespace
alter user user_name temporary tablespace tempgrup01;
To view temporary tablespace information:
1 2 3 4 5 |
select group_name "temp_grup", tablespace_name "ts_name" from dba_tablespace_groups; |
1 2 3 4 5 6 7 |
temp_grup ts_name ----------- -------- TEMPGRUP01 TEMP TEMPGRUP01 TEMP01 |
To view the users as well as the temporary tablespaces that they have been assigned to.
1 |
select username, temporary_tablespace from dba_users; |
1 2 3 4 5 6 7 8 9 |
USERNAME TEMPORARY_TABLESPACE -------- -------------------- HR TEMPGROUP01 SCOTT TEMPGROUP01 TSMSYS TEMPGROUP01 |
As tempgroup_1 has been assigned to a default temporary TS, the users within the group have also been assigned the properties of tempgroup_1.
Creating a NonStandard Block Size
TableSpaces can be created from different DB_BLOCK_SIZE settings than what are in the initialization file, spfile. This feature allows moving TSs between databases with different block sizes.
DN_nK_CACHE_SIZE must have at least one assigned value in the initialization parameters.
1 2 3 4 5 6 7 8 9 10 11 |
create tablespace finance datafile '/u01/app/oracle/oradata/orcl/temp01_01.dbf' size 50M extent management local uniform size 128K BLOCKSIZE 8K; |
Nologging
If TableSpace was made using the nologging parameter then transactions within the TS will not be redo capable and subsequently won’t be able to be recovered using RMAN (Recovery Manager) Flashback.
If a table is accidentally deleted it can be recovered by copying the last export. The advantage of this method of recovery is that it has a positive impact on the database’s performance as there aren’t any Redo records.
1 2 3 |
create table test as select * from hr.employees nologging; |
To prevent TableSpace problems due to a database being in Nologging mode, creating a TS in FORCE LOGGING mode configures it to create RedoLogs in all conditions.
Shifting a TS in FORCE LOGGING mode to another database, which isn’t in FORCE LOGGING mode, will disable its FORCE LOGGING.
1 2 3 4 5 6 7 8 9 |
create tablespace test1 datafile '/u01/app/oracle/oradata/orcl/test101.dbf' size 50M extent management local nologging/nologging/force logging; |
Online/Offline
While a database is in open mode, users that have alter tablespace or manage tablespace privileges can take TSs online or offline.
Offline TSs can’t be accessed by users, this doesn’t affect access to other TSs.
• When offline backups are required (a backup can be made while the TS is online)
• While an application is loading or being updated
• While a TS is being renamed or moved (relocated)
• If there is a problem with a datafile
The following types of TableSpaces cannot be taken offline:
• System
• Undo
• Temporary
1 |
alter tablespace sales offline/online; |
A TS can be taken offline using 3 types of parameters.
• normal : Doesn’t require media recovery.
• temporary : Used if a TS can’t be taken offline. May require recovery media.
• immediate : The available option when the above parameters can’t be applied. Requires media recovery and can only be used in archive mode.
Read-Only
New data can’t be entered or new objects can’t be created into a TS in Read-Only mode, but the TS can be queried and dropped.
A Bigfile TS should be put into read-only mode before being backed up to external storage devices or removable media such as DVDs. In read-only mode a TS can also be moved to other databases.
The following points should be considered when switching a tablespace into read only mode.
• As changes are made to datafile headers during backup, a datafile should not be in an online backup process.
• All undo transactions should be completed.
• The tablespace to be placed into read-only mode must not be SYSTEM or active undo tablespaces.
1 |
alter tablespace sales read only; |
1 |
alter tablespace sales read write ; |
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE TEST_TABLE1 ( ID NUMBER, NAME VARCHAR2(20 BYTE) ) TABLESPACE SALES; |
A variety of insert operations have been made on the SALES tablespace without being committed.
alter tablespace sales read only; was run and remains in that state.
1 2 3 4 5 6 7 |
select sql_text, SADDR from v$sqlarea, v$session where v$sqlarea.address = v$session.sql_address and sql_text like 'alter tablespace%'; |
1 2 3 4 5 6 7 8 9 10 11 |
SQL_TEXT -------------------------------------------------------------------------------- SADDR -------- alter tablespace sales read only 2EAF591C |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select ses_addr, start_scnb 2 from v$transaction 3 order by start_scnb; SES_ADDR START_SCNB -------- ---------- 2EAF6BD0 1965814 2EAF591C 1967631 |
alter tablespace sales offline temporary; is hanging
alter tablespace sales offline immediate; is hanging
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@ora ~]# tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log Fri Feb 12 15:38:32 2010 alter tablespace sales read only Fri Feb 12 15:51:41 2010 alter tablespace sales read only Fri Feb 12 15:51:50 2010 ORA-3135 signalled during: alter tablespace sales read only... Fri Feb 12 15:54:05 2010 alter tablespace sales offline temporary Fri Feb 12 15:55:32 2010 alter tablespace sales offline immediate |
The following queries can be used to find transactions that are in read-only mode
1 2 3 4 5 6 7 8 9 |
select s.username, s.sid, s.serial#, t.start_time, n.name from v$session s, v$transaction t, v$rollstat r, v$rollname n where s.saddr=t.ses_addr and r.usn=n.usn and to_date(t.start_time,'MM/DD/YY HH24:MI:Ss')<(sysdate-(1/24)); |
USERNAME | SID | SERIAL# | START_TIME | NAME |
SYS | 141 | 1949 | 02/12/10 14:59:23 | SYSTEM |
SYS | 140 | 251 | 02/12/10 15:38:30 | SYSTEM |
SYS | 141 | 1949 | 02/12/10 14:59:23 | _SYSSMU11$ |
SYS | 140 | 251 | 02/12/10 15:38:30 | _SYSSMU11$ |
SYS | 141 | 1949 | 02/12/10 14:59:23 | _SYSSMU12$ |
SYS | 140 | 251 | 02/12/10 15:38:30 | _SYSSMU12$ |
SYS | 141 | 1949 | 02/12/10 14:59:23 | _SYSSMU13$ |
SYS | 140 | 251 | 02/12/10 15:38:30 | _SYSSMU13$ |
SYS | 141 | 1949 | 02/12/10 14:59:23 | _SYSSMU14$ |
1 |
alter sytem kill session 'SID_NO,SERIAL'; |
1 |
alter system kill session '141,1949'; |
When the following code is run, all alter tablespace sales offline… queries will now appear as alter tablespace altered.
1 |
alter tablespace sales online ; TS has been put online. |
Note: It is recommended to set the read_only_open_delayed parameter, in databases that use large TSs, to true.
This allows read-only TSs to be read before checking the datafiles while starting a database.
• Datafiles, belonging to lost or faulty read-only TS, aren’t checked while the the database opens. They are read when first accessed.
• “Alter system check datafiles” “Alter tab … online” “Alter database datafile … online”, “alter database noarchivelog” statements don’t control read-only datafiles.
• v$recover_file, v$backup, v$datafile_header, v$datafile and v$revover_log cannot access read-only TSs.
Note: Whatever data the “recover database”, “alter database open resetlogs” commands write to the initialization file, access to read-only datafiles will still remain. To prevent access to the datafiles, they should be taken offline.
If there are backup control files in use, read-only datafiles could create issues so should be taken into consideration.
Adding a Datafile
1 2 3 |
alter tablespace sales add datafile '/u01/app/oracle/oradata/orcl/sales02.dbf' SIZE 10M; |
Alternatively:
1 2 3 4 5 6 7 8 9 10 11 |
alter tablespace satis add datafile '/u01/app/oracle/oradata/orcl/sales02_tbs.dbf' size 10M autoextend ON next 10M maxsize 50M; |
Renaming
Names of the Permanent TSs, where user data is stored, and Temporary TSs can be changed.
1 |
alter tablespace sales rename to sales_2009; |
The DBMS is able to change the data dictionary control file and datafile headers, in the sales TS, but cannot change a TableSpace’s ID.
In addition to the name that a user gives a tablespace, Oracle also assigns an ID. To change a tablespace’s name:
• A database should be compatible with version 10 or above (initialization parameter compatible = 10.0 and above).
• System and sysaux TS cannot be renamed (will produce a message error).
• It isn’t possible to rename an offline TS or datafile (will produce a message error).
• A read-only TS can be renamed, however the operation will enter various error messages into the alertlog file. Read write operations afterwards will be done using the new name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Tablespace 'SALES' is renamed to 'SALES_2009'. Tablespace name change is not propagated to file headersbecause the tablespace is read only. Starting control autobackup Wed Feb 17 16:29:33 2010 Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_23691.trc: Wed Feb 17 16:29:33 2010 Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_23691.trc: Wed Feb 17 16:29:33 2010 Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_23691.trc: |
After TS has been renamed using Undo, it also needs to be changed in the parameter file.
Drop
Users that have Drop TS privileges can drop (delete) unused TSs as well as contained objects. Care should be taken and a user should ensure that the objects in a TS won’t be needed before it is deleted. A full backup before and after a TS is deleted is strongly recommended.
The syntax below deletes the TS, but the datafile will remain as an operating system file. If needed, the datafile can be deleted through operating system commands.
1 |
drop tablespace sales_2009 including contents; |
The code below completely deletes a TableSpace.
1 |
drop tablespace sales_2009 including contents and datafiles; |
A TS containing an active segment, i.e. containing rollback or table undo data, cannot be deleted. It is strongly recommended taking a TS offline before deleting it.
Sysaux Tablespace
Introduced in 10G, a sysaux tablespace is a feature that supports a System TS. It contains data such as: Workspace Manager, Logical Standby, Oracle Streams and Automatic Workload Repository. The entire list can be viewed in v$sysaux_occupants.
In situations where a sysaux can’t be accessed, if it is damaged for instance, a database can still carry out its main functions but will be limited to the activities that can be done outside of the sysaux tablespace contents.
Sysaux components can be imported or exported to other TSs using PL/SQL. This is especially useful for components such as the Automatic Workload Repository (AWR) which uses a lot of space.
DataFile Management
Oracle assigns 2 different numbers to a datafile, an Absolute File Number and a Relative File Number.
Absolute: An absolute datafile is assigned a unique number. This unique number can be used in SQL Syntax instead of a long datafile name. This information can be accessed in the file_id column in dba_data_files and dba_temp_files as well as the “file#” column in v$datafile and v$tempfile.
Relative: This is also a unique number. In small and medium-sized databases the absolute and relative numbers are the same, in large databases they need to be different.
Before setting up Oracle, the amount of required TableSpaces and Datafiles should be calculated and the operating system should be configured accordingly. The reason for this is so that the restrictions due to an operating system may require the altering the settings of a database.
In the spfile, used for initialization, the db_files parameter specifies the maximum number of datafiles. By default, this amount is 200 and the maximum is the amount that can be supported by the operating system. If the parameter value is changed the database would have to be restarted.
1 |
show parameter db_files; |
Adding a Datafile
The syntax below shows how to keep on increasing the size of a datafile to accomodate the growth of objects, to a maximum size of 100M.
1 2 3 4 5 6 7 8 9 |
alter tablespace sales_2009 add datafile '/u01/app/oracle/oradata/orcl/sales03_tbs.dbf' autoextend on next 1M Maxsize 100M |
Cancelling the automatic datafile increase.
1 2 3 |
alter database datafile '/u01/app/oracle/oradata/orcl/sales03_tbs.dbf' autoextend off; |
Changing the size
1 2 3 |
alter database datafile '/u01/app/oracle/oradata/orcl/sales03_tbs.dbf' resize 150M; |
A datafile can be resized, according to the disksize and operating system, to a bigger amount or a smaller amount. However attempting to shrink the datafile below the amount used by data would give an error.
Modifying access to a datafile
Datafiles and tempfiles can be taken offline or put online individually, they can’t be accessed while in offline mode.
This feature allows the following:
• Backups to be made in an offline mode.
• A datafile should first be made offline if it is to be renamed or moved to a different path.
• If Oracle encounters a write problem, it takes the datafile offline in order to solve the issue.
• If a datafile gets corrupted or becomes inaccessible by the system, it would be necessary to take it offline in order to start the instance.
A datafile within a TS in Read_Only mode can also be taken offline or online, a Read-Only mode TS would have to be switched to a Read-Write mode for data to be written to a contained datafile.
The following code takes all the datafiles within the sales_2009 TableSpace, offline.
1 |
alter tablespace sales_2009 offline/online; |
Online/Offline in ArchiveLog Mode
1 |
alter database datafile '/u01/app/oracle/oradata/orcl/sales03_tbs.dbf' online; |
1 |
alter database datafile '/u01/app/oracle/oradata/orcl/sales03_tbs.dbf' offline; |
By being in ArchiveLog mode, the above method allows changing a datafle without having to make the all datafiles within a TS offline. In the worst case, the datafile would be lost.
Online/Offline in NoArchiveLog Mode
Putting the database to NoarchiveLog Mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Activating the database's mount mode shutdown immediate; startup mount alter database noarchivelog; giving the command alter database open ; opening the database archive log list ; the code that shows that the archive log has been disabled Closed using shutdown immediate Opened using startup and checked to see if it's in ArchiveLog Mode The database would appear to be remaining in NoArchiveLog in the list |
The Offline for drop parameter should be used for a database instance that isn’t in archive mode. A command to execute the drop transaction would be given once the database is taken offline.
1 |
alter database datafile 'u01/app/oracle/oradata/orcl/satis03_tbs.dbf' offline for drop; |
Alternatively:
1 2 3 4 5 |
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/satis01_tbs.dbf' OFFLINE; ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/satis01_tbs.dbf' OFFLINE * |
1 2 3 |
ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled |
This error is due to the database not being in recovery mode.
Renaming and changing the path
It’s necessary to take a full backup before the following operations.
1 |
alter tablespace sales_2009 offline; |
Rename the file using the operating system.
1 |
$mv /u01/app/oracle/oradata/orcl/sales01_tbs.dbfu01/app/oracle/oradata/orcl/sales_200901_tbs.dbf |
The mv (move) command is widely used for renaming, in Linux operating systems.
1 2 3 4 5 6 7 8 9 |
alter tablespace sales_2009 rename datafile '/u01/app/oracle/oradata/orcl/sales01_tbs.dbf' to '/u01/app/oracle/oradata/orcl/sales_200901_tbs.dbf' |
It’s possible to change more than one datafile at the same time.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
alter tablespace sales_2009 rename datafile '/u01/app/oracle/oradata/orcl/sales01_tbs.dbf' '/u01/app/oracle/oradata/orcl/sales02_tbs.dbf' to '/u01/app/oracle/oradata/orcl/sales_200901_tbs.dbf' '/u01/app/oracle/oradata/orcl/sales_200902_tbs.dbf'' |
In situations where there may not be enough available disk space or where there are performance issues, a user would want to install a new disk and move all the datafiles there. Installing and mounting a new disk to a system is explained in the Linux section.
It is recommended that the new disk is installed in a directory named salests within the /u01/app/oracle/oradata/orcl/ folder and mounted there.
1 |
alter tablespace sales_2009 offline; |
The code below moves the datafile to a new location in the operating system. To prevent any type of operating system error the datafile is first copied. If successfully copied the original copy is later deleted.
1 |
cp /u01/app/oracle/oradata/orcl/sales_200901_tbs.dbf /u01/app/oracle/oradata/orcl/salests |
1 2 3 4 5 6 7 8 9 |
ALTER TABLESPACE sales_2009 RENAME DATAFILE '/u01/app/oracle/oradata/orcl/sales_200901_tbs.dbf' TO '/u01/app/oracle/oradata/orcl/salests/sales_200901_tbs.dbf' |
It is also possible to simultaneuosly move more than one datafile.
1 |
alter database sales_2009 online; |
This syntax makes the database accessible again.
Any unused datafile, i.e. doesn’t contain extents can be deleted. (Requires a database to be in Open Mode)
1 |
ALTER TABLESPACE sales_2009 DROP DATAFILE '/u01/app/oracle/oradata/orcl/salests/sales_200902_tbs.dbf'; |
Data dictionaries containing control file data, used by sales_200902_tbs.dbf are deleted and so is the datafile in the operating system.
Similarly the temp datafile can also be deleted.
1 |
ALTER TABLESPACE developertemp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/developertemp02.dbf'; |
1 |
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/developertemp02.dbf' DROP INCLUDING DATAFILES; |
This code does the same operation, but differs in this way: If developertemp02.dbf was the last datafile in a temporary TableSpace the ALTER TABLESPACE line wouldn’t have carried out the operation.
ALTER DATABASE would immediately have deleted the datafile, closing and reopening the datafile wouldn’t cause any issues, however there would be a Temporary TS without a datafile and the following error would be written to the alert file.
1 2 3 4 5 6 7 8 9 10 11 |
WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may be necessary to add files to these tablespaces. That can be done using the SQL statement: ALTER TABLESPACE <tablespace_name> ADD TEMPFILE |
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: DEVELOPERTEMP
Using RMAN for...
12 March 2019