1 / 24

Chapter 4

Chapter 4. Tablespaces and Datafiles. Introduction. After installing the binaries, creating a database, and configuring your environment, the next logical step is to prepare the database to house application data.

vail
Download Presentation

Chapter 4

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. Chapter 4 Tablespaces and Datafiles

  2. Introduction • After installing the binaries, creating a database, and configuring your environment, the next logical step is to prepare the database to house application data. • DBAs must be knowledgeable of creating and maintaining tablespaces and corresponding datafile operations.

  3. Tablespaces Usually Created by Default • SYSTEM • SYSAUX • UNDO • TEMP • USERS

  4. Relationships of Oracle Storage Objects

  5. Creating Additional Tablespaces create tablespace tools datafile '/ora01/dbfile/INVREP/tools01.dbf' size 100m extent management local uniform size 128k segment space management auto;

  6. Locally Managed Tablespaces • Use the EXTENT MANAGEMENT LOCAL  clause • A locally managed tablespace uses a bitmap in the datafile to efficiently determine whether an extent is in use. • When using locally managed tablespaces, you don’t need to worry about the storage parameters of NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT • Don’t create tablespaces using the old dictionary managed extents.

  7. Automatic Segment Space Management within a Tablespace • The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block. • There is no need to specify parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS.

  8. Creating an autoextend tablespace with a maximum size create tablespace tools datafile '/ora01/dbfile/INVREP/tools01.dbf' size 100m autoextend on maxsize 1000m extent management local uniform size 128k segment space management auto;

  9. Using Ampersand Variables when Creating Tablespaces define tbsp_large=5G define tbsp_med=500M -- create tablespace reg_data datafile '/ora01/oradata/INVREP/reg_data01.dbf' size &&tbsp_large extent management local uniform size 128k segment space management auto; -- create tablespace reg_index datafile '/ora01/oradata/INVREP/reg_index01.dbf' size &&tbsp_med extent management local uniform size 128k segment space management auto;

  10. Best Practices for Creating Tablespaces • Create separate tablespaces for different applications using the same database. • For an application, separate table data from index data in different tablespaces. • Don’t use the AUTOALLOCATE feature for datafiles. If you do use AUTOALLOCATE, specify a maximum size. • Create tablespaces as locally managed. You shouldn’t create a tablespace as dictionary managed. • For a tablespace’s datafile naming convention, use a name that contains the tablespace name followed by a two-digit number that’s unique within datafiles for that tablespace. • Try to minimize the number of datafiles associated with a tablespace. • In tablespace CREATE scripts, use ampersand variables to define aspects such as storage characteristics.

  11. Renaming a Tablespace SQL> alter tablespace foobar rename to users; • Rename to conform better to standards • Rename to better align with the use of the objects within the tablespace

  12. Creating a Tablespace with Nologging create tablespace inv_mgmt_data datafile '/ora02/dbfile/O11R2/inv_mgmt_data01.dbf'' size 100m extent management local uniform size 128k segment space management auto nologging;

  13. Nologging • This minimizes the generation redo for direct path operations (this feature has no effect on regular DML operations) • Use a direct path loading feature, such as the following: • INSERT /*+ APPEND */ on queries that use a subquery for determining which records are inserted. • INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause • CREATE TABLE…AS SELECT • Direct path SQL*Loader

  14. Changing a Tablespace’s Write Mode SQL> alter tablespace inv_mgmt_rep read only; SQL> alter tablespace inv_mgmt_rep read write;

  15. Check for Objects within the Tablespace before Dropping • Before dropping a tablespace, run a script similar to this to determine if any objects exist in the tablespace: select owner, segment_name, segment_type from dba_segments where tablespace_name=upper('&&tbsp_name');

  16. Dropping a Tablespace • Consider taking the tablespace offline before dropping it SQL> alter tablespace inv_data offline; • Dropping a tablespace and removing the datafiles SQL> drop tablespace inv_data including contents and datafiles;

  17. Using Oracle Managed Files • The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement, naming, and sizing • You control OMF by setting the following initialization parameters: • DB_CREATE_FILE_DEST • DB_CREATE_ONLINE_LOG_DEST_N • DB_RECOVERY_FILE_DEST

  18. Creating a Bigfile Tablespace • Tablespace contains only one datafile • Allows for very large datafiles (128 TB) create bigfile tablespace inv_big_data datafile '/ora02/dbfile/O11R2/inv_big_data01.dbf' size 100g extent management local uniform size 128k segment space management auto;

  19. Displaying Tablespace Size • DBA_FREE_SPACE • DBA_DATA_FILES

  20. Altering Tablespace Size • You alter a tablespace’s size by modifying the size of one or more of its datafiles or adding a datafile: SQL> alter database datafile '/ora01/oradata/INVREP/reg_data01.dbf' resize 5g; SQL> alter tablespace reg_data add datafile '/ora01/dbfile/INVREP/reg_data02.dbf' size 100m;

  21. Taking a Datafile Offline • ALTER TABLESPACE ... OFFLINE NORMAL • ALTER TABLESPACE ... OFFLINE TEMPORARY • ALTER TABLESPACE ... OFFLINE IMMEDIATE • ALTER DATABASE DATAFILE ... OFFLINE • ALTER DATABASE DATAFILE ... OFFLINE FOR DROP

  22. Renaming or Relocating a Datafile • Use the following query to determine the names of existing datafiles: SQL> select name from v$datafile; • Take the datafile offline using either the ALTER TABLESPACE or ALTER DATABASE DATAFILE statement (see the previous section for details on how to do this). You can also shut down your database and then start it in mount mode; the datafiles can be moved while in this mode because they aren’t open for use. • Physically move the datafile to the new location using either an OS command (like mv or cp) or the COPY_FILE procedure of the DBMS_FILE_TRANSFER built-in PL/SQL package. • Use either the ALTER TABLESPACE ... RENAME DATAFILE ... TO statement or the ALTER DATABASE RENAME FILE ... TO statement to update the control file with the new datafile name. • Alter the datafile online.

  23. Using ORADEBUG to Display the Trace File’s Name • When you’re working with a trace file, another way of showing the file’s name and location is to use the oradebugcommand: SQL> oradebugsetmypid SQL> alter database backup controlfile to trace noresetlogs; SQL> oradebugtracefile_name • Here is some sample output after running the last statement: /oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace/O11R2_ora_9628.trc • This way, you can directly display the name of the trace file that you generated with the ALTER DATABASE BACKUP statement.

  24. Summary • Tablespaces are logical containers for a group of datafiles. • Datafiles are the physical files on disk that contain data. • Tablespaces allow you to separate the data of one application from another. • Tablespaces provide a way to better manage applications that have different availability and backup and recovery requirements. • As a DBA you must be proficient in managing tablespaces and datafiles

More Related