use multiple tablespaces
Download
Skip this Video
Download Presentation
Use Multiple Tablespaces

Loading in 2 Seconds...

play fullscreen
1 / 21

Use Multiple Tablespaces - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Use Multiple Tablespaces' - rudolf


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 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 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
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 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;
mover tablespaces1
Mover Tablespaces
  • SHUTDOWNSTARTUP MOUNTCopy the datafile to it's new locationALTER DATABASE RENAME FILE '' TO '';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
datafile
Datafile
  • Resize An Existing Datafile
    • ALTER DATABASE DATAFILE '' RESIZE K|M;
ad