Category Archives: ORACLE 12C

ORACLE 12C Invisible Columns and Column Ordering

Posted on by

在ORACLE 12C中有了Invisible Columns的概念,就是在表中真实的存在该列,但是通过设置Invisible导致该列不可显示.官方说明:

The property of whether a column is visible can be controlled by the user. 
Invisible columns are not seen unless specified explicitly in the SELECT list.
Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) 
  will not show invisible columns.

数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建含INVISIBLE列表

SQL> CREATE TABLE t_xifenfei (a number, b number INVISIBLE, c number);

Table created.

SQL> desc t_xifenfei
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 A                                                              NUMBER
 C                                                              NUMBER

SQL>  select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          YES
C          NO           2

通过观察可以发现INVISIBLE列在一般的查询中不显示,在USER_TAB_COLS的视图中显示:HIDDEN_COLUMN为YES而且COLUMN_ID为空

设置INVISIBLE列为VISIBLE

SQL> ALTER TABLE t_xifenfei MODIFY (b VISIBLE);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 C                                                  NUMBER
 B                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          NO           3
C          NO           2

当设置为VISIBLE时HIDDEN_COLUMN为YES而且COLUMN_ID为递增值

修改列展示顺序(Column Ordering)

SQL> ALTER TABLE t_xifenfei MODIFY (a invisible);

Table altered.

SQL> ALTER TABLE t_xifenfei MODIFY (a visible);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER
 B                                                  NUMBER
 A                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           3
B          NO           2
C          NO           1

