Use multiple tablespaces
Sponsored Links
This presentation is the property of its rightful owner.
1 / 21

Use Multiple Tablespaces PowerPoint PPT Presentation


  • 64 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Use Multiple Tablespaces


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.


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.


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;


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;


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;


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


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.


DroppingTablespaces

  • DROP TABLESPACE users INCLUDING CONTENTS;

  • DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

  • Drop tablespace <nombre_tablespace> including contents and datafiles cascade constraints;


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');


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


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


ViewingTablespaceInformation


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;


Mover Tablespaces

  • Si es totalmente necesario.Paso 1.: Bajar la BDShutdown 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;


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.


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


Borrar Datafile

  • alter database datafile 'datos01.dbf' offline drop


Resumen


Drop Tablespace


Datafile

  • Resize An Existing Datafile

    • ALTER DATABASE DATAFILE '<data_file_name>' RESIZE <n> K|M;


  • Login