4 TableSpace Management in oracle

Tablespaces

Hello, this article aims to describe how Oracle stores data and how to set up this storage in the database. If the first part is rather theoretical and complex, the second part, it concerns only the practical aspect of tablespaces. A note that is placed in a Oracle 9i and later.

1. What is a tablespace?

A tablespace is a tablespace that contains the objects stored in the database as tables or indexes.
A tablespace is composed of at least one datafile, ie a data file that is physically on the server at the location specified during its creation.
Each datafile consists of segments of at least one extent (or page) itself consists of at least three blocks: the smallest element of a database.
The extent has no particular meaning, it's just a group of contiguous blocks that can accommodate the data, we see, however, that this notion of extent can be problematic management of disk space.

1.1. Different types of tablespaces

a. Temprarytsorary tablespace

A temprarytsorary tablespace is a tablespace specific sorting SORT_AREA_SIZE why is not large enough.
This tablespace is not intended to accommodate objects of the database and its use is reserved for the system.
Since from 9i, Oracle can define a default tablespace at database (the database creation) or user, each user can have its own temprarytsorary tablespace which is particularly useful if there is a specific user large batch example.

Parameterization of the temprarytsorary tablespace of the database

CREATE DATABASE <SID> ...      DEFAULT TEMPRARYTSORARY TABLESPACE tempraryts;

Changing the temprarytsorary tablespace of the database

ALTER DATABASE DEFAULT TEMPRARYTSORARY TABLESPACE tempraryts2;

Create a temprarytsorary tablespace

CREATE TEMPRARYTSORARY TABLESPACE Tempraryts        TEMPRARYTSFILE 'g: \ oradisk \ oradata \ oradb \ tempraryts01.dbf'        SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Assigning a user to a temprarytsorary tablespace

ALTER USER oradb      TEMPRARYTSORARY TABLESPACE tempraryts;

b. The UNDO tablespace

The UNDO tablespace, as its name implies, is reserved exclusively for the cancellation of DML commands (UPDATE, INSERT, etc ...).
When executing the DELETE command for example, Oracle starts by copying the rows to be deleted in the UNDO tablespace and then indicates that the blocks containing the data in the original tablespace are free.
Allow a ROLLBACK back while COMMIT remove the lines of UNDO tablespace (here we understand better why a DELETE is so long: 2 entries for deletion :-/).
The UNDO tablespace is a single instance (because RAC, there are as many as UNDO instance) database and is guided by the following parameters in the initialization file of the database:

UNDO MANAGEMENT	AUTO or MANUAL	Let the system automatically manage the rollback segment or can use the UNDO as ROLLBACK SEGMENT earlier versions.
UNDO TABLESPACE	tablespace name	Specifies the name of tablespace.
UNDO_SUPPRESS_ERRORS	TRUE or FALSE	Or not to ignore errors related to inappropriate management of UNDO tablespace (ie control provided for rollback segments in the UNDO: ALTER ROLLBACK TRANSACTION USE ROLLBACK ... SET ...).
UNDO_RETENTION	number of seconds	Retention time data in the UNDO tablespace

Parameterization of UNDO tablespace base

CREATE DATABASE <SID> ...      UNDO tablespace UNDOTABLSPC        DATAFILE 'g: \ oradisk \ oradata \ oradb \ undotablspc.dbf' size 100M;

UNDO tablespace creation

CREATE UNDO TABLESPACE UNDOTABLSPC      DATAFILE 'g: \ oradisk \ oradata \ oradb \ undotablspc.dbf' size 100M;

Changing the UNDO tablespace used

ALTER SYSTEM      SET = UNDO_TABLESPACE undotablspc2;

c. Transportable tablespace

Transportable tablespace, introduced in version 8i, used to copy data between two databases.
Since 9i, the block size of the base should not be necessarily identical.
In the following example we will copy tablespaces and DATATBS INDEX_TBS base DVP1 to DVP2.
For a tablespace can be transported, it must contain all interdependent objects. We can not carry such a tablespace that contains a table whose indexes are created in another tablespace.

ATTENTION: tablespaces are not transportable in any condition:

• The source databases and target must be on identical platforms, so you can not move a tablespace from a Sun Solaris server to a Windows 2000 server.
• The source and target must use the same character set (character set and national character set).
• It is obviously impossible to transport a tablespace in a database that contains a tablespace of the same name.
• Finally, do not support transportable tablespaces: materialized views and indexes of function

Creating tablespaces

