Oracle Fundamentals I Case Study

The Oracle Fundamentals I Case Study consists of 22 of the most common DBA tasks. In your organization, as a DBA, you are expected to know at least how to: Obtain the most important dictionary views; Startup and Shutdown the database; Multiplex controlfiles; Read the ALERT file; Change the database mode; Use the Server Parameter File-SPFILE; Configure the database to the archive mode; Maintain and Relocate the Redo Log files; Multiplex and Maintain the Online Redo Log files; Maintain Tablespaces and Datafiles; Maintain a TEMPORARY tablespace; Maintain a Tablespace; Manage Storage Structures; Maintain and Configure UNDO tablespaces; Maintain and Configure an UNDO tablespace manually; Create and Maintain a TEMPORARY table; Detect ROW Migration and Chaining; Monitor an object usage; Audit a database; and Create a new database.

Hands-On Oracle Fundamentals I-CD#02

(Database Administrator-DBA)

Hands-On 12 (Maintaining a Tablespace)

As a DBA, you are responsible for maintaining tablespaces and datafiles due to a user’s usage of the tablespace. If your user does not update any tables in the tablespace, you may want to change the tablespace mode to the READ ONLY mode. Or if you have any I/O problems on a disk, you can relocate the tablespace to a new not busy disk. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining a tablespace mode

READ ONLY

Relocating the tablespace

Using OMF

Without using OMF

Using the DBA_TABLESPACES dictionary view

TABLESPACE_NAME

STATUS

Altering a tablespace mode to READ ONLY

Altering a tablespace mode to READ WRITE

Performing activities in a READ ONLY tablespace mode

Dropping table in a READ ONLY mode

Using the DBA_TABLESPACES view

Relocating a tablespace

Copying a datafile

Altering the database to a new location

Changing a tablespace status

Commands:

ALTER TABLESPACE READ ONLY

ALTER TABLESPACE READ WRITE

ALTER TABLESPACE OFFLINE

ALTER TABLESPACE ONLINE

HOST COPY

HOST ERASE

ALTER DATABASE RENAME FILE

Hands-On 13 (Managing Storage Structures)

As a DBA, you are responsible to manage a table storage structure due to performance problems or a spacing issue. If you have a table that has lots of update transactions you would want to be sure that you have enough space in the PCTFREE space. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Oracle Segments

Extent Allocations

PCTFREE

PCTUSED

Using the DBA_SEGMENTS view

INDEX segment

TABLE segment

Creating an INDEX_ORGANIZED table

Using the ORGANIZATION INDEX parameter

Using the TABLESPACE option

Using the PCTTHRESHOLD parameter

Using the OVERFLOW TABLESPACE parameter

Understanding Different Segment Types

Using the DBA_SEGMENTS view

Using the DBA_TABLES view

Using the DBA_EXTENTS view

Using the MAXEXTENTS option

Space allocation in the Oracle Block unit

Setting the PCTFREE parameter

Setting the PCUSED parameter

Commands:

DROP TABLE my_

CREATE TABLE STORAGE

ANALYZE TABLE COMPUTE STATISTICS

ALTER TABLE STORAGE

Hands-On 14 (Maintaining and Configuring UNDO tablespace)

As a DBA, you are responsible for maintaining UNDO tablespaces due to an users’ database transactions, thanks to Oracle and their handy UNDO_MANAGEMENT parameter. You will indeed find this feature extremely handy. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Creating an UNDO tablespace automatically

Configuring an UNDO tablespace

Displaying the UNDO MANAGEMENT parameter

Setting the UNDO MANAGEMENT parameter

Using the DBA_ROLLBACK_SEGS view

Creating an UNDO tablespace using OMF

Displaying the OMF created file destination

Setting a tablespace status to ONLINE

Setting the UNDO segments to ONLINE

Changing the UNDO tablespace

Setting the UNDO retention time

Dropping the UNDO tablespace

Deactivate the UNDO tablespace

Commands:

SHOW PARAMETER

ALTER SYSTEM SET db_create_file_dest='c:\newfolder'

