Oracle 学习笔记（六）

Oracle 数据库常用的闪回sql 语句及其它操作语句：

--Oracle 数据库dml sql

-- 查看当前用户所拥有的表
  select * from tab;

--表空间,auto: 自动管理， manual: 手动管理
  create tablespace  tsp1 datafile 'D:\ORACLE\ORADATA\O10\tsp1.dbf'
  size 2M segment space management auto; 

-- 查看表空间
  select tablespace_name,segment_space_management from dba_tablespaces;
  
-- 回退段管理
  --系统回退段，存放于 system 表空间，只有system 表空间内的对象才可以使用
  
-- 查看回退段
  select * from v$rollname;
  show parameter undo ;

-- 数据回退

-- 当前系统的scn 号， delete 数据后， commit 获取scn
  select dbms_flashback.get_system_change_number() from dual;
 
-- 闪回
  execute dbms_flashback.enable_at_system_change_number(#####);
 
 -- 结束闪回
  execute dbms_flashback.disable();
  
 -- ORA-08182: operation not supported while in Flashback mode, 这时要结束闪回
  execute dbms_flashback.disable();
 
-- 闪回到指定的时间点，物理时间和数据库时间的SCN 的对照表，每5分钟采样 , purge 表后，无法再恢复
  select to_char(time_dp,'yyyy/mm/dd:hh24:mi:ss'),SCN from sys.smon_scn_time;

  execute dbms_flashback.enable_at_time(to_date('2017/9/2/24:12:22','yyyy/mm/dd:hh24:mi:ss'));
  
--闪回---取值到游标----停止闪回----将游标中的值插入原表
	declare
	cursor c1 is select * from scott.e2 where empno=7369;
	v_sal c1%rowtype;
	begin
	DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_number (13346413);
	open c1;
	fetch c1 into v_sal;
	dbms_flashback.disable();
	update scott.e2 set sal=v_sal.sal where empno=v_sal.empno;
	close c1;
	end;
	/
		
-- Flashback Transaction Query, 通过整条来闪回，利用此功能进行诊断问题、性能分析和审计事务

  select xid from v$transaction;
  
  select * from flashback_transaction_query where xid='050025002F020000'

-- 管理回收站
-- 清空回收站
    purge recyclebin;
	
-- 恢复回收站中指定的表 
   flashback table  表名 to before drop ;
   
-- 彻底删除表，不放在回收站中, SYS用户删除的表、视图是不进入回收站的，因此不支持闪回删除
   drop table 表名 purge;
		
-- 查看表的更改痕迹

  select versions_starttime,versions_endtime,versions_xid,versions_operation, sal
  from t1
  versions between timestamp minvalue and maxvalue
  order by versions_starttime
  
  select versions_starttime, versions_endtime , versions_xid, versions_operation,id 
  from t2 versions between timestamp 
  to_timestamp('2017-10-17 14:53:00','yyyy-mm-dd hh24:mi:ss') 
  and to_timestamp('2017-10-17 14:54:30','yyyy-mm-dd hh24:mi:ss');
  
-- 查看 一个时间段内的更改痕迹 
  select versions_starttime,versions_endtime,versions_xid,versions_operation, sal
  from t1
  versions between timestamp to_date('2017/9/2/24:12:22','yyyy/mm/dd:hh24:mi:ss')
  and maxvalue
  order by versions_starttime
  
-- 查看原sql 语句
  select undo_sql from  flashback_transaction_query where xid='08001100C7010000'
   
-- 多次drop 后，建立同名表, 必须9i或10g以上版本支持，flashback无法恢复全文索引
  flashback  table t1 to before drop  rename to t2;
  
-- 构造rowid
  select dbms_rowid.rowid_create(1,5413,4,32,7) from dual;
  --参数1： 新版本的rowid 格式，64进制，18位， 0：旧版本的rowid 格式，16进制，16位
  -- 上面的rowid 代表 4号文件的，第32个块，第7行，将8位16进制转换成10进制，正好是4M,
  -- 所以一个数据块的上限为 4M 个oracle 块。
  
-- 查看每个块中的行数,行的长度不同，块内存放的行数也不同
  select dbms_rowid,rowid_block_number(rowid), block#,count(*)
  from t1
  group by dbms_rowid,rowid_block_number(rowid);
  
-- 删除指定的列
   alter table t1 drop column 列名 checkpoint 1000;
   --drop 过程中表的状态为 invalid,如果过程中停电，启动数据库后 
   alter table t1 drop  columns 列名 continue   
  
-- 查看索引在哪个表哪个列上
   select index_name,table_name,column_name from user_ind_columns order by 2,32
   
-- 查看索引的属性
   select index_name,index_type,table_name,uniqueness from user_indexes;
   
-- 查看索引的内部信息
   select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats
   -- height: 高度， blocks:索引总块数， br_blks: 枝干块数， lf_rows:叶子内行数
   -- del_lf_rows: 叶子中被删除的行数

-- 索引的合并
   alter  index 索引名 	coalesce;

-- 重新索引
   alter index 索引名 rebuild;
   
-- 查看索引的引用状态
  select * from v$objcet_usage;
  
-- 授权 
  grant  create session to u1
  
-- 回收权限
  revoke create session  from  u1;
  
-- 查看字符编码集
  select * from v$nls_valid_values where PARAMETER='CHARACTERSET' order by 2;
  
-- 提取元数据
  select dbms_metadata.get_ddl('TABLE','EMP') FROM DUAL;
  
-- 数据库连接（连接到远程的数据库）
   CREATE PUBLIC DATABASE LINK dh connect to system identified by manager using '111';
--一个共有的数据库连接,名称是 dh,连接到 111 所描述的数据库中的 system 用户,
-- 密码为manager

-- 查看数据库连接
  select * from dba_db_links
  
-- 关闭数据库连接
  ALTER SESSION CLOSE DATABASE LINK dh;
     
-- 强制使用 hash 连接
-- 适用于大量数据的连接，将两个表中较小的表连接列建立一个hash表，
--放入到内存中，必须有等值条件
  select   /*+ use_hash(t1 t2) */ t1.* from  t1 ,t2  where t1.id=t2.id
  
-- 强制使用 merge 连接
-- 排序融合连接，hash 连接大部分时候都比排序融合连接性能好
-- 如果不是等值条件，即 > < >= <= ，不能使用hash 连接，使用排序连接和嵌套循环连接
  select  /*+ use_merge(t1 t2) */ t1.* from  t1 ,t2  where t1.id=t2.id
 
-- 强制使用 nest_loop 连接, 嵌套循环连接，外部表的每一个行都要和内部表的所有行连接
-- 当表的行数较少的时候，数据库会选择这种连接方式
  select  /*+ use_nl(t1 t2) */ t1.* from  t1 ,t2  where t1.id=t2.id  
    
-- 强制使用 全表扫描而不使用主键
  select  /*+ full(t1) */  * from  t1  
 
-- 强制使用主键而不使用全表扫描 
  select  /*+ index(index_name) */  * from  t1   
   
-- 强制使用并行查询，提高全表扫描效率
  select  /*+ full(t1) parallel(t1,4) */  * from  t1;
     
-- flashback database   sys 用户不支持闪回，sys 为dba 账号
--先关闭数据库
   shutdown immediate;
-- 启动数据库到 mount
   startup mount;
-- 闪回数据库
   flashback database to timestamp to_date('2017-10-17 16:13:00','yyyy-mm-dd hh24:mi:ss'); 
-- 打开数据库到 read only
   alter database open read only;
   alter database 数据库名  open;
   
-- 查询删除的数据 delete , commit 后
  select * from t3 as of timestamp to_timestamp('2017-10-18 11:25:00','yyyy-mm-dd hh24:mi:ss');
  select * from t3 as of scn 2323267;
  
-- 向删除的表中插入数据 
  insert into t3 select * from t3 as of  timestamp to_timestamp('2017-10-18 11:25:00','yyyy-mm-dd hh24:mi:ss');
  
-- 修改闪回恢复区 flashback database 时需要配置 flash_recovery_area
  alter system set db_recovery_file_dest_size=3g scope=both;
  alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
  show parameter db_recovery_file_dest;
  show parameter db_flashback
  
-- 查看闪回恢复区
  show parameter db_recovery  
  
-- 查看闪回恢复区的使用情况,在11.2以后，v$flash_recovery_area_usage已经被v$recovery_area_usage取代
  select * from v$flash_recovery_area_usage;
  
-- 查看 flashback 状态
  select flashback_on from v$database;
  
--查询当前的scn 号，
  select current_scn from v$database;
  
-- 恢复数据库到特定的scn
  flashback database to scn  2323267;
  
-- 查看事务对应的scn
  select xid,commit_scn,commit_timestamp,operation,undo_sql
  from flashback_transaction_query q
  where q.xid in(select versions_xid
  from B versions between scn 413946 and 413959);
  
-- 闪回时，需要启用表 row movement
  alter table  t3 enable row movement;
  alter table t3 disable row movement;
  
-- 查看表结构
   desc 表名
  
-- 以dba连接数据库  
  connect  用户名/密码 as sysdba;
  
-- 与rowid 一样， ora_rowscn 也是伪列
  select ora_rowscn from  表名
  
-- 显示当前用户
   show user;
   
-- 修改oracle 的时间格式
   alter session set nls_date_formate='yyyy-mm-dd hh24:mi:ss'
   
--设置sqlplus 窗口的SQL> 提示
  set sqlprompt"_user'@'_connect_identifier _date>"

-- oracle 查询列名合并
select distinct b.s_stuffid, b.s_stuffname ||'(' || u.s_username || ')' s_stuffname
from US_B_STUFF_DICT b