Transcript Use Multiple Tablespaces

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.
 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 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.
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;
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;
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;
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
Taking Tablespaces 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
 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
 DBMS_SPACE_ADMIN
 SEGMENT_VERIFY
 SEGMENT_CORRUPT
 SEGMENT_DROP_CORRUPT
 TABLESPACE_VERIFY
 TABLESPACE_FIX_BITMAPS
 EXECUTE
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_L
OCAL('SYSTEM');
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_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
Viewing Tablespace Information
Mover Tabla de un TBS a otro
 Alter table DEPT move tablespace TEST;
 SELECT object_type ,COUNT(*) FROM
dba_objects WHERE owner=‘X' GROUP BY
object_type;
Mover Tablespaces
 Si es totalmente necesario.
Paso 1.: Bajar la BD
Shutdown de la DB
Paso 2.:
Copia el datafile a la nueva ubicación.
Paso 3.
Startup mount
Paso 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
 SHUTDOWN
STARTUP MOUNT
Copy the datafile to it's new location
ALTER DATABASE RENAME FILE
'<old_full_path>' TO '<new_full_path>';
then
ALTER DATABASE OPEN;
After that, you can safely delete the old datafile.
Mover Tablespaces

conn / as sysdba
shutdown immediate;
startup mount
host
cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf
exit
alter database rename file '/u01/oradata/tools01.dbf' to
'/u06/oradata/tools01.dbf';
alter database open
host
rm /u01/oradata/tools.01.dbf
exit
Borrar Datafile
 alter database datafile 'datos01.dbf' offline
drop
Resumen
Off-line
Place On-line
Make Read Only
ALTER TABLESPACE <tablespace_name> OFFLINE;
ALTER TABLESPACE tools OFFLINE;
ALTER TABLESPACE <tablespace_name> ONLINE;
ALTER TABLESPACE tools ONLINE;
ALTER TABLESPACE <tablespace_name> READ ONLY;
ALTER TABLESPACE tools READ ONLY;
Make A Tablespace Read
Write
ALTER TABLESPACE <tablespace_name> READ WRITE;
Prepare Tablespace For
Backup (archive
logging must be
active)
ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;
End Tablespace Backup
ALTER TABLESPACE tools READ WRITE;
ALTER TABLESPACE tools BEGIN BACKUP;
ALTER TABLESPACE <tablespace_name> END BACKUP;
ALTER TABLESPACE tools END BACKUP;
Drop Tablespace
Drop Tablespace
Drop Tablespace
Including Contents
Drop Tablespace
Including Contents &
Datafiles
Drop Tablespace
Including Contents &
Datafiles When There
Are Referential
Constraints
DROP TABLESPACE <tablespace_name>;
DROP TABLESPACE tools;
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS;
DROP TABLESPACE tools
INCLUDING CONTENTS;
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tools
INCLUDING CONTENTS AND DATAFILES CASCADE
CONSTRAINTS;
Datafile
 Resize An Existing Datafile
 ALTER DATABASE DATAFILE '<data_file_name>'
RESIZE <n> K|M;