CREATE UNDO TABLESPACE DATAFILE

ALTER TABLESPACE ONLINE

ALTER SYSTEM SET undo_tablespace=

ALTER SYSTEM SET undo_retention=

DROP TABLESPACE

Hands-On 15 (Maintaining and Configuring an UNDO tablespace manually)

As a DBA, you are responsible for maintaining UNDO tablespaces manually due to an users’ database transactions.  Always try to use the auto UNDO segments option, versus the manual unless you have a good reason to do so. Now, due to your organization’s backward compatibility, you should maintain a manual UNDO tablespace. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining an UNDO tablespace manually

Creating an UNDO tablespace manually using OMF

Using the DICTIONAY-MANAGED tablespace

Creating UNDO segments with OPTIMAL option

Setting the UNDO MANAGEMENT to the MANUAL mode

Shutting down and Starting up the database using SPFILE

OFFLINE an UNDO tablespace

ONLINE an UNDO tablespace

Setting the Rollback Segment status to ONLINE

Dropping the UNDO manual tablepace

Commands:

CREATE TABLESPACE EXTENT MANAGEMENT DICTIONARY

CREATE ROLLBACK SEGMENT TABLESPACE

STORAGE (INITIAL NEXT MAXEXTENTS OPTIMAL)

ALTER SYSTEM SET undo_management=MANUAL SCOPE=spfile

SHUTDOWN IMMEDIATE

STARTUP

ALTER ROLLBACK SEGMENT ONLINE

DROP TABLESPACE

Hands-On 16 (Creating and Maintaining a TEMPORARY table)

As a DBA, you are responsible for creating and maintaining a TEMPORARY table due to your organization’s developer requirements. They need to use this space to dynamically manipulate data in the memory without using any PL/SQL tables. You will find this feature extremely handy. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a TRANSACTION temporary table

Creating a SESSION temporary table

Maintaining a TRANSACTION temporary table

Maintaining a SESSION temporary table

Using the ON COMMIT DELETE ROWS option

Using the ON COMMIT PRESERVE ROWS option

Testing a TRANSACTION temporary table

Testing a SESSION temporary table

Dropping a TRANSACTION or SESSION temporary table

Disconnecting from a session

Commands:

CREATE GLOBAL TEMPORARY TABLE

   ON COMMIT DELETE ROWS

INSERT INTO VALUES (100,'Borna')

DROP TABLE

CREATE GLOBAL TEMPORARY TABLE

   ON COMMIT PRESERVE ROWS

DISCONNECT

Hands-On 17 (Detecting ROW Migration and Chaining)

As a DBA, you are responsible for detecting row migration and chaining. The more migration and chaining you have, cause more performance problems for application software. You should identify them and if there are many of them, organize the table. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a table

Inserting lots of records

Generating lots of Migration and Chaining

Analyzing a table

Using the USER_TABLES view

Displaying table statistics

The NUM_ROWS column

The BLOCKS column

The CHAIN_CNT column

Moving or relocating a table

Grant a system privilege to a user

Checking an index table

Using the USER_INDEXES view

Rebuilding or relocating an index table

Analyzing an index table

Using the INDEX_STATS view

Checking the DELETED ROWS RATIO value

The lf_rows column

The del_lf_fows column

Dropping a table

Dropping an index table

Dropping a tablespace

Revoking a system privilege from a user

Commands:

CREATE TABLE CONSTRAINT PRIMARY KEY

ANALYZE TABLE COMPUTE STATISTICS

CREATE TABLESPACE DATAFILE

GRANT CREATE TABLESPACE TO

GRANT DROP TABLESPACE TO

ALTER TABLE MOVE TABLESPACE

ALTER INDEX REBUILD TABLESPACE

ANALYZE VALIDATE STRUCTURE

DROP TABLE

DROP TABLESPACE

REVOKE CREATE TABLESPACE FROM

Hands-On 18 (Monitoring an object usage)

As a DBA, you are also responsible for monitoring newly created indexes. Your organization wants you to monitor the index column so that if the indexed column was not used then you can drop it, since so many indexing in the database affects the database performance. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a new index table

