• dump iot表


    SQL> create user scan identified by scan default tablespace users;
    
    User created.
    
    SQL> grant dba to scan;
    
    Grant succeeded.
    
    
    
    
    create table t2 (id int primary key,a1 char(10),a2 char(10))organization index; 
    
    
    begin 
    for i in 1 .. 25 
    loop 
    insert into t2 values(i,i,'a'||i); 
    end loop 
    ; 
    commit; 
    end;
    
    
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCAN',
                                    tabname          => 'T2',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size repeat',
                                    no_invalidate    => FALSE,
                                    degree           => 8,
                                    cascade          => TRUE);
    END;
    / 
    
    
    SQL> select object_name,object_id from user_objects; 
    
    OBJECT_NAME			OBJECT_ID
    ------------------------------ ----------
    SYS_C00147518			   260454
    T3				   260453
    SYS_IOT_TOP_260451		   260452
    T1				   260449
    T2				   260451
    SYS_C00147516			   260450
    
    6 rows selected.
    
    
    SQL>  select index_name,index_type,table_owner,TABLE_NAME,TABLE_TYPE from user_indexes;
    
    INDEX_NAME		       INDEX_TYPE		   TABLE_OWNER			  TABLE_NAME			 TABLE_TYPE
    ------------------------------ --------------------------- ------------------------------ ------------------------------ -----------
    SYS_C00147516		       NORMAL			   SCAN 			  T1				 TABLE
    SYS_IOT_TOP_260451	       IOT - TOP		   SCAN 			  T2				 TABLE
    SYS_C00147518		       NORMAL			   SCAN 			  T3				 TABLE
    
    
    
    IOT表排序:
    SQL> select * from t2;
    
    	ID A1	      A2
    ---------- ---------- ----------
    	 1 1	      a1
    	 2 2	      a2
    	 3 3	      a3
    	 4 4	      a4
    	 5 5	      a5
    	 6 6	      a6
    	 7 7	      a7
    	 8 8	      a8
    	 9 9	      a9
    	10 10	      a10
    	11 11	      a11
    
    	ID A1	      A2
    ---------- ---------- ----------
    	12 12	      a12
    	13 13	      a13
    	14 14	      a14
    	15 15	      a15
    	16 16	      a16
    	17 17	      a17
    	18 18	      a18
    	19 19	      a19
    	20 20	      a20
    	21 21	      a21
    	22 22	      a22
    
    	ID A1	      A2
    ---------- ---------- ----------
    	23 23	      a23
    	24 24	      a24
    	25 25	      a25
    
    25 rows selected.
    
    SQL> select * from t2 order by t2.id;
    
    	ID A1	      A2
    ---------- ---------- ----------
    	 1 1	      a1
    	 2 2	      a2
    	 3 3	      a3
    	 4 4	      a4
    	 5 5	      a5
    	 6 6	      a6
    	 7 7	      a7
    	 8 8	      a8
    	 9 9	      a9
    	10 10	      a10
    	11 11	      a11
    
    	ID A1	      A2
    ---------- ---------- ----------
    	12 12	      a12
    	13 13	      a13
    	14 14	      a14
    	15 15	      a15
    	16 16	      a16
    	17 17	      a17
    	18 18	      a18
    	19 19	      a19
    	20 20	      a20
    	21 21	      a21
    	22 22	      a22
    
    	ID A1	      A2
    ---------- ---------- ----------
    	23 23	      a23
    	24 24	      a24
    	25 25	      a25
    
    25 rows selected.
    
    
    
    
    
    /********dump iot表**********************/
    
    
    将索引dump到trace文件中
    SQL> select value from v$diag_info where name='Default Trace File';
    
    VALUE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/ncdb/podinndb/trace/podinndb_ora_29689.trc
    
    SQL> alter session set events 'immediate trace name treedump level 260452';
    
    会话已更改。
    
    ----- begin tree dump
    leaf: 0x1000093 16777363 (0: nrow: 25 rrow: 25)
    ----- end tree dump
    
    
    SQL> select dbms_utility.data_block_address_file(16777363)fno,
    dbms_utility.data_block_address_block(16777363) bkno from dual   2  
      3  ;
    
           FNO	 BKNO
    ---------- ----------
    	 4	  147
    
    
    alter system dump datafile 4 block 147;
    
    
    row#0[8002] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 02    --表示1
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 20 20 20 20 20 20 20 20 20  --表示1
    col  1: [10]  61 31 20 20 20 20 20 20 20 20  --表示a1 
    row#1[7972] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 03     --表示2
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2      
    col  0: [10]  32 20 20 20 20 20 20 20 20 20   --2
    col  1: [10]  61 32 20 20 20 20 20 20 20 20   --表示a2
    row#2[7942] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 04
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  33 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 33 20 20 20 20 20 20 20 20
    row#3[7912] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 05
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  34 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 34 20 20 20 20 20 20 20 20
    row#4[7882] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 06
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  35 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 35 20 20 20 20 20 20 20 20
    row#5[7852] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 07
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  36 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 36 20 20 20 20 20 20 20 20
    row#6[7822] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 08
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  37 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 37 20 20 20 20 20 20 20 20
    row#7[7792] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 09
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  38 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 38 20 20 20 20 20 20 20 20
    row#8[7762] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0a
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  39 20 20 20 20 20 20 20 20 20
    col  1: [10]  61 39 20 20 20 20 20 20 20 20
    row#9[7732] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0b
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 30 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 30 20 20 20 20 20 20 20
    row#10[7702] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0c
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 31 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 31 20 20 20 20 20 20 20
    row#11[7672] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0d
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 32 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 32 20 20 20 20 20 20 20
    row#12[7642] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0e
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 33 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 33 20 20 20 20 20 20 20
    row#13[7612] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 0f
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 34 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 34 20 20 20 20 20 20 20
    row#14[7582] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 10
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 35 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 35 20 20 20 20 20 20 20
    row#15[7552] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 11
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 36 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 36 20 20 20 20 20 20 20
    row#16[7522] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 12
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 37 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 37 20 20 20 20 20 20 20
    row#17[7492] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 13
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 38 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 38 20 20 20 20 20 20 20
    row#18[7462] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 14
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  31 39 20 20 20 20 20 20 20 20
    col  1: [10]  61 31 39 20 20 20 20 20 20 20
    row#19[7432] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 15
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 30 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 30 20 20 20 20 20 20 20
    row#20[7402] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 16
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 31 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 31 20 20 20 20 20 20 20
    row#21[7372] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 17
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 32 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 32 20 20 20 20 20 20 20
    row#22[7342] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 18
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 33 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 33 20 20 20 20 20 20 20
    row#23[7312] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 19
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 34 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 34 20 20 20 20 20 20 20
    row#24[7282] flag: K-----, lock: 2, len=30
    col 0; len 2; (2):  c1 1a
    tl: 25 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [10]  32 35 20 20 20 20 20 20 20 20
    col  1: [10]  61 32 35 20 20 20 20 20 20 20
    ----- end of leaf block dump -----
    End dump data blocks tsn: 4 file#: 4 minblk 147 maxblk 147
    
     declare  
       n number;  
     begin  
       dbms_stats.convert_raw_value('c102',  
                                    n);  
       dbms_output.put_line(n);  
     end; 
    
    
    DECLARE
       n   VARCHAR2 (2000);
    BEGIN
       DBMS_STATS.convert_raw_value ('34', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
    END;
    
    
    结论:IOT索引存储表的所有数据

  • 相关阅读:
    Vue学习(十七)修饰符
    组件学习(一)开发组件前必读
    vue学习(十六)“就地更新”策略
    nrm学习(一)
    帧动画的多种实现方式与性能对比
    JavaScript中的二进制对象
    生成式模型与判别式模型
    堡垒机
    vue生产环境nginx配置代理转发跨域
    Ceph nautilus 集群部署
  • 原文地址:https://www.cnblogs.com/zhaoyangjian724/p/6199031.html
Copyright © 2020-2023  润新知