Shulou(Shulou.com)06/01 Report--

Preface

Tablespace is a unique logical structure of Oracle, which is a way to manage and organize Oracle data files. An Oracle database can have one or more tablespaces, while one tablespace corresponds to one or more physical database files. The table space of Oracle is divided into permanent space and temporary table space, and at the same time is divided into smallfile tablespace and bigfile tablespace. Table space management is an important daily work of Oracle dba.

Today, the editor introduces in detail the specific operation of Oracle tablespace management from two dimensions: permanent tablespace management and temporary tablespace management.

Permanent tablespace management

1.1 create tablespaces

Parameter description:

EXTENT MANAGEMENT LOCAL AUTOALLOCATE: the zone size is automatically assigned by the system. BLOCKSIZE 8K: the block size is 8K. SEGMENT SPACE MANAGEMENT AUTO: segment management is automatic. FLASHBACK ON: turn on flashback.

1.2 modify tablespace

1. Modify the size of the data file

SQL > ALTER DATABASE DATAFILE'/ u01/app/oracle/oradata/TSH1/users01.dbf'RESIZE 10m

2. Add tablespace data files

ALTER TABLESPACE USERS ADD DATAFILE'/ u01 SIZE AUTOEXTEND ON NEXT AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

Note: in daily work, it is recommended to expand the tablespace by adding data files, because for online business systems, changing the size of data files will have a certain impact on the business.

1.3 rename tablespace

1) set tablespace offline

SQL > ALTER TABLESPACE USERS OFFLINE NORMAL

2) rename this file such as UNIX's cp or mv, or windows's copy command with an operating system utility

$cp / u01/app/oracle/test01.dbf / u02/app/oracle/test01.dbf

3) rename the data file in the database using the following command before the data file is online

SQL > ALTER TABLESPACE USERS RENAME DATAFILE'/u01/app/oracle/oradata/TSH1/users01.dbf'TO'/u02/app/oracle/oradata/TSH1/users01.dbf'

4) set tablespace online

SQL > ALTER TABLESPACE USERS ONLINE

1.4 set tablespace read-only

1) set tablespace read-only

SQL > ALTER TABLESPACE USERS READ ONLY

2) set the tablespace to be writable again

SQL > ALTER TABLESPACE USERS READ WRITE

1.5 set tablespaces offline and online

1) set tablespace offline

SQL > ALTER TABLESPACE USERS OFFLINE NORMAL

2) set the tablespace online

SQL > ALTER TABLESPACE USERS ONLINE

1.6 Delete tablespace

SQL > DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES

1.7 change the tablespace to the default permanent tablespace

1) change the table space to the default permanent table space

SQL > ALTER DATABASE DEFAULT TABLESPACE users

2) query the default permanent tablespace of the current database

SQL > SELECT property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE'

Temporary tablespace management

2.1 create temporary tablespaces

SQL > CREATE TEMPORARY TABLESPACE TEMP TEMPFILE'/ u01 SIZE AUTOEXTEND ON NEXT AUTOEXTEND ON NEXT 640K oradata 1m

Parameter description:

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m: the zone management is local, and the zone size is 1m.

2.2 modify temporary tablespace

SQL > ALTER DATABASE TEMPFILE'/ u01/app/oracle/oradata/TSH1/temp01.dbf'RESIZE 60m

2.3 Delete temporary tablespaces

SQL > DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

2.4 temporary tablespace group management

2.4.1 create temporary tablespace groups

SQL > CREATE TEMPORARY TABLESPACE TEMP TEMPFILE'/ u01 MAXSIZE UNLIMITEDTABLESPACE GROUP tmpgrp1 AUTOEXTEND ON NEXT AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITEDTABLESPACE GROUP tmpgrp1

Description:

Add a temporary tablespace group named tmpgrp1.

2.4.2 add tablespaces to temporary tablespace groups

SQL > ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2

2.4.3 set a group as the default temporary tablespace

SQL > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmpgrp1

Views involved in tablespace management

To manage the tablespaces of the ORACLE database, familiarize yourself with several key dictionary views:

DBADATAFILESDBA_TABLESPACESDBAFREESPACEDBA_SEGMENTS

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.