SQL语句之alter database和alter tablespace语句

引言：此文只是说明alter database（或是tablespace）各种具体子句共有的特点，不描述具体子句的用法。

 

一、以alter database和alter tablespace语句两者都有的删除文件子句为例子来说明

1、alter database和alter tablespace语句各自能操作哪些类型的文件

在表空间级别上，没有对重做日志文件的操作，因为表空间这个逻辑对象对应的物理文件只包括数据文件和临时数据文件。

例如，alter tablespace  add datafile_tempfile_clauses ，即添加的文件包括数据文件和临时数据文件，但是没有重做日志文件。

在数据库级别上，则是可以对重做日志文件的操作，因为数据库这个逻辑对象对应的物理文件包括数据文件和临时数据文件，重做日志文件，控制文件。

在数据库级别上，对控制文件进行操作时，可能会对参数文件进行相关修改，即在参数文件中修改控制文件的路径。

在数据库级别上，不能对参数文件进行操作，因为一方面是参数文件不属于数据库的文件范围（见，何明），二是依据下图可知参数文件已经是最顶端的文件了，若是通过在数据库级别的语句进行修改参数文件的路径，则不知道将该路径放到哪里。（参数文件的路径自然是要放到某一个配置文件上的，oracle实例通过该配置文件找到参数文件的。只是还不知道如何修改读取参数文件的默认路径。还有，设计一个软件系统时，配置文件间若存在下图的位置关系的话，那么最顶层的配置文件的路径肯定要放在该软件里的某一个变量上的，若该变量是常量变量的话，则相当于存放最顶层的配置文件的路径已经在软件里写死了，若不是常量变量的话，则相当于存放最顶层的配置文件的路径还是可以在软件里修改的。）

例如，alter database语句，具体如下：

database_file_clauses：=

注释：

1、database_file_clauses中的database_file专门指的是数据库中的数据文件（datafile）和临时文件（tempfile）这两类型文件。

2、

alter database语句没有create_tempfile_clause？什么语句可以实现create_tempfile_clause？

create_datafile_clause貌似不是本义上的意思(即不相当于add_datafile_clause，一个新的数据文件要被创建的话要通过alter tablespace语句的add_datafile_clause从句而产生，而一个新的临时文件要被创建的话要通过alter （temp） tablespace语句的add_tempfile_clause从句而产生，之所以新的数据文件和临时文件创建要以alter tablespace语句形式来add是因为只有这两种文件类型有表空间这个概念，即文件的上一层就是表空间，而控制文件和日志文件则没有表空间这个概念在其上一层，所以这两类文件要以alter database语句来add)，

还有alter database语句没有drop_datafile_clause?【只有altertablespace语句有drop_datafile_clause，类似alter database  datapfile ‘D:\app\hao\oradata\orcl\user01.dbf’ offline for  drop这样的语句中offline是该语句的中心意思（即数据文件要脱机），for  drop是修饰offline的（即数据文件要以怎样形式脱机），临时文件也类似？】

logfile_clauses：=

controlfile_clauses：=

2、alter database或是alter tablespace语句上文件类型关键字的作用
         在数据库级别上，对一个文件进行操作时，在alter database语句上要写上表示该文件是哪一种类型文件的关键字，如关键字tempfile（临时数据文件）、datafie（数据文件）、logfile（重做日志文件）、controlfile（控制文件）等。
        例如，数据库级别的删除语句：
        alter database  tempfile ‘D:\app\hao\oradata\orcl\tmp01.dbf’ drop;    
    和alter database  datapfile ‘D:\app\hao\oradata\orcl\user01.dbf’ offline for  drop;      //for关键字可以省略      不算删除语句，而是脱机语句？
还有alter database drop  logfile  member ‘D:\app\hao\oradata\orcl\redo01.log’ ;

        在表空间级别上， 对一个文件进行操作时，也是要在alter tablespace语句上要写上表示该文件是哪一种类型文件的关键字。
        例如，表空间级别的删除语句：
        alter tablespace drop  tempfile ‘D:\app\hao\oradata\orcl\tmp01.dbf’ ;
    和alter  tablespace drop datapfile ‘D:\app\hao\oradata\orcl\user01.dbf’ ;
   

