Oracle splitting partitions简单小结

            本文简单记录在Oracle 10g数据库上对范围分区表的Splitting Partitions测试过程和结论,并不涉及到Oracle数据库中分区技术的详细描述。

1 测试环境及平台:

OS:

[root@localhost ~]# uname -rm
2.6.18-164.el5 x86_64
[root@localhost ~]#

Oracle:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> 

2 创建范围分区表:

SQL> show user;
USER is "SYS"
SQL> create table part_range(id number,name varchar2(30))
  2  partition by range(id)
  3  (partition partmax values less than (maxvalue))
  4  tablespace users;

Table created.

SQL>

3 插入测试数据:

SQL> insert into part_range select object_id,object_name from dba_objects
  2  where object_id<2000;

1953 rows created.

SQL>

4 在分区表part_range上创建2种分区索引:

本地分区索引【Locally partitioned index】

SQL> create index part_range_id_idx on part_range(id) local;

Index created.

SQL>

全局分区索引【Globally partitioned index】

SQL> create index part_range_name_idx on part_range(name) tablespace users;

Index created.

SQL>

5 查看分区表信息:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS

SQL> 

6 查看索引信息:

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL> 

7 对分区表part_range执行Splitting partitions【分区分裂】操作:

SQL> alter table part_range split partition partmax at (2000) 
  2  into (partition p1,partition partmax);

Table altered.

SQL> 

注意,这里的分区分裂操作临界值是2000,即id<2000的记录将全部重组到p1分区,而partmax分区将为空,即0记录

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL> 

8 再次分别查看分区表、索引信息:

分区表:

SQL>  select table_name,partition_name,tablespace_name from user_tab_partitions
  2   where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL> 

索引信息:

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL> 

小结:对于分区分裂之后,如果包含有空分区的话,那么对于本地分区索引和全局分区索引都是可用的。这种分区分裂的方式通常也叫做快速分裂【Fast Splitting】,索引不需要rebuild。

9 如果在上述步骤7中,执行的分区分裂操作如下:

SQL> alter table part_range split partition partmax at (1000)
  2  into (partition p1,partition partmax);

Table altered.

SQL> 

即分区分裂操作临界值是1000id<1000的记录将重组到p1分区,id>=1000的记录将重组到partmax分区。也就是此时,分裂出来的p1和partmax这两个分区均不为空。

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
       953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
      1000

SQL> 

那么,查看到的分区表、索引信息如下:

分区表:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL> 

索引信息:

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL> 

小结:对于分区分裂之后,如果不包含空分区的话,那么对于本地分区索引和全局分区索引都将不可用,索引的状态都变为UNUSABLE。均需要重建

SQL> alter index PART_RANGE_ID_IDX rebuild partition p1;

Index altered.

SQL> alter index PART_RANGE_ID_IDX rebuild partition partmax;

Index altered.

SQL> alter index PART_RANGE_NAME_IDX rebuild;

Index altered.

SQL> 

重建之后,本地分区索引、全局分区索引信息,已由UNUSABLE变为USABLE

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL> 

当然,如果在分裂分区的同时带上UPDATE INDEXES的话,可以在分裂分区的同时重建索引【包含本地分区索引和全局分区索引,状态均为USABLE、VALID】:

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update indexes;

Table altered.

SQL>

分裂分区之后,表信息:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL> 

索引信息:

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL> 

而如果在分裂分区的同时带上UPDATE GLOBAL INDEXES的话,可以在分裂分区的同时重建全局分区索引【不包含本地分区索引,只有全局分区索引状态为VALID】,而本地分区索引需要重建:

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update global indexes;

Table altered.

SQL>

分裂分区之后,表信息:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL> 

索引信息:

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL> 

需要注意的是,在分裂分区的同时重建索引,将会消耗更多时间来完成分裂工作,以及消耗更多的系统资源。如果系统资源较为充足的话,可以考虑带上UPDATE INDEXES选项。

10 最后,再看看另外一种比较特殊的情况。在分裂分区的时候,如果将新分区指向新的表空间【由USERS到EXAMPLE表空间】的话,并且分裂之后,包含空分区的情况。即,分裂的语句如下:

