Create and manage temporary, permanent, and undo tablespaces

Create and manage temporary, permanent, and undo tablespaces
关于tablespace的操作语句有三大类，分别是create tablespace、alter tablespace、drop tablespace
表空间如标题所说，总体也分为三大类，temporary tablespace 、system/sysaux/users tablespaces(永久表空间，这里也把普通建的表空间归到这类)、undo tablespace
这三类的语法创建（system和sysaux会在建立数据库时默认创建，并且，这两个表空间的维护与其常用表空间的维护也是有点小区别)
permanent tablespace:
create smallfile/bigfile tablespace xxxx datafile xxxxx(可以通过设置db_create_file_dest这个参数，从而不指定datafile子句，一般不建议这样做)
SQL> create tablespace t datafile '/u01/app/oracle/oradata/datafile/t.dbf' size 10m;
Tablespace created.

SQL> create tablespace ttt extent management local;
create tablespace ttt extent management local
                                           *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/datafile/' scope=both;
System altered.

SQL> create tablespace tt extent management local;==>本地管理的表空间
Tablespace created.
生成出来的文件名字是系统指定，这个格式应该可以修改的，这里我没有做过认证。

temporary tablespace:
create smallfile/bigfile temporary tablespace xxx tempfile xxxxx;
QL>  create bigfile temporary tablespace t tempfile '/u01/app/oracle/oradata/datafile/t.dbf' size 512m extent management local;
Tablespace created.

SQL> create bigfile temporary tablespace tt extent management local;
create bigfile temporary tablespace tt extent management local
                                                           *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

SQL> alter system set db_create_file_dest='/u01/' scope=both;
System altered.

SQL> create bigfile temporary tablespace ttt extent management local;
Tablespace created.

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/

SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME                                                         TABLESPACE_NAME
----------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/datafile/t.dbf                            T
/u01/ORCL11G/datafile/o1_mf_ttt_9zov3jtb_.tmp                     TTT==>可以看出当你指定文件时，它会自动生成两个子目录即xxx/实例名/datafile/xxx

undo tablespaces：
SQL> create bigfile undo tablespace t datafile '/u01/app/t.dbf' size 512m extent management local;
Tablespace created.

较为特殊的system和sysaux：
这两个表空间一般在创建数据库时，就会创建；system tablespace包含了数据库服务的基本信息，像数据字典和system rollback segment，一般不要rename或drop 、offline此表空间，可能带来不必要的麻烦。sysaux tablespace包含了oracle的部件或新特性，这个表空间出问题，oracle库的核心功能可用，但是相关部件或新特性就无法使用了。这就是为什么上面没有介绍说明怎么手工去创建这两个表空间。
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.
V$SYSAUX_OCCUPANTS通过这个视图，可以查看sysaux表空间里一些部件的信息，在这个表空间里，主要部件那就是awr和oem。

1、临时表空间组：
临时表空间组里的最后一个的表空间被删除，表空间组也会被删除
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/datafile/temp1.dbf' size 100m tablespace group g1;==>创建临时表空间，并且创建g1组
Tablespace created.

SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/datafile/temp2.dbf' size 140m;
Tablespace created.

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
G1                             TEMP1

SQL> alter tablespace temp2 tablespace group g2;==>将temp2放到g2组，虽然没有，oracle会自动处理创建g2组
Tablespace altered.

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
G1                             TEMP1
G2                             TEMP2

SQL> alter tablespace temp2 tablespace group g1;
Tablespace altered.

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
G1                             TEMP1
G1                             TEMP2

SQL> alter tablespace temp2 tablespace group g3;
Tablespace altered.

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
G1                             TEMP1
G3                             TEMP2

SQL> drop tablespace  temp2 including contents and datafiles cascade constraints;==>删除组里最后一个表空间，组也会被删除
Tablespace dropped.

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
G1                             TEMP1


SQL> create user t identified by t default tablespace test temporary tablespace temp1;==>指定一个用户的默认临时表空间
User created.

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='T';
USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
T                              TEST                           TEMP1

2、针对表空间设置非标准块大小
多个表空间的block_size大小，可以不同，这里做什么要开启一些参数才可以，db_nk_cache_size
SQL> show parameter _cache_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 32M
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/datafile/t1.dbf' size 100m extent management local blocksize 4k;
Tablespace created.

SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/datafile/t2.dbf' size 100m extent management local blocksize 16k;
create tablespace t2 datafile '/u01/app/oracle/oradata/datafile/t2.dbf' size 100m extent management local blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> alter system set db_16k_cache_size=32m scope=both;
System altered.

SQL> show parameter _cache_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 32M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 32M
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/datafile/t2.dbf' size 100m extent management local blocksize 16k;
Tablespace created.

SQL> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
SYSTEM                               8192
SYSAUX                               8192
UNDOTBS                              8192
TEMPTS1                              8192
USERS                                8192
TRSEN                                8192
TEST                                 8192
BIGTBS                               8192
TEMP1                                8192
T1                                   4096
T2                                  16384

3、改变表空间的可用性：
You cannot take the following tablespaces offline:
SYSTEM
The undo tablespace
Temporary tablespaces

在归档模式下，offline normal/temporary/immediate三大参数简单说明
SQL> alter database datafile '/u01/app/oracle/oradata/test.dbf' offline;
Database altered.

SQL> select file_name,tablespace_name,status,online_status from dba_data_files order by tablespace_name;
FILE_NAME                                                    TABLESPACE_NAME                STATUS    ONLINE_
------------------------------------------------------------ ------------------------------ --------- -------
/u01/app/oracle/oradata/datafile/orcl11g/sysaux01.dbf        SYSAUX                         AVAILABLE ONLINE
/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf        SYSTEM                         AVAILABLE SYSTEM
/u01/app/oracle/oradata/test.dbf                             TRSEN                          AVAILABLE RECOVER==>对trsen表空间的此数据文件进行offline，并且成功，这种方法，做了之后，需要做恢复了，因为没有做tablespace checkpoint
/u01/app/oracle/oradata/datafile/orcl11g/test.dbf            TRSEN                          AVAILABLE ONLINE
/u01/app/oracle/oradata/datafile/orcl11g/undotbs01.dbf       UNDOTBS                        AVAILABLE ONLINE
/u01/app/oracle/oradata/datafile/orcl11g/users01.dbf         USERS                          AVAILABLE ONLINE

SQL> alter tablespace trsen offline normal;
alter tablespace trsen offline
*
ERROR at line 1:
ORA-01191: file 9 is already offline - cannot do a normal offline
ORA-01110: data file 9: '/u01/app/oracle/oradata/test.dbf'

SQL> alter tablespace trsen offline temporary;
Tablespace altered.

如果首先对某个数据文件进行offline，然后在其对tablespace进行temporary的offline，然后测试online之前offline的数据文件，发现需要介质恢复
SQL> alter database datafile '/u01/app/oracle/oradata/test.dbf' online;  
alter database datafile '/u01/app/oracle/oradata/test.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/test.dbf'

SQL> /
FILE_NAME                                                    TABLESPACE_NAME                STATUS    ONLINE_
------------------------------------------------------------ ------------------------------ --------- -------
/u01/app/oracle/oradata/datafile/orcl11g/sysaux01.dbf        SYSAUX                         AVAILABLE ONLINE
/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf        SYSTEM                         AVAILABLE SYSTEM
/u01/app/oracle/oradata/test.dbf                             TRSEN                          AVAILABLE RECOVER
/u01/app/oracle/oradata/datafile/orcl11g/test.dbf            TRSEN                          AVAILABLE OFFLINE==>可以看出，单独对数据文件offline和对表空间进行offline，的状态不同，做了tablespace checkpoint
/u01/app/oracle/oradata/datafile/orcl11g/undotbs01.dbf       UNDOTBS                        AVAILABLE ONLINE
/u01/app/oracle/oradata/datafile/orcl11g/users01.dbf         USERS                          AVAILABLE ONLINE

再次对表空间进行online时，发现需要介质恢复
SQL> alter tablespace trsen online ;
alter tablespace trsen online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/test.dbf'

SQL> recover datafile 9;
Media recovery complete.


SQL> alter tablespace trsen offline immediate;
Tablespace altered.

SQL> alter tablespace trsen online;
alter tablespace trsen online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/datafile/orcl11g/test.dbf'

SQL> recover datafile 5;
Media recovery complete.
SQL> alter tablespace trsen online;
alter tablespace trsen online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/test.dbf'

SQL> recover datafile 9;
Media recovery complete.
SQL> alter tablespace trsen online;==>online一个表空间，就是简单的一句话
Tablespace altered.