Monitoring an index usage

Using the V$OBJECT_USAGE view

Checking the USED colum

Checking the MONITORING column

Checking the END_MONITORING column

Creating a unique index column

Starting monitoring an index usage

Using a monitored index column

Stopping monitoring an index usage

Dropping an index table

Commands:

CREATE UNIQUE INDEX ON TABLESPACE

ALTER INDEX MONITORING USAGE

ALTER INDEX NOMONITORING USAGE

DROP INDEX

Hands-On 19 (EXCEPTIONS INTO EXCEPTIONS)

As a DBA, you want to use the advantages of the EXCEPTIONS clause. You can use this clause to identify duplication or any constraint violations and delete these records. In this hands-on exercise, your organization wants you to separate your salesmen using the EXCEPTIONS clause. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Using the EXCEPTIONS table in a schema

Adding a disabled constraint

Describing the EXCEPTIONS table

Running the UTLEXCPT.SQL script

Enabling a disabled constraint

Dropping a constraint

Commands:

ALTER TABLE ADD

   (CONSTRAINT CHECK (DISABLE)

DESC exceptions

SET ECHO

START %ORACLE_HOME%\rdbms\admin\utlexcpt.sql

ALTER TABLE ENABLE VALIDATE CONSTRAINT

   EXCEPTIONS INTO EXCEPTIONS

DELETE FROM

COMMIT

ALTER TABLE DROP CONSTRAINT

Hands-On 20 (Maintaining user’s account and profile)

As a DBA, you are responsible for maintaining user accounts due to the growth of the organization. Also, due to new users and the abuse of the database resources, you have been assigned to create a profile to limit a group of users from using the database resources. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a user

Granting object privileges to a user

Assigning a default tablespace to a user

Assigning a temporary tablespace to a user

Assigning a quota to a user

Assigning a profile to a user

Expiring a user password

Creating a profile

Changing a password

Allocating resource limitations to a profile

Locking a user account

Unlocking a user account

Using the DBA_USERS view

Using the ALL_OBJECTS view

Using the DEFAULT profile

Activating the resource limit system parameter

Dropping a user

Dropping a profile

Commands:

CREATE USER

CONNECT system/manager AS SYSDBA

ALTER USER ACCOUNT LOCK

ALTER USER ACCOUNT UNLOCK

CREATE PROFILE LIMIT

ALTER USER PROFILE

ALTER SYSTEM SET resource_limit=TRUE

DROP USER CASCADE

DROP PROFILE CASCADE

Hands-On 21 (Auditing a database)

As a DBA, you are responsible for auditing the database due to a suspicious transaction on certain table. An unknown user is deleting records and you have been assigned the task to investigate and find out who that person is. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Starting auditing

Stopping auditing

Reading from the AUDIT TRAIL table

Truncating the AUD$ table

Using the AUDIT_TRAIL view

Listing the AUDIT_TRAIL parameter

Setting the AUDIT_TRAIL parameter

Shutting down and startup a database using SPFILE

Using the AUD$ table

Auditing an auditor

Auditing who deletes a record

Viewing the AUD$ table

Stopping all auditing trail

Commands:

TRUNCATE TABLE aud$

SHOW PARAMETER

ALTER SYSTEM SET audit_trail=db SCOPE=spfile

SHUTDOWN IMMEDIATE

CONNECT system/manager AS SYSDBA

STARTUP

AUDIT delete ON sys.aud$

AUDIT DELETE  ON BY ACCESS WHENEVER SUCCESSFUL

NOAUDIT ALL

Hands-On 22 (Creating a new database)

As a DBA, you are responsible for creating a new database. It is very healthy and normal that an organization has three different databases for one single application, such as testing, development, and production environments. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Using the Oracle Database Configuration Assistant

Creating a new database

Specifying the database and system identifier name

Changing the SGA memory allocation

Changing the database mode to the ARCHIVELOG mode

Starting automatic archival

Changing the archive log format

Changing the database block size

Changing the sort area size

Changing the database character set

Change the location of the Parameters and trace file

Changing user passwords