1 / 21

Use Multiple Tablespaces

Utilizing multiple tablespaces in a database maximizes flexibility and operational efficiency. By separating user data from dictionary data, you can reduce contention and improve performance. Individual applications can be isolated, preventing downtime impacts when a tablespace goes offline. Storing datafiles on different disk drives further minimizes I/O contention. You can back up and manage tablespaces individually for better availability. This approach enhances the overall database reliability and performance, allowing you to optimize usage based on application needs.

rudolf
Télécharger la présentation

Use Multiple 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. Use Multiple Tablespaces

  2. Use MultipleTablespaces • Using multiple tablespaces allows you more flexibility in performing database operations. • Separate user data from data dictionary data to reduce contention among dictionary objects and schema objects for the same datafiles. • Separate one application’s data from another’s to prevent multiple applications from being affected if a tablespace must to be taken offline. • Store different tablespaces’ datafiles on separate disk drives to reduce I/O contention.

  3. Use MultipleTablespaces • Separate rollback segment data from user data, preventing a single disk failure from causing permanent loss of data. • Take individual tablespaces offline while others remain online, providing better overall availability. • Reserve a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage. This enables you to optimize usage of the tablespace. • Back up individual tablespaces.

  4. Use MultipleTablespaces • CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; • ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

  5. Use MultipleTablespaces • CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf‘ SIZE 20M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; • ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M REUSE;

  6. Use MultipleTablespaces • ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE; • ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE; • ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 4M; • ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

  7. TakingTablespaces Offline • To make a portion of the database unavailable while allowing normal access to the remainder of the database • To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use) • To make an application and its group of tables temporarily unavailable while updating or maintaining the application

  8. TakingTablespaces Offline • ALTER TABLESPACE users OFFLINE NORMAL; • ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE} • ALTER TABLESPACE flights READ ONLY; • Initialization parameter COMPATIBLE is 8.1.0 or greater.

  9. DroppingTablespaces • DROP TABLESPACE users INCLUDING CONTENTS; • DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES; • Drop tablespace <nombre_tablespace> including contents and datafiles cascade constraints;

  10. Diagnosing and RepairingLocallyManagedTablespaceProblems • DBMS_SPACE_ADMIN • SEGMENT_VERIFY • SEGMENT_CORRUPT • SEGMENT_DROP_CORRUPT • TABLESPACE_VERIFY • TABLESPACE_FIX_BITMAPS • EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

  11. TransportingTablespacesBetweenDatabases: • A transportable tablespace set consists of datafiles for the set of tablespaces being transported and a file containing structural information for the set of tablespaces. • ALTER TABLESPACE sales_1 READ ONLY; • ALTER TABLESPACE sales_2 READ ONLY; • EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp

  12. Transporting Tablespaces Between Databases: • CONNECT SYS/password AS SYSDBA • IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('/db/sales_jan','/db/sales_feb', ...) TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee) FROMUSER=(dcranney,jfee) TOUSER=(smith,williams) • ALTER TABLESPACE sales_1 READ WRITE • ALTER TABLESPACE sales_1 READ WRITE

  13. ViewingTablespaceInformation

  14. Mover Tabla de un TBS a otro • Alter table DEPT movetablespaceTEST; • SELECT object_type ,COUNT(*) FROM dba_objects WHERE owner=‘X' GROUP BY object_type;

  15. Mover Tablespaces • Si es totalmente necesario.Paso 1.: Bajar la BD Shutdown de la DBPaso 2.: Copia el datafile a la nueva ubicación.Paso 3. Startup mountPaso 4. Alter database rename file '/old path/archivo de system.dbf' to '/new path/archivo de system.dbf';Paso 5. alter database open;

  16. Mover Tablespaces • SHUTDOWNSTARTUP MOUNTCopy the datafile to it's new locationALTER DATABASE RENAME FILE '<old_full_path>' TO '<new_full_path>';thenALTER DATABASE OPEN;After that, you can safely delete the old datafile.

  17. Mover Tablespaces • conn / as sysdbashutdown immediate;startup mounthostcp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbfexitalter database rename file '/u01/oradata/tools01.dbf' to '/u06/oradata/tools01.dbf';alter database openhostrm /u01/oradata/tools.01.dbfexit

  18. Borrar Datafile • alter database datafile 'datos01.dbf' offline drop

  19. Resumen

  20. Drop Tablespace

  21. Datafile • Resize An Existing Datafile • ALTER DATABASE DATAFILE '<data_file_name>' RESIZE <n> K|M;

More Related