通过INVISIBLE和VISIBLE相关操作,实现A列从头移尾,实现列的Column Ordering效果.
补充说明:在以前的blog中,提供了修改col$基表的方法(通过修改col$.col#改变列展示顺序)来实现列的顺序修改,相对于这种方法来说,修改数据字典的方法风险太大,需要非常谨慎,而且不被ORACLE SUPPORT

ORACLE 12C 支持multiple partitions同时操作

Posted on by

ORACLE 12C在分区维护方面有了不少的增强,在12C的beta版本中已经支持多分区的add/truncate/drop/merge操作,大大的提高了分区维护的效率.
数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

在FF PDB中创建xff用户

SQL> alter session set container=ff;

Session altered.

SQL> create user xff identified by xifenfei;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> conn xff/xifenfei@ff
Connected.
SQL> show con_name;

CON_NAME
------------------------------
FF

创建分区表RANGE PARTITIONS

SQL> CREATE TABLE t_xifenfei
  2  (name varchar2(100),time_id DATE)
  3  partition by range(time_id)
  4  (partition xff_2006 values less than (TO_DATE('01-01-2007','dd-MM-yyyy')),
  5  partition xff_2007 values less than (TO_DATE('01-01-2008','dd-MM-yyyy')),
  6  partition xff_2008 values less than (TO_DATE('01-01-2009','dd-MM-yyyy')),
  7  partition xff_2009 values less than (TO_DATE('01-01-2010','dd-MM-yyyy')));

Table created.

SQL> SET LONG 30
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'

ADD 多个分区

SQL> ALTER TABLE t_xifenfei ADD 
  2    PARTITION XFF_2010 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy')),
  3    PARTITION XFF_2011 VALUES LESS THAN (TO_DATE('01-01-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012 VALUES LESS THAN (TO_DATE('01-01-2013','dd-MM-yyyy'));

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'

7 rows selected.

Split多个分区

SQL> ALTER TABLE t_xifenfei  split PARTITION  XFF_2012 INTO
  2    (PARTITION XFF_2012_03 VALUES LESS THAN (TO_DATE('01-03-2012','dd-MM-yyyy')),
  3    PARTITION XFF_2012_06 VALUES LESS THAN (TO_DATE('01-06-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012_09 VALUES LESS THAN (TO_DATE('01-09-2012','dd-MM-yyyy')),
  5    PARTITION XFF_2012);

Table altered.

SQL>  select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00'
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00'
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00'

10 rows selected.

插入分区数据

SQL>INSERT INTO t_xifenfei VALUES('www.orasos.com',SYSDATE-100);

1 row created.

SQL> INSERT INTO t_xifenfei VALUES('www.orasos.com',SYSDATE-200);

1 row created.

SQL> INSERT INTO t_xifenfei VALUES('www.orasos.com',SYSDATE-300);

1 row created.

SQL>  INSERT INTO t_xifenfei VALUES('www.orasos.com',SYSDATE-10);

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a20
SQL> select * from t_xifenfei;

NAME                 TIME_ID
-------------------- ---------
www.orasos.com     17-FEB-12
www.orasos.com     27-MAY-12
www.orasos.com     04-SEP-12
www.orasos.com     03-DEC-12

SQL>select * from t_xifenfei PARTITION(XFF_2012_03);

NAME                 TIME_ID
-------------------- ---------
www.orasos.com     17-FEB-12

SQL> select * from t_xifenfei PARTITION(XFF_2012_06);

NAME                 TIME_ID
-------------------- ---------
www.orasos.com     27-MAY-12

SQL>  select * from t_xifenfei PARTITION(XFF_2012_09);

no rows selected

SQL>  select * from t_xifenfei PARTITION(XFF_2012);

NAME                 TIME_ID
-------------------- ---------
www.orasos.com     04-SEP-12
www.orasos.com     03-DEC-12

TRUNCATE 多个分区

SQL> Alter table t_xifenfei truncate partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;

Table truncated.

--剩下两条记录存在于XFF_2012中
SQL> select * from t_xifenfei;

NAME                 TIME_ID
-------------------- ---------
www.orasos.com     04-SEP-12
www.orasos.com     03-DEC-12

SQL> SELECT SUBOBJECT_NAME,object_id,data_object_id from user_objects where SUBOBJECT_NAME like 'XFF_2012_0%';

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
XFF_2012_09                         90603          90603    <---为什么没有变
XFF_2012_06                         90602          90606
XFF_2012_03                         90601          90605

SQL> select PARTITION_NAME,HIGH_VALUE,SEGMENT_CREATED FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE                     SEGM
------------------------------ ------------------------------ ----
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00' NO
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00' NO
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00' NO
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00' NO
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00' NO
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00' NO
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00' YES
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00' YES
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00' YES
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00' NO  
--XFF_2012_09因为块延迟创建,没有segment导致truncate对应的dataobj#不变

10 rows selected.

DROP 多个分区

SQL>  Alter table t_xifenfei DROP  partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'

7 rows selected.

MERGE 多分区

SQL> Alter table t_xifenfei merge partitions XFF_2006, XFF_2007, XFF_2008 into partition XFF_OLD;

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_OLD                        TO_DATE(' 2009-01-01 00:00:00'

本测试是基于Range partitions进行,其实在ORACLE 12C中对于分区表的维护做了比较大的增强,上面试验的多分区操作,也支持List partitions和subpartitions.ddl一次性操作多个分区,给分区经常做维护的DBA来说,带来了不少的方便,省去了很多重复行工作.

Enterprise Manager Database Express 12c 欣赏

Posted on by

12C里面不再提供完整的Enterprise Manager,而是提供了Enterprise Manager Database Express 12c,功能在以前的EM基础之上有了很大的折扣,这里贴几张图出来供大家欣赏





以前的版本中,Enterprise Manager是通过dbconsole来控制的,现在的版本直接在监听中注册端口来实现,默认访问为:http://xifenfei:5500/em

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.0.2        on 13-DEC-2012 15:37:35

Copyright (c) 1991, 2012, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2       
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 17 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
Service "xifenfeiXDB" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
The command completed successfully

ORACLE 12C 依然支持 bbed

Posted on by

ORACLE 12C的测试版出来了,对于习惯了使用bbed(ORACLE 手术刀)的人来说,12C是否继续被支持是一个很让人关注的问题,通过本实验测试,12C继续支持bbed,很多艰难的数据库恢复依然可以通过bbed来实现,也从侧面说明,ORACLE 12C在块的结构上还依然和以往版本相似
数据库信息

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Thu Dec 13 09:55:35 2012

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile where rownum=1;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xifenfei/system01.dbf

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

12C中编译bbed

[oracle@xifenfei bbed_64]$ ls -l
total 32
-rw-r--r-- 1 oracle oinstall  8704 Apr 20  2010 bbedus.msb
-rw-r--r-- 1 oracle oinstall 10270 Jul 25  2000 bbedus.msg
-rw-r--r-- 1 oracle oinstall  3976 Apr 20  2010 sbbdpt.o
-rw-r--r-- 1 oracle oinstall  3306 Apr 20  2010 ssbbded.o
[oracle@xifenfei ~]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/lib
[oracle@xifenfei lib]$ cp ~/bbed_64/s* ./
[oracle@xifenfei lib]$ cd ../
[oracle@xifenfei rdbms]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/mesg
[oracle@xifenfei mesg]$ cp ~/bbed_64/bbedus.ms* ./
[oracle@xifenfei ~]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/lib
[oracle@xifenfei lib]$ make -f ins_rdbms.mk /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed

Linking BBED utility (bbed)
rm -f /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed
/u01/app/oracle/product/12.1/db_1/bin/orald -o /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed 
…………
/u01/app/oracle/product/12.1/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1/db_1/lib
[oracle@xifenfei lib]$ mv bbed /u01/app/oracle/product/12.1/db_1/bin/

测试12C中bbed

[oracle@xifenfei ~]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 13 09:58:16 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/app/oracle/oradata/xifenfei/system01.dbf'
        FILENAME        /u01/app/oracle/oradata/xifenfei/system01.dbf

BBED> show
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /u01/app/oracle/oradata/xifenfei/system01.dbf
        BIFILE          bifile.bbd
        LISTFILE       
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> map
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 1112 bytes                   @0       

 ub4 tailchk                                @8188    

BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes                   @0       
   ub1 type_kcbh                            @0        0x0b
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400001
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x21a8
   ub2 spare3_kcbh                          @18       0x0000

BBED> set block 1233
        BLOCK#          1233

BBED> map
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 1233                                  Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdxle, 32 bytes                     @92      

 sb2 kd_off[517]                            @124     

 ub1 freespace[302]                         @1158    

 ub1 rowdata[6660]                          @1460    

 ub4 tailchk                                @8188

ORACLE 12C move datafile测试

Posted on by

通过一段时间的宣传,很多人都知道了ORACLE 12C可以实现在线move数据文件,使得我们从以前先offline相关文件任何系统级别mv,然后在rename file方便了很多。该功能的强大之处在于:
1.可以在库open的情况下move system表空间文件
2.对于其他表空间的数据文件move不用offline(意味着不用停业务)
3.大大简化了以前的操作步骤,很多初级dba对于原来的操作方法不理解,经常导致datafile最终异常
move datafile语法

move datafile之前操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Thu Dec 13 05:57:18 2012

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select *from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> alter session set container = ff;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
FF

SQL> col name for a65
SQL> set lines 134
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xff*
-rw-r----- 1 oracle oinstall 365699072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
-rw-r----- 1 oracle oinstall   5251072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 597696512 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
-rw-r----- 1 oracle oinstall  20979712 Dec 12 21:28 /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf

/u01/app/oracle/oradata/xifenfei/xff:
total 0

执行move datafile操作

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       259

SQL> alter database move datafile 16 to '/u01/app/oracle/oradata/xifenfei/xff/system01.dbf';

Database altered.

--new session 
SQL> select con_id,sid,event from v$session where sid=259;

    CON_ID        SID EVENT
---------- ---------- -----------------------------------------------
         1        259 db file single write
SQL> /

    CON_ID        SID EVENT
---------- ---------- ------------------------------------------------
         1        259 db file sequential read
SQL> /

    CON_ID        SID EVENT
---------- ---------- -------------------------------------------------
         1        259 db file single write
--通过新会话监控等待事件发现,move datafile的主要等待是
--db file sequential read和db file single write

--继续执行move datafile
SQL> alter database move datafile 
  2  '/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf' to
  3  '/u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf';

Database altered.

SQL> alter database move datafile 18 to 
  2  '/u01/app/oracle/oradata/xifenfei/xff/users01.dbf';

Database altered.

SQL> alter database move datafile 19 to 
  2  '/u01/app/oracle/oradata/xifenfei/xff/example01.dbf';

Database altered.

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

     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xff/system01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xff/users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xff/example01.dbf