Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to set tablespaces in Oracle. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. * * tablespace management

1. Create a tablespace

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.

two。 Modify tablespace

(1) modify the size of the data file

SQL > ALTER DATABASE DATAFILE'/ u01 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.

3. Rename tablespace

Set tablespace offline

SQL > ALTER TABLESPACE USERS OFFLINE NORMAL

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

Rename the data file in the database using the following command before the data file is online

SQL > ALTER TABLESPACE USERS RENAME DATAFILE'/ u01ActionAccording to oradataUS01.dbf's01.dbf' / u02qappr ALTER TABLESPACE USERS RENAME DATAFILE'/ u02qapplemagenoradataSplash oradataS01.dbf'

Set tablespace online

SQL > ALTER TABLESPACE USERS ONLINE

4. Set tablespace read-only

Set tablespace read-only

SQL > ALTER TABLESPACE USERS READ ONLY

Set the table space to be writable again

SQL > ALTER TABLESPACE USERS READ WRITE

5. Set tablespaces offline and online

Set tablespace offline

SQL > ALTER TABLESPACE USERS OFFLINE NORMAL

Set tablespace online

SQL > ALTER TABLESPACE USERS ONLINE

6. Delete tablespace

SQL > DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES

7. Change the tablespace to the default * tablespace

Change the tablespace to the default * tablespace

SQL > ALTER DATABASE DEFAULT TABLESPACE users

Query the default * * tablespace of the current database

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

II. Temporary tablespace management

1. Create a temporary tablespace

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: zone management is local, and zone size is 1m.

two。 Modify temporary tablespace

SQL > ALTER DATABASE TEMPFILE'/ u01 RESIZE

3. Delete temporary tablespace

SQL > DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

4. Temporary tablespace group management

(1) create a temporary tablespace group

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

Description:

Add a temporary tablespace group named tmpgrp1.

(2) add tablespaces to temporary tablespace groups

SQL > ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2

(3) set a group as the default temporary tablespace

SQL > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmpgrp1; on how to set up tablespaces in Oracle to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.