SQL> alter table part_range split partition partmax at (2000)
  2  into (partition p1 tablespace example,partition partmax tablespace example);

Table altered.

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL> 

那么可以看到分区表:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        EXAMPLE
PART_RANGE                     P1                             EXAMPLE

SQL> 

索引分区信息:

SQL>  select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             EXAMPLE                        UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        EXAMPLE                        USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL> 

小结:在分裂分区的时候,如果将新分区指向新的表空间的话,并且分裂之后,即使包含空分区的情况下,只有新空分区的本地索引不需要重建,而含有数据的新分区的本地分区索引以及全局分区索引均需重建。这有别于快速分裂,或者说是快速分裂的一种特殊情况

本文部分内容参考范大师

删除undo表空间遇到ORA-30013及ORA-01548的解决思路

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时,遭遇ORA-30013及ORA-01548的错误,下面记录过程及解决方法。

1 创建新的UNDO表空间UNDOTBS3,并将当前实例的UNDO切换到UNDOTBS3之后,删除旧的UNDOTBS1时,遇到下述错误:

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

2 从上可以看到UNDOTBS1目前正在被使用。查询MetaLink,Unable to Drop Undo Tablespace ORA-30013 [ID 835944.1]获取基本思路,依据该文档给出的提示,执行下述命令:

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs 
  2  where tablespace_name='UNDOTBS1' and status = 'ONLINE';

SEGMENT_NAME     OWNER  TABLESPACE_NAME    STATUS
---------------- ------ ------------------ ---------
_SYSSMU2$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU3$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU6$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU8$        PUBLIC UNDOTBS1           ONLINE
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.

3 从上步的结果,结合文档ID 835944.1,基本上可以找到问题的原因:当前数据库中有未结束的分布式事务,而这些未结束的分布式事务占用了UNDOTBS1,最终导致不能删除UNDOTBS1。 文档ID 835944.1给出的解决方案是结束掉这些分布式事务。MetaLink上给出的思路是参照 Note 401302.1文档,而不凑巧的是,现在这篇文档无法查看,不得已,尝试重启数据库并删除UNDOTBS1。 重启之后,alert日志里看到如下错误信息:

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-10 11:27:15.527
*** SESSION ID:(431.1) 2012-02-10 11:27:15.527
*** 2012-02-10 11:27:15.527
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:27:50.676
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:28:42.707
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found

尝试再次删除UNDOTBS1:

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

这次报出ORA-01548的错误!!!原来还是分布式事务未提交导致的:

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.
SQL>

4 继续MetaLink:ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]根据该文档,执行下述命令:

SQL> Select segment_id,segment_name,status,tablespace_name
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         2 _SYSSMU2$                      PARTLY AVAILABLE UNDOTBS1
         3 _SYSSMU3$                      PARTLY AVAILABLE UNDOTBS1
         6 _SYSSMU6$                      PARTLY AVAILABLE UNDOTBS1
         8 _SYSSMU8$                      PARTLY AVAILABLE UNDOTBS1
SQL>

You find you have segments that are 'Partly Available' This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. 当回滚段状态为Partly Available时,说明还是有事务没结束!!!

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn
  7  in(2,3,6,8);

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL>

通过上面的结果,看到2、3、6、8号回滚段上有活动的事务。该文档依然指出解决方案是查看Note 401302.1文档,而该文档又无法打开,不得已Google之,参照 http://blog.itpub.net/post/38439/477038  获得解决问题的最终方法。 

5 根据http://blog.itpub.net/post/38439/477038 直接强制提交这些活动的分布式事务:

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '2.38.583286';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '8.7.957413';

Commit complete.

SQL> commit force '3.29.982959';

Commit complete.

SQL> commit force '6.14.945326';

Commit complete.

SQL> commit force '13.19.507098';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             forced commit
3.29.982959            forced commit
6.14.945326            forced commit
13.19.507098           forced commit

6 rows selected.

SQL> Select segment_id,segment_name,status,tablespace_name           
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

no rows selected

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 
  2  KTUXESTA Status,                                          
  3  KTUXECFL Flags                                            
  4  FROM x$ktuxe                                              
  5  WHERE ktuxesta!='INACTIVE'                                
  6  AND ktuxeusn                                              
  7  in(2,3,6,8);                                              

