13518219792
表空间的四种状态：online，offline，read write和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表空间状态改变：
SQL>alter tablespace users offline;
SQL>alter tablespace users online;
SQL>alter tablespace users read write;
SQL>alter tablespace users read only;
创建表空间：
SQL>create tablespace tb01 datafile '/u01/oracle/oradata/orcl/tb01.dbf' size 10m autoextent on;
表空间重命名：
SQL>alter tablespace tb01 rename to tb02;
表空间自动扩张,AUTOEXTENSIBLE自动扩张字段：
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;
表空间文件重命名：
前提是数据库是归档模式，数据库处于mount或者open模式，open模式的时候，表空间要offline，最后把表空间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;
需要介质恢复的话：
SQL>recover tablespace tb01;
删除表空间：
SQL>drop tablespace tb01 including contents and datafiles;
查看表空间：
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
