(10) Managing Undo Data

• show parameter undo;

• 

  

• 

  SQL> alter tablespace xxx offline (immediate,temporary or for recovery)

  SQL>recover datafile 'xxx'

  SQL> show parameter undo
• NAME                                 TYPE                   VALUE
  ------------------------------------ ---------------------- ---------
  undo_management                      string                 AUTO(manual)
  undo_retention                       integer                10800
  undo_suppress_errors                 boolean                FALSE
  undo_tablespace                      string                 UNDOTBS1

  SQL> select * from dba_rollback_segs;

  SQL> drop tablespace xxx  (先切换，并且老的里面transaction已经都提交了）

  SQL> create rollback segment rbs1 tablespace undotbs; （手工创建回滚段）

  SQL> execute dbms_flashback.enable_at_time()

  SQL> execute dbms_flashback.disable

  Undo Data Statistics

  SQL> select end_time,begin_time,undoblks from v$undostat;

   

  UndoSpace = [UR(s) * (UPS * DBS)] + （DBS*24)

  UR: UNDO_RETENTION

  UPS: Number of undo data blocks generated per second

  DBS: db_block_size

   

  UNDO Quota for users

  UNDO_POOL

   

  DBA_ROLLBACK_SEGS

  v$ROLLNAME  v$ROLLSTAT  v$UNDOSTAT  v$SESSION  v$TRANSACTION

   

  手工管理回滚段

  SQL> show parameter transactions

• NAME                                 TYPE                   VALUE
  ------------------------------------ ---------------------- ------
  transactions                         integer                187
  transactions_per_rollback_segment    integer                5

  SQL> show parameter rollback

• NAME                                 TYPE                   VALUE
  ------------------------------------ ---------------------- ------
  fast_start_parallel_rollback         string                 LOW
  max_rollback_segments                integer                37
  rollback_segments                    string                 ('rbs1','rbs2',...)
  transactions_per_rollback_segment    integer                5

  SQL> create (public) rollback segment prbs1 tablespace undotbs;

  SQL> alter rollback segment rbs1 online;