no rows selected

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> 

6 终于干掉了那个旧的UNDO,而此时,alert日志里的信息如下:

Fri Feb 10 15:08:16 CST 2012
DISTRIB TRAN 44444444.D7D4863A714B974489CD48496956271900000000
  is local tran 2.38.583286 (hex=02.26.8e676)
  change pending prepared tran, scn=125560421 (hex=0.077be665)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:08:56 CST 2012
DISTRIB TRAN 44444444.D88A6D59B486E44D8BAD8DABFDCF289C00000000
  is local tran 8.7.957413 (hex=08.07.e9be5)
  change pending prepared tran, scn=194136242 (hex=0.0b9248b2)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:08 CST 2012
DISTRIB TRAN 44444444.4A67F0F3F3EA464081883577EE646AAB00000000
  is local tran 3.29.982959 (hex=03.1d.effaf)
  change pending prepared tran, scn=195270309 (hex=0.0ba396a5)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:20 CST 2012
DISTRIB TRAN 44444444.97CB87A6BAE9E943B761C9C7FDA9844600000000
  is local tran 6.14.945326 (hex=06.0e.e6cae)
  change pending prepared tran, scn=196753377 (hex=0.0bba37e1)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:36 CST 2012
DISTRIB TRAN 44444444.192AEFF6D316B2468F5D74FE5EBDC9EC00000000
  is local tran 13.19.507098 (hex=0d.13.7bcda)
  change pending prepared tran, scn=332059676 (hex=0.13cad41c)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:13:17 CST 2012
drop tablespace undotbs1 including contents and datafiles
Fri Feb 10 15:13:25 CST 2012
Deleted Oracle managed file +ORADATA/glndb/datafile/undotbs1.261.726057859
Completed: drop tablespace undotbs1 including contents and datafiles

7 总结:对于分布式事务,目前还不是很清楚。而这个案例中涉及到的情况还有可能出现更为复杂的情况,需要深入研究一下,而我遇到的这种情况属于比较简单的。

oracle 10g SYSAUX tablespace

        The SYSAUX(System Auxiliary) tablespace is a new feature of Oracle 10g database,before 10g release,there wasn't this tablespace.After you created your database via DBCA(Database Configuration Assistant) tool,the SYSAUX tablespace was created for you automatically.

       But what's the mainly function of that tablespace? Why Oracle present the SYSAUX tablespace? Does it bring some benefit for the database or the DBA?

       Yep,the answer is ture.The SYSAUX tablespace is an auxiliary tablespace of the SYSTEM tablespace.In the earlier release before 10g,there is only way to store the components and other metadata in the SYSTEM tablespace.But now,with the SYSAUX tablespace,Oracle separate some components from SYSTEM tablespace,bring 'sth' from the SYSTEM tablespace to SYSAUX tablespace.By that,the workload of SYSTEM tablespace becomes light,decreases the space fragment in the SYSTEM tablespace,and it becomes  easy to manage the SYSTEM tablespace for DBAs.

       In addition,if the status of SYSAUX tablespace becomes offline(in fact we can bring it offline explicitly) or invalid,the Oracle database can works normally still.

       Now,let's find out which components were stored in the SYSTEM tablespace logically.First,we should be familar with the V$SYSAUX_OCCUPANTS dictionary table. V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.

       So,we  need to do is only to issue a query against of the V$SYSAUX_OCCUPANTS view.

