Oracle Database 12c
Oracle Database 12c
select * from dual;
The notorious v$
- Oracle Database 12c Fundamentals
Oracle is called an object-relational database
- Key Terms
- Relation = Table
- Attribute = Column
- 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
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:
- spfile - binary
- 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.
- Set %TNS_ADMIN% environmental variable mapped to %ORACLE_HOME%
- Listener name
- Protocol address(es) that the listener is accepting requests on
- Database services
LISTENER = // default name (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orserver)(PORT=1521)) } )
- Indents must be spaces
- '(' and ')' MUST line up.
listener control utility http://is.gd/gjnng5
Oracle Naming Methods
TNSNAMES.ORA, client side file. same syntax.
Oracle Internet Directory (OID) - Same as Windows Active Directory
- LDAPv3 directory service
Oracle Database Client
Oracle Storage Structures
- 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.
Creating Database Objects
- 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
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.
'grant create session' to new users so they can connect.
- Undo Data
ROLLBACK - undo tablespace is wonderful.
Atomic Consistent Isolated Durable (ACID) -
Parent Article: Main Page