ExaGridDba

maximum possible number of rows in one block

Nov 24th, 2017
133
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.81 KB | None | 0 0
  1. What is the practical maximum number of rows that can stored on a single block?
  2. Not theoretical.
  3. Also, one column, all all rows null, is not very "practical".
  4. My answer: 2967 rows.
  5. No change for:
  6. initrans
  7. maxtrans
  8. compress
  9. bigfile
  10.  
  11. SQL> create smallfile tablespace sfts32 blocksize 32k datafile size 16m autoextend on next 16m;
  12.  
  13. Tablespace created.
  14.  
  15. SQL> create table tsfts32 ( c char(1) ) tablespace sfts32 pctfree 0;
  16.  
  17. Table created.
  18.  
  19. SQL> insert into tsfts32 select null from dual connect by level <= 1000000;
  20.  
  21. 1000000 rows created.
  22.  
  23. SQL> select max(dbms_rowid.rowid_row_number(rowid)) from tsfts32;
  24.  
  25. MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID))
  26. ---------------------------------------
  27. 2966
  28.  
  29.  
  30. SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,user,'TSFTS32') from tsfts32 where rownum = 1;
  31.  
  32. DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,USER,'TSFTS32')
  33. ------------------------------------------------------
  34. 1032
  35.  
  36. SQL> select dbms_rowid.rowid_block_number(rowid) from tsfts32 where rownum = 1;
  37.  
  38. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  39. ------------------------------------
  40. 419
  41.  
  42.  
  43. SQL> alter system dump datafile 1032 block 419;
  44.  
  45. System altered.
  46.  
  47. SQL> @ tracefilename.sql
  48.  
  49. TRACEFILENAME
  50. ----------------------------------------------------------------------------------------------------
  51. /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_19214.trc
  52.  
  53. data_block_dump,data header at 0x7fe87b727064
  54. ===============
  55. tsiz: 0x7f98
  56. hsiz: 0x1740
  57. pbl: 0x7fe87b727064
  58. 76543210
  59. flag=--------
  60. ntab=1
  61. nrow=2967
  62. frre=-1
  63. fsbo=0x1740
  64. fseo=0x5cd3
  65. avsp=0x9
  66. tosp=0x9
  67. 0xe:pti[0] nrow=2967 offs=0
  68. etc.
  69. tab 0, row 2966, @0x5d9f
  70. tl: 3 fb: --H-FL-- lb: 0x1 cc: 0
  71. end_of_block_dump
Add Comment
Please, Sign In to add comment