Data Blocks, Extents, Segments<\/p>\n
\u00a7Data Blocks
\n\u2022Overhead
\n\tHeader: block addr, seg type
\n\tTable dir: tables having rows
\n\tRow dir: actual rows info
\n\u2022Free space
\n\u2022Row data<\/p>\n
\u00a7PCTFREE, PCTUSED, Row Chaining
\n\u2022PCTFREE: pct of block spc reseved for updates
\n\u2022PCTUSED: before new rows can be inserted
\n\u2022Row Chaining: a row spanning more than two data blocks
\n\u2022Row Migrating: caused by update<\/p>\n
\u00a7Extents
\n\u2022Locally managed
\n-reduce data dictionry contention
\n-don’t care number of extents a segment acquires
\n-easier space management
\n-never fragment with uiniform policy<\/p>\n
\u2022Dictionary managed<\/p>\n
Estimate orcle table size Index<\/strong><\/p>\n – 5% rule: Data Blocks, Extents, Segments \u00a7Data Blocks \u2022Overhead Header: block addr, seg type Table dir: tables having rows Row dir: actual rows info \u2022Free space \u2022Row data \u00a7PCTFREE, PCTUSED, Row Chaining \u2022PCTFREE: pct of block spc reseved for updates \u2022PCTUSED: before … Continue reading
\n\u2022ANALYZE TABLE
\n\u2022SELECT avg_row_len
\nFROM dba_tables
\nWHERE table_name=’
\n\u2022rough estimate of table size
\navg_row_len*number_of_rows*(1+PCTFREE\/100)*1.15<\/p>\n
\nuse idx when accessing < 5% of rows.\n\n- concatenated indexes:\nmust include leading columns\n\n-DBA_INDEXES\n-DBA-IND_COLUMNS\n-USER_INDEXES\n-USER_IND_COLUMNS\n\nLocally managed tablespace\n\u2022available since 8.1.5\n\u2022minimum extent size is 64k\n\u2022size default to 1m\n\u2022default to autoallocate\n\n\u2022ex1\ncreate tablespace local_uniform_size datafile 'e:\\ora816\\db\\local_tbs01.dbf' size 10m extent management local uniform size 10k;\n\n\u2022ex2\ncreate tablespace local_uniform_size datafile 'e:\\ora816\\db\\local_tbs01.dbf' size 10m extent management local autoallocate;\n\n\u2022check tablespace info\nselect substr(tablespace_name,1,20) Tablespace, initial_extent\/1024 \"I-Ex(kb)\", next_extent\/1024 \"N-Ex(kb)\", min_extlen, extent_management, allocation_type from dba_tablespaces where tablespace_name like 'LOC%';\n\n\u2022Migrate to lmt (available 8.1.6)\nexecute SYS.dbms_space_admin.tablespace_migrate_TO_local('SAMTABLESPACE');\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"