DBA Fundamentals

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:

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@yourhost 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.

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.
 

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?

Q: How do you drop a tablespace?

Q: How do you drop a tablespace if it contains objects?