注释：

1、对datafile类型的文件进行删除操作时，无论以alter database或是alter tablespace语句执行，V$datafile里的相关数据行依然存在。参考：解决办法：异地冷恢复时 如果发现v$datafile里的有些用户用的数据文件没有备份

2、我们看看oracle官方文档里，对alter database语句的FOR DROP选项的解释：

FOR DROP

 If the database is in NOARCHIVELOG mode,

then you must specifyFOR DROP clause totake a data fileoffline（即FOR DROP修饰offline，FOR DROP的出现就是为了达到offline的目的的）. However, this clause does not remove the data file from the database. To do that, you must use an operating system command or drop the tablespace in which the data file resides.Until you do so, the data file remains in the data dictionary with the statusRECOVER orOFFLINE.

【对datafile类型的文件进行删除操作时，无论以alter database或是alter tablespace语句执行，V$datafile里的相关数据行依然存在。出于什么考虑，为什么字典里还有当datafile类型的文件删除时？】

If the database is in ARCHIVELOG mode,

then Oracle Database ignores the FORDROP clause.

例句，alter database  datapfile ‘D:\app\hao\oradata\orcl\user01.dbf’ offline for  drop; 

二、以alter database和alter tablespace语句两者都有的移动文件子句为例子来说明

3、关于rename file 'A‘   to  'B'子句的说明

rename file 'A‘   to  'B'；中，

A必须是数据字典表中存在的且状态为offline？可以在数据字典表中不存在？还有是，可以没有操作系统级别的物理文件存在。

B必须是一个操作系统级别上存在的物理文件，而关于B的信息是否数据字典表里存在无所谓的。若关于B的信息存在于数据字典表里，则状态必须为为offline。

如果操作系统级别上不存在B这个物理文件，则执行rename file 'A‘   to  'B'；语句时，会提示ORA-01141: 重命名数据文件 6 时出错 - 未找到新文件'B‘。【数据文件 6 指的是文件A原来对应的在数据库中的文件编号】

rename file 'A‘   to  'B'；

相当于逻辑删除A之后再给表空间或是数据库添加B文件。

rename file 'A‘   to  'B'子句中file的含义：

4、 alter database 语句和alter tablespace语句可以在哪些数据库启动阶段执行

       alter database 语句可以在mount阶段和open阶段执行，但是 alter  tablespace语句只能在open阶段执行，若是在mount阶段执行alter  tablespace语句，则会提示“ORA-01109: 数据库未打开”。这个说明语句中带有 tablespace的，即与 tablespace（表空间）有关的语句，必须在open阶段执行。因为表空间这个对象，只有在数据文件处于打开状态后，才形成、存在的。

表空间这个对象创建在控制文件中，因为v$tablespace可以在mount阶段运行。

alter  tablespace语句大部分时候只在open阶段执行，但是有时候也会在mount阶段运行，如

alter  tablespace   system datafile offline；

【或者alter  tablespace   system  offline；？？】

注释：

1、表空间对象和用户对象这些应该存放在控制文件中，不是数据文件中【我们可以学习下控制文件结构格式，看里面具体存放了什么信息，应该是有表空间对象和用户对象这些信息的】。不对，如果用户对象存放在控制文件中，那么以该用户登陆数据库时，数据库若处于nomount阶段（还没读取控制文件），怎么该用户还可以登陆数据库呢？用户对应于一个数据库（控制文件数据文件日志文件组成），而非对应于数据库实例。

2、altertablespace rename file 'A‘   to  'B'；不能在在mount阶段执行，而altertablespace rename file 'A‘   to  'B'；altertablespace rename file 'A‘   to  'B'；可以，为什么？

3、alter database语句和altertablespace语句执行很多操作前，都要先执行脱机处理操作？

0.文件或表空间的状态级别由低到高：recovery、offline、online？

5、alter database语句和altertablespace语句执行很多操作前，都要先执行脱机处理操作

         --------rename子句在两个语句间的区别，共性是open阶段都要先脱机处理

