{"id":2594,"date":"2019-03-12T12:12:18","date_gmt":"2019-03-12T09:12:18","guid":{"rendered":"https:\/\/sysdba.org\/?p=2594"},"modified":"2025-07-02T06:01:07","modified_gmt":"2025-07-02T06:01:07","slug":"table-space-management","status":"publish","type":"post","link":"https:\/\/sysdba.org\/en\/table-space-management\/","title":{"rendered":"Table Space Management"},"content":{"rendered":"

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.)<\/p>\n

 <\/p>\n

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.<\/p>\n

 <\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

 <\/p>\n

Temporary TS: A user’s session data, such as queues and sort transactions, gets stored in a temp file rather than a datafile.<\/p>\n

Undo TS: Used to maintain consistency of data that is read. (The consistency of undo data will be explained in further sections)<\/p>\n

 <\/p>\n

TableSpace Types<\/h3>\n

1- System tablespace:<\/strong> Gets created along with the database and is compulsory for every database. Stores the Data Dictionary and parts of programs.<\/p>\n

2- Non-System tablespace:<\/strong> Are created by database administrators (DBAs) and users.<\/p>\n

 <\/p>\n

The 2 types of Extent options when creating TableSpaces<\/h3>\n

1- Dictionary Managed TableSpace (DMT):<\/strong> Extent Size is managed by the Data Dictionary. This type of TS is not generally recommended.<\/p>\n

2- Locally Managed TableSpace (LMT):<\/strong> 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.
\n[crayon]create tablespace test<\/p>\n

datafile ‘\/u01\/DataFiles\/test_tbs01’<\/p>\n

size 50MB<\/p>\n

Extent management LOCAL<\/p>\n

Uniform SIZE 128K ;
\n[\/crayon]
\nA 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.<\/p>\n

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.<\/p>\n

\"SegmentExtenAndDataBlocks\"<\/a><\/p>\n

Reminder:<\/strong><\/em><\/p>\n

The empty areas in the manual segment’s space are managed using the bitmap in the Freelist Automatic Space Management.<\/p>\n

The Segment Space Management is set to Automatic by default and is more efficient. There is no need to coalesce the free areas.
\n[crayon]create tablespace test<\/p>\n

datafile ‘\/u01\/DataFiles\/test_tbs01’<\/p>\n

size 50M<\/p>\n

Extent management LOCAL<\/p>\n

SEGMENT SPACE MANAGEMENT AUTO;
\n[\/crayon]<\/p>\n

There are two options for managing the size of an Extent.<\/h3>\n

1.<\/strong> 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.<\/p>\n

2.<\/strong> 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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

64 KB small segments<\/p>\n

1 MB medium segments<\/p>\n

63 MB large segments<\/p>\n

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.<\/p>\n

The diagram below summarizes the mentioned points:<\/p>\n

\"Table<\/a><\/p>\n

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.<\/p>\n

LOCAL UNIFORM:<\/strong> There needs to be a minimum of 5 available database blocks to select this option.<\/p>\n

LOCAL AUTOALLOCATE:<\/strong> 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.
\n[crayon]create tablespace sales<\/p>\n

datafile ‘\/u1\/appa\/oracle\/orcl\/sales_tbs.dbf’<\/p>\n

size 50M ;
\n[\/crayon]<\/p>\n

[crayon]create tablespace accounting<\/p>\n

datafile ‘\/u1\/appa\/oracle\/orcl\/accounts_tbs.dbf’<\/p>\n

size 50M<\/p>\n

extent management LOCAL<\/p>\n

segment space management auto;
\n[\/crayon]
\nThe 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.)
\n[crayon]select * from dba_tablespaces;
\n[\/crayon]<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
TABLESPACE_NAME<\/strong><\/span><\/td>\nSALES<\/strong><\/span><\/td>\nACCOUNTING<\/strong><\/span><\/td>\nSHIPPING<\/strong><\/span><\/td>\n<\/tr>\n
BLOCK_SIZE<\/span><\/strong><\/td>\n8192<\/span><\/td>\n8192<\/span><\/td>\n8192<\/span><\/td>\n<\/tr>\n
INITIAL_EXTENT<\/span><\/strong><\/td>\n65536<\/span><\/td>\n65536<\/span><\/td>\n65536<\/span><\/td>\n<\/tr>\n
NEXT_EXTENT<\/span><\/strong><\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
MIN_EXTENTS<\/span><\/strong><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
MAX_EXTENTS<\/span><\/strong><\/td>\n2147483645<\/span><\/td>\n2147483645<\/span><\/td>\n2147483645<\/span><\/td>\n<\/tr>\n
PCT_INCREASE<\/span><\/strong><\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
MIN_EXTLEN<\/span><\/strong><\/td>\n65536<\/span><\/td>\n65536<\/span><\/td>\n65536<\/span><\/td>\n<\/tr>\n
STATUS<\/span><\/strong><\/td>\nONLINE<\/span><\/td>\nONLINE<\/span><\/td>\nONLINE<\/span><\/td>\n<\/tr>\n
CONTENTS<\/span><\/strong><\/td>\nPERMANENT<\/span><\/td>\nPERMANENT<\/span><\/td>\nPERMANENT<\/span><\/td>\n<\/tr>\n
LOGGING<\/span><\/strong><\/td>\nLOGGING<\/span><\/td>\nLOGGING<\/span><\/td>\nLOGGING<\/span><\/td>\n<\/tr>\n
FORCE_LOGGING<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n
EXTENT_MANAGEMENT<\/span><\/strong><\/td>\nLOCAL<\/span><\/td>\nLOCAL<\/span><\/td>\nLOCAL<\/span><\/td>\n<\/tr>\n
ALLOCATION_TYPE<\/span><\/strong><\/td>\nSYSTEM<\/span><\/td>\nSYSTEM<\/span><\/td>\nSYSTEM<\/span><\/td>\n<\/tr>\n
PLUGGED_IN<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n
SEGMENT_SPACE_MANAGEMENT<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\nAUTO<\/span><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
DEF_TAB_COMPRESSION<\/span><\/strong><\/td>\nDISABLED<\/span><\/td>\nDISABLED<\/span><\/td>\nDISABLED<\/span><\/td>\n<\/tr>\n
RETENTION<\/span><\/strong><\/td>\nNOT APPLY<\/span><\/td>\nNOT APPLY<\/span><\/td>\nNOT APPLY<\/span><\/td>\n<\/tr>\n
BIGFILE<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

