DBA Fundamentals I -- Table of contents

Hands-On 01 (The most important Dictionary Views)

Creating a data dictionary; Using the CATALOG.SQL script; Using the ALL_; DBA_; and USER_ dictionary views; Using the DBA_VIEWS dictionary view; Using the DBA_TABLES dictionary view; Using the V$PWFILE_USERS view; Connecting as SYSDBA or SYSOPER; Using the V$PARAMETER; Using the V$SYSTEM_PARAMETER view; Using the SHOW PARAMETER command; Using the V$SGA view; Using the V$OPTION view; Using the V$PROCESS view; Using the V$SESSION view; Using the V$VERSION view; Using the V$INSTANCE view; Using the V$THREAD view; Using the V$PARAMETER view; Using the V$CONTROLFILE view; Using the V$DATABASE view; CONTROLFILE TYPE; CONTROLFILE CREATED; CONTROLFILE SEQUENCE NUMBER; CONTROLFILE CHANGE NUMBER; Using the V$DATAFILE view; Using the V$LOGFILE view

Hands-On 02 (Startup and Shutdown the database)

Starting up the instance with the NOMOUNT option; Starting the instance with the MOUNT option; Starting up the instance with the FORCE option; Starting the instance with the OPEN option; Shutting down the database with the TRANSACTIONAL option; Shutting down the database with the IMMEDIATE option; Shutting down the database with the NORMAL option; Shutting down the database with the ABORT option; Using the IMMEDIATE option; Using the NOMOUNT option; Using the NORMAL option; Using the MOUNT option; Using the TRANSACTIONAL option; Using the OPEN option; Using the FORCE option; Using the READ ONLY mode; Using the ABORT option; Using the BACKGROUND_DUMP_DEST parameter

Hands-On 03 (Multiplexing controlfiles)

Multiplexing control files; Using the V$CONTROLFILE view; Editing the INIT.ORA file; Using the V$CONTROLFILE_RECORD_SECTION view; Reading the ALERT file: The location of the ALERT file; The date and time the database started; Oracle version; Operating System information; Number of processes; Shared pool size; Location of control files; Block size; Processes: Process ID; DB writer process (DBWn); LOG writer process(LGWR); Redo Log file archive information: Frequency of the checkpoint process; ERROR location: Looking at the backup controlfile with the TRACE option

Hands-On 05 (Changing the database mode)

Database modes: SUSPEND; RESUME; RESTRICTED SESSION; QUIESCE RESTRICTED; Using the RESOURCE_MANAGER_PLAN parameter; SHOW PARAMETER resource_manager_plan

Hands-On 06 (Server Parameter File-SPFILE)

Creating the Server Parameter File (SPFILE); Using the MEMORY option; Using the SPFILE; Using the BOTH; Using the Server Parameter File (SPFILE); ;Setting the RESOURCE_MANAGER_PLAN parameter; Creating the Parameter File (PFILE); Using the Parameter File (PFILE)

Hands-On 07 (Configure the database to the archive mode)

Archiving the database; Maintaining the Online Redo Log files; Maintaining the checkpoints; Using the NOARCHIVELOG mode; Using the ARCHIVELOG mode; Using the DBA_USERS view; Using the V$LOG dictionary view; The STATUS column; Using the LOG_CHECKPOINT_INTERVAL parameter; Using the LOG_CHECKPOINT_TIMEOUT parameter; Using the LOG_CHECKPOINTS_TO_ALERT parameter

Hands-On 08 ( Maintaining and Relocating the Redo Log files)

Maintaining and relocating the Redo Log files; Using the V$LOG directory view; Using the V$LOGFILE directory view; Adding an Online Redo Log Group; Relocating or Renaming the Online Redo Log file; Copying the Online Redo Log file; Dropping an Online Redo Log Group; Deleting the Online Redo Log file's physical file

Hands-On 09 (Multiplexing and Maintaining the Online Redo Log files)

Maintaining the Online Redo Log files; Multiplexing the Online Redo Log files; Using Oracle-Managed Files; Using User-Managed Files; Using the DB_CREATE_ONLINE_LOG_DEST parameter; Creating an Online Redo Log group; Creating an Online Redo Log member; Clearing an Online Redo Log group; Dropping an Online Redo Log group; Using the V$LOGFILE view

Hands-On 10 (Maintaining Tablespaces and Datafiles)

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

Hands-On 11 (Maintaining a TEMPORARY tablespace)

Maintaining a TEMPORARY tablespace with OMF; Maintaining a TEMPORARY tablespace without OMF; Using the DB_CREATE_FILE_DEST parameter; Creating a TEMPORARY tablespace with OMF; Using the DBA_TABLESPACES view; TABLESPACE_NAME; INITIAL_EXTENT; MAX_EXTENTS; CONTENTS; LOGGING; STATUS; Using the DBA_DATA_FILES directory view; Using the DBA_TEMP_FILES directory view; Using the V$SESSION dictionary view; Using the V$SORT_SEGMENT view; Dropping a TEMPORARY tablespace; Creating a TEMPORARY tablespace using original syntax; Advantages of using OMF; Creating a TEMPORARY tablespace using the TEMPFILE clause; Using Oracle-Managed Files; Using the UNIFORM option; Using the DBA_TEMP_FILES view

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

Hands-On 13 (Managing Storage Structures)

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

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

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

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

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

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

Hands-On 18 (Monitoring an object usage)

Creating a new index table; Monitoring an index usage; Using the V$OBJECT_USAGE view; Checking the USED column; 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

Hands-On 19 (EXCEPTIONS INTO EXCEPTIONS)

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

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

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

Hands-On 21 (Auditing a database)

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

Hands-On 22 (Creating a new database)

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