然后针对tablespace或某一数据文件进行online，是没有问题的
在归档模式下，对一个表空间的某个数据文件进行offline后，无法用normal参数对其表空间进行offline；immediate更为苛刻，做了之后，不会做checkpoint，online需要做介质恢复，然后才能起来，而且不能再非归档下进行
ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
可看出也可以对临时表空间的某些数据文件 进行online和offline

4、只读表空间
You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to use the new description until the tablespace is made read/write.

SQL> alter tablespace test read only;
Tablespace altered.

SQL> select file#,enabled from v$datafile;
NAME                                                         STATUS  ENABLED
------------------------------------------------------------ ------- ----------
/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf        SYSTEM  READ WRITE
/u01/app/oracle/oradata/datafile/orcl11g/sysaux01.dbf        ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/orcl11g/undotbs01.dbf       ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/orcl11g/users01.dbf         ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/orcl11g/test.dbf            ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf           ONLINE  READ ONLY==>此表空间为read only，此表空间为T用户的默认表空间
/u01/app/oracle/oradata/datafile/orcl11g/bigtbs.dbf          ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/t1.dbf                      ONLINE  READ WRITE
/u01/app/oracle/oradata/test.dbf                             ONLINE  READ WRITE
/u01/app/oracle/oradata/datafile/t2.dbf                      ONLINE  READ WRITE

SQL> alter tablespace test read only;
Tablespace altered.

SQL> show user;
USER is "T"
SQL> select * from e;
        ID NAME
---------- ----------
         1 a
         1 a
         1 a

SQL> create table e1 as select * from e;
create table e1 as select * from e
                                *
ERROR at line 1:
ORA-01647: tablespace 'TEST' is read-only, cannot allocate space in it

SQL> drop index idx_id;
Index dropped.

SQL> alter table e add address char(20);
Table altered.

SQL> desc e;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               CHAR(10)
 ADDRESS                                            CHAR(20)

SQL> insert into e values(1,'a','b');
insert into e values(1,'a','b')
            *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf'

SQL> alter table e drop column tel;
alter table e drop column tel
*
ERROR at line 1:
ORA-12985: tablespace 'TEST' is read only, cannot drop column

SQL> desc e;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               CHAR(15)
 ADDRESS                                            CHAR(20)
 TEL                                                CHAR(12)

SQL> alter table e modify name char(25);
Table altered.

SQL> desc e;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               CHAR(25)
 ADDRESS                                            CHAR(20)
 TEL                                                CHAR(12)

可以跟踪372事件来查看具体为什么不能执行成功的原因，下面语句，对当前session进行跟踪
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "372TRACE";
Session altered.
SQL> alter session set events '372 trace name ERRORSTACK level 3';
Session altered.
SQL> alter tablespace test read only;

虽然延迟打开只读表空间的数据文件，参数影响
Setting READ_ONLY_OPEN_DELAYED=TRUE has the following side-effects:
A missing or bad read-only file is not detected at open time. It is only discovered when there is an attempt to access it.
ALTER SYSTEM CHECK DATAFILES does not check read-only files.
ALTER TABLESPACE...ONLINE and ALTER DATABASE DATAFILE...ONLINE do not check read-only files. They are checked only upon the first access.
V$RECOVER_FILE, V$BACKUP, and V$DATAFILE_HEADER do not access read-only files. Read-only files are indicated in the results list with the error "DELAYED OPEN", with zeroes for the values of other columns.
V$DATAFILE does not access read-only files. Read-only files have a size of "0" listed.
V$RECOVERY_LOG does not access read-only files. Logs they could need for recovery are not added to the list.
ALTER DATABASE NOARCHIVELOG does not access read-only files.It proceeds even if there is a read-only file that requires recovery.

5、变更表空间或数据文件大小
Enabling and Disabling Automatic Extension for a Data File
SQL> select file_name,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME                                                    AUT
------------------------------------------------------------ ---
/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf           NO

SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/datafile/orcl11g/test2.dbf' size 10m autoextend on;
Tablespace altered.

SQL> alter database datafile '/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf' autoextend on;==>修改现有的数据文件是否自动扩展
Database altered.

SQL> select file_name,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME                                                    AUT
------------------------------------------------------------ ---
/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf           YES
/u01/app/oracle/oradata/datafile/orcl11g/test2.dbf           YES

