Oracle tablespace oracle10g Learning Series (10)

Source: Internet
Author: User

Four states of tablespace: 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 table space state changes:

Sql>alter tablespace users offline;

Sql>alter tablespace users online;

Sql>alter tablespace users Read write;

Sql>alter tablespace users Read only;


To create a table space:

Sql>create tablespace tb01 datafile '/u01/oracle/oradata/orcl/tb01.dbf ' size 10m autoextent on;


Table Space Rename:

Sql>alter tablespace tb01 Rename to tb02;


Table Space Auto-expansion, autoextensible Auto-expand field:

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 '/u01/oracle/oradata/orcl/tb01.dbf ' size 30m autoextent on;


Table Space File Rename:

The premise is that the database is an archive mode, the database is in mount or open mode, open mode, the table space to offline, and finally the table space 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 '/u01/oracle/oradata/orcl/tb01.dbf ' to '/u01/oracle/oradata/orcl/tb02.dbf ';

Sql>alter tablespace TB01 Online;

If media recovery is required:

Sql>recover tablespace tb01;


To delete a table space:

Sql>drop tablespace tb01 including contents and datafiles;


To view a table space:

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


This article is from "Kaka West" blog, please be sure to keep this source http://whnba.blog.51cto.com/1215711/1604183

Oracle tablespace oracle10g Learning Series (TEN)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.