[crayon]create tablespace stock<\/p>\n

datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/stock01_tbs.dbf’<\/p>\n

SIZE 50M<\/p>\n

extent management local uniform ;
\n[\/crayon]
\nThe 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.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
TABLESPACE_NAME<\/span><\/strong><\/td>\nSALES<\/span><\/strong><\/td>\nACCOUNTING<\/span><\/strong><\/td>\nPATIENTS<\/span><\/strong><\/td>\n<\/tr>\n
BLOCK_SIZE<\/span><\/strong><\/td>\n8192<\/span><\/td>\n8192<\/span><\/td>\n8192<\/span><\/td>\n<\/tr>\n
INITIAL_EXTENT<\/span><\/strong><\/td>\n65536<\/span><\/td>\n65536<\/span><\/td>\n1048576<\/span><\/td>\n<\/tr>\n
NEXT_EXTENT<\/span><\/strong><\/td>\n<\/td>\n<\/td>\n1048576<\/span><\/td>\n<\/tr>\n
MIN_EXTENTS<\/span><\/strong><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
MAX_EXTENTS<\/span><\/strong><\/td>\n2147483645<\/span><\/td>\n2147483645<\/span><\/td>\n2147483645<\/span><\/td>\n<\/tr>\n
PCT_INCREASE<\/span><\/strong><\/td>\n<\/td>\n<\/td>\n0<\/span><\/td>\n<\/tr>\n
MIN_EXTLEN<\/span><\/strong><\/td>\n65536<\/span><\/strong><\/td>\n65536<\/span><\/strong><\/td>\n1048576<\/span><\/strong><\/td>\n<\/tr>\n
STATUS<\/span><\/strong><\/td>\nONLINE<\/span><\/td>\nONLINE<\/span><\/td>\nONLINE<\/span><\/td>\n<\/tr>\n
CONTENTS<\/span><\/strong><\/td>\nPERMANENT<\/span><\/td>\nPERMANENT<\/span><\/td>\nPERMANENT<\/span><\/td>\n<\/tr>\n
LOGGING<\/span><\/strong><\/td>\nLOGGING<\/span><\/td>\nLOGGING<\/span><\/td>\nLOGGING<\/span><\/td>\n<\/tr>\n
FORCE_LOGGING<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n
EXTENT_MANAGEMENT<\/span><\/strong><\/td>\nLOCAL<\/span><\/td>\nLOCAL<\/span><\/td>\nLOCAL<\/span><\/td>\n<\/tr>\n
ALLOCATION_TYPE<\/span><\/strong><\/td>\nSYSTEM<\/span><\/td>\nSYSTEM<\/span><\/td>\nUNIFORM<\/span><\/td>\n<\/tr>\n
PLUGGED_IN<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n
SEGMENT_SPACE_MANAGEMENT<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\nAUTO<\/span><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
DEF_TAB_COMPRESSION<\/span><\/strong><\/td>\nDISABLED<\/span><\/td>\nDISABLED<\/span><\/td>\nDISABLED<\/span><\/td>\n<\/tr>\n
RETENTION<\/span><\/strong><\/td>\nNOT APPLY<\/span><\/td>\nNOT APPLY<\/span><\/td>\nNOT APPLY<\/span><\/td>\n<\/tr>\n
BIGFILE<\/span><\/strong><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\nNO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

BigFile TableSpace<\/h3>\n

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.<\/p>\n

The following points should be considered when considering using BigFile TableSpaces:<\/p>\n

\u2022 They were developed for use in Automatic Storage Management or ASM or other expandable disk architectures, such as RAID.<\/p>\n

\u2022 The DBMS creates a smallfile tablespace by default.<\/p>\n

\u2022 By using the ALTER TABLESPACE syntax users can perform operations on a whole tablespace rather than individual datafiles, this is known as datafile transparency.<\/p>\n

 <\/p>\n

