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

Table space maintenance

(1) create tablespaces

CREATE TABLESPACE SUNTX DATAFILE

'/ app1/oracle/oradata/SUNTX/SUNTX01.dbf' SIZE 10240M AUTOEXTEND OFF

'/ app1/oracle/oradata/SUNTX/SUNTX02.dbf' SIZE 10240M AUTOEXTEND OFF

Create a new undo tablespace

CREATE UNDO TABLESPACE UNDO_NEW DATAFILE

'/ app1/oracle/oradata/SUNTX/UNDO_NEW1.dbf' SIZE 10240M AUTOEXTEND OFF

'/ app1/oracle/oradata/SUNTX/UNDO_NEW2.dbf' SIZE 10240M AUTOEXTEND OFF

'/ app1/oracle/oradata/SUNTX/UNDO_NEW3.dbf' SIZE 10240M AUTOEXTEND OFF

# Note: only one UNDO tablespace can be used at some times in OPEN. If you want to use the newly created tablespace, you must switch it.

ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_NEW

Establish a temporary tablespace

CREATE TEMPORARY TABLESPACE temp_data TEMPFILE

'/ oracle/oradata/db/TEMP_DATA.dbf' SIZE 50m

(2) expand the tablespace

First check the name and file of the tablespace

Select tablespace_name, file_id, file_name, round (bytes/ (1024,1024), 0)

Total_space from dba_data_files order by tablespace_name

1. Add data file ALTER TABLESPACE game ADD DATAFILE

'/ oracle/oradata/db/GAME02.dbf' SIZE 1000m

two。 Manually increase the data file size ALTER DATABASE DATAFILE'/ oracle/oradata/db/GAME.dbf'

RESIZE 4000M

3. Set the data file to automatically extend ALTER DATABASE DATAFILE'/ oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M MAXSIZE 10000M

View tablespace information after setting

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE

(B.BYTES*100) / A.BYTES "% USED", (C.BYTES*100) / A.BYTES "% FREE" FROM

SYS.SM$TS_AVAIL A, S, M, S, Y, B, Y, S, Y, S, Y, B, C, WHERE, free, free.

A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME

= shrink TEMP (create new TEMP- "switch -" delete old one | repeat operation)

1.create temporary tablespace TEMP2 TEMPFILE

'/ oradata/test/temp02.dbf' SIZE 512m REUSE AUTOEXTEND ON NEXT 640K

MAXSIZE UNLIMITED

2.alter database default temporary tablespace temp2

3.drop tablespace temp including contents and datafiles

4.create temporary tablespace TEMP TEMPFILE'/ oradata/test/temp01.dbf'

SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

5.alter database default temporary tablespace temp

6.drop tablespace temp2 including contents and datafiles

= shrink UNDO (create new UNDO- "switch -" delete old one | repeat operation)

1.create undo tablespace apps_undots2 DATAFILE

'/ oradata/test/undo03.dbf' SIZE 2000m

2.alter system set undo_tablespace=apps_undots2

3.drop tablespace apps_undots1 including contents and datafiles

4.create undo tablespace apps_undots1 DATAFILE'/ oradata/test/undo01.dbf' SIZE

4000M autoextend on

5.alter system set undo_tablespace=apps_undots1

6.drop tablespace apps_undots2 including contents and datafiles

(3) change the state of the tablespace

1. Take the tablespace offline

ALTER TABLESPACE game OFFLINE

If you accidentally delete the data file, you must have the RECOVER option

ALTER TABLESPACE game OFFLINE FOR RECOVER

two。 Bring tablespaces online

ALTER TABLESPACE game ONLINE

3. Take the data file offline

ALTER DATABASE DATAFILE 3 OFFLINE

4. Bring data files online

ALTER DATABASE DATAFILE 3 ONLINE

5. Make the tablespace read-only

ALTER TABLESPACE game READ ONLY

6. Make the tablespace readable and writable

ALTER TABLESPACE game READ WRITE

(4) Delete tablespace

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES

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.