Manually Resizing a Data File
SQL> alter database datafile '/u01/app/oracle/oradata/datafile/orcl11g/test2.dbf' resize 100m;
Database altered.

SQL> select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='TEST';
FILE_NAME                                                            MB
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/datafile/orcl11g/test1.dbf                   50
/u01/app/oracle/oradata/datafile/orcl11g/test2.dbf                  100

SQL> alter tablespace test resize 20m;==>small tablespace不适合这样操作，只有big tablespace才能满足此操作
alter tablespace test resize 20m
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST

SQL> select d.file_name,d.bytes/1024/1024 MB,t.bigfile from dba_data_files d,dba_tablespaces t where t.tablespace_name=d.tablespace_name and t.tablespace_name='BIGTBS';
FILE_NAME                                                            MB BIG
------------------------------------------------------------ ---------- ---
/u01/app/oracle/oradata/datafile/orcl11g/bigtbs.dbf                1024 YES

SQL> alter tablespace bigtbs resize 100m;
Tablespace altered.

SQL>  select d.file_name,d.bytes/1024/1024 MB,t.bigfile from dba_data_files d,dba_tablespaces t where t.tablespace_name=d.tablespace_name and t.tablespace_name='BIGTBS';
FILE_NAME                                                            MB BIG
------------------------------------------------------------ ---------- ---
/u01/app/oracle/oradata/datafile/orcl11g/bigtbs.dbf                 100 YES

6、重命名数据文件和remove其位置，在物理上，DBMS_FILE_TRANSFER来做copy动作，此包不局限数据文件是否在asm磁盘上
在做重命名或更改位置时，主要要保证scn一致，这为什么是offline或者mount的根本原因
Procedure for Relocating Data Files in a Single Tablespace即，常规表空间是
SQL> CREATE DIRECTORY SOURCE_DIR1 AS '/u01/app/oracle/oradata/datafile/orcl11g';
Directory created.

SQL> CREATE OR REPLACE DIRECTORY SOURCE_DIR2 AS '/u01/app/oracle/oradata';
Directory created.

SQL> CREATE OR REPLACE DIRECTORY DEST_DIR    AS '/u01/app/oracle/oradata/datafile/';       
Directory created.

SQL> GRANT READ ON DIRECTORY source_dir1 TO PUBLIC;
Grant succeeded.

SQL>GRANT READ ON DIRECTORY source_dir2 TO PUBLIC;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY dest_dir TO PUBLIC;
Grant succeeded.


SQL> alter tablespace trsen offline;一致性处理
Tablespace altered.

SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='TRSEN';
FILE_NAME                                                    TABLESPACE_NAME                ONLINE_
------------------------------------------------------------ ------------------------------ -------
/u01/app/oracle/oradata/datafile/trsen1.dbf                  TRSEN                          OFFLINE
/u01/app/oracle/oradata/test.dbf                             TRSEN                          OFFLINE

SQL> BEGIN
  2    DBMS_FILE_TRANSFER.COPY_FILE(
  3          source_directory_object       =>  'SOURCE_DIR1',
  4          source_file_name              =>  'test.dbf',
  5          destination_directory_object  =>  'DEST_DIR',
  6          destination_file_name         =>  'trsen1.dbf');
  7  END;
  8  /
 PL/SQL procedure successfully completed.

SQL> ALTER TABLESPACE trsen RENAME DATAFILE '/u01/app/oracle/oradata/datafile/orcl11g/test.dbf' TO '/u01/app/oracle/oradata/datafile/trsen1.dbf';
Tablespace altered.

SQL> BEGIN
  2    DBMS_FILE_TRANSFER.COPY_FILE(
  3          source_directory_object       =>  'SOURCE_DIR2',
  4          source_file_name              =>  'test.dbf',
  5          destination_directory_object  =>  'DEST_DIR',
  6          destination_file_name         =>  'trsen2.dbf');
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> ALTER TABLESPACE trsen RENAME DATAFILE '/u01/app/oracle/oradata/test.dbf' TO '/u01/app/oracle/oradata/datafile/trsen2.dbf';
Tablespace altered.

SQL> alter tablespace trsen online;
Tablespace altered.

SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='TRSEN';
FILE_NAME                                                    TABLESPACE_NAME                ONLINE_
------------------------------------------------------------ ------------------------------ -------
/u01/app/oracle/oradata/datafile/trsen1.dbf                  TRSEN                          ONLINE
/u01/app/oracle/oradata/datafile/trsen2.dbf                  TRSEN                          ONLINE