SQL> create tablespace DATATBS      2 datafile 'd: \ DVP1 \ oradata \ datatbs.ora' size 1M reuse      3 AUTOEXTEND OFF      4 ONLINE      5 default storage      6 (initial 32K next 32K      7 MINEXTENTS 2 MAXEXTENTS unlimited      8 PCTINCREASE 1);   Tablespace created.   SQL> create tablespace INDEX_TBS      2 datafile 'd: \ DVP1 \ oradata \ index_tbs.ora' size 1M reuse      3 AUTOEXTEND OFF      4 ONLINE      5 default storage      6 (initial 32K next 32K      7 MINEXTENTS 2 MAXEXTENTS unlimited      8 PCTINCREASE 1);   Tablespace created.

Table creation and Primary Key

SQL> create table oradb.matable (col1 NUMBER, col2 NUMBER)      2 DATATBS tablespace;   Table created.   SQL> alter table add oradb.matable     2 (matable_pk constraint primary key (col1)     3 USING INDEX TABLESPACE INDEX_TBS     4);   Table altered.

Here, the primary key is created in the tablespace INDX_TBS while the table is in DATATBS.

Checking the transportability of tablespaces

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATATBS', TRUE);   PL / SQL procedure successfully completed.   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;   no rows selected   SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('INDEX_TBS', TRUE);   PL / SQL procedure successfully completed.   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;   VIOLATIONS     -------------------------------------------------- ----------------------------   ORADB.MATABLE_PK index tablespace in primary INDX_TBS Enforces constraints     of ORADB.MATABLE table in tablespace DATATBS

The tablespace can be transported DATATBS unlike INDX_TBS. Effectively, how to create indexes on a table that does not exist?

Creating a test transportability and FK

SQL> create table oradb.matable_fille     2 (col1 NUMBER)     3 INDX_TBS tablespace;   Table created.   SQL> alter table oradb.matable_fille     Matable_matablefille_fk 2 add constraint foreign key (col1)     Oradb.matable three references (col1);   Table altered.   SQL>     SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATATBS', TRUE);   PL / SQL procedure successfully completed.   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;   no rows selected   SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('INDX_TBS', TRUE);   PL / SQL procedure successfully completed.   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;   VIOLATIONS     -------------------------------------------------- ----------------------------   Between MATABLE_MATABLEFILLE_FK Constraint table in ORADB.MATABLE     DATATBS table tablespace and tablespace in ORADB.MATABLE_FILLE INDX_TBS     ORADB.MATABLE_PK index tablespace in primary INDX_TBS Enforces constraints     of ORADB.MATABLE table in tablespace DATATBS

It now has a second problem with INDX_TBS the tablespace containing the table MATABLE_FILLE ORADB.MATABLE related to these two tables are in two separate tablespaces.

And if you check both transport tablespaces?

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATATBS, INDX_TBS', TRUE);   PL / SQL procedure successfully completed.   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;   no rows selected

Here, the transport of the two tablespaces is possible, all constraints are lifted.

After putting in READ ONLY tablespaces can be transported via the import and export tools for Oracle.

In command line mode

exp userid = 'system / manager @ DVP1'                            TRANSPORT_TABLESPACE = y                            TABLESPACES = DATATBS, INDX_TBS                          TRIGGERS = y                            CONSTRAINTS = y                            GRANTS = y                          ROWS = y   imp userid = 'system / manager @ DVP2'             TRANSPORT_TABLESPACE = y             TABLESPACES = DATATBS, INDX_TBS           DATAFILES = d: \ DVP2 \ oradata \ datatbs.ora, d: \ DVP2 \ oradata \ indx_tbs.ora

1.2. Management of space allocation

When creating a segment (eg a table), Oracle creates an extent (whose size is defined in terms of storage) in the target tablespace of the object. When filled the segment filled Oracle data blocks that constitute the complete extent to the extent completely creates a new extent and if the previous one is full.
And integration of large volumes can generate a large number of new extents that can be costly in the long run.

There are two modes of space management for tablespaces: management by local or dictionary (which appeared with version 8i).
A dictionary managed tablespace by Oracle ordered to store information relating to the allocation of space in the data dictionary which induces an additional charge for all operations on objects in a tablespace and requires the DBA to tune the tuner extent size to avoid excessive fragmentation of files and unwanted access to the data dictionary.
Fortunately, since version 8i, Oracle frees the notion of extent through locally managed tablespace (known locally managed) that stores all the information in the header of the tablespace storage.
A tablespace is locally managed by default since 9i and it is particularly advisable to use even with 8i. Indeed, it avoids contention on data dictionary, simplify space management in a tablespace that is completely automatic, and finally allows us to forget the notion of extent which tended to complicate things unnecessarily . Note that when creating the database, type the SYSTEM tablespace has an irreversible impact on the type of the other tablespaces of thumb: If the SYSTEM tablespace is LOCALLY MANAGED tablespace then the other base should also be managed locally.