alter database 语句有时候只能在mount阶段执行。

alter database语句

在mount阶段，执行alter database的所有具体语句，例如，alter database rename file 'A‘   to  'B'；时，都不需要对所操作的文件进行脱机操作，因为此时数据文件未打开（即脱机），无所谓要脱机处理。

在open阶段，执行alter database 某些具体语句，例如，alter database rename file 'A‘   to  'B'；，需要先对所操作的文件进行脱机操作：

 1、在ARCHIVRLOG模式下的更改DataFile状态

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

2、在NOARCHIVELOG模式下使DataFile脱机

 

    由于在NOARCHIVELOG模式下，数据文件脱机后会造成数据的遗失，所以只能使用ALTER DATABASE语句下带有DATAFILE和OFFLINE DROP子句的选项将该DataFile直接取消，例如该DataFile只包含临时段数据，并没有备份时

 

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users3.dbf' OFFLINE DROP;

注释 ： 在alter database语句上的  联机操作，相当于使得该单个的数据文件处于打开状态。

              在alter tablespace语句上的  联机操作，相当于使得属于该表空间的所有的数据文件处于打开状态。

在open阶段，如果没有【对文件B】进行脱机操作，就会发生以下情况：

例如，

SQL>startup mount

SQL>alter database rename file 'C‘   to  'A'；

数据库已更改

SQL>alter database open；

SQL>alter database rename file 'A‘   to  'B'；

alter database rename file 'A‘   to  'B'；

ORA-01511: 重命名日志/数据文件时出错

ORA-01523: 无法将数据文件（（指的是B））重命名为'A' - 文件（指的是B）已是数据库的一部分

至于原因，ORA-01523里已经说明了，即文件（指的是B）已是数据库的一部分，此时数据文件处于打开状态了。

alter tablespace 语句

在open阶段，执行alter tablespace 某些具体语句，例如，alter tablespace rename file 'A‘   to  'B'；，也是需要先对所操作的文件进行脱机操作：

>alter tablespace tb_sys OFFLINE;
$>mv /dev/dbvg01/a.dbf /dev/dbvg02/b.dbf
>alter tablespace tb_sys RENAME FILE '/dev/dbvg01/a.dbf' TO '/dev/dbvg02/b.dbf';
>alter tablespace tb_sys ONLINE;

 

注释：如果这里的b.dbf不是属于表空间tb_sys的数据文件a.dbf复制而来的话，比如，b.dbf原本是别的表空间或别的数据库的，那么最后一步online时可能不会成功，因为b.dbf的文件头里的checkpoint_chang#和属于表空间tb_sys的各个数据文件的这个值不一致。

详见  

        ALTER DATABASE 与 ALTER TABLESPACE OFFLINE的区别

 在mount阶段，不能执行alter tablespace 大部分具体语句，例如，altertablespace rename file 'A‘   to  'B'。

下面以移动数据文件的操作方法来说明alter tablespace和alter database语句可以在哪些数据库启动阶段执行：

Oracle 移动数据文件的操作方法

总结：

影响alter tablespace和alter database两个语句只能否执行成功rename子句的因素是数据库处于哪个启动阶段和数据库为归档模式还是非归档模式。具体如下：

处于open阶段时，alter database和alter tablespace两个语句中要执行rename子句的话，都得先执行offline子句。

此时，若数据库处于归档模式，则alter database和alter tablespace两个语句中执行offline子句都可以成功。之后，两个语句就可以成功执行各自的rename子句。最后，alter database和alter tablespace两个语句中执行online子句都可以成功。这样，移动数据文件操作完成。执行offline子句都可以成功。之后，两个语句就可以成功执行各自的rename子句。最后，alter database和alter tablespace两个语句中执行online子句都可以成功。这样，移动数据文件操作执行各自的rename子句。最后，alter database和alter tablespace两个语句中执行online子句都可以成功。这样，移动数据文件操作完成。

