解决方案:UNDO自动管理和手工管理、闪回操作、表空间的空间管理算法
优采云 发布时间: 2022-11-24 04:37解决方案:UNDO自动管理和手工管理、闪回操作、表空间的空间管理算法
撤消表空间 --“ 使用回滚段在数据修改前保存旧映像
如何管理回滚段：
SQL>
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
11 rows selected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> alter session set container=PDB1;
Session altered.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/pdb1/undotbs02.dbf' size 10m;
Tablespace created.
SQL>
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_3588498444$ UNDOTBS1 ONLINE
_SYSSMU2_2971032042$ UNDOTBS1 ONLINE
_SYSSMU3_3657342154$ UNDOTBS1 ONLINE
_SYSSMU4_811969446$ UNDOTBS1 ONLINE
_SYSSMU5_3018429039$ UNDOTBS1 ONLINE
_SYSSMU6_442110264$ UNDOTBS1 ONLINE
_SYSSMU7_2728255665$ UNDOTBS1 ONLINE
_SYSSMU8_801938064$ UNDOTBS1 ONLINE
_SYSSMU9_647420285$ UNDOTBS1 ONLINE
_SYSSMU10_2262159254$ UNDOTBS1 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2950309372$ UNDOTBS2 OFFLINE
_SYSSMU12_3550765581$ UNDOTBS2 OFFLINE
_SYSSMU13_2003268147$ UNDOTBS2 OFFLINE
_SYSSMU14_200385032$ UNDOTBS2 OFFLINE
_SYSSMU15_2315478656$ UNDOTBS2 OFFLINE
_SYSSMU16_3694721358$ UNDOTBS2 OFFLINE
_SYSSMU17_302315325$ UNDOTBS2 OFFLINE
_SYSSMU18_2589589835$ UNDOTBS2 OFFLINE
_SYSSMU19_2290367767$ UNDOTBS2 OFFLINE
_SYSSMU20_459963109$ UNDOTBS2 OFFLINE
21 rows selected.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
12 rows selected.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
/u02/oradata/CDB1/undotbs02.dbf
13 rows selected.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_1261223759$ UNDOTBS1 ONLINE
_SYSSMU2_27624015$ UNDOTBS1 ONLINE
_SYSSMU3_2421748942$ UNDOTBS1 ONLINE
_SYSSMU4_625702278$ UNDOTBS1 ONLINE
_SYSSMU5_2101348960$ UNDOTBS1 ONLINE
_SYSSMU6_813816332$ UNDOTBS1 ONLINE
_SYSSMU7_2329891355$ UNDOTBS1 ONLINE
_SYSSMU8_399776867$ UNDOTBS1 ONLINE
_SYSSMU9_1692468413$ UNDOTBS1 ONLINE
_SYSSMU10_930580995$ UNDOTBS1 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$ UNDOTBS2 OFFLINE
_SYSSMU12_3852438105$ UNDOTBS2 OFFLINE
_SYSSMU13_4053449264$ UNDOTBS2 OFFLINE
_SYSSMU14_3568283922$ UNDOTBS2 OFFLINE
_SYSSMU15_4273800205$ UNDOTBS2 OFFLINE
_SYSSMU16_3358818169$ UNDOTBS2 OFFLINE
_SYSSMU17_1656102559$ UNDOTBS2 OFFLINE
_SYSSMU18_3046128593$ UNDOTBS2 OFFLINE
_SYSSMU19_2820437463$ UNDOTBS2 OFFLINE
_SYSSMU20_1569246834$ UNDOTBS2 OFFLINE
21 rows selected.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
12 rows selected.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
/u02/oradata/CDB1/undotbs02.dbf
13 rows selected.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_1261223759$ UNDOTBS1 ONLINE
_SYSSMU2_27624015$ UNDOTBS1 ONLINE
_SYSSMU3_2421748942$ UNDOTBS1 ONLINE
_SYSSMU4_625702278$ UNDOTBS1 ONLINE
_SYSSMU5_2101348960$ UNDOTBS1 ONLINE
_SYSSMU6_813816332$ UNDOTBS1 ONLINE
_SYSSMU7_2329891355$ UNDOTBS1 ONLINE
_SYSSMU8_399776867$ UNDOTBS1 ONLINE
_SYSSMU9_1692468413$ UNDOTBS1 ONLINE
_SYSSMU10_930580995$ UNDOTBS1 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$ UNDOTBS2 OFFLINE
_SYSSMU12_3852438105$ UNDOTBS2 OFFLINE
_SYSSMU13_4053449264$ UNDOTBS2 OFFLINE
_SYSSMU14_3568283922$ UNDOTBS2 OFFLINE
_SYSSMU15_4273800205$ UNDOTBS2 OFFLINE
_SYSSMU16_3358818169$ UNDOTBS2 OFFLINE
_SYSSMU17_1656102559$ UNDOTBS2 OFFLINE
_SYSSMU18_3046128593$ UNDOTBS2 OFFLINE
_SYSSMU19_2820437463$ UNDOTBS2 OFFLINE
_SYSSMU20_1569246834$ UNDOTBS2 OFFLINE
21 rows selected.
SQL> show user
USER is "SYS"
SQL> col username for a10
SQL> select s.USERNAME,t.XIDUSN,t.USED_UBLK from v$session s, v$transaction t where t.SES_ADDR=s.SADDR;
USERNAME XIDUSN USED_UBLK
---------- ---------- ----------
SCOTT 8 20
SCOTT 2 1
SQL>
SQL> select name from v$rollname where usn=8;
NAME
------------------------------
_SYSSMU8_399776867$
SQL> select name from v$rollname where usn=2;
NAME
------------------------------
_SYSSMU2_27624015$
SQL>
撤消手动管理
SQL>
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL>
SQL> alter system set undo_management=manual scope=spfile
2 ;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 629145392 bytes
Fixed Size 9137968 bytes
Variable Size 184549376 bytes
Database Buffers 427819008 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL>
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_1261223759$ UNDOTBS1 OFFLINE
_SYSSMU2_27624015$ UNDOTBS1 OFFLINE
_SYSSMU3_2421748942$ UNDOTBS1 OFFLINE
_SYSSMU4_625702278$ UNDOTBS1 OFFLINE
_SYSSMU5_2101348960$ UNDOTBS1 OFFLINE
_SYSSMU6_813816332$ UNDOTBS1 OFFLINE
_SYSSMU7_2329891355$ UNDOTBS1 OFFLINE
_SYSSMU8_399776867$ UNDOTBS1 OFFLINE
_SYSSMU9_1692468413$ UNDOTBS1 OFFLINE
_SYSSMU10_930580995$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$ UNDOTBS2 OFFLINE
_SYSSMU12_3852438105$ UNDOTBS2 OFFLINE
_SYSSMU13_4053449264$ UNDOTBS2 OFFLINE
_SYSSMU14_3568283922$ UNDOTBS2 OFFLINE
_SYSSMU15_4273800205$ UNDOTBS2 OFFLINE
_SYSSMU16_3358818169$ UNDOTBS2 OFFLINE
_SYSSMU17_1656102559$ UNDOTBS2 OFFLINE
_SYSSMU18_3046128593$ UNDOTBS2 OFFLINE
_SYSSMU19_2820437463$ UNDOTBS2 OFFLINE
_SYSSMU20_1569246834$ UNDOTBS2 OFFLINE
21 rows selected.
SQL>
切换到手动管理 UNDO 表空间以执行更新语句时，会遇到以下错误：
[oracle@oracle-db-19c ~]$
[oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 11:04:26 2022
<p>
" />
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Nov 23 2022 10:46:24 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "SCOTT"
SQL>
SQL> set pagesize 200
SQL> set linesize 200
SQL>
SQL> update e01 set sal=sal+1;
update e01 set sal=sal+1
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
SQL> </p>
将表 e01 移动到系统表空间后，表更新可用。
SQL>
SQL> alter table e01 move tablespace system;
Table altered.
SQL>
SQL> update e01 set sal=sal+1;
14 rows updated.
SQL> roll
Rollback complete.
SQL>
SQL>
尝试在 Scott 用户下进行表构建实验：
SQL>
SQL>
SQL> select * from tab
2 ;
TNAME TABTYPE CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
T02 TABLE
T01 TABLE
T03 TABLE
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DETAIL_DEPT TABLE
VU10 VIEW
VUTEST VIEW
E01 TABLE
TEMP TABLE
TEMP02 TABLE
OB1 TABLE
14 rows selected.
SQL>
SQL>
SQL>
SQL> create table e02 as select * from emp;
create table e02 as select * from emp
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
SQL> desc e02;
ERROR:
ORA-04043: object e02 does not exist
SQL> create table e02 as select * from emp where 1=0;
Table created.
SQL> desc e02;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
结论：可以创建一个空表，但不能创建收录
数据的表。表之所以能成功，是因为deferred_segment_creation是开放的。（延迟段创建）。
SQL>显示参数段
名称类型值
------------------------------------ ----------- ------------------------------
deferred_segment_creation布尔值为 TRUE
rollback_segments字符串
transactions_per_rollback_segment整数 5
SQL>
让我们尝试关闭此延迟分段创建，以查看 SCOTT 用户是否可以继续创建表？如下图所示：
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 15:55:34 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=PDB1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> show parameter segment
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set deferred_segment_creation=FALSE;
System altered.
SQL> show parameter segment
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
rollback_segments string
transactions_per_rollback_segment integer 5
SQL>
结论是，您可以简单地创建一个没有数据的表，但不能创建一个带有数字的表。
SQL> create table e02 as select * from emp;
create table e02 as select * from emp
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
SQL> create table e03 as select * from emp where 1=0;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
T02 TABLE
T01 TABLE
T03 TABLE
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DETAIL_DEPT TABLE
VU10 VIEW
VUTEST VIEW
E01 TABLE
TEMP TABLE
TEMP02 TABLE
E03 TABLE
OB1 TABLE
15 rows selected.
SQL>
手动管理回滚段：
创建回滚段 RBS1 表空间 UNDOTBS1;
创建回滚段 RBS2 表空间 UNDOTBS1;
创建回滚段 RBS3 表空间 UNDOTBS1;
更改系统集 rollback_segments='RBS1'，'RBS2'， 'RBS3' 范围=spfile;
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_3588498444$ UNDOTBS1 OFFLINE
_SYSSMU2_2971032042$ UNDOTBS1 OFFLINE
_SYSSMU3_3657342154$ UNDOTBS1 OFFLINE
_SYSSMU4_811969446$ UNDOTBS1 OFFLINE
_SYSSMU5_3018429039$ UNDOTBS1 OFFLINE
_SYSSMU6_442110264$ UNDOTBS1 OFFLINE
_SYSSMU7_2728255665$ UNDOTBS1 OFFLINE
_SYSSMU8_801938064$ UNDOTBS1 OFFLINE
_SYSSMU9_647420285$ UNDOTBS1 OFFLINE
_SYSSMU10_2262159254$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2950309372$ UNDOTBS2 OFFLINE
_SYSSMU12_3550765581$ UNDOTBS2 OFFLINE
_SYSSMU13_2003268147$ UNDOTBS2 OFFLINE
_SYSSMU14_200385032$ UNDOTBS2 OFFLINE
_SYSSMU15_2315478656$ UNDOTBS2 OFFLINE
_SYSSMU16_3694721358$ UNDOTBS2 OFFLINE
_SYSSMU17_302315325$ UNDOTBS2 OFFLINE
_SYSSMU18_2589589835$ UNDOTBS2 OFFLINE
_SYSSMU19_2290367767$ UNDOTBS2 OFFLINE
_SYSSMU20_459963109$ UNDOTBS2 OFFLINE
21 rows selected.
SQL> create rollback segment rbs1 tablespace UNDOTBS1;
Rollback segment created.
SQL>
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_3588498444$ UNDOTBS1 OFFLINE
_SYSSMU2_2971032042$ UNDOTBS1 OFFLINE
_SYSSMU3_3657342154$ UNDOTBS1 OFFLINE
_SYSSMU4_811969446$ UNDOTBS1 OFFLINE
_SYSSMU5_3018429039$ UNDOTBS1 OFFLINE
_SYSSMU6_442110264$ UNDOTBS1 OFFLINE
_SYSSMU7_2728255665$ UNDOTBS1 OFFLINE
_SYSSMU8_801938064$ UNDOTBS1 OFFLINE
_SYSSMU9_647420285$ UNDOTBS1 OFFLINE
_SYSSMU10_2262159254$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
RBS1 UNDOTBS1 OFFLINE
_SYSSMU11_2950309372$ UNDOTBS2 OFFLINE
_SYSSMU12_3550765581$ UNDOTBS2 OFFLINE
_SYSSMU13_2003268147$ UNDOTBS2 OFFLINE
_SYSSMU14_200385032$ UNDOTBS2 OFFLINE
_SYSSMU15_2315478656$ UNDOTBS2 OFFLINE
_SYSSMU16_3694721358$ UNDOTBS2 OFFLINE
_SYSSMU17_302315325$ UNDOTBS2 OFFLINE
_SYSSMU18_2589589835$ UNDOTBS2 OFFLINE
_SYSSMU19_2290367767$ UNDOTBS2 OFFLINE
_SYSSMU20_459963109$ UNDOTBS2 OFFLINE
22 rows selected.
SQL>
SQL> show parameter rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL>
SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_3588498444$ UNDOTBS1 OFFLINE
_SYSSMU2_2971032042$ UNDOTBS1 OFFLINE
_SYSSMU3_3657342154$ UNDOTBS1 OFFLINE
_SYSSMU4_811969446$ UNDOTBS1 OFFLINE
_SYSSMU5_3018429039$ UNDOTBS1 OFFLINE
_SYSSMU6_442110264$ UNDOTBS1 OFFLINE
_SYSSMU7_2728255665$ UNDOTBS1 OFFLINE
_SYSSMU8_801938064$ UNDOTBS1 OFFLINE
_SYSSMU9_647420285$ UNDOTBS1 OFFLINE
_SYSSMU10_2262159254$ UNDOTBS1 OFFLINE
RBS1 UNDOTBS1 OFFLINE
_SYSSMU11_2950309372$ UNDOTBS2 OFFLINE
_SYSSMU12_3550765581$ UNDOTBS2 OFFLINE
_SYSSMU13_2003268147$ UNDOTBS2 OFFLINE
_SYSSMU14_200385032$ UNDOTBS2 OFFLINE
_SYSSMU15_2315478656$ UNDOTBS2 OFFLINE
_SYSSMU16_3694721358$ UNDOTBS2 OFFLINE
_SYSSMU17_302315325$ UNDOTBS2 OFFLINE
_SYSSMU18_2589589835$ UNDOTBS2 OFFLINE
_SYSSMU19_2290367767$ UNDOTBS2 OFFLINE
_SYSSMU20_459963109$ UNDOTBS2 OFFLINE
22 rows selected.
SQL>
SQL>
SQL> alter system set rollback_segments='RBS1' scope=spfile;
System altered.
SQL> startup force
Pluggable Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_3588498444$ UNDOTBS1 OFFLINE
_SYSSMU2_2971032042$ UNDOTBS1 OFFLINE
_SYSSMU3_3657342154$ UNDOTBS1 OFFLINE
_SYSSMU4_811969446$ UNDOTBS1 OFFLINE
_SYSSMU5_3018429039$ UNDOTBS1 OFFLINE
_SYSSMU6_442110264$ UNDOTBS1 OFFLINE
_SYSSMU7_2728255665$ UNDOTBS1 OFFLINE
_SYSSMU8_801938064$ UNDOTBS1 OFFLINE
_SYSSMU9_647420285$ UNDOTBS1 OFFLINE
_SYSSMU10_2262159254$ UNDOTBS1 OFFLINE
RBS1 UNDOTBS1 ONLINE
_SYSSMU11_2950309372$ UNDOTBS2 OFFLINE
_SYSSMU12_3550765581$ UNDOTBS2 OFFLINE
_SYSSMU13_2003268147$ UNDOTBS2 OFFLINE
_SYSSMU14_200385032$ UNDOTBS2 OFFLINE
_SYSSMU15_2315478656$ UNDOTBS2 OFFLINE
_SYSSMU16_3694721358$ UNDOTBS2 OFFLINE
_SYSSMU17_302315325$ UNDOTBS2 OFFLINE
_SYSSMU18_2589589835$ UNDOTBS2 OFFLINE
_SYSSMU19_2290367767$ UNDOTBS2 OFFLINE
_SYSSMU20_459963109$ UNDOTBS2 OFFLINE
22 rows selected.
SQL>
为 DML 操作提供闪回处理
闪回查询：
SQL> select * from e01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
<p>
" />
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> update e01 set sal = 1;
14 rows updated.
SQL> commit;
Commit complete.
SQL> rollback;
Rollback complete.
SQL> select * from e01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1 500 30
7566 JONES MANAGER 7839 02-APR-81 1 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 1 30
7782 CLARK MANAGER 7839 09-JUN-81 1 10
7788 SCOTT ANALYST 7566 24-JAN-87 1 20
7839 KING PRESIDENT 17-NOV-81 1 10
7844 TURNER SALESMAN 7698 08-SEP-81 1 0 30
7876 ADAMS CLERK 7788 02-APR-87 1 20
7900 JAMES CLERK 7698 03-DEC-81 1 30
7902 FORD ANALYST 7566 03-DEC-81 1 20
7934 MILLER CLERK 7782 23-JAN-82 1 10
14 rows selected.
SQL> select * from e01 as of timestamp(sysdate-5/1440);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> alter table e01 enable row movement;
Table altered.
SQL> flashback table e01 to timestamp(sysdate-5/1440);
Flashback complete.
SQL> select * from e01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL></p>
您可以闪回 15 分 900 秒。
SQL> select * from e01 as of timestamp(sysdate-15/1440);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
闪回版本查询
SQL> update e01 set sal=sal+100 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> update e01 set sal=sal*1.1 where deptno=30;
6 rows updated.
SQL> commit;
Commit complete.
SQL> update e01 set sal=3;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select * from e01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 3 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3 300 30
7521 WARD SALESMAN 7698 22-FEB-81 3 500 30
7566 JONES MANAGER 7839 02-APR-81 3 20
7654 MARTIN SALESMAN 7698 28-SEP-81 3 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3 30
7782 CLARK MANAGER 7839 09-JUN-81 3 10
7788 SCOTT ANALYST 7566 24-JAN-87 3 20
7839 KING PRESIDENT 17-NOV-81 3 10
7844 TURNER SALESMAN 7698 08-SEP-81 3 0 30
7876 ADAMS CLERK 7788 02-APR-87 3 20
7900 JAMES CLERK 7698 03-DEC-81 3 30
7902 FORD ANALYST 7566 03-DEC-81 3 20
7934 MILLER CLERK 7782 23-JAN-82 3 10
14 rows selected.
SQL> --闪回版本查询
SQL> select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369;
select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> select
2 versions_startscn,
3 versions_endscn,
4 versions_operation,
5 versions_xid,
6 sal
7 from e01
8 versions between scn minvalue and maxvalue
9 where empno=7369;
VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID SAL
----------------- --------------- - ---------------- ----------
17826843 U 03000300BC030000 3
17826652 17826843 I 070010006E030000 800
17826652 D 070010006E030000 1
17825320 17826652 U 03002000BB030000 1
17825320 800
SQL>
SQL> col versions_starttime for a25;
SQL> col versions_endtime for a25;
SQL> select
2 versions_starttime
3 versions_endtime,
4 --versions_startscn,
5 --versions_endscn,
6 versions_operation,
7 versions_xid,
8 sal
9 from e01
10 versions between scn minvalue and maxvalue
11 where empno=7369;
VERSIONS_ENDTIME V VERSIONS_XID SAL
------------------------- - ---------------- ----------
23-NOV-22 09.55.07 PM U 03000300BC030000 3
23-NOV-22 09.50.16 PM I 070010006E030000 800
23-NOV-22 09.50.16 PM D 070010006E030000 1
1
SQL> --闪回版本查询
SQL> select * from e01 as of scn 17826652;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> flashback table e01 to scn 17826652;
Flashback complete.
SQL> select * from e01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
注意：为了进行闪回表操作，您必须首先打开行移动，如果不打开行移动，您将无法执行闪回操作。
SQL> alter table e01 enable row movement;
Table altered.
启用追加日志数据模式：
SQL>
SQL> alter database add supplemental log data;
Database altered.
SQL>
SQL>
SQL> update e01 set sal=sal*1.15 where deptno=30;
6 rows updated.
SQL>
SQL> select
2 --versions_starttime
3 --versions_endtime,
4 versions_startscn,
5 versions_endscn,
6 versions_operation,
7 versions_xid,
8 sal
9 from e01
10 versions between scn minvalue and maxvalue
11 where empno=7844;
VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID SAL
----------------- --------------- - ---------------- ----------
17846142 I 02000B0061030000 1500
17846142 D 02000B0061030000 3
17846142 3
SQL>
闪回事务查询
select
--versions_starttime
--versions_endtime,
versions_startscn,
versions_endscn,
versions_operation,
versions_xid,
sal
from e01
versions between scn minvalue and maxvalue
where empno=7844;
---闪回事务查询，sys用户才可以查询
SQL> select undo_sql from flashback_transaction_query where xid='02000B0061030000';
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16 rows selected.
SQL>
事实:大数据采集手段_大数据采集和处理的内容
大数据采集方式_大数据采集处理内容 大数据开启了数据*敏*感*词*生产、共享和应用的时代，给技术和商业带来了巨大的变革。麦肯锡研究表明，在医疗、零售和制造领域，大数据每年可以提高劳动生产率0.5-1个百分点。大数据在核心领域的渗透速度是有目共睹的。但调查显示，未被利用的信息比例高达99.4%，这很大程度上是由于无法获取和采集
高价值信息。因此，在大数据时代的背景下，如何从大数据中采集
有用的信息已经是大数据发展的关键因素之一，那么什么是大数据...
大家好，我是建筑先生，一个会写代码会吟诗的架构师。今天就来说说大数据采集的手段_大数据采集处理的内容，希望能帮助大家进步！！！
大数据开启了数据*敏*感*词*生产、共享和应用的时代，给技术和商业带来了巨大变革。麦肯锡研究表明，在医疗、零售和制造领域，大数据每年可以提高劳动生产率0.5-1个百分点。大数据在核心领域的渗透速度是有目共睹的。但调查显示，未被利用的信息比例高达99.4%，这很大程度上是由于无法获取和采集
高价值信息。因此，在大数据时代的背景下，如何从大数据中采集
有用的信息已经是大数据发展的关键因素之一，那么什么是大数据采集
技术呢？本期为大家介绍大数据采集技术，
▌什么是数据采集
？
数据采集（Data Acquisition，DAQ），又称数据采集，是指从传感器等被测器件等模拟和数字被测单元自动采集信息的过程。数据分类 在新一代数据体系中，对传统数据体系中没有考虑到的新数据源进行归纳和分类，可分为上网行为数据和内容数据两大类。
上网行为数据：页面数据、交互数据、表单数据、会话数据等。
内容数据：应用日志、电子文档、机器数据、语音数据、社交媒体数据等。
大数据的主要来源：1）商业数据2）互联网数据3）传感器数据
▌数据采集与大数据采集的区别
" />
▌传统数据采集的不足
传统的数据采集来源单一，存储、管理和分析的数据量较小，大部分可以通过关系数据库和并行数据仓库进行处理。在依靠并行计算提高数据处理速度方面，传统的并行数据库技术追求高一致性和容错性。按照CAP理论，其可用性和可扩展性是很难保证的。
▌大数据采集新方法
系统日志采集
方法
很多互联网公司都有自己的海量数据采集工具，多用于系统日志采集，比如Hadoop的Chukwa、Cloudera的Flume、Facebook的Scribe等，这些工具采用分布式架构，可以满足单机上百MB的日志数据第二。采集和传输要求。
网络数据获取方式
网络数据采集是指通过网络爬虫或网站公共API从网站获取数据信息。该方法可以将网页中的非结构化数据提取出来，存储为统一的本地数据文件，并进行结构化存储。支持图片、音频、视频等文件或附件的采集
，附件与文本可自动关联。除了网络中收录
的内容外，还可以使用 DPI 或 DFI 等带宽管理技术处理网络流量的采集
。
其他数据采集
方法
对于企业生产经营数据或学科研究数据等保密要求高的数据，可通过与企业或研究机构合作，采用特定系统接口等相关方式采集
数据。
▌大数据采集平台
" />
最后给大家介绍几个应用广泛的大数据采集平台，供大家参考。
1）阿帕奇水槽
Flume是Apache下一个开源、高可靠、高扩展、易于管理、支持客户扩展的数据采集系统。Flume是使用JRuby构建的，所以它依赖于Java运行环境。
2）流利
Fluentd 是另一个开源数据采集
框架。Fluentd使用C/Ruby开发，使用JSON文件统一日志数据。其可插拔架构支持各种类型和格式的数据源和数据输出。最后，它还同时提供了高可靠性和良好的可扩展性。Treasure Data, Inc 为该产品提供支持和维护。
3) 日志存储
Logstash是著名的开源数据栈ELK（ElasticSearch、Logstash、Kibana）中的L。Logstash 使用 JRuby 开发，所有运行时都依赖于 JVM。
4)Splunk转发器
Splunk 是一个分布式机器数据平台，主要有三个角色： Search Head 负责数据的搜索和处理，提供搜索过程中的信息提取；Indexer负责数据的存储和索引；Forwarder负责数据的采集、清洗、变形，并发送给Indexer
很多初学者对大数据的概念比较模糊。什么是大数据，它能做什么，什么时候学，应该走什么路线，学完后往哪里发展，想了解更多，想学习欢迎同学们加入大数据学习扣群：805127855，里面有海量干货（零基础进阶经典实战）分享给大家，更有清华毕业资深大数据讲师免费授课，分享国内最全。大数据高端实战实战学习流程体系
