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

Four states of tablespaces: online,offline,read write and ready only

SQL > select file#,status,enabled from v$datafile

FILE# STATUS ENABLED

1 SYSTEM READ WRITE

2 ONLINE READ WRITE

3 ONLINE READ WRITE

4 ONLINE READ WRITE

5 ONLINE READ WRITE

Users tablespace state changes:

SQL > alter tablespace users offline

SQL > alter tablespace users online

SQL > alter tablespace users read write

SQL > alter tablespace users read only

Create a tablespace:

SQL > create tablespace tb01 datafile'/ u01qqoracle size oradata autoextent on tb01.dbf' oradata

Rename the tablespace:

SQL > alter tablespace tb01 rename to tb02

Tablespace auto-expansion, AUTOEXTENSIBLE auto-expansion fields:

SQL > select tablespace_name,file_name,autoextensible from dba_data_files

SQL > select tablespace_name,file_name,autoextensible from dba_data_files

TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE

-

USERS / home/oracle/oradata/orcl/users01.dbf YES

SYSAUX / home/oracle/oradata/orcl/sysaux01.dbf YES

UNDOTBS1 / home/oracle/oradata/orcl/undotbs01.dbf YES

SYSTEM / home/oracle/oradata/orcl/system01.dbf YES

EXAMPLE / home/oracle/oradata/orcl/example01.dbf YES

SQL > select file#,name from v$datafile

FILE# NAME

1 / home/oracle/oradata/orcl/system01.dbf

2 / home/oracle/oradata/orcl/undotbs01.dbf

3 / home/oracle/oradata/orcl/sysaux01.dbf

4 / home/oracle/oradata/orcl/users01.dbf

5 / home/oracle/oradata/orcl/example01.dbf

SQL > alter database datafile 6 resize autoextend on

SQL > alter tablespace tb01 add datafile'/ u01qqoracle size oradata autoextent on tb01.dbf' oradata

Rename the tablespace file:

The premise is that the database is in archive mode, the database is in mount or open mode, and when the database is in open mode, the tablespace should be offline, and finally the tablespace online:

SQL > alter tablespace tb01 offline

SQL > ho cp / u01/oracle/oradata/orcl/tb01.dbf / u01/oracle/oradata/orcl/tb02.dbf

SQL > alter tablespace rename datafile'/ u01qqoracle _ to'/ u01qorl _

SQL > alter tablespace tb01 online

If media recovery is required:

SQL > recover tablespace tb01

Delete tablespaces:

SQL > drop tablespace tb01 including contents and datafiles

View the tablespace:

SQL > select tablespace_name from dba_data_files

TABLESPACE_NAME

-

USERS

SYSAUX

UNDOTBS1

SYSTEM

EXAMPLE

SQL > select name from v$tablespace

NAME

-

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

EXAMPLE

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.