iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 11

"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?