HOME > Detailed Objectives

Detailed Objectives

OSS-DB is a qualification certification for engineers involved in OSS-DB system. OSS-DB system is commonly used and the number of companies relying on the system is increasing each year.
This exam adopts "PostgreSQL 9.0" as the standard RDBMS, which is widely used in enterprise systems.

OSS-DB SilverOSS-DB Gold

OSS-DB Silver: Detailed Objectives

General Knowledge(20%)
  • Features of OSS-DB
  • License
  • Community and information retrieval
  • General knowledge about relational database
  • Basic concept of relational data model
  • Role of database management system
  • General knowlede about SQL, sorts of SQL(DDL, DML and DCL)
  • Database design and normalization
Server Administration(50%)
  • Installation
    • initdb command
    • Concept of database cluster
    • Template database
  • Usage of server management tools
    • pg_ctl, createuser, dropuser, createdb, dropdb, psql
  • Configuration files
    • postgresql.conf
      • Setting parameters
      • Connections and authentication
      • Client connection defaults
      • Error reporting and logging
    • pg_hba.conf
    • Usage of SET/SHOW commands
  • Backup and restore
    • pg_dump, pg_dumpall, pg_restore and psql commands
    • File system level backup
    • Point-in-Time Recovery(PITR), including specific operations
    • COPY command and \copy meta-command
  • Basic server administration
    • Adding, deleting and modifying database users
    • Autovacuum
    • System information functions (ex. version, current_user)
    • Information schema
    • System catalogs(ex. pg_roles, pg_authid), not including statistics views
    • Manipulating table privileges with GRANT/REVOKE
SQL and Development(30%)
  • SQL commands
    • SELECT statement
      • Specifying columns and tables
        LIMIT, OFFSET and ORDER BY clauses
      • DISTINCT, GROUP BY, HAVING, subqueries
      • JOIN (including outer joins), EXISTS, IN
    • INSERT, UPDATE and DELETE statements
    • Datatypes
      Boolean, character strings, numbers, date and time,
      NULL, serial, OID, arrays
    • Table definition
      Constraints, default values
    • Index
    • View
    • Rule and Trigger
    • Schema
    • Function definition and PL/pgSQL
  • Functions and operators
    • Aggregate functions (count, sum, avg, max, min)
    • Mathematical functions and operators
    • String functions (char_length, lower, upper, substring, replace, trim)
    • String operators (||, ~, LIKE, SIMILAR TO)
    • Date/time functions (age, current_date, current_timestamp, current_time, extract, to_char)
  • Transactions
    • Syntax (BEGIN, COMMIT, ROLLBACK, SAVEPOINT and their equivalences)
    • Transaction isolation level
    • Locks (implicit locks, LOCK statement, row-level locks, table locks)

OSS-DB Gold: Detailed Objectives

Advanced Server Administration(30%)
  • Setting up database server
    • Estimating table and index sizes
    • Security
      • Encrypting communication channels
      • Encrypting data
      • Client authentication
  • Commands for database administration
    • Backup and PITR
    • Autovacuum
    • Managing server log files
    • Monitoring disk spaces
  • Database architecture
    • Architecture of database cluster
    • Database processes
    • Physical structure of data and data files
  • Operation of hot-standby database
Monitoring Performance(30%)
  • Access statistics
    • Related parameters
    • pg_locks
    • pg_stat_activity, pg_stat_databases
    • Row-level statistics, ex. pg_stat_all_tables
    • Block-level statistics, ex. pg_statio_all_tables
  • Statistics for tables and columns
    • pg_class
    • pg_stats
  • Execution plans
    • Detecting slow queries
    • EXPLAIN and tuning
  • Related parameters
    • Analysis using contrib tools
  • Analysis using contrib tools
    • oid2name, pgbench, pgstattuple
Performance Tuning(20%)
  • Parameters related to performance
    • Resource consumption
    • Write ahead log
    • Query planning
    • Run-time statistics
    • Lock management
  • Tuning database and queries
    • Tuning parameters
    • Tuning execution plan
    • Tuning SQL
    • Distributing disk I/O
  • Troubleshooting common failures
    • Server failure
    • Dubious behaviors
    • Data lost
    • OS resource shortage
  • Recovering damaged cluster
    • Recovering transaction log
    • Recovering indexes of system tables
    • Options for developers
  • Recovering hot-standby databases

Top of this Page