Oracle Database Administration

Posted on Sep 25, 2024 in Computers

Space UNDO

Handles system failures and ensures read consistency. Rollback transactions are managed through this space. The UNDO_MANAGEMENT initialization parameter must be set to AUTO.

ALTER ROLLBACK SEGMENT <NAME>
STORAGE (INITIAL <XX>
NEXT <XX>
MINEXTENTS <XX>
MAXEXTENTS <XX>
OPTIMAL <XX>);

Default Tablespace

Indicates the storage space where schema objects are created by the user when no specific tablespace is defined. If no tablespace is specified, the SYSTEM tablespace is used.

Temporary Tablespace

Indicates the storage space where temporary segments required by the user are stored.

Predefined User Roles

Oracle provides predefined roles to assist with database administration. Some key roles include:

  • CONNECT: Includes privileges such as ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW.
  • RESOURCE: Includes privileges such as CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE.
  • DBA: Grants all system privileges with the ADMIN OPTION.
  • EXP_FULL_DATABASE: Grants full and incremental database export privileges.
  • IMP_FULL_DATABASE: Grants full database import privileges.
  • DELETE_CATALOG_ROLE: Grants delete privileges on the system audit table (AUD$).
  • EXECUTE_CATALOG_ROLE: Grants execute privileges on objects in the data dictionary.
  • SELECT_CATALOG_ROLE: Grants select privileges on objects in the data dictionary.

SQL*Plus Commands

  • RUN: Displays and executes the SQL statement in the buffer.
  • GET <filename.extension>: Loads the contents of the specified file into the buffer.
  • EXIT/QUIT: Ends the SQL*Plus session.
  • DISCONNECT: Closes the current connection but keeps the SQL*Plus application open.
  • DESC[RIBE] <table_name>: Describes the structure of the specified table or object.
  • USER TABLES: Displays a list of the user’s tables.
  • SET PAGES[IZE] <n>: Sets the number of lines displayed on the screen before pausing.
  • SET PAU[SE] {ON|OFF}: Enables or disables pausing after each page of output.
  • SET LIN[ESIZE] <n>: Sets the maximum length of a line on the screen.
  • SHOW LINESIZE/PAGESIZE: Displays the current values of LINESIZE and PAGESIZE.
  • COLUMN <column_name> FORMAT <format>: Sets the display format for a column.
  • TTITLE/BTITLE: Defines the header and footer of a report.
  • SPOOL <filename>: Starts or stops spooling output to a file.
  • SET FEEDBACK {6|n|OFF|ON}: Controls the display of the number of rows affected by a SQL statement.
  • SET UNDERLINE <text>: Sets the character used to underline column headers.
  • SET TERMOUT {ON|OFF}: Enables or disables output to the screen.
  • SET COLSEP <text>: Sets the column separator character.

Resource Parameters

  • SESSIONS_PER_USER: Maximum number of concurrent sessions allowed per user.
  • CPU_PER_SESSION: Maximum CPU time per session.
  • CPU_PER_CALL: Maximum CPU time per call.
  • CONNECT_TIME: Maximum total time for a session.
  • IDLE_TIME: Maximum idle time in a session.
  • LOGICAL_READS_PER_SESSION: Maximum number of data blocks read per session.
  • LOGICAL_READS_PER_CALL: Maximum number of data blocks read per call.
  • PRIVATE_SGA: Amount of private SGA space reserved per session.
  • COMPOSITE_LIMIT: Total resource cost limit per session.
  • FAILED_LOGIN_ATTEMPTS: Number of failed login attempts before account lockout.
  • PASSWORD_LIFE_TIME: Number of days a password is valid.
  • PASSWORD_REUSE_TIME: Number of days before a password can be reused.
  • PASSWORD_REUSE_MAX: Number of password changes required before a password can be reused.
  • PASSWORD_LOCK_TIME: Number of days an account is locked after too many failed login attempts.
  • PASSWORD_GRACE_TIME: Grace period during which a user can connect with an expired password.