1 / 17

Miscellaneous Topics

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.

jace
Download Presentation

Miscellaneous Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Miscellaneous Topics

  2. 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

  3. 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

  4. 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

  5. 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.

  6. Creating Bigfile Tablespaces

  7. SQL Statement Clauses SMALLFILE| BIGFILE Database Tablespace Smallfile Bigfile Data file Data file K | M | G | T

  8. 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 ;

  9. 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;

  10. 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

  11. 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;

  12. 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 …

  13. 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

  14. Creating and Maintaining Temporary Tablespace Groups

  15. 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

  16. 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

  17. 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

More Related