Oracle Small Knowledge Point (Memo) ____oracle

--① System Change number--Gets the data time insertion time ORA_ROWSCN the SCN that returned the last modification of the row.
Select Scn_to_timestamp (ORA_ROWSCN), tt.* from TT;
--SCN and timestamp convert 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 and Listagg/*wm_concat exist before 10g 11g with a custom function strcat 11g and then exist Listagg Wm_concat can go heavy listagg can be sorted/select Tt.id,  Wm_concat (distinct tt.name) name1, Listagg (Tt.name, '; ') within group (order by name) Name2 from TT Group
 
 
by Tt.id;
  --③ computes the expression select Dbms_aw.eval_number (' 20*20 ') from dual; 
  The example calculates that the combination of 1-10 and 10 is in conjunction with the Connect_by_path () with the T as (select 1 A from dual union ALL select 2 A and dual union all Select 3 A from dual union ALL select 4 A and dual union ALL select 5 A to dual union ALL select 6 a from D UAL UNION ALL Select 7 A-dual union ALL select 8 A and dual union ALL select 9 A from dual), T1 as (SE Lect 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;
--④ migration Data is a disable/restore constraint ALTER TABLE test_phone_tab disable constraint test_phone_pk; 


ALTER TABLE test_phone_tab enable constraint test_phone_pk; The--⑤ partition can have a default partition (it has not been carefully thought out) list can save multiple values

<span style= "font-family:arial, Helvetica, Sans-serif;" >create table t_partition_list (ID number,name varchar2) </span>

Partition by list (ID)
(partition T_LIST_P1 values (1,2,3,4,5,6,7,8,9)-  -Multiple values can be
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));   --can have default partitions  need to develop
/***** range partitioning  < MaxValue  default partitions ***/

  /***** hash partitions  are randomly allocated partitions ***/

--oracle Sub-partition template subpartition template  add at 2016.08.25 CREATE TABLE Csdn_sub_part (ID number, grade VARCHAR2 (10), Sal number) partition by list (grade) Subpartition by range (SAL) subpartition template-Keyword here (subpartition subpart_ 
Values less than, subpartition subpart_40 values less than (), subpartition subpart_60 values less than (60), Subpartition subpart_80 values less than, subpartition subpart_100 values less than (MB), Subpartition Subpart_ot Her values less than (maxvalue)--range default partition) (Partition p_01 values (' A ', ' B '), partition p_02 values (' C ', ' D ', ' E '), p Artition p_03 VALUES (default)--list defaults)--Increase the partition using the sub-partition template ALTER TABLE Csdn_sub_part add partition values (' F
');  -------------ora-14233: Unable to add partition when default partition is present---/* Delete default partition (Backup) Create new sub partition defaults insert back into table partition store/CREATE TABLE ZZ_DL
As SELECT * from Csdn_sub_part where 1=0;
ALTER TABLE Csdn_sub_part Exchange partition p_03 with table zz_dl; --Ya's combination partition cannot exchange insert INto ZZ_DL SELECT * 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: Cannot specify the physical attributes of the table partition values are written as value ALTER TABLE Csdn_sub_part add partition P_default values (default);
INSERT INTO Csdn_sub_part select * from ZZ_DL; Commit

--⑥job Perform multiple tasks

What      => ' sp_emp_01;sp_emp_02; '

Or (seemingly a meaning)

What      => '
begin
sp_emp_01;
sp_emp_02;
End; '

⑦sqlplus C and edit these two features

C/T/T1 records last executed command specific C/T/T1 What do you mean position
Edit edit Last Command modified to C/T/T1 query

⑧, subquery results are displayed as cursor select 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⑨, Common table transition ingredient table ① create a new partition table insert into partition table ② wear One New partition table create partition corresponding to several underlying tables Exchange statement ③ online redefinition dbms_redefinition--online redefinition (normal table side partition table)--① create a new partition table insert into partition table--② wear a new partition table create partition corresponding to several basic tables Exchange statements--③ online redefine dbms_redefinition--① create a new partition table insert into Partition table CREATE table comm_table (ID n
 

Umber, name Varchar2 (m), 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 (), Data_date varchar2 (8)) partition by list (data_date) (partition part_20161201 values (' 20161201 ') tablespace users, partition part_20161202 values (' 20161202 ') tablespace users, Partit Ion Part_20161203 values (' 20161203 ') tablespace users);

insert/*+append*/into the part_table nologging select/*+parallel (a,4) * * from comm_table A;


SELECT * from part_table partition (part_20161203); --② wear a new partition table create a partition corresponding to several underlying tables Exchange statement creation table Part_table2 (ID number, name varchar2 (), Data_date varchar2 (8)) p Artition by List (data_date) (Partition part_20161201 values (' 20161201 ') tablespace users, Partition part_20161202
 
 Es (' 20161202 ') tablespace users, partition part_20161203 values (' 20161203 ') tablespace users);
 ALTER TABLE PART_TABLE2 Exchange partition part_20161201 with table comm_table; --ora-14099 does not specify all row CREATE table comm_table_1201 as select/*+parallel (a,4) * * from comm_table a where a.data_d
 
 Ate= ' 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;
--③ Online Redefine dbms_redefinition--source table CREATE table demo as select Empno,ename,sal,deptno from Scott.emp;

--no primary key. ALTER TABLE DEMO add constraint DEMO_PK primary key (EMPNO);

Create INDEX Demo_idx on demo (ename); --Detect redefinition rationality begin dbms_redefinition.
Can_redef_table (' Logminer ', ' demo ');
End --middle Table CREATE table Demo_tmp partition by Range (DEPTNO) (partition P1 values less than (one), partition P2 values le

SS than (partition P3 values less than) as SELECT * from demo where 1=2; --Performs an online redefinition of the BEGIN dbms_redefinition.
 Start_redef_table (' Logminer ', ' DEMO ', ' demo_tmp ');
 
 End; Select Object_id,object_name,object_type,statusFrom User_objects; We note that Oracle has created two new tables Rupd$_demo and Mlog$_demo, but Oracle online redefinition is achieved through materialized view log.
 After this step, there is also the same data select * from DEMO in the middle table;

SELECT * from Demo_tmp; --Ends the online redefinition of the Begin dbms_redefinition.
Finish_redef_table (' Logminer ', ' DEMO ', ' demo_tmp ');

End
SELECT * from DEMO; --Successful/************** conclusion ************//* need to create a primary key, (index can not), create the middle table, the practicality is not very good, remember this function */⑩, pl/sql data genertor Select Users, after the table, in the data selected
Choose the desired data, generate random numbers good [picture]⑩①, archive log full sqlplus/nolog connect/as sysdba select * 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; Rman Connect target sys/sys_passwd DELETE archivelog all COMPLETED before ' SYSDATE-7 ';