Oracle 小知識點（備忘）____Oracle

--①系統變更號--擷取資料時間插入時間  ora_rowscn返回行最近一次修改的scn.select scn_to_timestamp(ora_rowscn),tt.* from tt;--scn與timestamp轉換select timestamp_to_scn(sysdate) from dual ;select to_char(scn_to_timestamp(timestamp_to_scn(sysdate)),'yyyy-mm-dd hh24:mi:ss') from dual ;--②wm_concat 與listagg/*wm_concat就存在10g 11g  之前用自訂函數 strcat11g 及之後存在listaggwm_concat 可以去重  listagg 可以排序*/select tt.id,       wm_concat(distinct tt.name) name1,       listagg(tt.name, ';') within group(order by name) name2  from tt group by tt.id;  --③計算運算式select dbms_aw.eval_number('20*20') from dual ;  例子計算1-10  合為10的組合  跟connect_by_path() 結合使用with t as (select 1 a from dual  union all  select 2 a from dual  union all  select 3 a from dual  union all  select 4 a from dual  union all  select 5 a from dual  union all  select 6 a from dual  union all  select 7 a from dual  union all  select 8 a from dual  union all  select 9 a from dual),  t1 as (  select t.*,         dbms_aw.eval_number(substr(sys_connect_by_path(a, '+'), 2)) dd,         substr(sys_connect_by_path(a, '+'), 2) cc    from t  connect by nocycle a > prior a   and level <=4  )          select * from t1 where dd =10;--④ 遷移資料是 禁用/恢複 約束ALTER TABLE test_phone_tab disable constraint test_phone_pk;ALTER TABLE test_phone_tab enable constraint test_phone_pk; --⑤分區可以有預設分區 （自己思維定式 未仔細想過）   列表可存多個值

<span style="font-family: Arial, Helvetica, sans-serif;">create table t_partition_list (id number,name varchar2(50))   </span>

 partition by list(id) (    partition t_list_p1 values (1,2,3,4,5,6,7,8,9) ,    --可以多個值   partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) ,       partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29),        partition t_list_pd values (default) ）;   --可以有預設分區  需要制定 /*****定界分割  < maxvalue  預設分區 ***/    

  /*****雜湊分割  本來就是隨機分配分區 ***/  

       insert into  t_partition_list      select level+15,'name'||level       from dual connect by level <26      select * from t_partition_list partition (t_list_p2)    ;        select * from t_partition_list partition (t_list_p3)     ;       select * from t_partition_list partition (t_list_pd);

--oracle 子分區模板  subpartition template  add at 2016.08.25create table csdn_sub_part(id number,grade varchar2(10),sal number)partition by list (grade) subpartition by range (sal)subpartition template   --此處關鍵字(subpartition subpart_20 values less than (20) ,subpartition subpart_40 values less than (40) ,subpartition subpart_60 values less than (60) ,subpartition subpart_80 values less than (80) ,subpartition subpart_100 values less than (100) ,subpartition subpart_other values less than (maxvalue)   --range 預設分區)(partition p_01 values ('A','B'),partition p_02 values ('C','D','E'),partition p_03 values (default)   --list 預設分區)--增加住分區 利用子分區模板alter table  csdn_sub_part add  partition p_04 values ('F');------------- ora-14233：在default分區存在時無法添加分區---/*先刪除default分區 （備份）建立新的子分區   預設分區重新insert進表  分區儲存*/create table zz_dl asselect * from csdn_sub_part where 1=0;alter table csdn_sub_part  exchange partition p_03 with table  zz_dl;--丫的組合分區 無法exchange insert into zz_dlselect * from csdn_sub_part partition (p_03);alter table csdn_sub_part drop partition p_03;alter table csdn_sub_part add partition p_03 values ('F');--ora-14020:不能指定表分區的物理屬性  values 寫成 valuealter table csdn_sub_part add partition p_default  values (default);insert into csdn_sub_partselect * from zz_dl;commit;

--⑥JOB執行多個任務

what      => 'sp_emp_01;sp_emp_02;'

或者(貌似一個意思)

what      => 'beginsp_emp_01;sp_emp_02;end;'

⑦ sqlplus c 和 edit 這兩個功能 

c/t/t1 記錄上次執行的命令 具體 c/t/t1啥意思位置
edit 編輯上次執行的命令 修改後可 c/t/t1查詢