111

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes from v$sysaux_occupants;

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  6144
LOGSTDBY                       SYSTEM               SYS.DBMS_LOGSTDBY.SET_TABLESPACE                   896
STREAMS                        SYS                                                                     512
XDB                            XDB                  XDB.DBMS_XDB.MOVEXDB_TABLESPACE                  49216
AO                             SYS                  DBMS_AW.MOVE_AWMETA                                768
XSOQHIST                       SYS                  DBMS_XSOQ.OlapiMoveProc                            768
XSAMD                          OLAPSYS              DBMS_AMD.Move_OLAP_Catalog                           0
SM/AWR                         SYS                                                                  250496
SM/ADVISOR                     SYS                                                                  176384
SM/OPTSTAT                     SYS                                                                  289216
SM/OTHER                       SYS                                                                   23424

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
STATSPACK                      PERFSTAT                                                                  0
ODM                            DMSYS                MOVE_ODM                                             0
SDO                            MDSYS                MDSYS.MOVE_SDO                                   22400
WM                             WMSYS                DBMS_WM.move_proc                                 7296
ORDIM                          ORDSYS                                                                  512
ORDIM/PLUGINS                  ORDPLUGINS                                                                0
ORDIM/SQLMM                    SI_INFORMTN_SCHEMA                                                        0
EM                             SYSMAN               emd_maintenance.move_em_tblspc                  164800
TEXT                           CTXSYS               DRI_MOVE_CTXSYS                                      0
ULTRASEARCH                    WKSYS                MOVE_WK                                              0
ULTRASEARCH_DEMO_USER          WK_TEST              MOVE_WK                                              0

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
EXPRESSION_FILTER              EXFSYS                                                                 3712
EM_MONITORING_USER             DBSNMP                                                                 1856
TSM                            TSMSYS                                                                    0
JOB_SCHEDULER                  SYS                                                                    1024

26 rows selected.

SQL> 

 

222   For a test purpose,we can bring the LogMiner component to the EXAMPLES tablespace.

 SQL> exec dbms_logmnr_d.set_tablespace('example');

PL/SQL procedure successfully completed.

SQL>

and then we issue a  query against the V$SYSAUX_OCCUPANTS to obtain the effect:

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes

  2  from v$sysaux_occupants

  3  where occupant_name like 'LOGMNR%'

  4  ;

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES

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

LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                     0

SQL> 

333 ok,the SPACE_USAGE_KBYTES value of the LOGMNR   is 0,instead of  the original value of 6144.

and then,let's bring it back to the SYSAUX tablespace.

SQL> exec dbms_logmnr_d.set_tablespace('SYSAUX');

PL/SQL procedure successfully completed.

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes
  2  from v$sysaux_occupants
  3  where occupant_name like 'LOGMNR%'
  4  ;

OCCUPANT_NAME                  SCHEMA_NAME          MOVE_PROCEDURE                      SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
LOGMNR                         SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  6144

SQL> 
444 Furthermore,we can do some more experiences on the SYSAUX tablespce.
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace


SQL> 

555 we can't drop the SYSAUX tablespace.

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> 

666 we can  switch the status(online to offline or else.) of the SYSAUX tablespace manually.

SQL> alter tablespace sysaux read only;
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only


SQL> 

777 we can not alter the SYSAUX tablespace to read only status.

SQL> alter tablespace sysaux rename to new_sys;
alter tablespace sysaux rename to new_sys
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace


SQL> 

888  Also,we can not rename the SYSAUX tablespace.

 

———————————-The End—————————–

Managing Oracle Tablespaces and Data Files

A collection of 19 FAQs on creating and managing tablespaces and data files. Clear answers are provided with tutorial exercises on creating and dropping tablespaces; listing available tablespaces; creating and dropping data files; setting tablespaces and data files offline; removing corrupted data files. Topics included in this FAQ are:

  1. What Is an Oracle Tablespace?
  2. What Is an Oracle Data File?
  3. How a Tablespace Is Related to Data Files?
  4. How a Database Is Related to Tablespaces?
  5. How To View the Tablespaces in the Current Database?
  6. What Are the Predefined Tablespaces in a Database?
  7. How To View the Data Files in the Current Database?
  8. How To Create a new Oracle Data File?
  9. How To Create a New Tablespace?
  10. How To Rename a Tablespace?
  11. How To Drop a Tablespace?
  12. What Happens to the Data Files If a Tablespace Is Dropped?
  13. How To Create a Table in a Specific Tablespace?
  14. How To See Free Space of Each Tablespace?
  15. How To Bring a Tablespace Offline?
  16. How To Bring a Tablespace Online?
  17. How To Add Another Datafile to a Tablespace?
  18. What Happens If You Lost a Data File?
  19. How Remove Data Files befor opening a Database?

Sample scripts used in this FAQ assumes that you are connected to the server with the SYSTEM user account on the default database instance XE. See other FAQ collections on how to connect to the server.