It is not advisable to use BigFile TS in the following scenarios:<\/p>\n

\u2022 Storage structures that don’t use striping, i.e. storing data sequentially on different physical devices.<\/p>\n

\u2022 In operating systems that don’t support large file sizes.<\/p>\n

 <\/p>\n

BigFile TSs are only supported in Locally Managed TSs that use Automatic Segment Space Management, with 3 exceptions:<\/p>\n

\u2022 Undo TS<\/p>\n

\u2022 Temporary TS<\/p>\n

\u2022 System TS<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Table_Space<\/span><\/strong><\/td>\nSSM<\/span><\/strong><\/td>\n<\/tr>\n
SYSTEM<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
UNDOTBS1<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
SYSAUX<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
TEMP<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
USERS<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
UNDOTBS2<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
EXAMPLE<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
SALES<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
ACCOUNTING<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
TEMP02<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
SHIPPING<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n
PATIENTS<\/span><\/strong><\/td>\nAUTO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Creating a Bigfile TS:[crayon]create bigfile tablespace customer<\/p>\n

datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/customer_bf_tbs.dbf’<\/p>\n

SIZE 100G
\n[\/crayon]
\nIf 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.
\n[crayon]alter tablespace customer resize 200G;
\n[\/crayon]
\nIt is possible to change the size of the TS.
\n[crayon]alter tablespace customer autoextend on next 10G;
\n[\/crayon]
\nUsing the above statement the TS can be configured to grow automatically, depending on requirements.
\n[crayon]DBA_TABLESPACES<\/p>\n

USER_TABLESPACES<\/p>\n

V$TABLESPACE
\n[\/crayon]
\nThe BigFile column in the TS indicates whether the TS is a BigFile or not.<\/p>\n

 <\/p>\n

Temporary TableSpace<\/h3>\n

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).<\/p>\n

Permanent\/non-volatile objects cannot be created in a Temp TS which is composed of temporary tables.<\/p>\n

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.<\/p>\n

A number of users can also use a common tablespace to share information.<\/p>\n

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.<\/p>\n

Creating more than a single TS and sharing it among users is another method of increasing performance.<\/p>\n

\u2022 V$SORT_SEGMENT<\/p>\n

\u2022 V$TEMPSEG_USAGE<\/p>\n

\u2022 V$TEMPFILE<\/p>\n

\u2022 V$DATAFILE<\/p>\n

\u2022 DBA_TEMP_FILES<\/p>\n

\u2022 DBA_DATA_FILES<\/p>\n

 <\/p>\n

Usage information can be determined through querying from the views.
\n[crayon]create temporary tablespace developertemp<\/p>\n

tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp01.dbf’<\/p>\n

size 50M ;
\n[\/crayon]<\/p>\n

[crayon]select * from dba_tablespaces ;
\n[\/crayon]
\nThe default values in the temporary TS can be viewed using the previous line.<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
TABLESPACE_NAME<\/span><\/strong><\/td>\nDEVELOPERTEMP<\/span><\/strong><\/td>\n<\/tr>\n
BLOCK_SIZE<\/span><\/strong><\/td>\n8,192<\/span><\/td>\n<\/tr>\n
INITIAL_EXTENT<\/span><\/strong><\/td>\n1,048,576<\/span><\/td>\n<\/tr>\n
NEXT_EXTENT<\/span><\/strong><\/td>\n1,048,576<\/span><\/td>\n<\/tr>\n
MIN_EXTENTS<\/span><\/strong><\/td>\n1<\/span><\/td>\n<\/tr>\n
MAX_EXTENTS<\/span><\/strong><\/td>\n<\/td>\n<\/tr>\n
PCT_INCREASE<\/span><\/strong><\/td>\n0<\/span><\/td>\n<\/tr>\n
MIN_EXTLEN<\/span><\/strong><\/td>\n1,048,576<\/span><\/td>\n<\/tr>\n
STATUS<\/span><\/strong><\/td>\nONLINE<\/span><\/td>\n<\/tr>\n
CONTENTS<\/span><\/strong><\/td>\nTEMPORARY<\/span><\/td>\n<\/tr>\n
LOGGING<\/span><\/strong><\/td>\nNOLOGGING<\/span><\/td>\n<\/tr>\n
FORCE_LOGGING<\/span><\/strong><\/td>\nNO<\/span><\/td>\n<\/tr>\n
EXTENT_MANAGEMENT<\/span><\/strong><\/td>\nLOCAL<\/span><\/td>\n<\/tr>\n
ALLOCATION_TYPE<\/span><\/strong><\/td>\nUNIFORM<\/span><\/td>\n<\/tr>\n
PLUGGED_IN<\/span><\/strong><\/td>\nNO<\/span><\/td>\n<\/tr>\n
SEGMENT_SPACE_MANAGEMENT<\/span><\/strong><\/td>\nMANUAL<\/span><\/td>\n<\/tr>\n
DEF_TAB_COMPRESSION<\/span><\/strong><\/td>\nDISABLED<\/span><\/td>\n<\/tr>\n
RETENTION<\/span><\/strong><\/td>\nNOT APPLY<\/span><\/td>\n<\/tr>\n
BIGFILE<\/span><\/strong><\/td>\nNO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

