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.
精彩链接
您阅读本篇文章共花了：
发表评论