|
"One of the
symptoms of an approaching nervous breakdown is the belief that
one's work is terribly important." - Bertrand Russell
(1872-1970) |
Read
first then play the video:
DBA-VIDEO -Maintaining
Tablespace and Datafiles
Maintaining Tablespace and
Datafiles
As a DBA, you are
responsible for maintaining tablespaces and datafiles due to the
growth of a user"s database. Your job"s responsibilities dictate
that you should at least be informed of the following basic
fundamental subjects:
Maintaining Tablespaces
using Oracle-Managed Files (OMF)
Maintaining Tablespaces
without using OMF
Maintaining Datafiles using
OMF
Maintaining Datafiles
without using OMF
Using the AUTOEXTEND ON
option
Using a default storage
option
Using the INITIAL parameter
Using the NEXT parameter
Using the MINEXTENTS
parameter
Using the MAXEXTENTS
parameter
Using the PERMANENT ONLINE
option
Using the DBA_TABLESPACES
view
The EXTENT_MANAGEMENT
column
Managing a tablespace
LOCALLY
Adding a datafile to a
tablespace
Using the DBA_DATA_FILES
view
Using the DBA_TABLESPACES
view
The STATUS column
Using the
DB_CREATE_FILE_DEST parameter
Commands:
ALTER TABLESPACE ADD
ALTER TABLESPACE OFFLINE
ALTER TABLESPACE ONLINE
DROP TABLESPACE INCLUDING
"
HOST ERASE
ALTER SYSTEM SET
CREATE TABLESPACE
Hands-on
In this exercise you will learn how to maintain tablespaces and
datafiles in the database with and without using Oracle-Managed Files
(OMF).
Now, connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS
SYSDBA
Create a tablespace using
user defined file
Create a permanent tablespace with the AUTOEXTEND ON option, and a
default storage option with an initial size of 100k, a next size of
100k, a minimum extent of 10, and a maximum extent of 200.
SQL> CREATE TABLESPACE
myfirst_tablespace
DATAFILE 'c:_tablespace_01.dbf'
SIZE 10M
AUTOEXTEND ON
DEFAULT STORAGE (INITIAL 100K NEXT 100K
MINEXTENTS 10 MAXEXTENTS 200)
PERMANENT ONLINE
/
From now on, any object that will be created in this
tablespace will have their default storage the same as the tablespace
default storage unless they have been specified by user.
View tablespace information
Query the DBA_TABLESPACES view to display the tablespace name and
their extent management columns.
SQL> SELECT tablespace_name,
extent_management
FROM dba_tablespaces
/
Notice the EXTENT_MANAGEMENT column. The default for
managing the tablespace is LOCAL. For performance reason, use locally
managed tablespace.
Add a datafile to a
tablespace
Add more datafile to MYFIRST_TABLESPACE.
SQL> ALTER TABLESPACE myfirst_tablespace
ADD
DATAFILE 'c:_tablespace_02.dbf'
SIZE 10M
/
View datafile information
Query the DBA_DATA_FILES view to display all information.
SQL> SELECT *
FROM dba_data_files
WHERE tablespace_name = 'MYFIRST_TABLESPACE'
/
Notice that MYFIRST_TABLESPCE has two datafiles.
Change tablespace status
Set the tablespace status to OFFLINE, and then query the
DBA_TABLESPACES view to display the tablespace name and status
columns.
SQL> ALTER TABLESPACE myfirst_tablespace
OFFLINE
/
SQL> SELECT tablespace_name, status
FROM dba_tablespaces
/
Check the STATUS column. Notice that it is OFFLINE.
Now, set the tablespace status to ONLINE.
SQL> ALTER TABLESPACE myfirst_tablespace
ONLINE
/
Drop a tablespace
Drop the tablespace, including all of the objects in it plus the
constraints.
SQL> DROP TABLESPACE myfirst_tablespace
INCLUDING CONTENTS CASCADE CONSTRAINTS
/
SQL> HOST ERASE c:_tablespace_01.dbf
SQL> HOST ERASE c:_tablespace_02.dbf
Notice that since we did not use Oracle-Managed Files, we
should delete the datafiles from the system.
Create a tablespace using
Oracle-Managed file
Create a tablespace using Oracle-Managed Files (OMF). First, you
should alter the system to set the DB_CREATE_FILE_DEST parameter to a
valid sub-directory. Then create a table with a no datafile option.
The database will then create and maintain the datafiles in the
defined Oracle file destination, for example c:directory.
SQL> ALTER SYSTEM SET db_create_file_dest='c:'
/
SQL> CREATE TABLESPACE my2nd_tablespace
/
Query the DBA_DATA_FILES directory view to display all of the
information.
SQL> SELECT *
FROM dba_data_files
WHERE tablespace_name = 'MY2ND_TABLESPACE'
/
Check the Oracle database naming convention. Notice that
the first eight characters of the tablespace name is part of the
datafile name.
Drop an OMF tablespace
Drop the tablespace including all of the objects in it including
the constraints.
SQL> DROP TABLESPACE my2nd_tablespace
INCLUDING CONTENTS CASCADE CONSTRAINTS
/
Notice that since we use Oracle-Managed Files we do not
need to delete the datafile from the system. Oracle automatically
deletes it from the system.
|
"Make everything
as simple as possible, but not simpler." - Albert Einstein
(1879-1955) |
Questions:
Q: Describe a tablespace.
Q: How do you create a
tablespace in a database?
Q: How do you maintain a
tablespace using the Oracle-Managed file technique?
Q: How do you maintain a
tablespace using the User-Managed file technique?
Q: How do you maintain a
datafile using the Oracle-Managed file technique?
Q: How do you maintain a
datafile using the User-Managed file technique?
Q: Describe the AUTOEXTEND
ON option in the CREATE TABLESPACE statement.
Q: Describe the following
storage options in the CREATE TABLESPCES statement.
INITIAL parameter
NEXT parameter
MINEXTENTS parameter
MAXEXTENTS parameter
Q: Describe the PERMANENT
ONLINE option.
Q: What does it mean that a
tablespace LOCALLY managed?
Q: Describe the
DBA_TABLESPACES view.
Q: How do you add a
datafile to an existing tablespace?
Q: When do you use the
DB_CREATE_FILE_DEST parameter?
Q: What do the following
SQL statement do? What are the differences between the first CREATE
statement and the second CREATE statement?
SQL> CREATE TABLESPACE
myfirst_tablespace
DATAFILE 'c:_tablespace_01.dbf'
SIZE 10M
AUTOEXTEND ON
DEFAULT STORAGE (INITIAL 100K NEXT 100K
MINEXTENTS 10 MAXEXTENTS 200)
PERMANENT ONLINE
/
SQL> CREATE TABLESPACE
my2nd_tablespace
/
Q: How do you drop a
tablespace?
Q: How do you drop a
tablespace if it contains objects?
|