此时，若数据库处于非归档模式，则alter database语句中执行offline子句不会成功，alter tablespace语句中执行offline子句可以成功。之后，alter tablespace语句就可以成功执行其的rename子句。最后，alter tablespace语句中执行online子句可以成功。这样，alter tablespace语句中alter tablespace语句中alter tablespace语句移动数据文件操作完成。

处于mount阶段时，不需要执行offline子句。

alter database和alter tablespace两个语句中，前者可要执行rename子句成功，后者不行。

总之，

处于open阶段时，无论数据库处于非归档模式还是归档模式，alter tablespace语句都可以执行rename子句成功；而alter database语句，只有数据库处于归档模式时，才能成功。

处于mount阶段时，数据库就无所谓处于非归档模式还是归档模式这个概念，因为只有处于open阶段时，才会打开重做日志文件，之后，才可设置归档模式为哪种类型。此时，alter database语句可执行rename子句成功。

将表空间和数据文件从一个位置移动到另一个位置的操作方法

一． open阶段的OFFLINE              //alter database和alter tablespace语句的rename中，都可以

OFFLINE 分为ALTER DATABASE 与 ALTER TABLESPACE OFFLINE，

他们的区别参看blog：http://blog.youkuaiyun.com/tianlesoftware/archive/2009/11/29/4898800.aspx

按数据文件来：  //按数据文件来，只有在ARCHIVELOG模式下可以

1.先将相应的数据文件 offline  

ALTER DATABASE DATAFILE 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' OFFLINE;
2.把数据文件 copy 到新位置
3. alter database rename file 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' to 'D:/TEST01.DBF';

4. 介质恢复(offline 数据文件必须要介质恢复)

recover datafile 'D:/TEST01.DBF'
5. 将相应的数据文件 online 

SQL>ALTER DATABASE DATAFILE 'D:/TEST01.DBF' ONLINE;

按表空间来：       //表空间，在NOARCHIVELOG模式下和在ARCHIVELOG模式下都可以

1.先将相应的表空间 offline  

SQL>alter tablespace test offline;  

2.把数据文件 copy 到新位置
3. alter tablespace TEST  rename datafile 'D:/TEST01.DBF' to 'D:/ORACLE/ORADATA/DBA/TEST01.DBF'

4. 将表空间 online 

SQL>alter tablespace test online;   

 

二． mount阶段  //alter database和alter tablespace语句的rename中，前者可用，后者不可此时

Shutdown 数据库

1. 关闭数据库 

C:>set ORACLE_SID=DBA

C:>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 29 11:14:02 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn sys/admin as sysdba

已连接。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>

2. 把数据文件 copy 到新位置

3. rename datafile

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size              71303872 bytes

Database Buffers          209715200 bytes

Redo Buffers                7139328 bytes

数据库装载完毕。

SQL> alter database rename file 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' to 'D:/TEST01

.DBF';

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select file#,name,status from v$datafile;

 FILE# NAME                                STATUS

-------- -------------------------------------------------------  ---------------

   1  D:/ORACLE/ORADATA/DBA/SYSTEM01  SYSTEM

   2  D:/ORACLE/ORADATA/DBA/UNDOTBS0  ONLINE

   3  D:/ORACLE/ORADATA/DBA/SYSAUX01  ONLINE

   4  D:/ORACLE/ORADATA/DBA/USERS01.   ONLINE

   5  D:/TEST01.DBF                              ONLINE

5、alter database rename file '................'   to  '................'；

这个语句只能在mount阶段可以执行，因为此时数据文件没有处于打开状态。

附加：

alter_tablespace::=

alter_tablespace之

datafile_tempfile_clauses 

datafile_tempfile_clauses ::=

DROP Clause

Specify DROP to drop from the tablespace an empty data file or temp file specified byfilename orfile_number.This clause causes the data file or temp file to be removed from the data dictionary and deleted from the operating system.The database must be openat the time this clause is specified.

The ALTER TABLESPACE ... DROP TEMPFILE statement is equivalent to specifying the ALTER DATABASE TEMPFILE ... DROP INCLUDINGDATAFILES.

Restrictions on Dropping Files To drop a data file or temp file, the data file or temp file:

• Must be empty.