As can be seen in this table, all Extents are locally managed and have a uniform size of 1MB.<\/p>\n

 <\/p>\n

To change the Uniform Size:
\n[crayon]create temporary tablespace nameOne<\/p>\n

tempfile ‘\/datafile path\/datafileName’<\/p>\n

size ;<\/p>\n

extent management local uniform size xx;
\n[\/crayon]<\/p>\n

[crayon]create bigfile temporary tablespace
\n[\/crayon]
\nThis code creates a bigfile tablespace.<\/p>\n

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.
\n[crayon]alter tablespace developertemp<\/p>\n

add tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’<\/p>\n

size 50m;
\n[\/crayon]<\/p>\n

[crayon]alter tablespace developertemp tempfile offline;<\/p>\n

alter tablespace developertemp tempfile offline;
\n[\/crayon]
\nThe same can be accomplished using the code below.
\n[crayon]alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’ offline;<\/p>\n

alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’ offline;
\n[\/crayon]
\nIt 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.
\n[crayon]alter tablespace developertemp tempfile offline;
\n[\/crayon]
\nTo change the size of the tempfile.
\n[crayon]alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’ resize 100M;
\n[\/crayon]
\nTo cancel the tempfile and to delete the datafile.
\n[crayon]alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’ drop including datafiles;
\n[\/crayon]
\nChanging the name and location:<\/strong><\/p>\n

1. Take the TS offline.<\/p>\n

2. Moved from the operating system and\/or change its name.<\/p>\n

3. Update the control file using the “alter database rename file” syntax.<\/p>\n

 <\/p>\n

Temporary TableSpace Group<\/strong><\/p>\n

Temporary TableSpace Group is a feature, introduced with 10G, that enables users in different sessions to concurrently use the temporary TS.<\/p>\n

\u2022 Formed out of a minimum of 1 TS, no maximum amount.<\/p>\n

\u2022 The group automatically gets deleted if all group members are deleted.<\/p>\n

\u2022 The same name is in the group name field of the TS for all group members.<\/p>\n

\u2022 The temporary TS name and the group name that a member belongs to can not be identical.<\/p>\n

\u2022 Members can be assigned the group name instead of the temporary TS.<\/p>\n

 <\/p>\n

Benefits of using a Temporary TS Group:<\/p>\n

\u2022 Stops congestion by preventing more than one TS being used simultaneously.<\/p>\n

\u2022 Perfomance benefits from spreading TS across several physical disks.<\/p>\n

\u2022 Increases the productivity of many temporary TS running in parallel.<\/p>\n

\u2022 One user can access more than one TS at the same time.<\/p>\n

 <\/p>\n

Creating TableSpaces<\/strong><\/p>\n

The following code changes the default temp tablespace.
\n[crayon]ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempxx;
\n[\/crayon]
\nDeleting the Temporary TS with the datafile.
\n[crayon]DROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES;
\n[\/crayon]
\nThis statement cancels the datafile and erases it from the system.
\n[crayon]alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’ drop including datafiles;
\n[\/crayon]
\nThe alertlog error entry (reminder : “tail -f \/u01\/app\/oracle\/admin\/orcl\/bdump\/alert_orcl.log<\/em><\/strong>“)
\n[crayon]
\nWed Feb 10 20:40:24 2010<\/p>\n

alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’ drop including datafiles<\/p>\n

Wed Feb 10 20:40:25 2010<\/p>\n

Deleted file \/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf<\/p>\n

Completed: alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’ drop including datafiles<\/p>\n

Wed Feb 10 20:43:47 2010<\/p>\n

alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’ drop including datafiles<\/p>\n

Wed Feb 10 20:43:47 2010<\/p>\n

ORA-1516 signalled during: alter database tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’ drop including datafiles …
\n[\/crayon]
\nWhen queried
\n[crayon]select * from dba_tablespace_groups;
\n[\/crayon]
\nthe DBMS outputs the following error:
\n[crayon]ORA-01516: nonexistent log file, datafile, or tempfile “\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf”
\n[\/crayon]
\nFirst, remove temp01 from the default temporary group:
\n[crayon]alter tablespace temp01 tablespace group ”;
\n[\/crayon]
\nThen:
\n[crayon]DROP TABLESPACE TEMP01 INCLUDING CONTENTS and datafiles;
\n[\/crayon]
\nFrom now on running select * from dba_tablespace_groups;<\/strong> will not produce an error message.
\n[crayon]create temporary tablespace temp01<\/p>\n

tempfile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’<\/p>\n

size 50M tablespace group tempgroup01;
\n[\/crayon]
\nUsing the previous syntax, a temporary TS called temp01 is created as well as a temporary group called tempgroup1, temp01 is included in tempgroup01.<\/p>\n

 <\/p>\n

A temporary TS must first be created before being added to a group. The code below assigns a temporary tablespace to a group:
\n[crayon]alter tablespace temp02 tablespace group tempgroup01 ;
\n[\/crayon]
\nAssigning a default temporary TS to a group:
\n[crayon]alter database default temporary tablespace tempgroup01;
\n[\/crayon]
\nAssigning a user to a default temporary group:
\n[crayon]create user user_name identified by user_password<\/p>\n

