UNDO表空间

oracle226

---

---

UNDO表空间

查看PPT

SQL>showparameterundo;

NAMETYPEVALUE

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

undo_managementstringAUTO--自动管理

undo_retentioninteger900--inactive的undo块数据保持时间，设置为最长的select时间

undo_tablespacestring UNDOTBS1--默认的undo表空间

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

------------------------------ ---------- --------- -----------查询表空间

UNDOTBS18192UNDOGUARANTEE

--下面是查询某个表空间对应的数据文件

SQL>selectddf.file_name,ddf.tablespace_name,ddf.blocks,ddf.online_statusfromdba_data_files ddfwhereddf.tablespace_name='UNDOTBS1';

FILE_NAME TABLESPACE_NAME BLOCKS ONLINE_STATUS

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

/u01/app/oracle/oradata/jiagulun/undotbs01.dbf UNDOTBS178720ONLINE

SQL>

SQL>createundotablespaceundotbs2datafile'/u01/app/oracle/oradata/jiagulun/undotbs02.dbf'size10m

2autoextendon;--创建undo表空间

Tablespacecreated

SQL>selectddf.file_name,ddf.tablespace_name,ddf.blocks,ddf.online_statusfromdba_data_files ddfwhereddf.tablespace_name='UNDOTBS2';

FILE_NAME TABLESPACE_NAME BLOCKS ONLINE_STATUS

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

/u01/app/oracle/oradata/jiagulun/undotbs02.dbf UNDOTBS21280ONLINE

SQL>altertablespaceundotbs2adddatafile'/u01/app/oracle/oradata/jiagulun/undotbs03.dbf'size10mautoextendon;

Tablespacealtered--为某个undo表空间添加数据文件

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

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

UNDOTBS18192UNDOGUARANTEE

UNDOTBS28192UNDONOGUARANTEE

SQL>

SQL>altersystemundo_tablespace='UNDOTBS2';

altersystemundo_tablespace='UNDOTBS2'

ORA-02065:非法的ALTERSYSTEM选项

SQL>altersystemSETundo_tablespace='UNDOTBS2';

Systemaltered--修改系统使用的表空间

SQL>showparameter undo_tablespace;

NAMETYPEVALUE

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

undo_tablespacestring UNDOTBS2

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

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

UNDOTBS18192UNDOGUARANTEE

UNDOTBS28192UNDONOGUARANTEE

SQL>altertablespaceundotbs1retentionguarantee;

;

Tablespacealtered

SQL>

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

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

UNDOTBS18192UNDOGUARANTEE

UNDOTBS28192UNDONOGUARANTEE

SQL>altertablespaceundotbs2retentionguarantee;

Tablespacealtered

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

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

UNDOTBS18192UNDOGUARANTEE

UNDOTBS28192UNDOGUARANTEE

SQL>

SQL>altertablespaceundotbs2retentionnoguarantee;

Tablespacealtered

SQL>selectdt.tablespace_name,dt.block_size,dt.contents,dt.retentionfromdba_tablespaces dtwheredt.contents='UNDO';

TABLESPACE_NAME BLOCK_SIZECONTENTSRETENTION

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

UNDOTBS18192UNDOGUARANTEE

UNDOTBS28192UNDONOGUARANTEE

SQL>selectto_char(us.BEGIN_TIME,'yyyy-mm-dd :hh24:mi:ss'),

to_char(us.END_TIME,'yyyy-mm-dd hh24:mi:ss'),

us.UNDOBLKS,us.TXNCOUNTfromv$undostat us;

--查询undo状态信息：undoblks表示在这段时间内使用块大小

--txncount：表示这段时间的事务多少

TO_CHAR(US.BEGIN_TIME,'YYYY-MM TO_CHAR(US.END_TIME,'YYYY-MM-D UNDOBLKS TXNCOUNT

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

TO_CHAR(US.BEGIN_TIME,'YYYY-MM TO_CHAR(US.END_TIME,'YYYY-MM-D UNDOBLKS TXNCOUNT

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

2015-01-20:16:49:542015-01-2016:59:54308185

2015-01-20:16:39:542015-01-2016:49:54193139

2015-01-20:16:29:542015-01-2016:39:54201120

2015-01-20:16:19:542015-01-2016:29:54279614

2015-01-20:16:09:542015-01-2016:19:54246179

25rowsselected

SQL>