当遇到ora错误时需要查询错误信息是其实可以在数据库服务器上来查看
[root@jingyong ~]# su - oracle [oracle@jingyong ~]$ cd $ORACLE_BASE [oracle@jingyong oracle]$ oerr ora 30012 30012, 00000, "undo tablespace '%s' does not exist or of wrong type" // *Cause: the specified undo tablespace does not exist or of the // wrong type. // *Action: Correct the tablespace name and reissue the statement.
oracle先会使用spfile
如果该文件不存在就使用查找spfile.ora文件
如果spfile
就会使用init
如果上述三个文件都没有就没有办法启动oracle实例
先通过spfile文件来创建pfile文件
SQL> create pfile from spfile;
File created.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db/dbs/spfilejingyong.ora
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
删除spfilejingyong.ora文件
[oracle@jingyong dbs]$ mv spfilejingyong.ora spfilejingyong.ora.bak SQL> startup nomount ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 75499764 bytes Database Buffers 88080384 bytes Redo Buffers 2973696 bytes SQL> show parameter spfile NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string
从上面可以看去现在使用的是initjingyong.ora参数文件
ora_rowscn
对于每一行数据,ora_rowscn返回每一行最近被修改的大概时间.这对于判断一行数据
大概是在什么时间被修改的还是有用的.因oracle是通过事务提交对行所在数据块来
进行scn的跟踪的所以说它不精确.可以通过在创建表时使用行级别的依赖跟踪来获得
一个更加精确的scn.create table … norowdependencies|rowdependencies
在对视图进行查询时不能使用ora_rowscn.但对于视图的基表是可以使用ora_rowscn.
也能在update或delete语句中的where子句中使用ora_rowscn
ora_rowscn不能用于回闪查询,但是可以用回闪版本查询来代替ora_rowscn.
ora_rowscn也不能用于外部表
获取行被修改的大根的scn
SQL> select ora_rowscn,t.test_id from test_jy t;
ORA_ROWSCN TEST_ID
---------- ---------------------
625591 3
通过scn来获取修改行记录大概的时间
SQL> select scn_to_timestamp(ora_rowscn),t.test_id from test_jy t; SCN_TO_TIMESTAMP(ORA_ROWSCN) TEST_ID -------------------------------------------------------------------------------- --------------------- 20-12月-12 06.23.22.000000000 下午 3
也可以通过时间来获得scn
SQL> select timestamp_to_scn(scn_to_timestamp(ora_rowscn)),t.test_id from test_jy t;
TIMESTAMP_TO_SCN(SCN_TO_TIMEST TEST_ID
------------------------------ ---------------------
625590 3
在使用序列的currval和nextval时的限制 创建一个序列
create sequence test_seq minvalue 1 maxvalue 10000000 start with 1 increment by 1 cache 20;
在delete,select,update语句的子查询中不能使用sequence的值
SQL> delete from test_jy where test_id < (select test_seq.currval from dual); delete from test_jy where test_id <(select test_seq.currval from dual) ORA-02287: 此处不允许序号 SQL> select * from test_jy where test_id < (select test_seq.currval from dual); select * from test_jy where test_id <(select test_seq.currval from dual) ORA-02287: 此处不允许序号 SQL> update test_jy set test_id=0 where test_id < (select test_seq.currval from dual); update test_jy set test_id=0 where test_id <(select test_seq.currval from dual) ORA-02287: 此处不允许序号
在查询视图或物化视图时
SQL> select a.* from test_v a where a.userid带有distinct操作符的select语句不能使用
SQL> select distinct a.*,test_seq.currval from test_v a ; select distinct a.*,test_seq.currval from test_v a ORA-02287: 此处不允许序号有group by,order by操作的select语句不能使用
SQL> select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id; select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id ORA-02287: 此处不允许序号 SQL> select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id; select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id ORA-02287: 此处不允许序号有UNION, INTERSECT, MINUS操作符的语句不能使用
SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 2 union 3 select test_jy.*,test_seq.currval from test_jy where test_id=2; select test_jy.*,test_seq.currval from test_jy where test_id=1 union select test_jy.*,test_seq.currval from test_jy where test_id=2 ORA-02287: 此处不允许序号 SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 2 intersect 3 select test_jy.*,test_seq.currval from test_jy where test_id=2; select test_jy.*,test_seq.currval from test_jy where test_id=1 intersect select test_jy.*,test_seq.currval from test_jy where test_id=2 ORA-02287: 此处不允许序号 SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 2 minus 3 select test_jy.*,test_seq.currval from test_jy where test_id=2; select test_jy.*,test_seq.currval from test_jy where test_id=1 minus select test_jy.*,test_seq.currval from test_jy where test_id=2 ORA-02287: 此处不允许序号在select语句中的where子句中
SQL> select test_jy.* from test_jy where test_id在create table或alter table语句的中default值是不能使用sequence
SQL> alter table test_jy modify test_id number(20) default test_seq.currval; alter table test_jy modify test_id number(20) default test_seq.currval ORA-00984: 列在此处不允许还有就在check约束中不能使用
SELECT d.tablespace_name TS_Name,
d.contents TS_Type,
d.status TS_Status,
d.extent_management TS_ExtentManagement,
trunc(NVL(a.bytes / 1024 / 1024, 0)) TS_Size,
trunc(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024) TS_UsedSize,
trunc(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) TS_Used
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name TS_Name,
d.contents TS_Type,
d.status TS_Status,
d.extent_management TS_ExtentManagement,
trunc(NVL(a.bytes / 1024 / 1024, 0)) TS_Size,
trunc(NVL(t.bytes, 0) / 1024 / 1024) TS_UsedSize,
trunc(NVL(t.bytes / a.bytes * 100, 0)) TS_Used
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
order by TS_Name;
TS_NAME TS_TYPE TS_STATUS TS_EXTENTMANAGEMENT TS_SIZE_MB TS_USEDSIZE_MB TS_USED
------------------------------ --------- --------- ------------------- ---------- -------------- ----------
EXAMPLE PERMANENT ONLINE LOCAL 100 77 77
SYSAUX PERMANENT ONLINE LOCAL 240 238 99
SYSTEM PERMANENT ONLINE LOCAL 480 475 99
TEMP TEMPORARY ONLINE LOCAL 20 18 90
UNDOTBS1 UNDO ONLINE LOCAL 35 28 81
USERS PERMANENT ONLINE LOCAL 5 3 65
--查看数据文件大小
select a.tablespace_name,a.file_name,a.bytes/1024/1024 total_mb, (a.bytes - nvl(c.bytes, 0))/1024/1024 use_mb
from (select a.*,
d.STATUS file_status,
a.increment_by * b.block_size extendbytes
from dba_data_files a, dba_tablespaces b, v$datafile d
where a.tablespace_name = b.tablespace_name
and a.file_id = d.FILE#
/*and a.file_id = :file_id*/) a
left join (select file_id, sum(bytes) bytes
from dba_free_space
/*where file_id = :file_id*/
group by file_id) c on a.file_id = c.file_id
union all
select a.tablespace_name,a.file_name,a.bytes/1024/1024 total_mb, c.bytes/1024/1024 use_mb
from (select a.*,
d.STATUS file_status,
a.increment_by * b.block_size extendbytes
from dba_temp_files a, dba_tablespaces b, v$tempfile d
where a.tablespace_name= b.tablespace_name
and a.file_id = d.FILE#
/*and a.file_id = :file_id*/) a
left join (select file_id, sum(bytes_cached) bytes
from v$temp_extent_pool
/*where file_id = :file_id*/
group by file_id) c on a.file_id = c.file_id
TABLESPACE_NAME FILE_NAME TOTAL_MB USE_MB
------------------------------ -------------------------------------------------------------------------------- ---------- ----------
SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\SYSTEM01.DBF 480 475.5
UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\UNDOTBS01.DBF 35 28.4375
USERS D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\USERS01.DBF 5 3.25
EXAMPLE D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\EXAMPLE01.DBF 100 77.6875
SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\SYSAUX01.DBF 240 238.8125
TEMP D:\ORACLE\PRODUCT\10.2.0\ORADATA\JINGYONG\TEMP01.DBF 20 18
create table MT_BIZ ( HOSPITAL_ID VARCHAR2(20) not null, SERIAL_NO VARCHAR2(16) not null, FEE_BATCH NUMBER(5) default 1 not null, CASE_ID NUMBER(12), BIZ_TYPE VARCHAR2(2) not null, ORDINAL_NO NUMBER(2) default 0 not null, DISTRICT_CODE VARCHAR2(6), INDI_ID NUMBER(12) not null, NAME VARCHAR2(20) not null, SEX CHAR(1) not null, PERS_TYPE VARCHAR2(3) not null, OFFICE_GRADE VARCHAR2(3) default '000' not null, IDCARD VARCHAR2(25), IC_NO VARCHAR2(25), BIRTHDAY DATE, TELEPHONE VARCHAR2(30), CORP_ID NUMBER(10) not null, CORP_NAME VARCHAR2(70) not null, TREATMENT_TYPE VARCHAR2(3) not null, BIZ_TIMES NUMBER(3), RELA_HOSP_ID VARCHAR2(20), RELA_SERIAL_NO VARCHAR2(16), SERIAL_APPLY NUMBER(12), REG_DATE DATE not null, REG_STAFF VARCHAR2(8) not null, REG_MAN VARCHAR2(20), REG_FLAG CHAR(1) default '0' not null, BEGIN_DATE DATE not null, REG_INFO VARCHAR2(10), IN_DEPT VARCHAR2(10), IN_DEPT_NAME VARCHAR2(20), IN_AREA VARCHAR2(10), IN_AREA_NAME VARCHAR2(20), IN_BED VARCHAR2(10), BED_TYPE CHAR(1), PATIENT_ID VARCHAR2(20), IN_DISEASE VARCHAR2(20) not null, FOREGIFT NUMBER(10,2), DIAGNOSE_DATE DATE, DIAGNOSE VARCHAR2(20), IN_DAYS NUMBER(4), FIN_DISEASE VARCHAR2(20), END_DATE DATE, END_STAFF VARCHAR2(8), END_MAN VARCHAR2(30), FIN_INFO VARCHAR2(10), IC_FLAG CHAR(1) default '0' not null, REIMBURSE_FLAG CHAR(1) default '0' not null, BIZ_END_DATE DATE, FINISH_FLAG CHAR(1) default '0' not null, POS_CODE VARCHAR2(10), LOCK_FLAG CHAR(1) default '0' not null, INJURY_BORTH_SN NUMBER(12), REMARK VARCHAR2(500), TRANS_FLAG CHAR(1) default '0' not null, CENTER_ID VARCHAR2(100) default 0 not null, PERS_TYPE_DETAIL VARCHAR2(3), CORP_TYPE_CODE VARCHAR2(3), SPECIAL_CODE VARCHAR2(3), DOCTOR_NO VARCHAR2(20), DOCTOR_NAME VARCHAR2(20), FIN_DISEASE1 VARCHAR2(20), FIN_DISEASE2 VARCHAR2(20), CASE_INFO VARCHAR2(100), BILL_NO VARCHAR2(20), HOS_SERIAL VARCHAR2(30), DISEASE_TYPE CHAR(1), DISEASE_FLAG CHAR(1) default '0', RECUR_FLAG CHAR(1) default '0', INJURY_TYPE VARCHAR2(2) default '01' ); -- Add comments to the table comment on table MT_BIZ is '医疗业务表'; -- Add comments to the columns comment on column MT_BIZ.HOSPITAL_ID is '医疗机构编号'; comment on column MT_BIZ.SERIAL_NO is '业务序列号'; comment on column MT_BIZ.FEE_BATCH is '费用批次'; comment on column MT_BIZ.CASE_ID is '病例分型序号'; comment on column MT_BIZ.BIZ_TYPE is '业务类别编号'; comment on column MT_BIZ.ORDINAL_NO is '内部序数'; comment on column MT_BIZ.DISTRICT_CODE is '社区编码(指个人所属行政区编码)'; comment on column MT_BIZ.INDI_ID is '个人编号'; comment on column MT_BIZ.NAME is '姓名'; comment on column MT_BIZ.SEX is '性别'; comment on column MT_BIZ.PERS_TYPE is '人员类别待遇代码'; comment on column MT_BIZ.OFFICE_GRADE is '公务员级别'; comment on column MT_BIZ.IDCARD is '公民身份号码'; comment on column MT_BIZ.IC_NO is 'IC卡号'; comment on column MT_BIZ.BIRTHDAY is '出生日期'; comment on column MT_BIZ.TELEPHONE is '联系电话'; comment on column MT_BIZ.CORP_ID is '单位编码'; comment on column MT_BIZ.CORP_NAME is '单位名称'; comment on column MT_BIZ.TREATMENT_TYPE is '待遇类别(用于区分同一业务类型的不同情况，比如生育门诊的三个月以上和三个月以上流产,不区分时为0)'; comment on column MT_BIZ.BIZ_TIMES is '本年业务次数'; comment on column MT_BIZ.RELA_HOSP_ID is '关联医疗机构编码'; comment on column MT_BIZ.RELA_SERIAL_NO is '关联业务序列号'; comment on column MT_BIZ.SERIAL_APPLY is '申请序列号'; comment on column MT_BIZ.REG_DATE is '业务登记日期'; comment on column MT_BIZ.REG_STAFF is '登记人工号'; comment on column MT_BIZ.REG_MAN is '登记人'; comment on column MT_BIZ.REG_FLAG is '登记标志(0：正常 1：转院 2：二次返院（审批通过后RELA_SERIAL_NO为空） 3：急诊留观转住院 4：90天或180天结算（处理后RELA_HOSP_ID为空，RELA_SERIAL_NO不为空）)'; comment on column MT_BIZ.BEGIN_DATE is '业务开始时间'; comment on column MT_BIZ.REG_INFO is '业务开始情况（FR：提取冻结费用的零报业务 MW：医疗转工伤的零报业务）'; comment on column MT_BIZ.IN_DEPT is '入院科室'; comment on column MT_BIZ.IN_DEPT_NAME is '入院科室名称'; comment on column MT_BIZ.IN_AREA is '入院病区'; comment on column MT_BIZ.IN_AREA_NAME is '入院病区名称'; comment on column MT_BIZ.IN_BED is '入院床位号'; comment on column MT_BIZ.BED_TYPE is '床位类型'; comment on column MT_BIZ.PATIENT_ID is '医院业务号'; comment on column MT_BIZ.IN_DISEASE is '入院疾病诊断'; comment on column MT_BIZ.FOREGIFT is '预付款总额'; comment on column MT_BIZ.DIAGNOSE_DATE is '确诊日期'; comment on column MT_BIZ.DIAGNOSE is '确诊疾病诊断'; comment on column MT_BIZ.IN_DAYS is '住院天数'; comment on column MT_BIZ.FIN_DISEASE is '出院疾病诊断'; comment on column MT_BIZ.END_DATE is '业务终结日期'; comment on column MT_BIZ.END_STAFF is '终结人工号'; comment on column MT_BIZ.END_MAN is '终结人'; comment on column MT_BIZ.FIN_INFO is '业务终结情况'; comment on column MT_BIZ.IC_FLAG is '用卡标志'; comment on column MT_BIZ.REIMBURSE_FLAG is '中心报帐标志'; comment on column MT_BIZ.BIZ_END_DATE is '诊次结束时间'; comment on column MT_BIZ.FINISH_FLAG is '完成标志'; comment on column MT_BIZ.POS_CODE is 'POS机编号'; comment on column MT_BIZ.LOCK_FLAG is '锁定标志'; comment on column MT_BIZ.INJURY_BORTH_SN is '对应的工伤生育业务号'; comment on column MT_BIZ.REMARK is '备注'; comment on column MT_BIZ.TRANS_FLAG is '传输标志(0:未传输 1:已成功传输 2:未成功传输）'; comment on column MT_BIZ.CENTER_ID is '医保中心编码'; comment on column MT_BIZ.PERS_TYPE_DETAIL is '人员类别详细代码(bs_person.PERS_TYPE)'; comment on column MT_BIZ.CORP_TYPE_CODE is '单位类型'; comment on column MT_BIZ.SPECIAL_CODE is '特殊人群编码'; comment on column MT_BIZ.DOCTOR_NO is '医生编号'; comment on column MT_BIZ.DOCTOR_NAME is '医生姓名'; comment on column MT_BIZ.FIN_DISEASE1 is '第一副诊断'; comment on column MT_BIZ.FIN_DISEASE2 is '第二副诊断'; comment on column MT_BIZ.CASE_INFO is '病历信息'; comment on column MT_BIZ.BILL_NO is '单据号'; comment on column MT_BIZ.HOS_SERIAL is '医院交易流水号'; comment on column MT_BIZ.DISEASE_TYPE is '病种分型(A：病种单纯 B：严重 C：严重并发 D：危重)'; comment on column MT_BIZ.DISEASE_FLAG is '职业病标志（0 不是职业病，1 是职业病）'; comment on column MT_BIZ.RECUR_FLAG is '工伤复发标志（0 不是工伤复发，1 是工伤复发）'; comment on column MT_BIZ.INJURY_TYPE is '工伤类别（01：新工伤,02老工伤,对应新增wi_injury_type码表）'; -- Create/Recreate primary, unique and foreign key constraints alter table MT_BIZ add constraint PK_MT_BIZ primary key (HOSPITAL_ID, SERIAL_NO) using index; -- Create/Recreate indexes create index IDX_MT_BIZ_BEGIN_DATE on MT_BIZ (BEGIN_DATE); create index IDX_MT_BIZ_BIZ_END_DATE on MT_BIZ (BIZ_END_DATE); create index IDX_MT_BIZ_CORP_ID on MT_BIZ (CORP_ID); create index IDX_MT_BIZ_IB on MT_BIZ (INJURY_BORTH_SN); create index IDX_MT_BIZ_INDI_ID on MT_BIZ (INDI_ID); create index IDX_MT_BIZ_RELA on MT_BIZ (RELA_HOSP_ID, RELA_SERIAL_NO);
在mt_biz表中有一个复合主键是由hospital_id与serial_no组成的
其中serial_no是唯一值是由序列生成的，所以在查询数据时有些语句只使用serial_no
造成了使用不上索引的问题
因为如果索引是建立在多个列上, 只有在它的第一个列也叫前导列(leading
column)被where子句引用时,优化器才会选择使用该索引.
例如,不使用合主键的唯一索引中的前导列hospital_id时的语句执行计划如下
SQL> set autotrace traceonly;
SQL> select * from mt_biz a where a.serial_no='15485197';
执行计划
----------------------------------------------------------
Plan hash value: 3513793642
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 244 | 42 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MT_BIZ | 1 | 244 | 42 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."SERIAL_NO"='15485197')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
2852 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从显示的执行计划可以看出当没有使用复合索引中的前导列hospital_id时
是执行的全表扫描
当使用复合索引中的前导列hospital_id时
SQL> select * from mt_biz a where a.hospital_id='4307000009';
已选择348行。
执行计划
----------------------------------------------------------
Plan hash value: 3033165289
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 4392 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MT_BIZ | 18 | 4392 | 8 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_MT_BIZ | 18 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."HOSPITAL_ID"='4307000009')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
345 consistent gets
0 physical reads
0 redo size
102775 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
348 rows processed
给出的执行计划是通过INDEX RANGE SCAN来执行查询
当使用复合索引中所有列时
SQL> select * from mt_biz a where a.hospital_id='4307000009' and a.serial_no='15485197';
执行计划
----------------------------------------------------------
Plan hash value: 2316229530
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 244 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MT_BIZ | 1 | 244 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_MT_BIZ | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."HOSPITAL_ID"='4307000009' AND "A"."SERIAL_NO"='15485197')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
2818 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
给出的执行计划是使用INDEX UNIQUE SCAN索引扫描来查询
所以当创建复合索引后如果查询要想使用这个复合索引就必须在查询条件中
使用复合索引的前导列才会让优化器使用这个复合索引
oracle回闪技术:代替按时间点恢复
oracle回闪技术提供了一组功能它有效的代替了查看数据过去的状态和数据修改前后的状态而不用要你从备份中还原大部分数据或执行按时间点恢复.回闪技术与介质恢复相比更有效和更小的损坏数据.
大多数据回闪技术功能是在逻辑级别的操作,象查看和维护数据库对象
回闪查询:让你指定一个目标时间然后运行查询，来查看在那个时间出现的查询结查.为了从一个不想要发生的改变象用户错误的更新一个,用户可以选择在出错前的某个时间点运行查询来检索丢失或改变的记录.
回闪版本查询:让你可以查询在一个指定时间间隔内一个表或多个表永久存的所有记录的所有版本.象在表上执行的所有更新.也可能检索行记录不同版本的元数据象开始时间，结束时间,进行的操作和这个事务的事务ID创建的版本.这个功能也用来恢复丢失的数据和查询审计表的改变.
回闪事务查询:让你可能查看由单个事务发生的改变或在一个时间周期内所有事务发生的改变.
回闪表:能让你将表还原到过去某一个时间点.你能在数据库联机的情况下还原表数据，而仅仅回滚你指定的表的数据.
回闪删除:能拆消drop table语句的影响
回闪表，回闪查询，回闪事务查询和回闪版本查询都是要依赖重做数据.重做数据是用来记录数据库中每一个更新操作的数据的.使用它的主要目的是用来给查询提供一至性读取和回滚事务。
回闪删除是建交在一种叫做回收站机制之上的，oracle使用它来管理删除的数据库对象，直到回收站空间不足以要给新对象分配空间时才会清除.
查询一个表的过去状态可以使用select as of子句来完成.
select *
from lv_insr_topay
as of timestamp (sysdate - 40/1440)
where corp_id=777;--删除前
select *
from lv_insr_topay
as of timestamp (sysdate - 10/1440)
where corp_id=777;--删除后
select *
from lv_insr_topay
as of timestamp to_timestamp('2011-12-26 15:00:00','yyyy-mm-dd hh24:mi:ss')
where corp_id=777;--删除前
select *
from lv_insr_topay
as of timestamp to_timestamp('2011-12-26 15:09:00','yyyy-mm-dd hh24:mi:ss')
where corp_id=777;--删除后
select count(*) from zw2004.gl_pznr versions
between timestamp to_timestamp('2012-01-15 10:40:01','yyyy-mm-dd hh24:mi:ss') and
to_timestamp('2012-01-17 09:50:00','yyyy-mm-dd hh24:mi:ss');
回闪版本查询
select versions_starttime,versions_endtime, versions_xid,versions_operation,POLICY_CODE,POLICY_VALUE,CENTER_OR_HOSP from fc_biz_policy versions between timestamp to_timestamp('2012-04-19 10:40:01','yyyy-mm-dd hh24:mi:ss') and
to_timestamp('2012-04-22 09:50:00','yyyy-mm-dd hh24:mi:ss') where policy_code='use_IC'
回闪事务查询
select * from flashback_transaction_query a where a.table_owner='ZW2004' and a.table_name='GL_PZNR'
回闪删除掉的表
flashback table jhk_map_center to before drop
重命名数据文件
如果primary 数据库重命令了一个或多个数据文件，该项修改并不会自动传播到standby 数据库。
如果你想让standby 和数据文件与primary 保持一致，那你也只能自己手工操作了。就算STANDBY_FILE_MANAGEMENT 也帮不上忙啦，不管它是auto 还是manual。
下面通过示例做个演示：
A).将重命名的数据文件所在表空间offline –primary 数据库操作
SQL> alter tablespace users offline; Tablespace altered.
B).手工将数据文件改名(操作系统) –primary 数据库操作
C).通过命令修改数据字典中的数据文件路径，并online 表空间–primary 数据库操作
SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/jytest/users01.dbf' to '/u01/app/oracle/oradata/jytest/myusers01.dbf'; Tablespace altered. SQL> alter tablespace users online; Tablespace altered.
D).暂停redo 应用，并shutdown –standby 数据库操作
SQL> alter database recover managed standby database cancel; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted
E).手工将数据文件改名(操作系统) –standby 数据库操作
F).重启standby，修改数据文件路径(数据字典) –standby 数据库操作
SQL> startup mount; ORACLE instance started. Total System Global Area 6442450944 bytes Fixed Size 2030368 bytes Variable Size 1090520288 bytes Database Buffers 5335154688 bytes Redo Buffers 14745600 bytes Database mounted. SQL> alter database rename file 2 '/u01/app/oracle/oradata/jytest/users01.dbf' to 3 '/u01/app/oracle/oradata/jytest/myusers01.dbf'; 表空间已更改
G).重新启动redo 应用。
SQL> alter system set standby_file_management='MANUAL' scope=both; System altered. SQL> alter database rename file '/u01/app/oracle/oradata/jytest/users01.dbf' to '/u01/app/oracle/oradata/jytest/myusers01.dbf'; Database altered. SQL> alter system set standby_file_management='AUTO' scope=both; System altered.
H).切换日志–primary 数据库操作
SQL> alter system switch logfile; 系统已更改。
STANDBY_FILE_MANAGEMENT设置为MANUAL，增加及删除表空间和数据文件
SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO SQL> alter system set standby_file_management='MANUAL' scope=both; System altered. SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL SQL>
A).增加新的表空间–primary 数据库操作
SQL>CREATE TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/jytest/mytest01.dbf' size 20M SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf /u01/app/oracle/oradata/jytest/mytest01.dbf 6 rows selected
切换日志
SQL> alter system switch logfile; System altered SQL>
B).验证standby 库–standby 数据库操作
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf /u01/app/oracle/product/10.2.0/db/dbs/UNNAMED00006 6 rows selected. SQL> SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA MYTEST 7 rows selected.
可以看到，表空间已经自动创建，但是，数据文件却被起了个怪名字，手工修改其与primary
数据库保持一致.
SQL>alter database create datafile '/u01/app/oracle/product/10.2.0/db/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/jytest/mytest01.dbf';
C).删除表空间–primary 数据库操作
SQL> drop tablespace mytest including contents and datafiles; Tablespace dropped SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf SQL> alter system switch logfile; System altered
D).验证standby 数据库–standby 数据库操作
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf /u01/app/oracle/oradata/jytest/mytest01.dbf 6 rows selected. SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA MYTEST 7 rows selected.
数据还在啊。查看alertjytest.log 文件，发现如下
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 3 17:03:34 2012
重启redo 应用再来看看：
SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA 6 rows selected.
注意，既使你在primary 数据库执行删除时加上了including 子句，在standby 数据库仍然只会
将表空间和数据文件从数据字典中删除，你还需要手工删除表空间涉及的数据文件。
STANDBY_FILE_MANAGEMENT设置为AUTO
增加及删除表空间和数据文件
我们先来看看初始化参数的设置： —-standby 数据库操作
SQL> show parameter standby_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO
A).增加新的表空间–primary 数据库操作
SQL>CREATE TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/jytest/mytest01.dbf' size 20M 表空间已创建。
检查刚添加的数据文件
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf /u01/app/oracle/oradata/jytest/mytest01.dbf 6 rows selected SQL> SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA MYTEST 7 rows selected
切换日志
SQL> alter system switch logfile; 系统已更改。
B).验证standby 库–standby 数据库操作
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf /u01/app/oracle/oradata/jytest/mytest01.dbf 6 rows selected. SQL> SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA MYTEST 7 rows selected.
可以看到，表空间和数据文件已经自动创建，你是不是奇怪为什么数据文件路径自动变成了jytest(因为我这里是主备不在同一台机器上且数据库结构目录相同),因为我们设置了db_file_name_convert 嘛。
C).删除表空间–primary 数据库操作
SQL> drop tablespace mytest including contents and datafiles; Tablespace dropped SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf SQL> alter system switch logfile; System altered
提示：使用including 子句删除表空间时，
D).验证standby 数据库–standby 数据库操作
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/jytest/system01.dbf /u01/app/oracle/oradata/jytest/undotbs01.dbf /u01/app/oracle/oradata/jytest/sysaux01.dbf /u01/app/oracle/oradata/jytest/users01.dbf /u01/app/oracle/oradata/jytest/hygeia01.dbf SQL> SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP HYGEIA 6 rows selected.
得出结论，对于初始化参数STANDBY_FILE_MANAGMENT 设置为auto 的话，对于表空间和数
据文件的操作完全无须dba 手工干预，primary 和standby 都能很好的处理