Since version 9i, it is possible to allocate a block size different from that of the base for each of the tablespaces when you create when setting DB_nK_CACHE_SIZE ad hoc inquired:

CREATE TABLESPACE oradata           DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA01.dbf' size 100M           BLOCKSIZE 4k;

Note that tablespaces of a table or a partitioned index must all have the same block size.

1.3. LOCALLY MANAGED tablespace extents and

There are two ways to manage locally managed extents mode: UNIFORM SIZE and AUTOALLOCATE.
UNIFORM SIZE mode requires Oracle to create extents of the same size as the mode AUTOALLOCATE asked to create more extents with large number of extents created (the first 16 extents are 64k, 64 next 1024k etc.). Note that the latter can not be used to create a temprarytsorary tablespace.
If the first can easily track the allocation of extents, the second allows the DBA to manage systems that do not control or badly because he is not master of created objects. Oracle also said it is better to allocate large extents rather than many small ones. In addition, it is interesting to define a size extents proportional to the value of DB_FILE_MULTIBLOCK_READ_COUNT.

Anyway, should be avoided as much as possible to monopolize system resources in the management of extents.

1.4. The High-Water Mark or waterline

Here is a concept often ignored but I'll stain to remedy ;-)
The High-Water Mark (or waterline) is a benchmark set by Oracle to specify which block to an object data have been informed as to when it should stop at a table scan ( FULL SCAN).
For example, if a database with a block size (db_block_size) 16k 40k data is inserted into a table, HWM will be positioned on the 3 blocks of the table.
Except that the HWM does not go down alone! That poses a big problem to Oracle ...
If you remove a lot of rows in a table then Oracle will scan it still until the HWM risk unnecessarily scan many empty blocks.
Here is one of the reasons requiring the DBA to reorganize space, since only a TRUNCATE (and obviously destruction :-)) table resets the HWM 0. To take the example of a great site that deals with the subject, HWM works as the level of mercury in a thermometer: it descends in shaking the thermometer ;-)

1.5. Parameter optimization and storage space

When inserting a row into a segment, Oracle fills the 1 free block that is leaving PCTFREE% free space.
PCTFREE is the free area reserved for updates of data to prevent migration thereof.
Indeed, if an update causes a row can not be contained in a single block, a portion of the line will be created in another block that has little chance of being adjacent to the first.
Attention must distinguish two types of data chaining: migration and chaining simple data. If the first case described above and can be avoided by changing the PCTFREE, the second case it is "normal" and relating to the case where data are inserted whose size is greater than the block size.

Example

SQL> CREATE TABLE MYTABLE (ID NUMBER NOT NULL,       2 COMMENTS VARCHAR2 (4000))        PCTFREE 0 TABLESPACE TOOLS 3 PCTUSED 0       4 STORAGE (INITIAL 4096 NEXT 4096 PCTINCREASE 0);     Table created.   SQL> INSERT INTO mytable (id) values ​​(1);     1 row created.   SQL> INSERT INTO mytable (id) values ​​(2);     1 row created.   SQL> INSERT INTO mytable (id) values ​​(3);     1 row created.   SQL> INSERT INTO mytable (id) values ​​(4);     1 row created.   SQL> INSERT INTO mytable (id) values ​​(5);   1 row created.   SQL> INSERT INTO mytable (id) values ​​(6);      1 row created.   SQL> COMMIT;     Commit.   SQL> create table CHAINED_ROWS (       2 owner_name varchar2 (30),       3 table_name varchar2 (30),       4 cluster_name varchar2 (30),       5 partition_name varchar2 (30),       6 subpartition_name varchar2 (30),       7 head_rowid rowid,       8 analyze_timestamp date);     Table created.   SQL> analyze table list chained rows into mytable chained_rows;     Table analyzed.   SQL> select count (*) from chained_rows;       COUNT (*)   ----------            0   SQL> declare       1 foo varchar2 (4000);       2 begin       3 for i in 1 .. 4000 loop       4 foo: = foo | | 'a';       5 end loop;       6 update mytable set comments = foo;       7 COMMIT;       8 end;       9 /     PL / SQL procedure successfully completed.   SQL> analyze table list chained rows into mytable chained_rows;     Table analyzed.   SQL> select count (*) from chained_rows;       COUNT (*)   ----------            2   SQL> declare     2 foo varchar2 (4000);     3 begin     4 for i in 1 .. 4000 loop     5 foo: = foo | | 'a';     6 end loop;     Insert into mytable values ​​7 (7, foo);     8 COMMIT;     9 * end;     10 /   PL / SQL procedure successfully completed.   SQL> truncate table chained_rows;   Truncated table.   SQL> analyze table list chained rows into mytable chained_rows;     Table analyzed.   SQL>     SQL> select * from chained_rows;     no rows selected