default tablespace tablspace_name<\/p>\n

temporary tablespace tempgroup01;
\n[\/crayon]
\nRe-assigning a user to a temporary tablespace<\/p>\n

 <\/p>\n

alter user user_name temporary tablespace tempgrup01;<\/p>\n

 <\/p>\n

 <\/p>\n

To view temporary tablespace information:
\n[crayon]select group_name “temp_grup”,<\/p>\n

tablespace_name “ts_name”<\/p>\n

from dba_tablespace_groups;
\n[\/crayon]<\/p>\n

[crayon]
\ntemp_grup ts_name<\/p>\n

———– ——–<\/p>\n

TEMPGRUP01 TEMP<\/p>\n

TEMPGRUP01 TEMP01
\n[\/crayon]
\nTo view the users as well as the temporary tablespaces that they have been assigned to.
\n[crayon]select username, temporary_tablespace from dba_users;
\n[\/crayon]<\/p>\n

[crayon]USERNAME TEMPORARY_TABLESPACE<\/p>\n

——– ——————–<\/p>\n

HR TEMPGROUP01<\/p>\n

SCOTT TEMPGROUP01<\/p>\n

TSMSYS TEMPGROUP01
\n[\/crayon]
\nAs tempgroup_1 has been assigned to a default temporary TS, the users within the group have also been assigned the properties of tempgroup_1.<\/p>\n

 <\/p>\n

Creating a NonStandard Block Size<\/h3>\n

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.<\/p>\n

DN_nK_CACHE_SIZE must have at least one assigned value in the initialization parameters.
\n[crayon]create tablespace finance<\/p>\n

datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/temp01_01.dbf’<\/p>\n

size 50M<\/p>\n

extent management local<\/p>\n

uniform size 128K<\/p>\n

BLOCKSIZE 8K;
\n[\/crayon]<\/p>\n

Nologging<\/h3>\n

If TableSpace was made using the nologging<\/strong> 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.<\/p>\n

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.
\n[crayon]create table test as select * from hr.employees<\/p>\n

nologging;
\n[\/crayon]
\nTo 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.<\/p>\n

Shifting a TS in FORCE LOGGING mode to another database, which isn’t in FORCE LOGGING mode, will disable its FORCE LOGGING.
\n[crayon]create tablespace test1<\/p>\n

datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/test101.dbf’<\/p>\n

size 50M<\/p>\n

extent management local<\/p>\n

nologging\/nologging\/force logging;
\n[\/crayon]<\/p>\n

Online\/Offline<\/h3>\n

While a database is in open mode, users that have alter tablespace<\/strong> or manage tablespace<\/strong> privileges can take TSs online or offline.<\/p>\n

Offline TSs can’t be accessed by users, this doesn’t affect access to other TSs.<\/p>\n

\u2022 When offline backups are required (a backup can be made while the TS is online)<\/p>\n

\u2022 While an application is loading or being updated<\/p>\n

\u2022 While a TS is being renamed or moved (relocated)<\/p>\n

\u2022 If there is a problem with a datafile<\/p>\n

 <\/p>\n

The following types of TableSpaces cannot be taken offline:<\/p>\n

\u2022 System<\/p>\n

\u2022 Undo<\/p>\n

\u2022 Temporary
\n[crayon]alter tablespace sales offline\/online;
\n[\/crayon]
\nA TS can be taken offline using 3 types of parameters.<\/p>\n

\u2022 normal : Doesn’t require media recovery.<\/p>\n

\u2022 temporary : Used if a TS can’t be taken offline. May require recovery media.<\/p>\n

\u2022 immediate : The available option when the above parameters can’t be applied. Requires media recovery and can only be used in archive mode.<\/p>\n

 <\/p>\n

Read-Only<\/h3>\n

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.<\/p>\n

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.<\/p>\n

The following points should be considered when switching a tablespace into read only mode.<\/p>\n

\u2022 As changes are made to datafile headers during backup, a datafile should not be in an online backup process.<\/p>\n

\u2022 All undo transactions should be completed.<\/p>\n

\u2022 The tablespace to be placed into read-only mode must not be SYSTEM or active undo tablespaces.
\n[crayon]alter tablespace sales read only;
\n[\/crayon]<\/p>\n

[crayon]alter tablespace sales read write ;
\n[\/crayon]<\/p>\n

[crayon]CREATE TABLE TEST_TABLE1<\/p>\n

(<\/p>\n

ID NUMBER,<\/p>\n

NAME VARCHAR2(20 BYTE)<\/p>\n

)<\/p>\n

TABLESPACE SALES;
\n[\/crayon]
\nA variety of insert operations have been made on the SALES tablespace without being committed.<\/p>\n

alter tablespace sales read only;<\/em><\/strong> was run and remains in that state.
\n[crayon]select sql_text, SADDR<\/p>\n

from v$sqlarea, v$session<\/p>\n

where v$sqlarea.address = v$session.sql_address<\/p>\n

and sql_text like ‘alter tablespace%’;
\n[\/crayon]<\/p>\n

[crayon]SQL_TEXT<\/p>\n

