Advanced concepts on database

How to use Data Pump in Oracle:

This article explains how to use Data Pump in Oracle to export/import data to/from the Oracle cluster. Data pump is a Utility provided by Oracle to Export(EXPDP)/Import(IMPDP) the data to/from the new/old Oracle cluster started from Oracle 10g(10.1), which are performance-wise better as compared to the original Export(EXP) and Import(IMP) utilities. It supports different modes …

How to use Data Pump in Oracle: Read More »

Collections in oracle pl sql with examples

With this article, we will know how to create and use PL/SQL collection and record variables. All these composite variables have elements that we can treat as individual variables. We can pass composite variables to subprograms as a parameter.To create a collection or record variable, first we need to define a collection or record type, …

Collections in oracle pl sql with examples Read More »

Nested SubProcedure

Nested SubProcedure Purpose of Nested SubProcedure/Function: A Nested function/procedure definitions are a form of information hiding and are useful for dividing procedural tasks into subtasks which are only meaningful locally.  This avoids confusion with other parts of the program with functions and variables that are unrelated to those specific parts. After go through the below …

Nested SubProcedure Read More »

oracle performance tuning 12c

oracle performance tuning 12c SQL Tuning The main objective of “Oracle performance tuning”, “SQL tuning” is to avoid performing unnecessary work to access rows that do not affect the result. Scenarios: If a table has huge data, and some SQL queries are taking so much time to execute, what could be the reason? How to tune that query? 1. …

oracle performance tuning 12c Read More »

Oracle Data Redaction

Oracle Data Redaction Oracle Database 12c introduced a new feature related to Advanced Security option which enables the protection of data shown to the user in real-time, without requiring changes to the application. The feature is called Data-redaction. During Data redaction the stored data remain unchanged, while the data to be displayed is transformed on-the-fly before …

Oracle Data Redaction Read More »

DBMS_REDACT API parameters

DBMS_REDACT API parameters: NameDatatype Value Description Add Policy NONEBINARY_INTEGER 0 No redaction, zero effect on the result of a query against table. FULLBINARY_INTEGER 1 Full redaction, redacts full values of the column data. PARTIALBINARY_INTEGER 2 Partial redaction, redacts a portion of the column data. FORMAT_PRESERVING BINARY_INTEGER 3 Format Preserving RANDOMBINARY_INTEGER 4 Random redaction, each query …

DBMS_REDACT API parameters Read More »

High Level Watermark, PCTFREE and PCTUSED

High Level Watermark(HLW) in datablock: High Level Watermark is a term used with table segment in DB to show the amount of datablock occupied(level) by the table data. As per PCTUSED and PCTFREE mentioned for datablock, the data will fillup in datablocks. When a table created(without data), the High Level Watermark will be on left most datablock(consider the datablocks arrange horizontally). After inserting few records, it fills few datablock, …

High Level Watermark, PCTFREE and PCTUSED Read More »