I have generated lines migrated in 1 case (UPDATE) unlike the 2 cases (INSERT).

The FREELIST lists the blocks in which Oracle can write data. A block out of the freelist when it is filled to more than PCTUSED%. He returned as soon as the level drops below PCTUSED: If DELETE or UPDATE rows with smaller values.

So if PCTFREE is too large, too much space is reserved for UPDATE and you lose space. If it is too small increases the risk of lines migrated. And if PCTUSED is too large, Oracle loses a lot of time in managing the freelist but if it is too little disk space is lost again because the block is not considered free to write data .

2. Annotated syntax for creating a tablespace

2.1. Create tablespace

Here is the complete structure of a CREATE TABLESPACE command:

LOGGING: Indicates if the object creation in the tablespace must be recorded in the redo logs.
FORCE LOGGING: LOGGING to force the same on objects with the NOLOGGING option.
This option is invalid for type TEMPRARYTSORARY tablespaces and UNDO.
ONLINE or OFFLINE: to make the tablespace available or not, immediately after creation.
TEMPRARYTSORARY: create temprarytsorary tablespace managed by the dictionary.
To create a locally-managed temprarytsorary tablespace, use the CREATE TEMPRARYTSORARY TABLESPACE.
data_segment_compression: may take values ​​COMPRESS or UNCOMPRESS depending on whether or not you want to compress the data.

extent_management_clause:

LOCAL or DICTIONARY: Specifies that the tablespace is locally managed  or dictionary.
UNIFORM or AUTOALLOCATE: Specifies that the tablespace grows uniformly (in size extents) or automatically.

segment_management_clause:

AUTO: let the database manage free space. Oracle then ignores PCTUSED parameters, and FREELIST FREELIST GROUPS objects in the tablespace.

Caution: If the tablespace is in LOCAL UNIFORM, then you must ensure that the next extent will contain at least 5 blocks, ie the size defined for the next extents is at least six times the size of block database. If instead, the tablespace is LOCAL AUTOALLOCATE and basic blocks are at least 16k, then Oracle will create extents of at least 1 MB

2.2. Remove tablespace

Here is the complete structure of a DROP TABLESPACE command:

INCLUDING CONTENTS: Delete the contents of the tablespace. If this option is not specified, it will be impossible supprimmer a tablespace contains objects.

Warning: tablespaces containing partitioned tables can not be deleted even if this option is specified.

AND DATAFILES: Delete files also, a trace is recorded in the file alerts for each deleted files.
CASCADE CONSTRAINTS: Deletes the referential integrity constraints to objects in other tablespaces and constraints unique tablespace deleted.

2.3. Changing tablespace

Here is the complete structure of a command ALTER TABLESPACE:

COALESCE: to merge contiguous free extents.
BEGIN or END BACKUP is used to indicate the beginning or end of an open database backup of the database. This option is not applicable to temprarytsorary tablespaces READ ONLY or

2.4. Changing the database (specific tablespace)

Here is the complete structure of a command ALTER DATABASE: 

Here is the structure of the game database_file_clauses specific to our topic:

3. Examples of SQL statements to manage tablespaces

Creating a tablespace

CREATE TABLESPACE oradata   DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA01.dbf' size 100M            'G: \ oradisk \ oradata \ oradb \ ORADATA02.dbf' size 100M   MINIMUM EXTENT 400K - (V8 only)   DEFAULT STORAGE (initial 400K next 400K MAXEXTENTS PCTINCREAS 400 E 0);

Creating a locally managed tablespace

CREATE TABLESPACE oradata        DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA01.dbf' SIZE 10M      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

If the block size is 2k tablespace then it will run from 64 blocks each new extent.

Place a tablespace OFFLINE

ALTER TABLESPACE OFFLINE oradata;

This command can be useful to prevent the tablespace data are available, especially during maintenance operations.

Creating an UNDO tablespace of 10 MB CanGrow

CREATE UNDO TABLESPACE UNDOTABLSPC      DATAFILE 'undotablspc.dbf'      SIZE 10M AUTOEXTEND ON;

Deleting a tablespace and its constraints

DROP TABLESPACE INCLUDING CONTENTS CASCADE CONSTRAINTS oradata;

Modify the terms of a tablespace storage