What Is an Oracle Tablespace?

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.

What Is an Oracle Data File?

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

How a Tablespace Is Related to Data Files?

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

How a Database Is Related to Tablespaces?

A database’s data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

How To View the Tablespaces in the Current Database?

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

What Are the Predefined Tablespaces in a Database?

When you create a new database, Oracle server will create 4 required tablespaces for the new database:

  • SYSTEM Tablespace – Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.
  • SYSAUX Tablespace – The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
  • UNDO Tablespace – UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.
  • TEMP Tablespace – When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

How To View the Data Files in the Current Database?

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> col tablespace_name format a16;
SQL> col file_name format a36;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                           BYTES
--------------- ------------------------------- ---------
USERS           \ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            \ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840

How To Create a new Oracle Data File? There is no dedicated statement to create a data file. Data files are created as part of statements that manages other data structures, like tablespace and database. How To Create a New Tablespace? If you want a new dataspace, you can use the CREATE TABLESPACE … DATAFILE statement as shown in the following script:

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
MY_SPACE         ONLINE          PERMANENT

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                            BYTES
--------------- -------------------------------- ---------
USERS           \ORACLEXE\ORADATA\XE\USERS.DBF   104857600
SYSAUX          \ORACLEXE\ORADATA\XE\SYSAUX.DBF  461373440
UNDO            \ORACLEXE\ORADATA\XE\UNDO.DBF     94371840
SYSTEM          \ORACLEXE\ORADATA\XE\SYSTEM.DBF  356515840
MY_SPACE        \TEMP\MY_SPACE.DBF                10485760

So one statement created two structures: a tablespace and a data file. If you check your file system with Windows file explorer, you will see the data file is located in the \temp directory of. The data file size is about 10MB. Its contents should be blank and full of \x00 at this time.

How To Rename a Tablespace?

You can easily rename a tablespace by using the ALTER TABLESPACE … RENAME TO statement as shown in the example below:

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space RENAME TO your_space;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
YOUR_SPACE       ONLINE          PERMANENT

How To Drop a Tablespace?

If you have an existing tablespace and you don’t want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> DROP TABLESPACE my_space;
Tablespace dropped.

What Happens to the Data Files If a Tablespace Is Dropped?

If a tablespace is dropped, what happens to its data files? By default, data files will remain in OS file system, even if the tablespace they are mapped is dropped. Of course, you delete the data files using OS commands, if they are no longer needed.

Another way of deleting data files is to use the INCLUDING clause in the DROP TABLESPACE statement. Here is a SQL sample script:

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> DROP TABLESPACE my_space INCLUDING CONTENTS
  2  AND DATAFILES;
Tablespace dropped.

With the INCLUDING CONTENTS AND DATAFILES clause, all contents and mapped data files are also deleted.

How To Create a Table in a Specific Tablespace?

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space
  2  AS SELECT * FROM employees;
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM USER_TABLES
  3  WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME                     TABLESPACE_NAME    NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM                        MY_SPACE           -
EMPLOYEES                      USERS              107
...

How To See Free Space of Each Tablespace?

One of the important DBA tasks is to watch the storage usage of all the tablespaces to make sure there are enough free space in each tablespace for database applications to function properly. Free space information can be monitored through the USER_FREE_SPACE view. Each record in USER_FREE_SPACE represents an extent, a contiguous area of space, of free space in a data file of a tablespace.

Here is SQL script example on how to see free space of a tablespace:

SQL> connect HR/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
  2  FROM USER_FREE_SPACE
  3  WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                5   10354688
USERS                                   4  101974016
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536
USERS                                   4      65536

This tells us that:

  • MY_SPACE has a single free extent of 10MB.
  • USERS has one big free extent of 100MB, and many small free extents