• Cannot be the first file that was created in the tablespace. In such cases, drop the tablespace instead.

• Cannot be in a read-only tablespace that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.

• Cannot be offline.

ONLINE | OFFLINE Clauses

Use these clauses to take all data files or temp files in the tablespace offline or put them online（相当于表空间的所有数据文件各自执行了一次ALTERDATABASEdataFILE ...offline）.These clauses have no effect on theONLINE orOFFLINE status of the tablespace itself.（即即使表空间的所有数据文件都是offline状态，但是该表空间自己的状态还可以是online的）

The database must be mounted. Iftablespace isSYSTEM, or an undo tablespace, or the default temporary tablespace, then the database must not be open.

译文：ONLINE | OFFLINE 从句运行的条件是数据库至少处于mount阶段。而如果表空间是SYSTEM, or an undo tablespace, or the default temporary tablespace，那么数据库则不能处于open阶段，那只有处于mount阶段了。

alter_tablespace之

tablespace_state_clauses

tablespace_state_clauses::=

Use these clauses to set or change the state of the tablespace.

ONLINE | OFFLINE

Specify ONLINE to bring the tablespace online. SpecifyOFFLINE to take the tablespace offline and prevent further access to its segments. When you take a tablespace offline, all of its data files are also offline.

Suggestion:

Before taking a tablespace offline for a long time, consider changing the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. While the tablespace is offline, such users cannot allocate space for objects or sort areas in the tablespace. See ALTER USER for more information on allocating tablespace quota to users.

Restriction on Taking Tablespaces Offline You cannot take a temporary tablespace offline.

OFFLINE NORMAL SpecifyNORMAL to flush all blocks in all data files in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online.This is the default.

OFFLINE TEMPORARY If you specifyTEMPORARY, then Oracle Database performs a checkpoint for all online data files in the tablespace but does not ensure that all files can be written.Files that are offline when you issue this statement may requiremedia recovery before you bring the tablespace back online.

OFFLINE IMMEDIATE If you specifyIMMEDIATE, then Oracle Database does not ensure that tablespace files are availableanddoes not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.

注释：

OFFLINE NORMAL时，一要该表空间的所有数据文件联机，二才会做做检查点。

OFFLINE TEMPORARY时会对此时联机的该表空间的数据文件做检查点，而此时脱机的该表空间的数据文件（alter database datafile offline）不做检查点。

附加：

ALTER DATABASE

create_datafile_clause::=
Description of the illustration create_datafile_clause.gif

create_datafile_clause（不是新创建一个文件，而是重新创建原来有的文件）

Use the CREATE DATAFILE clause to create a new empty data file in place of an old one. You can use this clause tore-createa data file that was lost with no backup. Thefilename orfilenumber must identify a file that is or was once part of the database. If you identify the file by number, thenfilenumber is an integer representing the number found in theFILE# column of theV$DATAFILE dynamic performance view or in theFILE_ID column of theDBA_DATA_FILES data dictionary view.

• Specify AS NEW to create an Oracle-managed data file with a system-generated filename, the same size as the file being replaced, in the default file system location for data files.

• Specify AS file_specification to assign a file name (and optional size) to the new data file. Use thedatafile_tempfile_spec form offile_specification (see file_specification) to list regular data files and temp files in an operating system file system or to list Oracle Automatic Storage Management (Oracle ASM) disk group files.

If the original file (filename orfilenumber) is an existing Oracle-managed data file, then Oracle Database attempts to delete the original file after creating the new file. If the original file is an existing user-managed data file, then Oracle Database does not attempt to delete the original file.

If you omit the AS clause entirely, then Oracle Database creates the new file with the same name and size as the file specified byfilename orfilenumber.

During recovery, all archived redo logs written to since the original data file was created must be applied to the new, empty version of the lost data file.

Oracle Database creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.

Restrictions on Creating New Data Files The creation of new data files is subject to the following restrictions:

• You cannot create a new file based on the first data file of the SYSTEM tablespace.

• You cannot specify the autoextend_clause ofdatafile_tempfile_spec in thisCREATEDATAFILE clause.