Hands-On 10 (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 10 (Maintaining Tablespaces and Datafiles)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a50
col file_name format a60
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- 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.

pause


CREATE TABLESPACE myfirst_tablespace 
DATAFILE
'c:\oracle\oradata\school\myfirst_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.

pause

CLEAR SCR
-- Query the DBA_TABLESPACES view to display the tablespace
-- name and their extent management columns.

pause


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.

pause

CLEAR SCR
-- Add more datafile to MYFIRST_TABLESPACE.

pause


ALTER TABLESPACE myfirst_tablespace 
ADD 
DATAFILE 'c:\oracle\oradata\school\myfirst_tablespace_02.dbf' 
SIZE 10M
/

pause

CLEAR SCR
-- Query the DBA_DATA_FILES view to display all information.

pause


SELECT *
FROM dba_data_files
WHERE tablespace_name = 'MYFIRST_TABLESPACE'
/

-- Notice that MYFIRST_TABLESPCE has two datafiles.

pause

CLEAR SCR
-- Set the tablespace status to OFFLINE, and then query
-- the DBA_TABLESPACES view to display the tablespace name
-- and status columns.

pause


ALTER TABLESPACE myfirst_tablespace OFFLINE
/

SELECT tablespace_name, status 
FROM dba_tablespaces
/

-- Check the STATUS column. Notice that it is OFFLINE.

pause

CLEAR SCR
-- Now, set the tablespace status to ONLINE.

pause


ALTER TABLESPACE myfirst_tablespace ONLINE
/

pause

CLEAR SCR
-- Drop the tablespace, including all of the objects in it
-- plus the constraints.

pause

DROP TABLESPACE myfirst_tablespace 
INCLUDING CONTENTS CASCADE CONSTRAINTS
/

HOST ERASE c:\oracle\oradata\school\myfirst_tablespace_01.dbf

HOST ERASE c:\oracle\oradata\school\myfirst_tablespace_02.dbf

-- Notice that since we did not use Oracle-Managed Files 
-- we should delete the datafiles from the system.

pause

CLEAR SCR
-- 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:\newfolder directory.

pause


ALTER SYSTEM SET db_create_file_dest='c:\newfolder'
/

CREATE TABLESPACE my2nd_tablespace
/

pause

CLEAR SCR
-- Query the DBA_DATA_FILES directory view to display all of the
-- information.

pause


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.

pause

CLEAR SCR
-- Drop the tablespace including all of the objects in it
-- including the constraints.

pause

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.

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause