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

Use Multiple Tablespaces PowerPoint PPT Presentation


  • 60 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 Multiple Tablespaces


Use multiple tablespaces1

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 multiple tablespaces2

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 multiple tablespaces3

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 multiple tablespaces4

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 multiple tablespaces5

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;


Taking tablespaces offline

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


Taking tablespaces offline1

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.


Dropping tablespaces

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 repairing locally managed tablespace problems

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


Transporting tablespaces between databases

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 databases1

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


Viewing tablespace information

ViewingTablespaceInformation


Mover tabla de un tbs a otro

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

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 tablespaces1

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 tablespaces2

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

Borrar Datafile

  • alter database datafile 'datos01.dbf' offline drop


Resumen

Resumen


Drop tablespace

Drop Tablespace


Datafile

Datafile

  • Resize An Existing Datafile

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


  • Login