Use Multiple Tablespaces

1
Use Multiple Tablespaces
2
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.
• Separate one applications data from anothers 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 Multiple Tablespaces

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

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

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

• 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
Taking Tablespaces 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
Taking Tablespaces Offline

• ALTER TABLESPACE users OFFLINE NORMAL
• ALTER TABLESPACE ... DATAFILE ONLINEOFFLINE
• ALTER TABLESPACE flights READ ONLY
• Initialization parameter COMPATIBLE is 8.1.0 or
  greater.

9
Dropping Tablespaces

• DROP TABLESPACE users INCLUDING CONTENTS
• DROP TABLESPACE users INCLUDING CONTENTS AND
  DATAFILES
• Drop tablespace ltnombre_tablespacegt including
  contents and datafiles cascade constraints

10
Diagnosing and Repairing Locally Managed
Tablespace Problems

• DBMS_SPACE_ADMIN
• SEGMENT_VERIFY
• SEGMENT_CORRUPT
• SEGMENT_DROP_CORRUPT
• TABLESPACE_VERIFY
• TABLESPACE_FIX_BITMAPS
• EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOC
  AL('SYSTEM')

11
Transporting Tablespaces Between Databases

• 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_TABLESPACEy TABLESPACES(sales_1,s
  ales_2)
• TRIGGERSy CONSTRAINTSn GRANTSn
  FILEexpdat.dmp

12
Transporting Tablespaces Between Databases

• CONNECT SYS/password AS SYSDBA
• IMP TRANSPORT_TABLESPACEy FILEexpdat.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
Viewing Tablespace Information
14
Mover Tabla de un TBS a otro

• Alter table DEPT move tablespace TEST
• SELECT object_type ,COUNT() FROM dba_objects
  WHERE ownerX' 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
  'ltold_full_pathgt' TO 'ltnew_full_pathgt'thenAL
  TER DATABASE OPENAfter that, you can safely
  delete the old datafile.

17
Mover Tablespaces

• conn / as sysdbashutdown immediatestartup
  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
Off-line ALTER TABLESPACE lttablespace_namegt OFFLINE
Off-line ALTER TABLESPACE tools OFFLINE
Place On-line ALTER TABLESPACE lttablespace_namegt ONLINE
Place On-line ALTER TABLESPACE tools ONLINE
Make Read Only ALTER TABLESPACE lttablespace_namegt READ ONLY
Make Read Only ALTER TABLESPACE tools READ ONLY
Make A Tablespace Read Write ALTER TABLESPACE lttablespace_namegt READ WRITE
Make A Tablespace Read Write ALTER TABLESPACE tools READ WRITE
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE lttablespace_namegt BEGIN BACKUP
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE tools BEGIN BACKUP
End Tablespace Backup ALTER TABLESPACE lttablespace_namegt END BACKUP
End Tablespace Backup ALTER TABLESPACE tools END BACKUP
20
Drop Tablespace
Drop Tablespace DROP TABLESPACE lttablespace_namegt
Drop Tablespace DROP TABLESPACE tools
Drop Tablespace Including Contents DROP TABLESPACE lttablespace_namegtINCLUDING CONTENTS
Drop Tablespace Including Contents DROP TABLESPACE toolsINCLUDING CONTENTS
Drop Tablespace Including Contents Datafiles DROP TABLESPACE lttablespace_namegtINCLUDING CONTENTS AND DATAFILES
Drop Tablespace Including Contents Datafiles DROP TABLESPACE toolsINCLUDING CONTENTS AND DATAFILES
Drop Tablespace Including Contents Datafiles When There Are Referential Constraints DROP TABLESPACE lttablespace_namegtINCLUDING CONTENTS AND DATAFILESCASCADE CONSTRAINTS
Drop Tablespace Including Contents Datafiles When There Are Referential Constraints DROP TABLESPACE toolsINCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
21
Datafile

• Resize An Existing Datafile
• ALTER DATABASE DATAFILE 'ltdata_file_namegt'
  RESIZE ltngt KM