——————————————————————————–<\/p>\n

SADDR<\/p>\n

——–<\/p>\n

alter tablespace sales read only<\/p>\n

2EAF591C
\n[\/crayon]<\/p>\n

[crayon]SQL> select ses_addr, start_scnb<\/p>\n

2 from v$transaction<\/p>\n

3 order by start_scnb;<\/p>\n

 <\/p>\n

SES_ADDR START_SCNB<\/p>\n

——– ———-<\/p>\n

2EAF6BD0 1965814<\/p>\n

2EAF591C 1967631
\n[\/crayon]
\nalter tablespace sales offline temporary;<\/em><\/strong> is hanging<\/p>\n

alter tablespace sales offline immediate;<\/strong><\/em> is hanging
\n<\/span>
\n[crayon][root@ora ~]# tail -f \/u01\/app\/oracle\/admin\/orcl\/bdump\/alert_orcl.log<\/p>\n

Fri Feb 12 15:38:32 2010<\/p>\n

alter tablespace sales read only<\/p>\n

Fri Feb 12 15:51:41 2010<\/p>\n

alter tablespace sales read only<\/p>\n

Fri Feb 12 15:51:50 2010<\/p>\n

ORA-3135 signalled during: alter tablespace sales read only…<\/p>\n

Fri Feb 12 15:54:05 2010<\/p>\n

alter tablespace sales offline temporary<\/p>\n

Fri Feb 12 15:55:32 2010<\/p>\n

alter tablespace sales offline immediate
\n[\/crayon]
\nThe following queries can be used to find transactions that are in read-only mode
\n[crayon]select s.username, s.sid, s.serial#, t.start_time, n.name<\/p>\n

from v$session s, v$transaction t, v$rollstat r, v$rollname n<\/p>\n

where s.saddr=t.ses_addr<\/p>\n

and r.usn=n.usn<\/p>\n

and to_date(t.start_time,’MM\/DD\/YY HH24:MI:Ss’)<(sysdate-(1\/24));
\n[\/crayon]<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
USERNAME<\/span><\/strong><\/td>\nSID<\/span><\/strong><\/td>\nSERIAL#<\/span><\/strong><\/td>\nSTART_TIME<\/span><\/strong><\/td>\nNAME<\/span><\/strong><\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n141<\/span><\/td>\n1949<\/td>\n02\/12\/10 14:59:23<\/td>\nSYSTEM<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n140<\/span><\/td>\n251<\/td>\n02\/12\/10 15:38:30<\/td>\nSYSTEM<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n141<\/span><\/td>\n1949<\/td>\n02\/12\/10 14:59:23<\/td>\n_SYSSMU11$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n140<\/span><\/td>\n251<\/td>\n02\/12\/10 15:38:30<\/td>\n_SYSSMU11$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n141<\/span><\/td>\n1949<\/td>\n02\/12\/10 14:59:23<\/td>\n_SYSSMU12$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n140<\/span><\/td>\n251<\/td>\n02\/12\/10 15:38:30<\/td>\n_SYSSMU12$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n141<\/span><\/td>\n1949<\/td>\n02\/12\/10 14:59:23<\/td>\n_SYSSMU13$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n140<\/span><\/td>\n251<\/td>\n02\/12\/10 15:38:30<\/td>\n_SYSSMU13$<\/td>\n<\/tr>\n
SYS<\/span><\/strong><\/td>\n141<\/span><\/td>\n1949<\/td>\n02\/12\/10 14:59:23<\/td>\n_SYSSMU14$<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

[crayon]alter sytem kill session ‘SID_NO,SERIAL’;
\n[\/crayon]<\/p>\n

[crayon]alter system kill session ‘141,1949’;
\n[\/crayon]
\nWhen the following code is run, all alter tablespace sales offline…<\/strong><\/em> queries will now appear as alter tablespace altered<\/em>.
\n[crayon]alter tablespace sales online ; TS has been put online.
\n[\/crayon]
\nNote:<\/strong><\/em> It is recommended to set the read_only_open_delayed<\/em> parameter, in databases that use large TSs, to true.<\/p>\n

This allows read-only TSs to be read before checking the datafiles while starting a database.<\/p>\n

 <\/p>\n

\u2022 Datafiles, belonging to lost or faulty read-only TS, aren’t checked while the the database opens. They are read when first accessed.<\/p>\n

\u2022 “Alter system check datafiles” “Alter tab … online” “Alter database datafile … online”, “alter database noarchivelog”<\/em> statements don’t control read-only datafiles.<\/p>\n

\u2022 v$recover_file, v$backup, v$datafile_header, v$datafile<\/em> and v$revover_log<\/em> cannot access read-only TSs.<\/p>\n

 <\/p>\n

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.<\/p>\n

If there are backup control files in use, read-only datafiles could create issues so should be taken into consideration.<\/p>\n

 <\/p>\n

Adding a Datafile<\/h3>\n

[crayon]alter tablespace sales<\/p>\n

add datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales02.dbf’ SIZE 10M;
\n[\/crayon]
\nAlternatively:
\n[crayon]alter tablespace satis<\/p>\n

add datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales02_tbs.dbf’<\/p>\n