ALTER TABLESPACE oradata     DEFAULT STORAGE (INITIAL 100K                          NEXT 100K                               MINEXTENTS 1                               MAXEXTENTS 300                              PCTINCREASE 1);

Adding a datafile

ALTER TABLESPACE ADD oradata   DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA03.dbf' size 100M;

Moving a datafile

ALTER TABLESPACE OFFLINE NORMAL oradata;     - After copying the file ORADATA03.dbf   - Of g: \ oradisk \ oradata \ oradb to f: \ oradisk \ oradata \ oradb   ALTER TABLESPACE oradata     RENAME DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA03.dbf'     TO 'f: \ oradisk \ oradata \ oradb \ ORADATA03.dbf';     ALTER TABLESPACE ONLINE NORMAL oradata;

Deleting a datafile

ALTER DATABASE DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA03.dbf' OFFLINE DROP;

Changes the size of a datafile

ALTER DATABASE      DATAFILE 'g: \ oradisk \ oradata \ oradb \ ORADATA03.dbf' resize 50M;

Followed by filling tablespaces

set lines 150   Max_Libre for 999,999,999,999 col   Total_Dispo for 999,999,999,999 col   Taille_Ts for 999,999,999,999 col   Ts_Frag_Ext_Libres collar for A50   col tablespace_name for A10   SELECT tablespace_name, Taille_Ts, Max_Libre, Total_Dispo,              to_char (100 * Total_Dispo / Taille_Ts, '999 .99 ') | |'% 'AS pct_dispo,   DECODE (Total_Dispo - Max_Libre, 0, 'all contiguous free extents are'            'The nb of'' non-contiguous free extents is:' | | Nb_Frag)     FROM (SELECT tablespace_name,          sum (bytes) AS Taille_Ts          FROM dba_data_files          GROUP BY tablespace_name         )           (SELECT tablespace_name AS fs_ts_name,                           max (bytes) AS Max_Libre,                           sum (bytes) AS Total_Dispo,                           count (*) AS Nb_Frag                  FROM dba_free_space                  GROUP BY tablespace_name             )   WHERE tablespace_name = fs_ts_name   ORDER BY 5 desc;

4. Wringing the neck myths

4.1. Too many extents degrades performance

That belief well defended by many DBA no explanation can be provided. It is therefore to say that when the number of extents of an object is too large, it must rebuild to replace n by a small initial extents huge extent.
Firstly, the question is immediately set to the locally managed tablespaces, which as we have seen in Section 1.1, can completely get rid of the notion of extent.
But what about afficionados managed tablespaces in the dictionary?
I guess that myth is based on a belief of a data extent are organized on the disk they occupy such kinds of contiguous blocks.
But do you really think this is possible? What opportunity is there to find enough disk of contiguous blocks to place the entire large extent? And if by chance there was indeed enough contiguous blocks, there would still be a problem specific to the operation of a disk.
Fortunately for us, a record is not only dedicated to a task, so that it reads and writes data almost simultaneously so that the heads of the discs move frequently. If a read occurs while writing your initial large extent the disc after reading, continue writing in the free block closest to the last block read (to avoid that the playhead goes through a journey too large ), and of course, there was almost no chance that this free block is contiguous to the block previously written.
Add to this the operating cluster or in a drive bay and it eventually I hope to convince you.

It is time for me to weigh those words. Indeed, the word Oracle is sometimes idyllic, it is clear that excess extents really important problem. Thus, we try to reduce the number of extents without lapsing into paranoia.

4.2. COALESCE allows me to defrag my datafiles

This is absolutely wrong and the mistake is due to a misunderstanding of the COALESCE command.
To coalesce in the language of shaekspeare means merge, but this merger does not only data but free extents. Indeed, Oracle only contiguous free extents replace n by one greater extent.
If 0 is a free extent and X an extent not free then for an object of the form: X | X | X | 0 | 0 | X | 0 | 0 | 0 | X, the effect will coalesce to reorganize extents as follows: X | X | X | 00 | X | 000 | X. And as we saw earlier, is replace n extents by a larger extent has no impact on performance.

4.3. Tablespace data and index tablespaces amply

Then Oracle offers clear figures, an object has space management best when it contains up to 1024 extents. Thus, we will tablespaces for small, medium and large objects.

Extent size according to the size of objects

• Up to 160 MB, INITIAL = NEXT = 160 kb
• 160 MB to 5 GB, INITIAL = NEXT = 5 MB
• Beyond 5 GB, INITIAL = NEXT = 160 MB

Thus, a table 160M may well contain up to 1024 extents 160 kb.