-
Oracle对临时表空间管理
3.增加新的数据文件（bigfile tablespace不能使用）
- alter tablespace tbs2 add datafile '' size 10m;
数据库默认永久表空间：
- create user smith identified by smith;
- grant connect,resource to smith;
- create table t01 (x int);
启用默认永久表空间
alter database default tablespace tbs1;查看数据库默认永久表空间
select * from database_properties where rownum<4;查看用户的默认表空间：
select default_tablespace from dba_users where username='SCOTT';修改用户默认表空间：
alter user scott default tablespace tbs2;表空间的删除
drop tablespace tbs3 including contents and datafiles cascade constraints;当前哪一个会话在哪一个临时表空间中使用了多少个块？
- SQL>
- SQL> create global temporary table temp as select * from emp;
- Table created.
- SQL> insert into temp select * from emp;
- 14 rows created.
- SQL> select * from temp;
- 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> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage where USERNAME='SCOTT';
- USERNAME TABLESPACE BLOCKS
- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------
- SCOTT TEMP 128
- SQL>
事务级临时表：事务结束，数据消失。commit数据消失
create global temporary table temp as select * from emp;会话级临时表：会话结束，数据消失，connect数据消失。
- SQL>
- SQL> create global temporary table temp02 on commit preserve rows as select * from emp;
- Table created.
- SQL>
什么情况下使用临时表空间：
排序的中间结果,临时表的数据都会写到临时表空间。
监控临时表空间的SQL
SQL> select username,tablespace,blocks from v$sort_usage where username='SCOTT';第一步： 禁止11g的内存管理风格
- SQL> show parameter memory
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- hi_shared_memory_address integer 0
- inmemory_adg_enabled boolean TRUE
- inmemory_automatic_level string OFF
- inmemory_clause_default string
- inmemory_expressions_usage string ENABLE
- inmemory_force string DEFAULT
- inmemory_max_populate_servers integer 0
- inmemory_optimized_arithmetic string DISABLE
- inmemory_prefer_xmem_memcompress string
- inmemory_prefer_xmem_priority string
- inmemory_query string ENABLE
- inmemory_size big integer 0
- inmemory_trickle_repopulate_servers_ integer 1
- percent
- inmemory_virtual_columns string MANUAL
- inmemory_xmem_size big integer 0
- memory_max_target big integer 0
- memory_target big integer 0
- optimizer_inmemory_aware boolean TRUE
- shared_memory_address integer 0
- SQL> alter system set memory_target=0;
- System altered.
- SQL> show parameter pga;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_limit big integer 2G
- pga_aggregate_target big integer 200M
- SQL>
第二步：压缩排序的内存区
- SQL> show parameter pga;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_limit big integer 2G
- pga_aggregate_target big integer 200M
- SQL>
- SQL> alter system set pga_aggregate_target=10M;
- System altered.
- SQL> show parameter pga;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- pga_aggregate_limit big integer 2G
- pga_aggregate_target big integer 10M
- SQL>
- SQL> select * from ob1 order by 1;
- OWNER
- --------------------------------------------------------------------------------
- OBJECT_NAME
- --------------------------------------------------------------------------------
- SUBOBJECT_NAME
- --------------------------------------------------------------------------------
- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
- ---------- -------------- ----------------------- --------- ---------
- TIMESTAMP STATUS T G S NAMESPACE
- ------------------- ------- - - - ----------
- EDITION_NAME
- --------------------------------------------------------------------------------
- SHARING E O A
- ------------------ - - -
- DEFAULT_COLLATION
- --------------------------------------------------------------------------------
- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
- - - ------------- ------------- -------------- --------------
- APPQOSSYS
- OWNER
- --------------------------------------------------------------------------------
- OBJECT_NAME
- --------------------------------------------------------------------------------
- SUBOBJECT_NAME
- --------------------------------------------------------------------------------
- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
- ---------- -------------- ----------------------- --------- ---------
- TIMESTAMP STATUS T G S NAMESPACE
- ------------------- ------- - - - ----------
- EDITION_NAME
- --------------------------------------------------------------------------------
- SHARING E O A
- ------------------ - - -
- DEFAULT_COLLATION
- --------------------------------------------------------------------------------
- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
- - - ------------- ------------- -------------- --------------
- WLM_METRICS_STREAM
- SQL> select username,tablespace,blocks from v$sort_usage where username='SCOTT';
- no rows selected
- SQL> /
- USERNAME TABLESPACE BLOCKS
- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------
- SCOTT TEMP 1536
- SQL> /
临时表空间的数据字典：
- SQL>
- SQL> select name from v$tempfile;
- NAME
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf
- SQL>
永久表空间的数据字典：
- SQL>
- SQL> select file_name from dba_temp_files;
- FILE_NAME
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf
- SQL>
临时表空间要是被删了，启动数据库会有什么问题？（数据库会自动创建临时表空间）
- SQL>
- SQL> ！rm -f /u02/oradata/CDB1/pdb1/temp01.dbf
- SP2-0734: unknown command beginning "！rm -f /..." - rest of line ignored.
- SQL>
- SQL>
- SQL> !rm -f /u02/oradata/CDB1/pdb1/temp01.dbf
- SQL> !ls -l /u02/oradata/CDB1/pdb1/temp01.dbf
- ls: cannot access '/u02/oradata/CDB1/pdb1/temp01.dbf': No such file or directory
- SQL>
- SQL> startup force
- Pluggable Database opened.
- SQL> !ls -l /u02/oradata/CDB1/pdb1/temp01.dbf
- -rw-r-----. 1 oracle oinstall 37756928 Nov 22 14:29 /u02/oradata/CDB1/pdb1/temp01.dbf
- SQL>
临时表空间的创建：
- SQL>
- SQL> show user;
- USER is "SYS"
- SQL>
- SQL> create temporary tablespace temp02 tempfile '/u02/oradata/CDB1/pdb1/temp02.dbf' size 20m;
- Tablespace created.
- SQL>
- -- 查看表空间组
- SQL> select * from dba_tablespace_groups;
- --表空间重命名
- SQL> alter tablespace temp rename to temp01;
- Tablespace altered.
- SQL>
- --查看表空间
- SQL> set pagesize 200
- SQL> set linesize 200
- SQL>
- SQL> select name from v$tablespace;
- NAME
- ------------------------------
- SYSAUX
- SYSTEM
- UNDOTBS1
- USERS
- TEMP01
- SYSTEM
- SYSAUX
- UNDOTBS1
- TEMP
- SYSTEM
- SYSAUX
- UNDOTBS1
- TEMP
- USERS
- TEMP02
- 15 rows selected.
- SQL> select * from dba_tablespace_groups;
- no rows selected
- SQL> alter tablespace temp01 tablespace group tempgroup;
- Tablespace altered.
- SQL> select * from dba_tablespace_groups;
- GROUP_NAME TABLESPACE_NAME
- ------------------------------ ------------------------------
- TEMPGROUP TEMP01
- SQL>
临时表空间的扩容方法：
- SQL> select file_name from dba_temp_files where tablespace_name='TEMP02';
- FILE_NAME
- ----------------------------------------
- /u02/oradata/CDB1/pdb1/temp02.dbf
- SQL>
- SQL> alter database tempfile '/u02/oradata/CDB1/pdb1/temp02.dbf' resize 5m;
- Database altered.
- SQL>
- SQL> alter user scott temporary tablespace temp02;
- User altered.
- SQL> select file_name from dba_temp_files where tablespace_name='TEMP02';
- FILE_NAME
- ----------------------------------------
- /u02/oradata/CDB1/pdb1/temp02.dbf
- ---方法一：
- SQL> alter database tempfile '/u02/oradata/CDB1/pdb1/temp02.dbf' resize 15m;
- Database altered.
- SQL>
- ---方法二
- SQL>
- SQL> alter database tempfile '/u02/oradata/CDB1/pdb1/temp02.dbf' autoextend on;
- Database altered.
- SQL>
- -- 方法三
- SQL>
- SQL> alter tablespace temp02 add tempfile '/u02/oradata/CDB1/pdb1/temp03.dbf' size 10m;
- Tablespace altered.
- SQL>
恢复创建前的状态：
- QL> select file_name,tablespace_name from dba_temp_files;
- FILE_NAME TABLESPACE_NAME
- ---------------------------------------- ------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf TEMP
- /u02/oradata/CDB1/pdb1/temp02.dbf TEMP02
- /u02/oradata/CDB1/pdb1/temp03.dbf TEMP02
- SQL> alter database tempfile '/u02/oradata/CDB1/pdb1/temp03.dbf' drop;
- Database altered.
- SQL> select file_name,tablespace_name from dba_temp_files;
- FILE_NAME TABLESPACE_NAME
- ---------------------------------------- ------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf TEMP
- /u02/oradata/CDB1/pdb1/temp02.dbf TEMP02
- SQL> alter database tempfile '/u02/oradata/CDB1/pdb1/temp02.dbf' drop;
- Database altered.
- SQL> select file_name,tablespace_name from dba_temp_files;
- FILE_NAME TABLESPACE_NAME
- ---------------------------------------- ------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf TEMP
- SQL> alter user scott temporary tablespace temp;
- User altered.
- SQL> select file_name,tablespace_name from dba_temp_files;
- FILE_NAME TABLESPACE_NAME
- ---------------------------------------- ------------------------------------------------------------
- /u02/oradata/CDB1/pdb1/temp01.dbf TEMP
- SQL>
UNDO表空间不能保存对象！！
undo表空间保存老镜像作用：
- 1.为事务提供回退
- 2.为事务提供恢复
- 3.提供读一致性
- 4.提供对DML操作的闪回处理
- SQL>
- SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
- TABLESPACE_NAME
- ------------------------------
- UNDOTBS1
- SQL>
- SQL>
- 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
- 11 rows selected.
- SQL>
SYSTEM 只有系统表空间可用。
- 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
- SQL> desc v$rollname
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- USN NUMBER
- NAME NOT NULL VARCHAR2(30)
- CON_ID NUMBER
- SQL> select name from v$rollname where USN=8;
- NAME
- ------------------------------
- _SYSSMU8_399776867$
- 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
- 11 rows selected.
- SQL> select segment_name,tablespace_name,status from dba_rollback_segs where segment_name='_SYSSMU8_399776867$';
- SEGMENT_NAME TABLESPACE_NAME STATUS
- ------------------------------ ------------------------------ ----------------
- _SYSSMU8_399776867$ UNDOTBS1 ONLINE
- SQL>
undo tablespace之下 --》使用rollback segment保存数据修改前的老镜像
rollback segment的管理方法：
- 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>
- begin
- for i in 1..300000 loop
- update t01 set x=x+1'
- end loop;
- end;
- /
提供对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
- 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>
最多可以闪回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.
-
相关阅读:
二叉树的应用——堆排序
Typescript 学习笔记（二）高级类型二
【JAVA】Java 常见的垃圾收集器有哪些？
docker镜像学习
LeetCode--180 连续出现的数字
【毕业设计】深度学习 python opencv 实现人脸年龄性别识别
语言大模型的推理技巧
国家自然科学基金委资助项目简介
vue3.2封装一个listTable组件
IDEA如何运行SpringBoot项目（超详细截图）
- 原文地址：https://blog.csdn.net/u011868279/article/details/127968171