size 10M<\/p>\n

autoextend ON<\/p>\n

next 10M<\/p>\n

maxsize 50M;
\n[\/crayon]<\/p>\n

Renaming<\/h3>\n

Names of the Permanent TSs, where user data is stored, and Temporary TSs can be changed.
\n[crayon]alter tablespace sales rename to sales_2009;
\n[\/crayon]
\nThe DBMS is able to change the data dictionary control file and datafile headers, in the sales TS, but cannot change a TableSpace’s ID.<\/p>\n

In addition to the name that a user gives a tablespace, Oracle also assigns an ID. To change a tablespace’s name:<\/p>\n

\u2022 A database should be compatible with version 10 or above (initialization parameter compatible = 10.0 and above).<\/p>\n

\u2022 System and sysaux TS cannot be renamed (will produce a message error).<\/p>\n

\u2022 It isn’t possible to rename an offline TS or datafile (will produce a message error).<\/p>\n

\u2022 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.
\n[crayon]Tablespace ‘SALES’ is renamed to ‘SALES_2009’.<\/p>\n

Tablespace name change is not propagated to file headersbecause the tablespace is read only.<\/p>\n

Starting control autobackup<\/p>\n

Wed Feb 17 16:29:33 2010<\/p>\n

Errors in file \/u01\/app\/oracle\/admin\/orcl\/udump\/orcl_ora_23691.trc:<\/p>\n

Wed Feb 17 16:29:33 2010<\/p>\n

Errors in file \/u01\/app\/oracle\/admin\/orcl\/udump\/orcl_ora_23691.trc:<\/p>\n

Wed Feb 17 16:29:33 2010<\/p>\n

Errors in file \/u01\/app\/oracle\/admin\/orcl\/udump\/orcl_ora_23691.trc:
\n[\/crayon]
\nAfter TS has been renamed using Undo, it also needs to be changed in the parameter file.<\/p>\n

 <\/p>\n

Drop<\/h3>\n

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.<\/p>\n

 <\/p>\n

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.
\n[crayon]drop tablespace sales_2009 including contents;
\n[\/crayon]
\nThe code below completely deletes a TableSpace.
\n[crayon]drop tablespace sales_2009 including contents and datafiles;
\n[\/crayon]
\nA 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.<\/p>\n

 <\/p>\n

Sysaux Tablespace<\/h3>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

 <\/p>\n

DataFile Management<\/h3>\n

Oracle assigns 2 different numbers to a datafile, an Absolute File Number<\/strong> and a Relative File Number<\/strong>.<\/p>\n

Absolute:<\/strong> 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.<\/p>\n

Relative:<\/strong> 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.<\/p>\n

 <\/p>\n

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.<\/p>\n

In the spfile, used for initialization, the db_files<\/strong> 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.
\n[crayon]show parameter db_files;
\n[\/crayon]<\/p>\n

Adding a Datafile<\/h3>\n

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.
\n[crayon]alter tablespace sales_2009<\/p>\n

add datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales03_tbs.dbf’<\/p>\n

autoextend on<\/p>\n

next 1M<\/p>\n

Maxsize 100M
\n[\/crayon]
\nCancelling the automatic datafile increase.
\n[crayon]alter database datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales03_tbs.dbf’<\/p>\n

autoextend off;
\n[\/crayon]<\/p>\n

Changing the size<\/h3>\n

[crayon]alter database datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales03_tbs.dbf’<\/p>\n

resize 150M;
\n[\/crayon]
\nA 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.<\/p>\n

 <\/p>\n

Modifying access to a datafile<\/h3>\n

Datafiles and tempfiles can be taken offline or put online individually, they can’t be accessed while in offline mode.<\/p>\n

This feature allows the following:<\/p>\n

\u2022 Backups to be made in an offline mode.<\/p>\n

\u2022 A datafile should first be made offline if it is to be renamed or moved to a different path.<\/p>\n

\u2022 If Oracle encounters a write problem, it takes the datafile offline in order to solve the issue.<\/p>\n

\u2022 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.<\/p>\n

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.<\/p>\n

 <\/p>\n

The following code takes all the datafiles within the sales_2009 TableSpace, offline.
\n[crayon]alter tablespace sales_2009 offline\/online;
\n[\/crayon]<\/p>\n

Online\/Offline in ArchiveLog Mode<\/h3>\n

[crayon]alter database datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales03_tbs.dbf’ online;
\n[\/crayon]<\/p>\n

[crayon]alter database datafile ‘\/u01\/app\/oracle\/oradata\/orcl\/sales03_tbs.dbf’ offline;
\n[\/crayon]
\nBy 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.<\/p>\n

 <\/p>\n

Online\/Offline in NoArchiveLog Mode<\/h3>\n

Putting the database to NoarchiveLog Mode.
\n[crayon]Activating the database’s mount mode<\/p>\n

shutdown immediate;<\/p>\n

startup mount<\/p>\n

alter database noarchivelog; giving the command<\/p>\n

alter database open ; opening the database<\/p>\n

archive log list ; the code that shows that the archive log has been disabled<\/p>\n

Closed using shutdown immediate<\/p>\n

