Oracle Database 12c

From Minor Miracle Software
Jump to: navigation, search

Oracle Database 12c

select * from dual;

The notorious v$


  • Oracle Database 12c Fundamentals


Oracle University[1]

Oracle is called an object-relational database

  • Key Terms
  1. Relation = Table
  2. Attribute = Column
  3. Tuple = Row
  • Oracle Versions

Oracle 8i (1999) (i=internet)
Oracle 10g (2003) (g=grid computing) Oracle 12c (2014) (c=cloud)

Clients are applications that connect through middle ware or directly to the oracle server.

PDB Plugable databases

Oracle 12c Interactive Quick Reference[2]

Program Global Area (PGA) Components

Automatic Memory Management. Give Oracle a memory chunk and it does the rest.

OFA Naming standard.

Oracle block size. Match that to the OS block size on disk. Linux = 512. Windows = 4096.

Logical Storage Structures

  • Heat Map and Automatic Data Optimization
    • Graphically displays data access / usage patterns
    • Move and compress data through your tiered storage hierarchy based on the data "heat" index.
  • Oracle data block: Should match the OS file block.

Oracle Management Tools

  • SQL*Plus: CLI for the Oracle Database
  • SQL Developer: Graphical utility for DBA and development work
  • EM Express: Lightweight Web Interface - very weak
  • EM Cloud Control: Robust, multi-platform management tool
  • DBCA: Create and manage traditional and pluggable databases
  • NetCA: Wizard-based net services configuration tool
  • Netcfg: Java-based graphical front-end for Oracle Net Services
  • OUI: Java-based software installation and maintenance tool for Oracle products.
  • Must have java installed to use any sql developer tools.


  • Reads primary initialization parameter file: ORACLE_HOME/dbs/spfile.ora - this can be modified while the server is running. binary file type.
  • DB_NAME parameter
  • Key/value pairs are case-sensitive dependent on the OS. Single quotes for strings.

Never lose your command-line skills. Sometimes that's the only way to recover a crashed server.


shutdown and startup are the most graceful methods. Windows Service Control manager and net stop also work.

  • Alert log


Windows is not case-sensitive, LINUX is.

Initialization file choice is:

  1. spfile - binary
  2. pfile - text

show parameter - fetches all the parameters

shutdown - the graceful command

shutdown force - A hard crash, no active work is saved.

Oracle Net Services

Called Oracle Net or SQL*Net

Transparent Network Substrate (TNS)

The Oracle Net Listener

  • Server-side app accepts incoming client connection requests
  • LREG background process manages the listener's activity
  • Service Name: Logical name for an Oracle database service
  • Service ID (SID): Unique name for a specific Oracle database
  • One SID can have multiple service names. Say, one shared connection and one dedicated connection.


  •  %ORACLE_HOME%\Network\Admin
  • Set %TNS_ADMIN% environmental variable mapped to %ORACLE_HOME%

It contains

  • Listener name
  • Protocol address(es) that the listener is accepting requests on
  • Database services


LISTENER = // default name
      (ADDRESS = (PROTOCOL = TCP)(HOST = orserver)(PORT=1521))

  • Indents must be spaces
  • '(' and ')' MUST line up.

listener control utility

Oracle Naming Methods

TNSNAMES.ORA, client side file. same syntax.

Easy Connect

  • connection

Oracle Internet Directory (OID) - Same as Windows Active Directory

  • LDAPv3 directory service

Oracle Database Client

Oracle Storage Structures

Table Space

  • Segments
    • Table
    • Row piece
      • Blocks, which are allocated as 'Extent's
  • Row pieces can become chained within a block or between blocks (fragmentation can happen)
  • Blocks are row data, free space, and a header that contains metadata about that block

What is a table space?

  • A logical storage container for segments
    • Segments are database objects like tables or indexes
    • Table space one or more datafiles that are physical db objects
    • A segment cannot contain multiple tablespaces
  • SYSTEM and SYSAUX are crucial to operating the database
  • table spaces map directly to physical files.

Plugable Databases (PDB)s

Oracle Enterprise Manager works through the web browser with the link https://oramt:5501/em/login?returnUrl=/em/

PDBs, Table spaces, new stuff in 12c

  • In a standard database, all tablespaces belong to one database.
  • In a CDB, the root container owns one tablespace set.
  • A tablespace in a PDB can contain objects associate with only one PDB
  • Objects in a tablespace can have the same label because the objects are partitioned.

Single-instance database

Plugable database

Creating Database Objects

  • schema
  • table structure
  • SQL*Plus and SQL Developer

A schema is owned by a particular user and contains logical structures

  • Schemas have no direct relationship to tablespaces.
  • Syntax is
    • schema.object

Tables are stored in the data dictionary in UPPERCASE.

Static DD views


User Administration Basics

Common vs Local User

Seed PDB database, template for PDB creation.

Oracle Documentation[3]

'grant create session' to new users so they can connect.

  • Undo Data

ROLLBACK - undo tablespace is wonderful.

Atomic Consistent Isolated Durable (ACID) -

Oracle Cloud[4]

Internal Links

Parent Article: Main Page