1 Oracle使用还原(回滚段)目的:
1)事务回滚
2)事务恢复
3)保证数据的读一致性
2 查还原段是否自启动,所用表空间,保留的时间(秒)
SQL> l
1 select name,value from v$parameter
2* where name like '%undo%'
SQL> /
NAME VALUE
------------------------------ ------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
3 查所有的还原表空间
SQL> select tablespace_name,status,contents
2 from dba_tablespaces
3 where contents='UNDO';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
UNDOTBS1 ONLINE UNDO
4 创建还原表空间
先查询数据文件所在位置
SQL> select name from v$datafile;
NAME
------------------------------
G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\JXFGW\SYSTEM01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\JXFGW\UNDOTBS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\JXFGW\SYSAUX01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\JXFGW\USERS01.DBF
再创建还原表空间
SQL> create undo tablespace jinlian_undo
2 datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\jinlian_undo.dbf'
3 size 20M;
表空间已创建。
5 查询是否创建好
SQL> select tablespace_name,status,contents
2 from dba_tablespaces
3 where contents='UNDO';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
UNDOTBS1 ONLINE UNDO
JINLIAN_UNDO ONLINE UNDO
6 为还原表空间增加一个数据文件
SQL> alter tablespace jinlian_undo
2 add datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\jinlian2_undo.dbf'
3 size 10M;
表空间已更改。
7 查还原表空间所对应的数据文件
SQL> l
1 select file_id,file_name,tablespace_name,bytes/1024/1024 MB
2 from dba_data_files
3* where tablespace_name like 'JIN%'
SQL> /
FILE_ID FILE_NAME
---------- -------------------------------------------------------
TABLESPACE_NAME MB
------------------------------ ----------
8 G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN_UNDO.DBF
JINLIAN_UNDO 20
9 G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN2_UNDO.DB
F
JINLIAN_UNDO 10
8 查还原表空间的数据文件是否可以自动扩展
SQL> l
1 select file_id,file_name,tablespace_name,autoextensible
2 from dba_data_files
3* where tablespace_name like 'JIN%'
SQL> /
FILE_ID FILE_NAME
---------- -------------------------------------------------------
TABLESPACE_NAME AUT
------------------------------ ---
8 G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN_UNDO.DBF
JINLIAN_UNDO NO
9 G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN2_UNDO.DB
F
JINLIAN_UNDO NO
9 把数据文件改成自动扩展
SQL> alter database
2 datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN2_UNDO.DBF'
3 autoextend on;
数据库已更改。
10 查询是否自动扩展
SQL> select file_id,tablespace_name,file_name,autoextensible
2 from dba_data_files
3 where tablespace_name like 'JIN%';
FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME AUT
------------------------------------------------------- ---
8 JINLIAN_UNDO
G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN_UNDO.DBF NO
9 JINLIAN_UNDO
G:\ORACLE\PRODUCT\10.2.0\ORADATA\JXFGW\JINLIAN2_UNDO.DB YES
F
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-255248/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-255248/