Opened using startup and checked to see if it’s in ArchiveLog Mode<\/p>\n

The database would appear to be remaining in NoArchiveLog in the list
\n[\/crayon]
\nThe Offline for drop<\/strong> 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.
\n[crayon]alter database datafile ‘u01\/app\/oracle\/oradata\/orcl\/satis03_tbs.dbf’ offline for drop;
\n[\/crayon]
\nAlternatively:
\n[crayon]SQL> ALTER DATABASE DATAFILE ‘\/u01\/app\/oracle\/oradata\/orcl\/satis01_tbs.dbf’ OFFLINE;<\/p>\n

ALTER DATABASE DATAFILE ‘\/u01\/app\/oracle\/oradata\/orcl\/satis01_tbs.dbf’ OFFLINE<\/p>\n

*
\n[\/crayon]<\/p>\n

[crayon]ERROR at line 1:<\/p>\n

ORA-01145: offline immediate disallowed unless media recovery enabled
\n[\/crayon]
\nThis error is due to the database not being in recovery mode.<\/p>\n

 <\/p>\n

Renaming and changing the path<\/h3>\n

It’s necessary to take a full backup before the following operations.
\n[crayon]alter tablespace sales_2009 offline;
\n[\/crayon]
\nRename the file using the operating system.
\n[crayon]$mv \/u01\/app\/oracle\/oradata\/orcl\/sales01_tbs.dbfu01\/app\/oracle\/oradata\/orcl\/sales_200901_tbs.dbf
\n[\/crayon]
\nThe mv (move) command is widely used for renaming, in Linux operating systems.
\n[crayon]alter tablespace sales_2009<\/p>\n

rename datafile<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales01_tbs.dbf’<\/p>\n

to<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales_200901_tbs.dbf’
\n[\/crayon]
\nIt’s possible to change more than one datafile at the same time.
\n[crayon]alter tablespace sales_2009<\/p>\n

rename datafile<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales01_tbs.dbf’<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales02_tbs.dbf’<\/p>\n

to<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales_200901_tbs.dbf’<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales_200902_tbs.dbf”
\n[\/crayon]
\nIn 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.<\/p>\n

 <\/p>\n

It is recommended that the new disk is installed in a directory named salests within the \/u01\/app\/oracle\/oradata\/orcl\/<\/strong> folder and mounted there.
\n[crayon]alter tablespace sales_2009 offline;
\n[\/crayon]
\nThe 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.
\n[crayon]cp \/u01\/app\/oracle\/oradata\/orcl\/sales_200901_tbs.dbf \/u01\/app\/oracle\/oradata\/orcl\/salests
\n[\/crayon]<\/p>\n

[crayon]ALTER TABLESPACE sales_2009<\/p>\n

RENAME DATAFILE<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/sales_200901_tbs.dbf’<\/p>\n

TO<\/p>\n

‘\/u01\/app\/oracle\/oradata\/orcl\/salests\/sales_200901_tbs.dbf’
\n[\/crayon]
\nIt is also possible to simultaneuosly move more than one datafile.
\n[crayon]alter database sales_2009 online;
\n[\/crayon]
\nThis syntax makes the database accessible again.<\/p>\n

Any unused datafile, i.e. doesn’t contain extents can be deleted. (Requires a database to be in Open Mode)
\n[crayon]ALTER TABLESPACE sales_2009 DROP DATAFILE ‘\/u01\/app\/oracle\/oradata\/orcl\/salests\/sales_200902_tbs.dbf’;
\n[\/crayon]
\nData dictionaries containing control file data, used by sales_200902_tbs.dbf are deleted and so is the datafile in the operating system.<\/p>\n

 <\/p>\n

Similarly the temp datafile can also be deleted.
\n[crayon]ALTER TABLESPACE developertemp DROP TEMPFILE ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’;
\n[\/crayon]<\/p>\n

[crayon]ALTER DATABASE TEMPFILE ‘\/u01\/app\/oracle\/oradata\/orcl\/developertemp02.dbf’ DROP INCLUDING DATAFILES;
\n[\/crayon]
\nThis code does the same operation, but differs in this way: If developertemp02.dbf<\/strong> was the last datafile in a temporary TableSpace the ALTER TABLESPACE<\/strong> line wouldn’t have carried out the operation.<\/p>\n

 <\/p>\n

ALTER DATABASE<\/strong> 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.
\n[crayon]WARNING: The following temporary tablespaces contain no files.<\/p>\n

This condition can occur when a backup controlfile has<\/p>\n

been restored. It may be necessary to add files to these<\/p>\n

tablespaces. That can be done using the SQL statement:<\/p>\n

 <\/p>\n

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
\n[\/crayon]
\nAlternatively, if these temporary tablespaces are no longer<\/p>\n

needed, then they can be dropped.<\/p>\n

Empty temporary tablespace: DEVELOPERTEMP<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":1,"featured_media":2595,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[383,338],"tags":[345],"class_list":["post-2594","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dba_i","category-oracle-tr","tag-oracle"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=2594"}],"version-history":[{"count":1,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2594\/revisions"}],"predecessor-version":[{"id":4825,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2594\/revisions\/4825"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=2594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=2594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=2594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}