Transcript Chapter 1

CHAPTER 4
Tablespaces and Datafiles
Introduction
• After installing the binaries, creating a database, and
configuring your environment, the next logical step is to
prepare the database to house application data.
• DBAs must be knowledgeable of creating and maintaining
tablespaces and corresponding datafile operations.
Tablespaces Usually Created by Default
• SYSTEM
• SYSAUX
• UNDO
• TEMP
• USERS
Relationships of Oracle Storage Objects
Creating Additional Tablespaces
create tablespace tools
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m
extent management local
uniform size 128k
segment space management auto;
Locally Managed Tablespaces
• Use the EXTENT MANAGEMENT LOCAL clause
• A locally managed tablespace uses a bitmap in the
datafile to efficiently determine whether an extent is in
use.
• When using locally managed tablespaces, you don’t need
to worry about the storage parameters of NEXT,
PCTINCREASE, MINEXTENTS, MAXEXTENTS, and
DEFAULT
• Don’t create tablespaces using the old dictionary
managed extents.
Automatic Segment Space Management
within a Tablespace
• The SEGMENT SPACE MANAGEMENT AUTO clause
instructs Oracle to manage the space within the block.
• There is no need to specify parameters such as
PCTUSED, FREELISTS, and FREELIST GROUPS.
Creating an autoextend tablespace with a
maximum size
create tablespace tools
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m
autoextend on maxsize 1000m
extent management local
uniform size 128k
segment space management auto;
Using Ampersand Variables when
Creating Tablespaces
define tbsp_large=5G
define tbsp_med=500M
-create tablespace reg_data
datafile '/ora01/oradata/INVREP/reg_data01.dbf'
size &&tbsp_large
extent management local
uniform size 128k
segment space management auto;
-create tablespace reg_index
datafile '/ora01/oradata/INVREP/reg_index01.dbf'
size &&tbsp_med
extent management local
uniform size 128k
segment space management auto;
Best Practices for Creating Tablespaces
• Create separate tablespaces for different applications using the
•
•
•
•
•
•
same database.
For an application, separate table data from index data in
different tablespaces.
Don’t use the AUTOALLOCATE feature for datafiles. If you do
use AUTOALLOCATE, specify a maximum size.
Create tablespaces as locally managed. You shouldn’t create a
tablespace as dictionary managed.
For a tablespace’s datafile naming convention, use a name that
contains the tablespace name followed by a two-digit number
that’s unique within datafiles for that tablespace.
Try to minimize the number of datafiles associated with a
tablespace.
In tablespace CREATE scripts, use ampersand variables to
define aspects such as storage characteristics.
Renaming a Tablespace
SQL> alter tablespace foobar rename to users;
• Rename to conform better to standards
• Rename to better align with the use of the objects within
the tablespace
Creating a Tablespace with Nologging
create tablespace inv_mgmt_data
datafile '/ora02/dbfile/O12C/inv_mgmt_data01.dbf'' size
100m
extent management local
uniform size 128k
segment space management auto
nologging;
Nologging
• This minimizes the generation redo for direct path
operations (this feature has no effect on regular DML
operations)
• Use a direct path loading feature, such as the following:
• INSERT /*+ APPEND */ on queries that use a subquery for
determining which records are inserted.
• INSERT /*+ APPEND_VALUES */ on queries that use a VALUES
clause
• CREATE TABLE…AS SELECT
• Direct path SQL*Loader
Changing a Tablespace’s Write Mode
SQL> alter tablespace inv_mgmt_rep read only;
SQL> alter tablespace inv_mgmt_rep read write;
Check for Objects within the Tablespace
before Dropping
• Before dropping a tablespace, run a script similar to this
to determine if any objects exist in the tablespace:
select owner, segment_name, segment_type
from dba_segments
where tablespace_name=upper('&&tbsp_name');
Dropping a Tablespace
• Consider taking the tablespace offline before dropping it
SQL> alter tablespace inv_data offline;
• Dropping a tablespace and removing the datafiles
SQL> drop tablespace inv_data including contents and
datafiles;
Using Oracle Managed Files
• The Oracle Managed File (OMF) feature automates many
aspects of tablespace management, such as file
placement, naming, and sizing
• You control OMF by setting the following initialization
parameters:
• DB_CREATE_FILE_DEST
• DB_CREATE_ONLINE_LOG_DEST_N
• DB_RECOVERY_FILE_DEST
Creating a Bigfile Tablespace
• Tablespace contains only one datafile
• Allows for very large datafiles (128 TB)
create bigfile tablespace inv_big_data
datafile '/ora02/dbfile/O11R2/inv_big_data01.dbf'
size 100g
extent management local
uniform size 128k
segment space management auto;
Displaying Tablespace Size
• DBA_FREE_SPACE
• DBA_DATA_FILES
Altering Tablespace Size
• You alter a tablespace’s size by modifying the size of one
or more of its datafiles or adding a datafile:
SQL> alter database datafile
'/ora01/oradata/INVREP/reg_data01.dbf' resize 5g;
SQL> alter tablespace reg_data
add datafile '/ora01/dbfile/INVREP/reg_data02.dbf' size
100m;
Taking a Datafile Offline
• ALTER TABLESPACE ... OFFLINE NORMAL
• ALTER TABLESPACE ... OFFLINE TEMPORARY
• ALTER TABLESPACE ... OFFLINE IMMEDIATE
• ALTER DATABASE DATAFILE ... OFFLINE
• ALTER DATABASE DATAFILE ... OFFLINE FOR DROP
Renaming or Relocating a Datafile
1.
2.
3.
4.
5.
Use the following query to determine the names of existing
datafiles: SQL> select name from v$datafile;
Take the datafile offline using either the ALTER TABLESPACE or
ALTER DATABASE DATAFILE statement (see the previous section
for details on how to do this). You can also shut down your
database and then start it in mount mode; the datafiles can be
moved while in this mode because they aren’t open for use.
Physically move the datafile to the new location using either an OS
command (like mv or cp) or the COPY_FILE procedure of the
DBMS_FILE_TRANSFER built-in PL/SQL package.
Use either the ALTER TABLESPACE ... RENAME DATAFILE ... TO
statement or the ALTER DATABASE RENAME FILE ... TO
statement to update the control file with the new datafile name.
Alter the datafile online.
Moving/Relocating a Data File 12c
• Oracle Database 12c supports moving or relocating data
files while the data files remain online
• Nice feature because now you don’t have to take any
tablespaces/data files offline when performing move
operations
Using ORADEBUG to Display the
Trace File’s Name
• When you’re working with a trace file, another way of showing
the file’s name and location is to use the oradebug command:
SQL> oradebug setmypid
SQL> alter database backup controlfile to trace noresetlogs;
SQL> oradebug tracefile_name
• Here is some sample output after running the last statement:
/oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace/O11R2_ora_9
628.trc
• This way, you can directly display the name of the trace file that
you generated with the ALTER DATABASE BACKUP
statement.
Summary
• Tablespaces are logical containers for a group of
•
•
•
•
datafiles.
Datafiles are the physical files on disk that contain data.
Tablespaces allow you to separate the data of one
application from another.
Tablespaces provide a way to better manage applications
that have different availability and backup and recovery
requirements.
As a DBA you must be proficient in managing tablespaces
and datafiles