170 likes | 318 Views
Miscellaneous Topics. Appendix Overview. BFT ROWID TTG. This appendix assists you to: Use bigfile tablespaces for very large databases (VLDB) Describe the row IDs for bigfile tablespaces Use temporary tablespace groups (TTG) for VLDB. Bigfile Tablespaces: Overview.
E N D
Appendix Overview BFT ROWID TTG • This appendix assists you to: • Use bigfile tablespaces for very large databases (VLDB) • Describe the row IDs for bigfile tablespaces • Use temporary tablespace groups (TTG) for VLDB
Bigfile Tablespaces: Overview • A bigfile tablespace contains a single file. • The maximum file size ranges from 8 TB through 128 TB. • Tablespaces are logically equivalent to data files. Database Tablespace Datafile Datafile SMALLFILE BIGFILE
Benefits of Bigfile Tablespaces • Significantly increase the storage capacity • Simplify data file management for large databases by making tablespaces the main units of disk space administration … BFT n BFT 1 One-to-one mapping 8 EB 4 billionblocks
Database Block Size • Recommended Maximum Number of Extents • 2 KB • 100,000 • 4 KB • 200,000 • 8 KB • 400,000 • 16 KB • 800,000 Bigfile Tablespace: Usage Model • BFTs are supported only for locally managed tablespaces using Automatic Segment Space Management. • Use BFTs with logical volume managers or Automatic Storage Management. • OMF provides complete data file transparency.
SQL Statement Clauses SMALLFILE| BIGFILE Database Tablespace Smallfile Bigfile Data file Data file K | M | G | T
BFTs and SQL Statements: Examples CREATE DATABASE SET DEFAULT BIGFILE TABLESPACE DATAFILE '/u0/data/system.dbf' SIZE 200M SYSAUX DATAFILE '/u0/data/sysaux.dbf' SIZE 300M SMALLFILE DEFAULT TEMPORARY TABLESPACE stemp_tbs TEMPFILE '/u3/data/stemp_tbs1.dbf' SIZE 60M SMALLFILE UNDO TABLESPACE sundo_tbs DATAFILE '/u2/data/sundo_tbs1.dbf' SIZE 100M; 1 ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE; 2 CREATE BIGFILE UNDO TABLESPACE bundo_tbs DATAFILE '/u1/data/bundo_tbs.dbf' SIZE 1G; 3 4 ALTER TABLESPACE users RESIZE 2G; 5 ALTER TABLESPACE users AUTOEXTEND ON ;
Data Dictionary AdditionsTo Support VLDB SELECT property_value FROM database_properties WHERE property_name='DEFAULT_TBS_TYPE'; SELECT tablespace_name, bigfile FROM DBA_TABLESPACES; SELECT name, bigfile FROM V$TABLESPACE;
Extended ROWID Format and BFTs . BFT > ROWID TTG DBMS_ROWID.ROWID_BLOCK_NUMBER Bigfile tablespaces Smallfile tablespaces OOOOOO FFF BBBBBB RRR OOOOOO LLL LLLLLL RRR Rownumber Data objectnumber Data blocknumber Data objectnumber Encoded blocknumber Relative filenumber Rownumber
VLDB Support: DBMS_ROWID Package TS_TYPE_IN ROWID_INFO ROWID_BLOCK_NUMBER ROWID_RELATIVE_FNO SELECTDBMS_ROWID.ROWID_RELATIVE_FNO(ROWID,'BIGFILE') FROM employees;
Temporary Tablespace Group (TTG): Overview BFT ROWID > TTG • Groups multiple temporary tablespaces • Characteristics: • At least one temporary tablespace • Same namespace as tablespaces • Created implicitly on first assignment • No explicit deletion Default temporary tablespace group TEMP Default tablespace EXAMPLE Tablespace TEMP1 Tablespace TEMPn …
Temporary Tablespace Group: Benefits • Enables the use of multiple temporary tablespaces: • For the same user in multiple sessions • For slave processes in one parallel operation • For configuration at database level HR HR Serial Parallel Tablespace TEMP1 Tablespace TEMP2 Tablespace TEMP3 Temporary tablespace group TEMP
Temporary Tablespace Group: SQL Examples CREATE TEMPORARY TABLESPACE temp1 TEMPFILE 'tmp1.f' SIZE 100M TABLESPACE GROUP group1; 1 CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'tmp2.f' SIZE 200M TABLESPACE GROUP group2; 2 CREATE TEMPORARY TABLESPACE temp3 TEMPFILE 'tmp3.f' SIZE 50M TABLESPACE GROUP ''; 3
Temporary Tablespace Group: SQL Examples ALTER TABLESPACE temp3 TABLESPACE GROUP group2; 1 ALTER TABLESPACE temp2 TABLESPACE GROUP ''; 2 ALTER TABLESPACE temp1 TABLESPACE GROUP group2; 3 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2; 4 CREATE USER hr IDENTIFIED BY hr DEFAULT TABLESPACE TS1 TEMPORARY TABLESPACE group2; 5 ALTER USER hr TEMPORARY TABLESPACE group2; 6
Summary BFT ROWID TTG • In this appendix, you should have learned how to: • Use bigfile tablespaces for very large databases (VLDB) • Describe the row IDs for bigfile tablespaces • Use temporary tablespace groups (TTG) for VLDB