oracle课堂随笔----第二十九天
flashback database
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on; 数据库在归档模式下
SQL> show parameter db_flashback_retention_target
SQL> select OLDEST_FLASHBACK_TIME from v$flashback_database_log;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> truncate table t1;
SQL> create table after_truncate(x int); 其他正确操作
SQL> select OLDEST_FLASHBACK_TIME, OLDEST_FLASHBACK_SCN from v$flashback_database_log; 确认是否在恢复范围
SQL> shutdown abort
SQL> startup mount
SQL> flashback database to scn 1495195;
SQL> alter database open resetlogs;
SQL> select * from t1;
SQL> select * from after_truncate; 消失
移动数据
Sqlloader
单向数据装载（外面数据装进数据库）
SQL> create table t1(id int constraint t1_id_pk primary key, name varchar2(20), salary int constraint t1_salary_ck check(salary>0));
$ vi ~/loader.dat
100,"abc",1000
100,"def",2000
102,"xyz",-1000
em中常规导入，自动处理违反约束的记录
em中直接导入
SQL> select CONSTRAINT_NAME, STATUS from user_constraints where TABLE_NAME='T1';
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
SQL> alter table t1 enable validate constraint T1_SALARY_CK; 失败
SQL> @?/rdbms/admin/utlexpt1.sql
处理check约束：
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set salary=abs(salary) where id=102;
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
处理pk约束：
SQL> alter table t1 disable novalidate constraint T1_ID_PK;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set id=101 where name='def';
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
外部表
映射（快捷方式）
oracle_datapump driver
unloading:
CREATE TABLE oe.inventories_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
)
AS SELECT * FROM oe.inventories;
SQL> delete oe.inventories_xt; 失败
loading:
CREATE TABLE oe.inventories_xt2
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
);
SQL> delete oe.inventories_xt2; 失败
优化
DB time = CPU time + Wait time
