1 / 43

Lecture 4 ORACLE TABLESPACES

Lecture 4 ORACLE TABLESPACES. Database. Tablespace. Segment. Extent. Oracle data block. Storage Structures. Logical. Physical. A database is divided into logical storage units called tablespaces .

jennis
Download Presentation

Lecture 4 ORACLE TABLESPACES

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. Lecture 4 ORACLE TABLESPACES

  2. Database Tablespace Segment Extent Oracle datablock Storage Structures Logical Physical • A database is divided into logical storage units called tablespaces. • Each tablespace has many logical Oracle data blocks with DB_BLOCK_SIZE parameters OS file OSblock

  3. How Table Data Is Stored Columns Blocks Table A Table B Rows Segment Segment Table Tablespace Extent Row piece

  4. Anatomy of a Database Block • Data blocks contain the following: • Block header (segment type, data block address, table directory, row directory, and transaction slots of size 23 bytes each) • Row data (actual data for the rows in the block) • Free space (middle of the block ) Block header Growth Free space Row data

  5. Database Tablespace Data files Tablespaces and Data Files • The Oracle database stores data logically in tablespaces and physically in data files. • Tablespaces: • Can belong to only one database • Consist of one or more data files • Are further divided into logical units of storage • Data files: • Can belong to only onetablespace and one database • Are a repository for schema object data

  6. Oracle Managed Files (OMF) • Specify file operations in terms of database objects rather than file names.(Tablespaces , Redo log files, Control files, Archived logs, Block change tracking files, Flashback logs, RMAN) backups) Example: SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/Emam/oradata'; SQL> CREATE TABLESPACE tbs_1;

  7. Space Management in Tablespaces 1- Locally managed tablespace: • Free extents are managed in the tablespace. • A bitmap is used to record free extents. • Each bit corresponds to a block or group of blocks. • The bit value indicates free or used extents. • The use of locally managed tablespaces is recommended. 2- Dictionary-managed tablespace: • Free extents are managed by the data dictionary. • Appropriate tables are updated when extents are allocated or unallocated. • These tablespaces are supported only for backward compatibility.

  8. Tablespaces Prerequisites • You must have the CREATE TABLESPACE system privilege. • To create the SYSAUX tablespace, you must have the SYSDBA system privilege. • Before you can create a tablespace, you must create a database to contain it, and the database must be open. • You need to define Datafile location by DB_CREATE_FILE_DEST parameter and with size 100 MB. • Name of datafile will be similar to "ora_applicat_abcdefgh000.dbf"

  9. Click the links to view detailed information. Exploring the Storage Structure

  10. Creating a New Tablespace

  11. Storage for Locally Managed Tablespaces

  12. Tablespaces Syntax • CREATE [ BIGFILE | SMALLFILE ] { permanent_tablespace_clause | temporary_tablespace_clause | undo_tablespace_clause } ;

  13. Permanent Tablespace • { MINIMUM EXTENT integer [ K | M ] | BLOCKSIZE integer [ K ] | logging_clause | FORCELOGGING | DEFAULT [ table_compression ] storage_clause | { ONLINE | OFFLINE } | extent_management_clause | segment_management_clause | flashback_mode_clause [ MINIMUM EXTENT integer [ K | M ] | BLOCKSIZE integer [ K ] | logging_clause | FORCELOGGING | DEFAULT [ table_compression ] storage_clause | { ONLINE | OFFLINE } | extent_management_clause | segment_management_clause | flashback_mode_clause ]... }

  14. BIGFILE | SMALLFILE • A bigfiletablespace contains only one datafile or tempfile, which can contain up to 232 or 4G blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks. • A smallfiletablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to 222 or 4M blocks. • If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database.

  15. BLOCKSIZE • BLOCKSIZE : By Default blocksize define in the parameter DB_BLOCK_SIZE. • DEFAULT STORAGE : • INITIAL – Specifies the size of the object's first extent.3 k minmum for Locally and 2 k minimum Dictionary. • NEXT – Specifies the size of the object's sucessive extent. • PCTINCREASE – Specifies the ratio of the third or the preceding extent of the object. The default value for PCTINCREASE is 50 % and the minimum value is 0%. • MINEXTENTS – The total number of extent allocated to the segment at the time of creation • MAXEXTENTS – The maximum number of extent that can be allocated to the segment .

  16. EXTENT • MININUM EXTENT : The extent are multiple of the size specified in this clause .NEXT and INITIAL extent size specified should be multiple of minmum extent. • PERMANENT / TEMPORARY : Permannent is default, use to store the table,index etc. Temporary is for temporay segments can not store table,index in temporary tablespace. • LOGGING / NOLOGGING : Logging is default,the DDL operation & direct insert load are recorded in the redo log file. • ONLINE / OFFLINE : Online is default,tablespace is available as soon as created.

  17. Create Tablespace Examples 1- Creating a BigfileTablespace: The following example creates a bigfiletablespace bigtbs_01 with a datafile bigtbs_f1.dat of 10 MB:  CREATE BIGFILE TABLESPACE bigtbs_01 DATAFILE 'bigtbs_f1.dat' SIZE 20M AUTOEXTEND ON; 2- Creating an Undo Tablespace: The following example creates a 10 MB undo tablespace undots1: CREATE UNDO TABLESPACE undots1 DATAFILE 'undotbs_1a.f' SIZE 10M AUTOEXTEND ON RETENTION GUARANTEE;3- Creating a Temporary Tablespace: The serves assumes as the default temporary tablespace for database users in the sample database was created: CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

  18. UNDOTBS1 USERS EXAMPLE SYSTEM SYSAUX TEMP Tablespaces in the Preconfigured Database

  19. Tablespaces in the Preconfigured Database • SYSTEM : used by the Oracle server to manage the database and it contains the data dictionary and tables that contain administrative information about the database. • SYSAUX : This is an auxiliary tablespace to the SYSTEM tablespace. now use the SYSAUX tablespace. Every Oracle Database10g or later release must have a SYSAUX tablespace. • TEMP • UNDOTBS1 • USERS • EXAMPLE

  20. Actions with Tablespaces

  21. Viewing Tablespace Information

  22. Assign tablespaces to users • Users cannot create objects in a tablespace (even it's their default tablespace) unless they have a quota on it (or UNLIMITED TABLESPACE privilege). • Grant user Emam access to use all space in the tools tablespace: • CREATE TABLESPACE tools DATAFILE ‘/orcl/tools/file_1.dbf' SIZE 100M; • ALTER USER Emam QUOTA UNLIMITED ON tools; • ALTER USER scott QUOTA 0 ON system; • Prevent user scott from using space in the system tablespace:

  23. Altering a Tablespace

  24. Altering a Tablespace • After you create a tablespace, you can later alter it in several ways as the needs of your system change. • Renaming • Changing the status : A tablespace can be in one of the following statuses . • Read Write (The tablespace is online and can be read from and written to). • Read Only ( Specify read-only to place the tablespace in transition read-only mode. • The tablespace is online while in the read-only state. • You cannot make the SYSTEM or SYSAUX tablespace read-only.

  25. ALTER TABLESPACE • Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its datafiles or tempfiles. • To alter the SYSAUX tablespace, you must have the SYSDBA system privilege. • If you have ALTER TABLESPACE system privilege • If you have MANAGE TABLESPACE system privilege, then you can only perform the following operations: • Take the tablespace online or offline • Begin or end a backup • Make the tablespace read only or read write • Before you can make a tablespace read only, the following conditions must be met: The tablespace must be online. • The tablespace must not be involved in an open backup.

  26. ALTER Syntax

  27. ALTER Syntax Cont. ALTER TABLESPACEtablespace { DEFAULT [ table_compression ] storage_clause | MINIMUM EXTENT integer [ K | M ] | RESIZEsize_clause | COALESCE | RENAME TOnew_tablespace_name | { BEGIN | END } BACKUP | datafile_tempfile_clauses | tablespace_logging_clauses | tablespace_group_clause | tablespace_state_clauses | autoextend_clause | flashback_mode_clause | tablespace_retention_clause } ;

  28. ALTER Examples • Rename the datafile using an ALTER TABLESPACE statement with the RENAME DATAFILE clause: ALTER TABLESPACE tbs_02 RENAME DATAFILE 'diskb:tbs_f5.dat' TO 'diska:tbs_f5.dat'; • Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE clause: ALTER TABLESPACE tbs_02 ONLINE; • To adds a datafile to the tablespace • ALTER TABLESPACE tbs_03 ADD DATAFILE 'tbs_f04.dbf' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 100K; • To change the extent size to 128K: • ALTER TABLESPACE tbs_03 MINIMUM EXTENT 128K;

  29. Gathering Storage Information

  30. Viewing Tablespace Contents • 12061_1_sel_ts_3

  31. Dropping Tablespaces

  32. DROP TABLESPACE • DROP TABLESPACE used the DROP TABLESPACE from the database. • Oracle Database does not place it in the recycle bin. • Therefore, you cannot subsequently either purge or undrop the tablespace. • You must have the DROP TABLESPACE system privilege. • You cannot drop a tablespace if it contains any rollback segments holding active transactions. • You cannot drop the SYSTEM tablespace but can drop the SYSAUX tablespace

  33. Restrictions on Dropping Tablespaces • You cannot drop a tablespace that contains a domain index or any objects created by a domain index. • You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions. • You cannot drop a tablespace that has been designated as the default tablespace for the database. • You must first reassign another tablespace as the default tablespace and then drop the old default tablespace. • You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. You must first remove the tablespace from the database default temporary tablespace group and then drop it.

  34. DROP TABLESPACE Syntax

  35. DROP TABLESPACE Example • To drops tbs_01 tablespace and drops all referential integrity constraints that refer to primary and unique keys inside tbs_01: DROP TABLESPACE tbs_01 INCLUDING CONTENTS CASCADE CONSTRAINTS; • To drops the tbs_01 tablespace and deletes all associated operating system datafiles DROP TABLESPACE tbs_01 INCLUDING CONTENTS AND DATAFILES;

  36. Actions with Tablespaces • Add Datafile • Create Like • Generate DDL • Make Locally Managed if the tablespace is currently dictionary managed. • Make Readonly • Make Writable

  37. Making a Tablespace Read-Only • All tablespaces are initially created as read/write. • Use the READ ONLY clause in the ALTER TABLESPACE statement to change a tablespace to read-only. • You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. • The tablespace must be online. This is necessary to ensure that there is no undo information that needs to be applied to the tablespace. • The tablespace must not currently be involved in an online backup, because the end of a backup updates the header file of all datafiles in the tablespace. • ALTER TABLESPACE tabelspace_name READ ONLY; • Once the statement is issued, no transactions are allowed to make further changes to the tablespace being made read-only.

  38. Database SYSTEM tablespace INVENTORY tablespace Enlarging the Database • You can enlarge the database in the following ways: • Creating a new tablespace • Adding a data file to an existing tablespace • Increasing the size of a data file • Providing for the dynamic growth of a data file

  39. Automatic Storage Management(ASM) • ASM provides a vertical integration of the file system for single symmetric multiprocessing (SMP) machines or on Oracle Real Application Clusters (RAC) support. • Automatic Storage Management • Is a portable and high-performance cluster file system • Manages Oracle database files • Spreads data across disksto balance load • Mirrors data & Solves many storage management challenges Application Database Filesystem ASM Volumemanager Operating system

  40. ASM: Key Features and Benefits • ASM • Stripes files, but not logical volumes • Provides online disk reconfiguration and dynamic rebalancing • Allows for adjustable rebalancing speed • Provides redundancy on a per-file basis • Supports only Oracle database files • Is cluster aware • Is automatically installed

  41. ASM Concepts ASMdisk group Database ASM file Data file Tablespace Segment ASM disk Extent File system file orraw device Allocation unit Oracle datablock Physicalblock

More Related