在做rename和更改未位置的过程中，注意不要留空格等特殊符号，否则会不成功

Procedure for Renaming and Relocating Data Files in Multiple Tablespaces
To rename or relocate data files of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline
Ensure that the database is mounted but closed.这个条件很重要，我测试对system表空间进行位置的更改

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             734004408 bytes
Database Buffers          327155712 bytes
Redo Buffers                5541888 bytes
Database mounted.

按照文档可以做dbms_file_transfer的copy动作，可能是环境没有做好，否则如果system表空间在asm里，怎么处理?
SQL> BEGIN
  2    DBMS_FILE_TRANSFER.COPY_FILE(
  3          source_directory_object       =>  'SOURCE_DIR1',
  4          source_file_name              =>  'system01.dbf',
  5          destination_directory_object  =>  'DEST_DIR',
  6          destination_file_name         =>  'system.dbf');
  7  END;
  8  /
  DBMS_FILE_TRANSFER.COPY_FILE(
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_FILE_TRANSFER.COPY_FILE' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

在mount状态下，利用alter tablespace来做变更，是做不到的
SQL> ALTER TABLESPACE system RENAME DATAFILE '/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf' TO '/u01/app/oracle/oradata/datafile/system.dbf';
ALTER TABLESPACE system RENAME DATAFILE '/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf' TO '/u01/app/oracle/oradata/datafile/system.dbf'
*
ERROR at line 1:
ORA-01109: database not open

SQL> ALTER DATABASE RENAME FILE  '/u01/app/oracle/oradata/datafile/orcl11g/system01.dbf' TO '/u01/app/oracle/oradata/datafile/system.dbf';
Database altered.

SQL> select name,enabled,status from v$datafile where STATUS='SYSTEM';
NAME                                                         ENABLED    STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/datafile/system.dbf                  READ WRITE SYSTEM

SQL> alter database open;
Database altered.
在做的过程中，没有遇到需要恢复，因为是在测试库里，可能在正在写数据库的库中，启动库时，需要做介质恢复。

metalink上，用rman和用针对system和aux表空间的处理，如下
Using RMAN copy the file to new diskgroup.
RMAN> COPY DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO '+ASMDSK1';
 run {  set newname for datafile '+ASMDSK2/orcl/datafile/users.256.565313879' to '+ASMDSK1/orcl/datafile/users.259.565359071' ;
     switch datafile all; }
Recover the file.==>这这里提到做介质恢复，然后再online数据文件
           SQL:ORCL> RECOVER DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071'
Bring the file online.
             SQL:ORCL>ALTER DATABASE DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071' ONLINE
      Note:
      Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed.
      However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if you reference the file. Otherwise you will get an error (e.g. ORA-15177).
   e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE '+ASMDSK2/orcl/datafile/users.256.565313879';
Note: The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles. For System and Sysaux an approach similar to the one given below can be used:
Create a Copy of datafile in target Diskgroup:
       RMAN> backup as copy tablespace system format '<New DG>';
       RMAN> backup as copy tablespace sysaux format '<New DG>';
Then shutdown the database and restart to a mounted state
      RMAN> shutdown immediate;
      RMAN> startup mount;
switch the datafiles to the copy
      RMAN> switch tablespace system to copy;
      RMAN> switch tablespace sysaux to copy;
 Recover the changes made to these tablespaces;
      RMAN> recover database;

drop数据文件
The following example drops the data file identified by the alias example_df3.f in the Oracle ASM disk group DGROUP1. The data file belongs to the example tablespace.
ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
The next example drops the temp file lmtemp02.dbf, which belongs to the lmtemp tablespace.
ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
This is equivalent to the following statement:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

8、表空间名更改
SQL> select username,default_tablespace from dba_users where username='T';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
T                              TEST

SQL> alter tablespace test rename to t;
Tablespace altered.

SQL> select username,default_tablespace from dba_users where username='T';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
T                              T
如果是临时表空间，则利用database_properties来查看默认临时表空间，重新创建表空间，更改默认属性

9、收缩临时表空间两条语句有什么区别
收缩表空间到指定大小
ALTER TABLESPACE temp1 SHRINK SPACE KEEP 20M;
收缩表空间到尽可能大小
ALTER TABLESPACE temp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';