⑧、子查詢 結果顯示為cursorselect d.deptno,       d.dname,       cursor (select e.empno, e.ename, e.sal                 from emp e                where d.deptno = e.deptno) as details  from dept d⑨、普通錶轉變成分區表  ① 建立一個新的分區表 insert into 分區表  ② 穿件一個新的分區表 建立分區對應的幾個基礎資料表  exchange 語句  ③ 線上重新定義 DBMS_REDEFINITION--線上重新定義（普通表邊分區表）--  ① 建立一個新的分區表 insert into 分區表--  ② 穿件一個新的分區表 建立分區對應的幾個基礎資料表  exchange 語句--  ③ 線上重新定義 DBMS_REDEFINITION--① 建立一個新的分區表 insert into 分區表create table comm_table(id number, name varchar2(20), data_date varchar2(8)); select * from comm_table;insert /*+append*/into comm_table nologging  select level, level || 'name', '20161201'    from dual  connect by level <= 100000;   commit;select * from comm_table;create table part_table(id number, name varchar2(20), data_date varchar2(8))partition by list (data_date) (partition part_20161201 values ('20161201') tablespace users, partition part_20161202 values ('20161202') tablespace users, partition part_20161203 values ('20161203') tablespace users );insert/*+append*/ into part_table nologgingselect /*+parallel(a,4)*/  * from comm_table a;select * from part_table partition(part_20161203);--② 穿件一個新的分區表 建立分區對應的幾個基礎資料表  exchange 語句create table part_table2(id number, name varchar2(20), data_date varchar2(8))partition by list (data_date) (partition part_20161201 values ('20161201') tablespace users, partition part_20161202 values ('20161202') tablespace users, partition part_20161203 values ('20161203') tablespace users );  alter table part_table2 exchange partition part_20161201 with table comm_table; --ora-14099 未指定分區限定表中的所有行 create table comm_table_1201 as select /*+parallel(a,4)*/ * from  comm_table a where a.data_date='20161201';  create table comm_table_1202 as select /*+parallel(a,4)*/ * from  comm_table a where a.data_date='20161202'; create table comm_table_1203 as select /*+parallel(a,4)*/ * from  comm_table a where a.data_date='20161203'; select * from comm_table_1202;   alter table part_table2 exchange partition part_20161201 with table comm_table_1201;  alter table part_table2 exchange partition part_20161202 with table comm_table_1202;  alter table part_table2 exchange partition part_20161203 with table comm_table_1203;  select * from part_table2;  select * from comm_table_1201;  --③ 線上重新定義 DBMS_REDEFINITION--源表create table demo as select empno,ename,sal,deptno from scott.emp;--沒主鍵不行啊alter table demo add constraint demo_pk primary key(empno);create index demo_idx on demo(ename);--檢測重定義合理性begin   DBMS_REDEFINITION.CAN_REDEF_TABLE('logminer', 'demo');end;--中間表create table demo_tmp partition by range(deptno) (  partition p1 values less than (11),   partition p2 values less than (21),  partition p3 values less than (31)  )  as select * from demo where 1=2;--執行線上重定義 BEGIN    DBMS_REDEFINITION.START_REDEF_TABLE('LOGMINER', 'DEMO', 'DEMO_TMP'); END;  select object_id,object_name,object_type,status from user_objects; 我們注意到Oracle建立了兩張表RUPD$_DEMO和MLOG$_DEMO， 其實Oracle線上重定義是通過物化視圖的LOG來實現的。做完這一步後，在中間表中也有了相同的資料 select * from DEMO; select * from DEMO_TMP;--結束線上重定義begin    DBMS_REDEFINITION.FINISH_REDEF_TABLE('LOGMINER', 'DEMO', 'DEMO_TMP');end;select * from DEMO;--成功/**************結語************//*     需要建立主鍵，（索引可無），建立中間表，實用性不太好，記住這個功能*/⑩、pl/sql的data genertor選擇 使用者，表之後，在data中選擇想要的資料，產生隨機數不錯[圖片]⑩①、歸檔日誌滿了sqlplus /nologconnect /as sysdbaselect * from V$FLASH_RECOVERY_AREA_USAGE;select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=8g;show parameter recover;rmanconnect target sys/sys_passwdDELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';