of 64KB.

    How To Bring a Tablespace Offline?

    If you want to stop users using a tablespace, you can bring it offline

    using the ALTER TABLESPACE … OFFLINE statement as shown in the

    following script:

    SQL> connect HR/fyicenter
    Connected.
    
    SQL> CREATE TABLESPACE my_space
      2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
    Tablespace created.
    
    SQL> ALTER TABLESPACE my_space OFFLINE NORMAL;
    Tablespace altered.

    After bringing a tablespace offline, you can backup or rename the

    data file safely.

    How To Bring a Tablespace Online?

    If you have brought a tablespace offline, now you want to make it

    available to users again, you can use the ALTER TABLESPACE … ONLINE

    statement as shown in the following script:

    SQL> connect HR/fyicenter
    
    SQL> CREATE TABLESPACE my_space
      2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
    Tablespace created.
    
    SQL> ALTER TABLESPACE my_space OFFLINE NORMAL;
    Tablespace altered.
    
    ...
    
    SQL> ALTER TABLESPACE my_space ONLINE;
    Tablespace altered.

    How To Add Another Datafile to a Tablespace?

    If you created a tablespace with a data file a month ago, now 80% of

    the data file is used, you should add another data file to the

    tablespace. This can be done by using the ALTER TABLESPACE … ADD

    DATAFILE statement. See the following sample script:

    SQL> connect HR/fyicenter
    
    SQL> CREATE TABLESPACE my_space
      2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
    Tablespace created.
    
    SQL> ALTER TABLESPACE my_space
      2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
    Tablespace altered.
    
    SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
      2  FROM DBA_DATA_FILES;
    TABLESPACE_NAME FILE_NAME                             BYTES
    --------------- --------------------------------- ---------
    USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
    SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
    UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
    SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
    MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
    MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880
    
    SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
      2  FROM USER_FREE_SPACE
      3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
    TABLESPACE_NAME                   FILE_ID      BYTES
    ------------------------------ ---------- ----------
    MY_SPACE                                6    5177344
    MY_SPACE                                5   10354688

    This script created one tablespace with two data files.

    What Happens If You Lost a Data File?

    After you shuting down an Oracle database, you accidently deleted a

    data file from the operating system. If you try to start the database

    again you will get error when Oracle tries to open the database after

    mounting the database. The following tutorial examples shows you what

    will happen if the data file c:\temp\my_space.dbf is deleted. Oracle

    can still start the database instance and mount the database. But it

    will fail on openning the database as shown below in a SQL*Plus

    session:

    >sqlplus /nolog
    
    SQL> connect SYSTEM/fyicenter AS SYSDBA
    
    SQL> STARTUP
    ORACLE instance started.
    
    Total System Global Area  100663296 bytes
    Fixed Size                  1285956 bytes
    Variable Size              58720444 bytes
    Database Buffers           37748736 bytes
    Redo Buffers                2908160 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 5 - see DBWR
      trace file
    ORA-01110: data file 5: 'C:\TEMP\MY_SPACE.DBF'
    
    SQL> SHUTDOWN;
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.

    How Remove Data Files befor opening a Database?

    Let’s say you have a corrupted data file or lost a data file. Oracle

    can mount the database. But it will not open the database. What you

    can do is to set the bad data file as offline befor opening the

    database. The tutorial exercise shows you how to set two data files

    offline and open the database without them:

    >sqlplus /nolog
    
    SQL> connect SYSTEM/fyicenter AS SYSDBA
    
    SQL> STARTUP MOUNT;
    ORACLE instance started.
    
    Total System Global Area  100663296 bytes
    Fixed Size                  1285956 bytes
    Variable Size              58720444 bytes
    Database Buffers           37748736 bytes
    Redo Buffers                2908160 bytes
    Database mounted.
    
    SQL> ALTER DATABASE DATAFILE '\temp\my_space.dbf'
      2  OFFLINE DROP;
    Database altered.
    
    SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf'
      2  OFFLINE DROP;
    Database altered.
    
    SQL> ALTER DATABASE OPEN;
    Database altered.
    
    SQL> col file_name format a36;
    SQL> col tablespace_name format a16;
    SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
      2  FROM DBA_DATA_FILES;
    
    TABLESPACE_NAME FILE_NAME                             BYTES
    --------------- --------------------------------- ---------
    USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
    SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 503316480
    UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
    SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 367001600
    MY_SPACE        C:\TEMP\MY_SPACE.DBF
    MY_SPACE        C:\TEMP\MY_SPACE_2.DBF

    At this point, if you don’t care about the data in MY_SPACE, you can

    drop it now with the database opened.