Hands-On Oracle Fundamentals I-CD#02

(Database Administrator-DBA)

Hands-On 12 (Maintaining a Tablespace)

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)

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)

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)

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)

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)

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)

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

Commands:

CREATE UNIQUE INDEX ON TABLESPACE

ALTER INDEX MONITORING USAGE

ALTER INDEX NOMONITORING USAGE

DROP INDEX

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

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)

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)

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)

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