Overview

This guide provides methods for managing tablespaces, including the definition and modification of tablespaces, reorganization by object type, and more. 

Method

Tablespace Definition 

The highest unit among data storage units (Data Block > Extent > Segment > Tablespace)

• A logical space where tables and indexes are stored
• The actual physical space is a datafile (.dtf)
• TIBERO TABLESPACE types: SYSTEM, TEMP, UNDO, USER TABLESPACE

Using Tablespaces 

Creating a Tablespace (CREATE TABLE)

Syntax

CREATE_TABLESPACE

Example

SQL>CREATE TABLESPACE TEST_TBS

DATAFILE '/home/tibero/tbdata/test_tbs_01.dtf' SIZE 20M
DEFAULT STORAGE(INITIAL 128K NEXT 256K MAXEXTENTS UNLIMITED PCTINCREASE 0);

CREATE TABLE TEST.TEST01 (A CHAR(2000), B INT) TABLESPACE TEST_TBS;

Execution Result

SELECT SEGMENT_NAME, EXTENT_ID, BLOCK_ID, BLOCKS, BYTES/1024 ||'K' "SIZE"
FROM DBA_EXTENTS WHERE TABLESPACE_NAME='TEST_TBS'
ORDER BY SEGMENT_NAME,EXTENT_ID;

SEGMENT_NAME           EXTENT_ID   BLOCK_ID      BLOCKS SIZE
-------------------- ---------- ---------- ---------- --------------------
TEST01                   0           7           16 128K

TEST01                   1          23           16 128K

TEST01                   2          39           16 128K

TEST01                   3          55           16 128K

TEST01                   4          71           16 128K

TEST01                   5          87           16 128K

TEST01                   6         103           16 128K

TEST01                   7         119           16 128K

TEST01                   8         135          128 1024K

TEST01                   9         263          128 1024K

Even if a different INITIAL value is applied, the size remains 128K. (storage option not present)

Execution Result

Each datafile in a tablespace reserves approximately 64K for header_block plus header_bitmap space.

SQL> CREATE TABLESPACE test_tbs2

DATAFILE 'test_tbs_02.dbf' SIZE 2624K

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

--The size 2624K of the above tablespace is calculated by the following rule:
2624K = (128K*20) + (64K * 1 datafile)

SQL> CREATE TABLESPACE test_tbs2
DATAFILE 'test_tbs_02.dbf' SIZE 2560K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Execution Result

SQL> CREATE TABLESPACE TBS_WASTE
DATAFILE 'test_tbs_03.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M;

Execution Result

Modifying Tablespace (ALTER TABLE)

Syntax

ALTER_TABLESPACE

Others 

• USGMT that can be SHRUNK 

Only UNDO SEGMENTS/UNDO_RETENTION spaces without ongoing TRANSACTIONS can be SHRUNK. 

SHRINK criteria: usgmt must maintain at least two extents and be 512 blocks or more

• Whether offline status change is possible

SYSTEM, UNDO, TEMP tablespaces cannot be changed to offline status. 

Object Reorganization (Object Reorg)

This section guides reorganization by object type. 

Index Reorg

• Delete the index and then recreate or rebuild it
• While the index is being created, INSERT/UPDATE/DELETE operations on the table are not allowed
• Online index REBUILD is possible

Table Reorg

• EXPORT/IMPORT

INSERT .. SELECT

RENAME TESET TO TEST_BACK;

CREATE TABLE TEST;

INSERT INTO TEST SELECT * FROM TEST_BACK; commit;

DROP TABLE TEST_BACK;

Change TABLESPACE

ALTER TABLE TEST MOVE TABLESPACE USR;

ALTER TABLE TEST MOVE TABLESPACE TEST_TBS;



Use DBMS_REDEFINITION package

Caution

If PL/SQL stored procedures or functions exist during the process of deleting tables, objects such as INVALID, GRANT, SYNONYM, etc. will also be lost, so caution is required.