Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <<rows per block>> =
- floor( <<database block size>> *
- (1 - <<pctfree of table>>/100) /
- <<size of row>> ) + 1
- <<size of table>> =
- ceil( <<number of rows in table>> / <<rows per block>> ) *
- <<database block size>>
- SQL> create table foo(
- 2 foo_id number,
- 3 foo_str varchar2(100)
- 4 );
- Table created.
- <<rows per block>> =
- floor( 8192 *
- (1 - 10/100) /
- 56 ) + 1
- <<size of table>> =
- ceil( 1000000 / 132 ) *
- 8192
- SQL> ed
- Wrote file afiedt.buf
- 1 insert into foo
- 2 select level, dbms_random.string( 'p', dbms_random.value(1,100))
- 3 from dual
- 4* connect by level <= 1000000
- SQL> /
- 1000000 rows created.
- SQL> exec dbms_stats.gather_table_stats( 'SCOTT', 'FOO' );
- PL/SQL procedure successfully completed.
- SQL> ed
- Wrote file afiedt.buf
- 1 select avg_row_len, num_rows
- 2 from user_tables
- 3* where table_name = 'FOO'
- SQL> /
- AVG_ROW_LEN NUM_ROWS
- ----------- ----------
- 56 1000000
- SQL> select sum(bytes)/1024/1024 mb
- 2 from user_segments
- 3 where segment_name = 'FOO';
- MB
- ----------
- 72
- SQL> ed
- Wrote file afiedt.buf
- 1 select bytes, count(*)
- 2 from user_extents
- 3 where segment_name = 'FOO'
- 4 group by bytes
- 5* order by bytes
- SQL> /
- BYTES COUNT(*)
- ---------- ----------
- 65536 16
- 1048576 63
- 8388608 1
- SQL> ed
- Wrote file afiedt.buf
- 1 DECLARE
- 2 l_space_used NUMBER;
- 3 l_space_allocated NUMBER;
- 4 l_chained_pct NUMBER;
- 5 BEGIN
- 6 dbms_space.object_space_usage(
- 7 'SCOTT',
- 8 'FOO',
- 9 'TABLE',
- 10 NULL,
- 11 l_space_used,
- 12 l_space_allocated,
- 13 l_chained_pct);
- 14 dbms_output.put_line('Space Used: ' || TO_CHAR(round(l_space_used/1024/1024,2)) || ' MB');
- 15 dbms_output.put_line('Space Allocated: ' || TO_CHAR(l_space_allocated/1024/1024) || ' MB');
- 16 dbms_output.put_line('Chained Percentage: ' || TO_CHAR(l_chained_pct));
- 17* END;
- SQL> /
- Space Used: 66.22 MB
- Space Allocated: 72 MB
- Chained Percentage: 0
- PL/SQL procedure successfully completed.
- SQL> ed
- Wrote file afiedt.buf
- 1 DECLARE
- 2 l_used_bytes NUMBER;
- 3 l_allocated_bytes NUMBER;
- 4 BEGIN
- 5 dbms_space.create_table_cost('USERS',
- 6 56,
- 7 1000000,
- 8 10,
- 9 l_used_bytes,
- 10 l_allocated_bytes);
- 11 dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
- 12 dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
- 13* END;
- SQL> /
- Used Bytes: 63.52 MB
- Alloc Bytes: 64 MB
- PL/SQL procedure successfully completed.
- SQL> ed
- Wrote file afiedt.buf
- 1 DECLARE
- 2 l_used_bytes NUMBER;
- 3 l_allocated_bytes NUMBER;
- 4 l_cols sys.create_table_cost_columns;
- 5 BEGIN
- 6 l_cols := sys.create_table_cost_columns(
- 7 sys.create_table_cost_colinfo('NUMBER',10),
- 8 sys.create_table_cost_colinfo('VARCHAR2',100));
- 9 dbms_space.create_table_cost('USERS',
- 10 l_cols,
- 11 1000000,
- 12 10,
- 13 l_used_bytes,
- 14 l_allocated_bytes);
- 15 dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
- 16 dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
- 17* END;
- SQL> /
- Used Bytes: 67.94 MB
- Alloc Bytes: 72 MB
- PL/SQL procedure successfully completed.
Add Comment
Please, Sign In to add comment