8 Managing Tablespaces and Data Files Copyright Oracle

8 Managing Tablespaces and Data Files Copyright � Oracle Corporation, 1998. All rights reserved.

Database Structure Database Tablespace Logical Data file Physical Segment Extent Oracle block 8 -2 O/S Block Copyright � Oracle Corporation, 1998. All rights reserved.

SYSTEM and Non-SYSTEM Tablespaces SYSTEM Tablespace contains: – Data dictionary information – SYSTEM rollback segment Non-SYSTEM Tablespace contains: – Rollback segments – Temporary segments – Application data – Application indexes 8 -3 Copyright � Oracle Corporation, 1998. All rights reserved.

Creating Tablespaces Example CREATE TABLESPACE app_data DATAFILE ‘/DISK 4/app 01. dbf’ SIZE 100 M, ‘/DISK 5/app 02. dbf’ SIZE 100 M MINIMUM EXTENT 500 K DEFAULT STORAGE (INITIAL 500 K NEXT 500 K MAXEXTENTS 500 PCTINCREASE 0); 8 -4 Copyright � Oracle Corporation, 1998. All rights reserved.

Storage Parameters The following parameters influence the segment storage allocation: • INITIAL • NEXT • MAXEXTENTS • MINEXTENTS • PCTINCREASE 8 -5 Copyright � Oracle Corporation, 1998. All rights reserved.

Temporary Tablespace • Used for sort operations • Cannot contain any permanent objects CREATE TABLESPACE sort DATAFILE ‘/DISK 2/sort 01. dbf’ SIZE 50 M MINIMUM EXTENT 1 M DEFAULT STORAGE (INITIAL 2 M NEXT 2 M MAXEXTENTS 500 PCTINCREASE 0) TEMPORARY; 8 -6 Copyright � Oracle Corporation, 1998. All rights reserved.

Adding Data Files to a Tablespace Example ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK 5/app 03. dbf’ SIZE 200 M; Tablespace APP_DATA app 01. dbf 1 M 8 -7 app 02. dbf 1 M app 03. dbf 2 M Copyright � Oracle Corporation, 1998. All rights reserved.

Enabling Automatic Extension of Data Files Example ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK 6/app 04. dbf’ SIZE 200 M AUTOEXTEND ON NEXT 10 M MAXSIZE 500 M; Tablespace APP_ DATA app 01. dbf 1 M 8 -8 app 02. dbf 1 M app 03. dbf 2 M app 04. dbf 2 M Copyright � Oracle Corporation, 1998. All rights reserved.

Changing the Size of Data Files Manually Example ALTER DATABASE DATAFILE ‘/DISK 5/app 02. dbf’ RESIZE 200 M; Tablespace APP_DATA app 01. dbf 1 M app 02. dbf 1 M 1 M 8 -9 Copyright � Oracle Corporation, 1998. All rights reserved.

OFFLINE Status • The tablespace that is offline is not available for data access. • The SYSTEM tablespace and any tablespace with active rollback segments cannot be taken offline. Example ALTER TABLESPACE app_data OFFLINE; 8 -10 Copyright � Oracle Corporation, 1998. All rights reserved.

Moving Data Files: ALTER DATABASE • The database must be mounted. • The target data file must exist. Example ALTER DATABASE RENAME FILE ‘/DISK 1/system 01. dbf’ TO ‘/DISK 2/system 01. dbf’; 8 -11 Copyright � Oracle Corporation, 1998. All rights reserved.

The READ-ONLY Tablespace Status Example ALTER TABLESPACE app_data READ ONLY; The tablespace APP_DATA is only available for read operations. 8 -12 Copyright � Oracle Corporation, 1998. All rights reserved.

Making Tablespace Read-Only • The tablespace must be online. • No active transactions are allowed. • The tablespace must not contain active rollback segments. • The tablespace must not currently be involved in an online backup. 8 -13 Copyright � Oracle Corporation, 1998. All rights reserved.

Dropping Tablespaces The following statement removes the APP_DATA tablespace and all its contents. Example DROP TABLESPACE app_data INCLUDING CONTENTS; 8 -14 Copyright � Oracle Corporation, 1998. All rights reserved.

Obtaining Tablespace Information DBA_TABLESPACES • TABLESPACE_NAME • NEXT_EXTENT • MAX_EXTENTS • PCT_INCREASE • MIN_EXTLEN • STATUS • CONTENTS 8 -15 Copyright � Oracle Corporation, 1998. All rights reserved.

Obtaining Data File Information DBA_DATA_FILES • FILE_NAME • TABLESPACE_NAME • BYTES • AUTOEXTENSIBLE • MAXBYTES • INCREMENT_BY 8 -16 Copyright � Oracle Corporation, 1998. All rights reserved.

Guidelines • Use multiple tablespaces. • Specify storage parameters for tablespaces. • Assign tablespace quotas to users. 8 -17 Copyright � Oracle Corporation, 1998. All rights reserved.

Summary • Understanding the logical database structure • Administering tablespaces 8 -18 Copyright � Oracle Corporation, 1998. All rights reserved.