5 Simple (But Important) Things To Remember About Oracle Database 12c views ,parameters and packages



Last updated on August 23rd, 2016 at 05:09 am

5 Simple (But Important) Things To Remember About Oracle Database 12c views ,parameters and packages

Multitenant Database

Oracle is doing every thing to jump into the cloud bandwagon. With 12C, Oracle is trying to address the problem of Multitenancy through this feature. There is a radical change and a major change in the core database architecture through the introduction of Container Databases also called CBD and Pluggable Databases (PDB). The memory and process is owned by the Container Database. The container holds the metadata where the PDBs hold the user data. You can create upto 253 PDBs including the seed PDB

Pluggable Database Views,Parameter,Packages

CDB_xyz All of the objects in the CDB across all PDBs
DBA_xyz All of the objects in a container or PDB
CDB_pdbs All PDBs within CDB
CDB_tablespaces All tablespaces within CDB
CDB_users All users within CDB (common and local)
V$PDBS Displays information about PDBs associated with the current instance
V$CONTAINERS Displays information about PDBs and the root associated with the current instance
PDB_PLUG_IN_VIOLATIONS Displays information about PDB violations after compatibility check with CDB
RC_PDBS Recovery catalog view about PDB backups

 

CDB parameters

ENABLE_PLUGGABLE_DATABASE Required to create a CDB at CDB instance startup

 

PDB_FILE_NAME_CONVERT Maps names of existing files to new file names when processing a CREATE PLUGGABLE DATABASE statement
CDB_COMPATIBLE Enables you to get behavior similar to a non-CDB

 

PDB_OS_CREDENTIAL Enables another OS user than oracle to connect to PDBs

 

CDB packages

  • DESCRIBE
  • CHECK_PLUG_COMPATIBILITY

 

DATA REDACTION

This is one of the top features in Oracle 12C. Data Redaction in simple terms means, masking of data. You can setup a Data Redaction policy, for example salary field in a Employee table can be masked. This is called redaction.

When you do a select * from employee, it will show that the Salary is masked.
The new data masking will use a package called DBMS_REDACT. It is the extension to the FGAC and VPD present in earlier versions.

Views,Parameter,Packages

View

 

1) REDACTION_POLICIES

2) REDACTION_COLUMNS

3) REDACTION_VALUES_FOR_TYPE_FULL

 

Package

 

1) DBMS_REDACT.ADD_POLICY

2) DBMS_REDACT.ALTER_POLICY

3) DBMS_REDACT.DROP_POLICY

4) DBMS_REDACT.ENABLE_POLICY

5) DBMS_REDACT.DISABLE_POLICY

6) DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES

 

Privilege 1) EXEMPT REDACTION POLICY

2) EXEMPT DDL REDACTION POLICY

3) EXEMPT DML REDACTION POLICY

 

 

Heat Map and Automatic Data Optimization

ILM (Information Life Cycle) is to reduce the cost of storage, improve performance and access times for both current as well as archived data and retain data long enough so as to satisfy regulatory laws and statutes related to data preservation

Oracle 12c as a new feature called Heat Map which tracks and marks data even down to the row and block level  as it goes through life cycle changes.

ADO or Automatic Data  Optimization  works with the Heat Map feature and allows us to create policies  at the tablespace, object and even row level which specify conditions to dictate when data will be moved or compressed based on statistics related to the data usage.

Real-time data access statistics are collected in memory in the V$HEAT_MAP_SEGMENT view and then regularly flushed by DBMS_SCHEDULER_JOBS to tables on disk like HEAT_MAP_STAT$ which is presented to the DBA via views like  DBA_HEAT_MAP_SEG_HISTOGRAM and DBA_HEAT_MAP_SEGMENT.

Heat Map is enabled at the instance level by setting the parameter HEAT_MAP to ON.

Views,Parameter,Packages

Views 1) DBA_HEAT_MAP_SEG_HISTOGRAM

2) DBA_HEAT_MAP_SEGMENT

3) V$HEAT_MAP_SEGMENT

4) DBA_ILMOBJECTS

5) DBA_ILMPOLICIES, DBA_ILMDATAMOVEMENTPOLICIES

6) DBA_ILMTASKS, DBA_ILMEVALUATIONDETAILS

7)DBA_ILMRESULTS

 

Init ora parameter HEAT_MAP
Packages DBMS_HEAT_MAP

–   BLOCK_HEAT_MAP

–   EXTENT_HEAT_MAP

DBMS_ILM

–   EXECUTE_ILM, STOP_ILM

–   PREVIEW_ILM, ADD_TO_ILM, REMOVE_FROM_ILM

–   EXECUTE_ILM_TASK

DBMS_ILM_ADMIN

–   CUSTOMIZE

–   DISABLE_ILM, ENABLE_ILM

–   CLEAR_HEAT_MAP_ALL, CLEAR_HEAT_MAP_TABLE

–   SET_HEAT_MAP_START

–   SET_HEAT_MAP_ALL, SET_HEAT_MAP_TABLE

 

 

Temporary UNDO

Views,Parameter,Packages

View

 

V$TEMPUNDOSTAT
Parameter

 

TEMP_UNDO_ENABLED=TRUE

Oracle Database In-Memory

Oracle Database In-Memory provides a unique dual-format architecture that enables tables to be simultaneously represented in memory using traditional row format and a new in-memory column format. The Oracle SQL Optimizer automatically routes analytic queries to the column format and OLTP queries to the row format, transparently delivering best-of-both-worlds performance. Oracle Database 12c automatically maintains full transactional consistency between the row and the column formats, just as it maintains consistency between tables and indexes today. The new column format is a pure in-memory format and is not persistent on disk, so there are no additional storage costs or storage synchronization issues.

Views,Parameter,Packages

Packages DBMS_INMEMORY.REPOPULATE

 

New columns 1) INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION in DBA_TABLES, DBA_TAB_PARTITIONS

2) DEF_INMEMORY_PRIORITY, DEF_INMEMORY_DISTRIBUTE, DEF_INMEMORY_COMPRESSION in DBA_TABLESPACES

 

Init ora parameter INMEMORY_SIZE

INMEMORY_QUERY

INMEMORY_CLAUSE_DEFAULT

INMEMORY_FORCE

 

Views V$IM_COLUMN_LEVEL

V$IM_COL_CU

V$IM_HEADER

V$IM_SEGMENTS

V$IM_USER_SEGMENTS

V$IM_SEGMENTS_DETAIL

V$IM_SEG_EXT_MAP

V$IM_TBS_EXT_MAP

V$IM_SMU_HEAD

V$IM_SMU_CHUNK

 

Related articles

Very useful 10 new things in 12c database

Oracle database 12c: Container Database (CDB) and Pluggable Database (PDB)

Oracle Database 12c – Multitenant Architecture


Leave a Reply