New OCI GoldenGate Service is First of Any Major Cloud Provider to Deliver Operational and Analytic Integration into a Single Data Fabric

Oracle Corporation is unveiling Oracle Cloud Infrastructure GoldenGate. Oracle Cloud Infrastructure GoldenGate is a fully-managed cloud service that customers can use to help ensure that their valuable data is always available and analyzable in real-time. Best of all, Oracle Cloud Infrastructure GoldenGate is built on the industry leading, highly trusted real time data technology – Oracle GoldenGate. The new Oracle Cloud Infrastructure (OCI) GoldenGate service provides customers with a real-time data fabric platform to design, execute, orchestrate, and monitor their data replication and streaming data events. OCI GoldenGate is the first cloud service of its kind to provide an elastic, pay-per-use solution for general purpose database replication, operational integration, real-time data ingestion to cloud, and supporting time-series analytics while data is in flight.

About Oracle Cloud Infrastructure GoldenGate

The OCI GoldenGate service is the first of any major cloud provider cloud service to overcome the biggest obstacles IT organizations face when migrating their mission-critical databases to the cloud. This includes the time, expertise, and skills it takes to extract, replicate, and load their data into cloud data stores, making the process excessively complex and costly. In contrast, the OCI GoldenGate service uniquely delivers highly automated data integration, change data capture, and real-time transaction replication. It aligns database operational integration with real-time analytic insights in a multi-cloud ecosystem. The combination of cloud elasticity and operational simplicity typically results in lowering costs to customers by half as much as other cloud providers’ do-it-yourself options.

Key use cases of OCI GoldenGate include:

◉ Real-Time Data Warehouse. Provide continuous, real-time capture and delivery of changed data between OLTP and data warehouse systems.

◉ Oracle SaaS. Direct integration with Oracle SaaS to deliver real-time change data capture to downstream systems to provide deeper analysis and business insight.

◉ Operational Reporting. Offload reporting activity from production databases to lower cost of secondary systems with current data for real-time reporting.

◉ Operational Data Integration. Integrate operational data between OLTP systems in real-time. Enable service-oriented architectures to operate with real-time data by publishing changed data.

OCI GoldenGate runs on the latest Oracle GoldenGate 21c microservices architecture, which allows for a global mesh of connected data resources. OCI GoldenGate’s fully encapsulated micro services enable customers to seamlessly integrate the service into their dev-ops CI/CD pipeline as a first-class, infrastructure-as-code service. OCI GoldenGate is architected to integrate with on-premises Docker and Kubernetes “mesh” deployments of GoldenGate with no shared framework dependencies and no shared repository, enabling superior manageability with lower TCO. With a single click, developers and DBAs can set the service for 3x auto-scale—providing a scalable way to start small and grow as workloads increase. 

Get Started Today with OCI GoldenGate

Start today by creating your own OCI GoldenGate real-time data fabric in just minutes. 

OCI GoldenGate makes it easier than ever to get started. Out of the box OCI GoldenGate automates many functions, including configuration, workload scaling, and patching while maintaining high availability. Its intuitive interface empowers non-DBA users to drive digital transformation, simplifying on-premises to cloud integrations, multi-region data synchronization, data pipelines, and multi-cloud database replication. The new service is seamlessly integrated with GoldenGate Stream Analytics for OCI, providing multi-cloud streaming data analytics with AI/ML, geo-spatial and time-series data. OCI GoldenGate's web-based user interface removes the complexity associated with deployment, providing easy setup and ongoing monitoring.

To get started, login into the OCI Console and start creating OCI GoldenGate Deployments.

With just a couple of clicks an elastic GoldenGate data mesh is created providing the foundation for your real-time data integration. Enabling real time data replication to and from various end points including, on premise, 3rd party cloud, SaaS, OCI and many more. The following is an example of real time database replication between two distinct Oracle Autonomous Databases located in different OCI regions.

Also, because OCI GoldenGate is built on Oracle GoldenGate, it is fully managed. The service networking, physical storage, and security all are pre-configured, tuned, and ready to use. OCI GoldenGate service components are continually patched and upgraded for you. For patches to the underlying GoldenGate infrastructure, customers will receive advanced notifications, and the patches can be seamlessly applied as a function of OCI GoldenGate, enabling OCI GoldenGate to quickly evolve and adapt for years to come.

Finally, perhaps the biggest news related to the new OCI GoldenGate service is its highly affordable price. Now, you can get OCI GoldenGate running in OCI for less than US$1000 per month*. This price makes OCI GoldenGate accessible to enterprises of all sizes. Furthermore, there is no real time data fabric service like this from other vendors. OCI GoldenGate provides a powerful, affordable and easy to use data fabric service with no additional fees or limits.

OCI GoldenGate usage is currently priced at $1.34 per OCPU hour, with metering and billing based on OCPU usage. Each OCPU corresponds to two hardware execution threads, known as vCPUs.

Try OCI GoldenGate Today

OCI GoldenGate is available today and you can quickly walk through our Oracle LiveLab on OCI GoldenGate here - docs.oracle.com/en/cloud/paas/goldengate-service/tutorials.html. Walking through this quick start tutorial you can see how easy it is to setup a real-time data mesh between an Autonomous Transaction Processing (ATP) instance and an Autonomous Data Warehouse (ADW) instance. 

Take the OCI GoldenGate guided tour:

Source: oracle.com

Continue reading

DynamoDB - Basic Concepts

Before using DynamoDB, you must familiarize yourself with its basic components and ecosystem. In the DynamoDB ecosystem, you work with tables, attributes, and items. A table holds sets of items, and items hold sets of attributes. An attribute is a fundamental element of data requiring no further decomposition, i.e., a field.

Primary Key

The Primary Keys serve as the means of unique identification for table items, and secondary indexes provide query flexibility. DynamoDB streams record events by modifying the table data.

More Info: 1Z0-064: Oracle Database 12c - Performance Management and Tuning

The Table Creation requires not only setting a name, but also the primary key; which identifies table items. No two items share a key. DynamoDB uses two types of primary keys −

◉ Partition Key − This simple primary key consists of a single attribute referred to as the “partition key.” Internally, DynamoDB uses the key value as input for a hash function to determine storage.

◉ Partition Key and Sort Key − This key, known as the “Composite Primary Key”, consists of two attributes.

     ◉ The partition key and

     ◉ The sort key.

DynamoDB applies the first attribute to a hash function, and stores items with the same partition key together; with their order determined by the sort key. Items can share partition keys, but not sort keys.

The Primary Key attributes only allow scalar (single) values; and string, number, or binary data types. The non-key attributes do not have these constraints.

Secondary Indexes

These indexes allow you to query table data with an alternate key. Though DynamoDB does not force their use, they optimize querying.

DynamoDB uses two types of secondary indexes −

◉ Global Secondary Index − This index possesses partition and sort keys, which can differ from table keys.

◉ Local Secondary Index − This index possesses a partition key identical to the table, however, its sort key differs.

API

The API operations offered by DynamoDB include those of the control plane, data plane (e.g., creation, reading, updating, and deleting), and streams. In control plane operations, you create and manage tables with the following tools −

◉ CreateTable

◉ DescribeTable

◉ ListTables

◉ UpdateTable

◉ DeleteTable

In the data plane, you perform CRUD operations with the following tools −

Create	Read	Update	Delete
PutItem BatchWriteItem	GetItem BatchGetItem Query Scan	UpdateItem	DeleteItem BatchWriteItem

The stream operations control table streams. You can review the following stream tools −

◉ ListStreams

◉ DescribeStream

◉ GetShardIterator

◉ GetRecords

Provisioned Throughput

In table creation, you specify provisioned throughput, which reserves resources for reads and writes. You use capacity units to measure and set throughput.

When applications exceed the set throughput, requests fail. The DynamoDB GUI console allows monitoring of set and used throughput for better and dynamic provisioning.

Read Consistency

DynamoDB uses eventually consistent and strongly consistent reads to support dynamic application needs. Eventually consistent reads do not always deliver current data.

The strongly consistent reads always deliver current data (with the exception of equipment failure or network problems). Eventually consistent reads serve as the default setting, requiring a setting of true in the ConsistentRead parameter to change it.

Partitions

DynamoDB uses partitions for data storage. These storage allocations for tables have SSD backing and automatically replicate across zones. DynamoDB manages all the partition tasks, requiring no user involvement.

In table creation, the table enters the CREATING state, which allocates partitions. When it reaches ACTIVE state, you can perform operations. The system alters partitions when its capacity reaches maximum or when you change throughput.

Continue reading

Oracle Enterprise Database Service

Oracle Enterprise Database Service’s managed compute and storage infrastructure allow customers to efficiently run Oracle Database workloads in Oracle Cloud Infrastructure (OCI). Consumption-based subscription pricing for managed compute and storage infrastructure with low-cost OCI block storage enable IT departments to lower the cost of running Oracle Database workloads in the cloud.

Oracle Enterprise Database Service

1. Enterprise Database Service - Base

Low cost cloud for enterprise databases

Enterprise Database Service – Base allows customers to develop and run enterprise applications in low-cost Oracle Cloud Infrastructure environments using core Oracle Database Enterprise Edition capabilities running on virtual machines or bare metal cloud infrastructure.

Features

- Runs Oracle Database 11.2, 12.1, 12.2, 18c, 19c, or 21c with converged database capabilities to meet application-specific customer requirements

- Multilayered security including always-on transparent database encryption allows customers to protect databases at the source

- Advanced database security and management technologies, such as Data Safe, Data Masking and Subsetting Pack, and Diagnostics and Tuning Packs, enable DBAs to meet enterprise service level agreements

- Fast provisioning of single-node virtual machine database systems for Oracle Database with logical volume manager increases developer productivity

- Scaling of database OCPU licenses allows customers to meet application needs while minimizing costs

- Built-in Oracle Cloud Infrastructure automation increases DBA productivity by simplifying database lifecycle management

- Oracle Machine Learning is included at no cost, allowing developers to add innovative, in-database, machine learning (ML) capabilities to existing applications

- Hybrid Columnar Compression reduces storage requirements for customers’ data warehouses

- Automated or on-demand back up to Oracle Cloud Infrastructure Object Storage increases protection for customer databases

- Flexible subscription and BYOL subscription models enable customers enable customers to tightly manage costs

2. Enterprise Database Service - High Performance

Enhanced resource management and security for very large database environments

Enterprise Database Service – High Performance enables customers to implement efficient multitenant environments and tightly secure them. Oracle Multitenant, together with Oracle Database security capabilities, enable customers to increase operating efficiency, performance, and security for multitenant environments. Available on virtual machine and bare metal infrastructure.

Features

- Advanced capabilities to the Enterprise Database Service Base subscription

- Additional consolidation capabilities, such as Oracle Multitenant support of more pluggable databases per container database, increasing consolidation efficiency

- Additional security capabilities, including Oracle Database Vault Oracle Label Security, and Redaction allow customer to improve protection of sensitive data

- Additional very large database capabilities, such as Oracle Advanced Compression, Oracle Partitioning, and Oracle OLAP technologies, increase the efficiency and performance of customers’ data warehouses and business intelligence environments

- Additional manageability features, including Oracle Cloud Management Pack for Oracle Database and Oracle Database Lifecycle Management Pack improve database administrator productivity

3. Enterprise Database Service - Extreme Performance

All-inclusive functionality

Enterprise Database Cloud Service – Extreme Performance allows customers to optimize resources, utilization, availability, and performance for enterprise environments. Available on virtual machine and bare metal infrastructure.

Features

- Adds advanced availability and performance technologies to Enterprise Database Service High Performance subscription

- Enterprise database storage provisioning with Oracle Automatic Storage Management mirroring in virtual machine environments increases the availability of customer databases

- Additional high-availability capabilities, including Oracle Real Application Clusters (Oracle RAC) support in virtual machine environments and Oracle Active Data Guard, enable customers to deploy resilient environments across multiple systems, availability domains, or Oracle Cloud Infrastructure regions

- Additional performance capabilities, such as support for Oracle In-memory column stores and aggregation, allow customers to increase performance for data warehouses and analytics

- Additional Oracle Sharding capabilities supporting unlimited primary and standby shards for increased performance and deployment flexibility

4. Virtual machine infrastructure

Lowest-cost platform for Enterprise Database Cloud Service

Virtual machine infrastructure allows customers to minimize costs when running Oracle Enterprise Database Cloud Service on Oracle Cloud Infrastructure. Shared computing resources with provisioned block storage economically run a wide variety of database workloads and business applications.

Features

- Compute shapes with 1 to 24 cores support customers with small to large database processing requirements

- 15 GB of memory per OCPU and up to 40 TB of Oracle Cloud Infrastructure Block Storage allow customers to optimize costs for a wide range of database sizes

- Fast provisioning of single-node virtual-machine infrastructure for Oracle Database with logical volume manager increases developer productivity

- Two-node Oracle Real Application Clusters (Oracle RAC) deployments with Enterprise Database Service—extreme performance on virtual machine infrastructure increases scalability and availability for customer databases

- On-demand up and down scaling of database OCPUs without interrupting database operations on two-node Oracle Real Application Cluster deployments allows customers to meet application needs while minimizing costs

5. Bare metal infrastructure

High performance for Enterprise Database Cloud Service

Bare-metal infrastructure allows customers to optimize Oracle Enterprise Database Service performance using dedicated hardware with low-latency NVMe storage. Isolated database service resources allow customers to maximize security and achieve high levels of predictable performance.

Features

- Dedicated servers with 52 CPU cores and 768 GB of memory enable customers to run databases with high performance

- Built-in NVME storage optimizes performance for latency-sensitive customer databases with a choice of 16 TB of usable capacity with 2-way mirroring or 9 TB of capacity with 3-way mirroring

- On-demand up and down scaling of database OCPUs without interrupting database operations allows customers to meet application needs while minimizing costs

Source: oracle.com

Continue reading

SQL: Subqueries to Solve Queries

A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.

A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view. Subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query.

Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause.In practice, the limit of 255 levels is not really a limit at all because it is rare to encounter subqueries nested beyond three or four levels.

A subquery SELECT statement is very similar to the SELECT statement used to begin a regular or outer query.The complete syntax of a subquery is:

( SELECT [DISTINCT] subquery_select_parameter

  FROM {table_name | view_name}

               {table_name | view_name} ...

  [WHERE search_conditions]

  [GROUP BY column_name [,column_name ] ...]

  [HAVING search_conditions] )

Types of Subqueries

Single Row Sub Query: Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.

Multiple row sub query: Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.

Single Row Sub Query

A single-row subquery is used when the outer query's results are based on a single, unknown value. Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.

In the below SELECT query, inner SQL returns only one row i.e. the minimum salary for the company. It in turn uses this value to compare salary of all the employees and displays only those, whose salary is equal to minimum salary.

SELECT first_name, salary, department_id

FROM employees

WHERE salary = (SELECT MIN (salary) 

FROM employees); 

A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery's result must be compared with a group function, you must nest the inner query in the outer query's HAVING clause.

SELECT department_id, MIN (salary)

FROM employees

GROUP BY department_id

HAVING MIN (salary)  < (SELECT AVG (salary)

FROM employees)

Multiple Row Sub Query

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows,it must be handled by set comparison operators (IN, ALL, ANY).While IN operator holds the same meaning as discussed in earlier chapter, ANY operator compares a specified value to each value returned by the sub query while ALL compares a value to every value returned by a sub query.

Below query shows the error when single row sub query returns multiple rows.

SELECT first_name, department_id

FROM employees

WHERE department_id = (SELECT department_id

FROM employees

WHERE LOCATION_ID = 100)

department_id = (select

               *

ERROR at line 4:

ORA-01427: single-row subquery returns more than one row 

Usage of Multiple Row operators

◉ [> ALL] More than the highest value returned by the subquery

◉ [< ALL] Less than the lowest value returned by the subquery

◉ [< ANY] Less than the highest value returned by the subquery

◉ [> ANY] More than the lowest value returned by the subquery

◉ [= ANY] Equal to any value returned by the subquery (same as IN)

Above SQL can be rewritten using IN operator like below.

SELECT first_name, department_id

FROM employees

WHERE department_id IN (SELECT department_id

                        FROM departments

                        WHERE LOCATION_ID = 100)

Note in the above query, IN matches department ids returned from the sub query,compares it with that in the main query and returns employee's name who satisfy the condition.

A join would be better solution for above query, but for purpose of illustration, sub query has been used in it.

Correlated Sub Query

As opposed to a regular subquery, where the outer query depends on values provided by the inner query,a correlated subquery is one where the inner query depends on values provided by the outer query. This means that in a correlated subquery,the inner query is executed repeatedly, once for each row that might be selected by the outer query.

Correlated subqueries can produce result tables that answer complex management questions.

Consider the below SELECT query. Unlike the subqueries previously considered, the subquery in this SELECT statement cannot be resolved independently of the main query. Notice that the outer query specifies that rows are selected from the employee table with an alias name of e1. The inner query compares the employee department number column (DepartmentNumber) of the employee table with alias e2 to the same column for the alias table name e1.

SELECT EMPLOYEE_ID, salary, department_id

FROM   employees E

WHERE salary > (SELECT AVG(salary)

                FROM   EMP T

                WHERE E.department_id = T.department_id)

Multiple Column Sub Query

A multiple-column subquery returns more than one column to the outer query and can be listed in the outer query's FROM, WHERE, or HAVING clause. For example, the below query shows the employee's historical details for the ones whose current salary is in range of 1000 and 2000 and working in department 10 or 20.

SELECT first_name, job_id, salary

FROM emp_history

WHERE (salary, department_id) in (SELECT salary, department_id

  FROM employees

    WHERE salary BETWEEN 1000 and 2000 

  AND department_id BETWEEN 10 and 20)

ORDER BY first_name;

When a multiple-column subquery is used in the outer query's FROM clause, it creates a temporary table that can be referenced by other clauses of the outer query. This temporary table is more formally called an inline view. The subquery's results are treated like any other table in the FROM clause. If the temporary table contains grouped data, the grouped subsets are treated as separate rows of data in a table. Consider the FROM clause in the below query. The inline view formed by the subquery is the data source for the main query.

SELECT * 

FROM (SELECT salary, department_id

FROM employees

  WHERE salary BETWEEN 1000 and 2000);

Continue reading

Elevate your database into the cloud using Oracle Cloud Infrastructure Database Migration

Oracle has reinvented databases in the cloud by providing self-driving, self-securing, and self-repairing databases such as  Autonomous Transaction Processing and Autonomous Data Warehouse. These database solutions eliminate the complexities of operating a database, securing data, and developing data-driven applications. Today, we’re launching the new managed cloud service Oracle Cloud Infrastructure Database Migration (DMS) to ease the move of established databases from on-premise, on third-party, or on Oracle cloud to Oracle Autonomous Database.  

Oracle Cloud Infrastructure Database Migration is a fully-managed service that provides a high performing, self-service experience for migrating databases to Oracle Cloud Infrastructure (OCI). It provides logical online and offline migration for enterprise-level database migration with minimal downtime. Oracle Cloud Infrastructure Database Migration is based on industry-leading Oracle Zero Downtime Migration engine utilizing Oracle GoldenGate replication.

Migrating a database is as easy as opening the OCI Web Console and selecting the source and target database. The managed cloud service will validate your environment and perform a migration while your database and applications are online. While the database is migrated, all transactions are captured and replicated to the target using the highly performant and reliable Oracle GoldenGate replication solution.

Best of all, Oracle Cloud Infrastructure Database Migration is free to use for the first 6 months. 

Start now and watch a demo video how to migrate into the Oracle Cloud from all source locations such as on-premise, third-party or Oracle cloud, with or without a direct connection.

Source: oracle.com

Continue reading

Difference between SQL Server and Oracle

What is Microsoft SQL server?

MS SQL server is a database product of Microsoft. It allows users to SQL queries and execute them. It is among the most stable, secure and reliable database solutions. It supports wide variety of transaction processing, analytics, and business intelligence applications in corporate IT environments.

What is Oracle Database?

Oracle database is an RDMS system from Oracle Corporation. The software is built around the relational database framework. It allows data objects to be accessed by users using SQL language. Oracle is a completely scalable RDBMS architecture which is widely used all over the world.

Oracle is one of the biggest vendor in the IT market and the shorthand name of its flagship RDBMS product, that was formally called Oracle Database.

Difference between SQL Server and Oracle

Parameters	Ms- SQL Server	Oracle
Parent Company	It is owned by Microsoft Corporation.	It is owned by Oracle Corporation.
Syntax	Simpler and easier syntaxes.	Complex and more efficient syntaxes.
Download	120 or 180 days evaluation version are available from www. Micrsoft.com/sqlserver	Opensource version can be download from otn.oracle.com
Platform support	Can install on Windows server only. But version 2017 onwards can be installed on Linux	Run on a wide variety of platforms
Language	MS-SQL uses transact SQL or T-SQL.	PL/SQL or is used by Oracle Corporation.
Job scheduling	Job Scheduling via the SQL Server Agent	Job scheduling via Oracle scheduler or OEM
Bit map indexes	No bitmap indexes base on reverse keys and functions.	Uses bitmap, indexes based on functions, and reverse keys.
Query optimization	No query optimization.	Uses Star query optimization.
Triggers	Mostly uses "after" triggers.	Uses both "after" and "before" Triggers.
Support & Trouble Shooting	Provides technical notes, bug descriptions, scripts, patches, and download at a not additional charge.	Support call which is chargeable for each support case.
Roll back	Not allowed in the transaction process.	Rollback is allowed during the transaction process.
Concurrent accesses	Concurrent accesses are not allowed when a writer is in progress which increase the wait time.	Concurrent accesses are permitted and waits time are generally less.
Change of Value	Values are changed even before commit.	Values do not change before commit.
Method	Use Row or Page blocking method. It never allows a read while the page is blocked.	Use a copy of the record so while modifying it allows reads of original data while doing the modification.
Error handling	SQL Server executes each command separately, so it will be quite difficult to make changes if any errors are encountered during the process.	Oracle treats each new database connection as a new transaction.
Human Intervention	Follows Global memory allocation so less intrusion of Database admin. Therefore, very few chances of human errors.	Follows Dynamic memory allocation. DBA has to interrupt more. So higher chances or human errors.
Parallel execution	In MS SQL server INSERT, UPDATE, DELETE statements are executed serially.	Oracle will execute INSERT, UPDATE, DELETE, and MERGE statement in parallel.
Automation support	SQL upgrade advisor is available for automation.	Database upgrade assistant available for automation.
Redo stream	Redo streams are unique to each user and database.	One redo stream at the database level.
Schemas	"Schemas" within each use database.	Many "schemas" with the instance.
Protection	Logins authenticated at the instance level and database level.	User authenticated via database credential and OS roles.
Sharability	Every database has its own, unshared disk file on the server.	All the database objects are grouped by schemas. Subset collection of database objects and all the database objects are shared between all schemas and users.
Backups	It allows full, partial and incremental backups	It allows Database, full, file level, incremental & differential backups.
Maintenance	Tables usually stored Index-organized.	Automatically update statistics and identify SQL issue

Continue reading

Introduction of Object Oriented Programming

As the name suggests, Object-Oriented Programming or OOPs refers to languages that use objects in programming. Object-oriented programming aims to implement real-world entities like inheritance, hiding, polymorphism, etc in programming. The main aim of OOP is to bind together the data and the functions that operate on them so that no other part of the code can access this data except that function.

OOPs Concepts:

◉ Class

◉ Objects

◉ Data Abstraction 

◉ Encapsulation

◉ Inheritance

◉ Polymorphism

◉ Dynamic Binding

◉ Message Passing

1. Class:

A class is a user-defined data type. It consists of data members and member functions, which can be accessed and used by creating an instance of that class. It represents the set of properties or methods that are common to all objects of one type. A class is like a blueprint for an object.  

For Example: Consider the Class of Cars. There may be many cars with different names and brands but all of them will share some common properties like all of them will have 4 wheels, Speed Limit, Mileage range, etc. So here, Car is the class, and wheels, speed limits, mileage are their properties.

2. Object: 

It is a basic unit of Object-Oriented Programming and represents the real-life entities. An Object is an instance of a Class. When a class is defined, no memory is allocated but when it is instantiated (i.e. an object is created) memory is allocated. An object has an identity, state, and behavior. Each object contains data and code to manipulate the data. Objects can interact without having to know details of each other’s data or code, it is sufficient to know the type of message accepted and type of response returned by the objects. 

For example “Dog” is a real-life Object, which has some characteristics like color, Breed, Bark, Sleep, and Eats.

Object

3. Data Abstraction: 

Data abstraction is one of the most essential and important features of object-oriented programming. Data abstraction refers to providing only essential information about the data to the outside world, hiding the background details or implementation. Consider a real-life example of a man driving a car. The man only knows that pressing the accelerators will increase the speed of the car or applying brakes will stop the car, but he does not know about how on pressing the accelerator the speed is increasing, he does not know about the inner mechanism of the car or the implementation of the accelerator, brakes, etc in the car. This is what abstraction is.

4. Encapsulation: 

Encapsulation is defined as the wrapping up of data under a single unit. It is the mechanism that binds together code and the data it manipulates. In Encapsulation, the variables or data of a class are hidden from any other class and can be accessed only through any member function of their class in which they are declared. As in encapsulation, the data in a class is hidden from other classes, so it is also known as data-hiding.

Consider a real-life example of encapsulation, in a company, there are different sections like the accounts section, finance section, sales section, etc. The finance section handles all the financial transactions and keeps records of all the data related to finance. Similarly, the sales section handles all the sales-related activities and keeps records of all the sales. Now there may arise a situation when for some reason an official from the finance section needs all the data about sales in a particular month. In this case, he is not allowed to directly access the data of the sales section. He will first have to contact some other officer in the sales section and then request him to give the particular data. This is what encapsulation is. Here the data of the sales section and the employees that can manipulate them are wrapped under a single name “sales section”.

5. Inheritance: 

Inheritance is an important pillar of OOP(Object-Oriented Programming). The capability of a class to derive properties and characteristics from another class is called Inheritance. When we write a class, we inherit properties from other classes. So when we create a class, we do not need to write all the properties and functions again and again, as these can be inherited from another class that possesses it. Inheritance allows the user to reuse the code whenever possible and reduce its redundancy.

6. Polymorphism: 

The word polymorphism means having many forms. In simple words, we can define polymorphism as the ability of a message to be displayed in more than one form. For example, A person at the same time can have different characteristics. Like a man at the same time is a father, a husband, an employee. So the same person posses different behavior in different situations. This is called polymorphism.

7. Dynamic Binding:

In dynamic binding, the code to be executed in response to the function call is decided at runtime. Dynamic binding means that the code associated with a given procedure call is not known until the time of the call at run time. Dynamic Method Binding One of the main advantages of inheritance is that some derived class D has all the members of its base class B. Once D is not hiding any of the public members of B, then an object of D can represent B in any context where a B could be used. This feature is known as subtype polymorphism.

8. Message Passing:

It is a form of communication used in object-oriented programming as well as parallel programming. Objects communicate with one another by sending and receiving information to each other. A message for an object is a request for execution of a procedure and therefore will invoke a function in the receiving object that generates the desired results. Message passing involves specifying the name of the object, the name of the function, and the information to be sent.

Continue reading

Difference between OOP and POP

OOP:

Related with the real life objects and their properties. OOP Concepts:

1. Class and Objects

2. Data abstraction

3. Encapsulation

4. Polymorphism

5. Inheritance 

POP:

Related with the conventional style. This approach is also known as the top-down approach. In this approach, a program is divided into functions that perform specific tasks. This approach is mainly used for medium-sized applications. Data is global, and all the functions can access global data. The basic drawback of the procedural programming approach is that data is not secured because data is global and can be accessed by any function. Program control flow is achieved through function calls and go to statements.

Difference between OOP and POP:

OOP	POP
Object oriented.	Structure oriented.
Program is divided into objects.	Program is divided into functions.
Bottom-up approach.	Top-down approach.
Inheritance property is used.	Inheritance is not allowed.
It uses access specifier.	It doesn’t use access specifier.
Encapsulation is used to hide the data.	No data hiding.
Concept of virtual function.	No virtual function.
C++, Java.	C, Pascal.

Continue reading

Difference between Database Administrator vs Database Architect

1. Database Administrator:

Database administrator is a person who creates updates and maintains the database. It is more of a wide role as a data administrator might be someone who is hired to create, maintain, and backup the database, optimize the database for high performance or someone who helps in integrating databases into applications. The major skills required to be an excellent database administrator are troubleshooting, logical thought process and a strong will to learn as it involves a vast area. This role is also known as Database Coordinator or Database Programmer.

Responsibilities:

◉ Create and design a database

◉ Analyze and monitor database requirements

◉ Ensures data security

Database administrator skills:

◉ Knowledge of database design

◉ Knowledge on RDBMS (Relational Database Management System)

◉ SQL (Structured Query Language) 

◉ Analytical and problem-solving skills

◉ Data security

◉ ERP

2. Database Architect:

Database architect is a person who checks the database structure and decides the design of the database while also collaborating with the whole team to handle the requirements. Major skills required for this role are analytical skills, problem-solving skills, management skills and the most important communication skills. This role demands great knowledge of data architecture and is a difficult job.

Responsibilities :

◉ Designs the way data should flow in the organization

◉ Checks everyone’s data requirements

◉ Enforces databases standards

◉ Sets different data access levels for different teams

Database architect skills :

◉ Applied math and statistics

◉ Data visualization

◉ Design skills

◉ Data migration

◉ DBMS (Data Base Management System) Software

◉ Programming languages like SQL, Java, Python

◉ Database and cloud architecture

Difference between Database Administrator vs Database Architect :

DATABASE ADMINISTRATOR	DATABASE ARCHITECT
Database admin is also known as database coordinator or database programmer.	Database admin is also known as database coordinator or database programmer.
Database admin inputs data into the database.	Database architect decides the structure and design of the database.
Database admin optimizes and maintains the database.	Database architect sets different access
Database admin ensures database security.	Database architect enforces data standards for the whole organization.
Database admin handles issues with the database.	Database architect monitors the data and always tries to come up with a more efficient design system.
Database admin mostly requires logical thought process, troubleshooting and will to learn. However, good communication skill is the most essential requirement of this role.	Database architect requires analytical and problem-solving skills to come up with robust and feasible data models. However, good communication skill is the most essential requirement of this role.
Database admin is a wide role as it has multiple responsibilities.	Database architect is a difficult role as it requires you to collaborate with the whole team to address everyone’s issues.
A Database Administrator have the most privileged access to a database.	A database architect usually does not have direct access to the production databases.

Continue reading

Difference between Structured Programming and Object Oriented Programming

1. Structured Programming :

Structured Programming, as name suggests, is a technique that is considered as precursor to OOP and usually consists of well-structured and separated modules. In this programming, user can create its own user-defined functions as well as this methodology tries to resolve issues that are associated with unconditional transfers to allow programmers follow logic of programs. It also requires more discipline at the design and logical structing stage.

Example : Pascal, ALGOL, C, Modula-2, etc.

2. Object-Oriented Programming :

Object-Oriented Programming, as name suggests, is a different approach to programming that brings together data and functions that execute on them. It basically supports encapsulation, abstraction, inheritance, polymorphism, etc. It also includes data hiding feature therefore it is more secure. This model is based on real life entities that focuses on by whom task is to be done rather than focusing on what to do.

Example : JAVA, C#, C++, etc.

Difference between Structured Programming and Object-Oriented Programming:

Structured Programming	Object-Oriented Programming
It is a subset of procedural programming.	It relies on concept of objects that contain data and code.
Programs are divided into small programs or functions.	Programs are divided into objects or entities.
It is all about facilitating creation of programs with readable code and reusable components.	It is all about creating objects that usually contain both functions and data.
Its main aim is to improve and increase quality, clarity, and development time of computer program.	Its main aim is to improve and increase both quality and productivity of system analysis and design.
It simply focuses on functions and processes that usually work on data.	It simply focuses on representing both structure and behavior of information system into tiny or small modules that generally combines data and process both.
It is a method of organizing, managing and coding programs that can give or provide much easier modification and understanding.	It is a method in which set of objects can vary dynamically and can execute just by acting and reading to each other.
In this, methods are written globally and code lines are processed one by one i.e., Run sequentially.	In this, method works dynamically, make calls as per need of code for certain time.
It generally follows “Top-Down Approach”.	It generally follows “Bottom-Up Approach”.
It provides less flexibility and abstraction as compared to object-oriented programming.	It provides more flexibility and abstraction as compared to structured programming.
It is more difficult to modify structured program and reuse code as compared to object-oriented programs.	It is less difficult to modify object-oriented programs and reuse code as compared to structured programs.
It gives more importance of code.	It gives more importance to data.

Continue reading

Simple migration from Oracle multimedia to secure-file blob data type

Support for the Oracle Multimedia data type was ended in the Oracle Database 19c release, so we wanted to provide some guidance for working with this data type from that release onward.

Oracle recommends that you store multimedia content in secure files lob and use third party products such as the APEX multimedia extension (AME).

Current users of the Oracle Multimedia data type planning to upgrade to Oracle Database 19c will need to move their existing multimedia content to secure file lob.

Let’s take a look at few options to manage that transition.

SOLUTION 1: UPDATING COLUMN ON SAME TABLE

AN OVERVIEW OF THE PROCESS:

◉ TAKE BACKUP OF EXISTING TABLE

◉ ADD NEW COLUMN OF DATATYPE BLOB AND SECUREFILE

◉ UPDATE DATA FROM ORDIMAGE COLUMN TO BLOB COLUMN

◉ SET UNSED OLD COLUMN FROM TABLE

◉ RENAME NEW COLUMN TO ORIGINAL COLUMN NAME

◉ DELETE OLD COLUMN NAME

OBJECTS USED IN THE EXAMPLE:

TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE/IMAGE_TABLE_BKP
COLUMN WITH MULTIMEDIA DATATYPE	IMAGE
COLUMN WITH SECURE BLOB DATATYPE	IMAGE_BLOB

BACKUP OF TABLE: TAKE A BACKUP OF THE EXISTING TABLE (OPTIONAL), RECOMMENDED FOR SAFEGUARD OF DATA

CREATE TABLE IMAGE_TABLE_BKP AS SELECT * FROM IMAGE_TABLE;

ADD NEW COLUMN TO REPLACE THE EXISTING MULTIMEDIA COLUMN: CREATE A COLUMN IMAGE_BLOB WHICH WILL HOLD SECUREFILE

SQL> ALTER TABLE IMAGE_TABLE ADD (IMAGE_BLOB BLOB) LOB(IMAGE_BLOB) STORE AS SECUREFILE (TABLESPACE MULTI_SF);

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          PUBLIC.ORDIMAGE

IMAGE_BLOB                              BLOB

UPDATE NEW COLUMN WITH OLD COLUMN DATA:

SQL> UPDATE IMAGE_TABLE I SET I.IMAGE_BLOB=I.IMAGE.SOURCE.LOCALDATA;

2 ROWS UPDATED.

SQL> COMMIT;

COMMIT COMPLETE. 

MARK OLD COLUMN AS UNUSED:

SQL> ALTER TABLE IMAGE_TABLE SET UNUSED (IMAGE);

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE_BLOB                                BLOB

RENAME NEW COLUMN AS OLD COLUMN:

SQL> ALTER TABLE IMAGE_TABLE RENAME COLUMN IMAGE_BLOB TO IMAGE;

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          BLOB

DROP OLD COLUMN:

SQL> ALTER TABLE IMAGE_TABLE DROP UNUSED COLUMNS CHECKPOINT 250;

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          BLOB 

CHECK DATA IN TABLE:

SQL>  EXEC CHECK_SPACE_SECFILE('IMAGE_TABLE','MULTI_SF');

SEGMENT BLOCKS = 2072,  BYTES = 16973824

USED BLOCKS = 895,  BYTES = 7331840

EXPIRED BLOCKS = 1102,  BYTES = 9027584

UNEXPIRED BLOCKS = 0,  BYTES = 0

=============================================

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SOLUTION 2: MOVE TO A NEW TABLE 

AN OVERVIEW OF THE PROCESS:

◉ CREATE A TABLE IMAGE_TABLE_BLOB SIMILAR TO IMAGE_TABLE BUT WITH COLUMN DATA-TYPE AS BLOB INSTEAD OF ORDIMAGE DATA TYPE

◉ INSERT DATA FROM IMAGE_TABLE TO IMAGE_TABLE_BLOB

OBJECTS USED IN THE EXAMPLE:

TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE_BKP
COLUMN WITH MULTIMEDIA DATATYPE	IMAGE
TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE_BLOB
COLUMN WITH SECURE BLOB DATATYPE	IMAGE

ORIGINAL TABLE:

SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME LIKE 'IMAGE_TABLE_BKP';

OWNER                TABLE_NAME           COLUMN_NAME          DATA_TYPE

-------------------- -------------------- -------------------- --------------------

MULTI_SF             IMAGE_TABLE_BKP      IMAGE                ORDIMAGE

MULTI_SF             IMAGE_TABLE_BKP      ID                   NUMBER 

CREATE BACKUP TABLE:

CREATE TABLE IMAGE_TABLE_BLOB (ID NUMBER,IMAGE BLOB) LOB(IMAGE) STORE AS SECUREFILE (TABLESPACE MULTI_SF)

SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME LIKE 'IMAGE_TABLE_BLOB';

OWNER                TABLE_NAME           COLUMN_NAME          DATA_TYPE

-------------------- -------------------- -------------------- --------------------

MULTI_SF             IMAGE_TABLE_BLOB     ID                   NUMBER

MULTI_SF             IMAGE_TABLE_BLOB     IMAGE                BLOB

INSERT NEW DATA IN TO THE TABLE:

INSERT INTO IMAGE_TABLE_BLOB SELECT I.ID,I.IMAGE.SOURCE.LOCALDATA  FROM IMAGE_TABLE I;

CROSSCHECK DATA IN NEW TABLE:

SQL> EXEC CHECK_SPACE_SECFILE('IMAGE_TABLE_BLOB','MULTI_SF');

SEGMENT BLOCKS = 2072,  BYTES = 16973824

USED BLOCKS = 895,  BYTES = 7331840

EXPIRED BLOCKS = 1102,  BYTES = 9027584

UNEXPIRED BLOCKS = 0,  BYTES = 0

=============================================

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

VALIDATE DATA AND CHECK IF THE MIGRATION IS SUCCESSFUL

You can either drop the old table and rename the new table to OLD TABLE, or continue with the new table name

DROP TABLE IMAGE_TABLE;

RENAME IMAGE_TABLE_BLOB TO IMAGE_TABLE;

Continue reading

Blockchain Tables in Oracle Database 21c

A blockchain table is a tamper-proof, insert-only table with an associated table-level and row-level retention period.

This feature has been backported to Oracle 19.10, but also requires patch 32431413 and the COMPATIBLE parameter set to 19.10. In future release update for 19c, the additional patch shouldn't be necessary.

When learning about blockchain tables, be careful not to set excessively long retention periods, or you will have to wait a long time to drop your test tables.

◉ Create a Blockchain Table

In addition to adding the BLOCKCHAIN keyword to the CREATE TABLE command, there are three blockchain clauses.

The blockchain drop table clause determines how long the table is protected from being dropped. This only affects the DROP TABLE command. The table will still be removed if a DROP USER ... CASCADE command is issued to remove the whole schema.

◉ NO DROP [ UNTIL number DAYS IDLE ]

◉ NO DROP : The table can't be dropped. Be careful about using this setting during testing.

NO DROP UNTIL number DAYS IDLE : The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality.

The blockchain row retention clause determines how long each row will be protected from deletion.

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

◉ NO DELETE : Each row is retained forever. The absence of the LOCKED keyword implies the setting can be changed with the ALTER TABLE command, but it can't. Retention periods can only be increased.

◉ NO DELETE LOCKED : Same as NO DELETE.

◉ NO DELETE UNTIL number DAYS AFTER INSERT : Each row is protected from deletion for the specified number of days, but this setting can be increased using the ALTER TABLE command. Minimum 16 days.

◉ NO DELETE UNTIL number DAYS AFTER INSERT LOCKED : Each row is protected from deletion for the specified number of days, and this setting can't be changed using the ALTER TABLE command. Minimum 16 days.

The blockchain hash and data format clause is fixed in the current release. It looks like it will allow alternative hashing algorithms in future releases.

HASHING USING sha2_512 VERSION v1

Putting it all together gives us something like the following.

--drop table bct_t1 purge;

create blockchain table bct_t1 (

  id            number,

  fruit         varchar2(20),

  quantity      number,

  created_date  date,

  constraint bct_t1_pk primary key (id)

)

no drop until 0 days idle

no delete until 16 days after insert

hashing using "SHA2_512" version "v1";

Checking the USER_TAB_COLS view shows us several invisible columns have been added to our column list. The hidden columns are described here.

set linesize 120 pagesize 50

column column_name format a30

column data_type format a27

column hidden_column format a13

select internal_column_id,

       column_name,

       data_type,

       data_length,

       hidden_column

FROM   user_tab_cols       

WHERE  table_name = 'BCT_T1'

ORDER BY internal_column_id;

INTERNAL_ COLUMN_ID	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	HIDDEN_ COLUMN
1	ID	NUMBER	22	NO
2	FRUIT	VARCHAR2	25	NO
3	QUANTITY	NUMBER	22	NO
4	CREATED_DATE	DATE	7	NO
5	ORABCTAB_INST_ID$	NUMBER	22	YES
6	ORABCTAB_CHAIN_ID$	NUMBER	22	YES
7	ORABCTAB_SEQ_NUM$	NUMBER	22	YES
8	ORABCTAB_CREATION_TIME$	TIMESTAMP(6) WITH TIME ZONE	13	YES
9	ORABCTAB_USER_NUMBER$	NUMBER	22	YES
10	ORABCTAB_HASH$	RAW	2000	YES
11	ORABCTAB_SIGNATURE$	RAW	2000	YES
12	ORABCTAB_SIGNATURE_ALG$	NUMBER	22	YES
13	ORABCTAB_SIGNATURE_CERT$	RAW	16	YES
14	ORABCTAB_SPARE$	RAW	2000	YES

14 rows selected.

SQL>

The {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views display information about blockchain tables. It's a view over the SYS.BLOCKCHAIN_TABLE$ table.

column row_retention format a13

column row_retention_locked format a20

column table_inactivity_retention format a26

column hash_algorithm format a14

SELECT row_retention,

       row_retention_locked, 

       table_inactivity_retention,

       hash_algorithm  

FROM   user_blockchain_tables 

WHERE  table_name = 'BCT_T1';

ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM

------------- -------------------- -------------------------- --------------

           16 NO                                            0 SHA2_512

SQL>

◉ Alter a Blockchain Table

The documentation suggests the blockchain drop table clause can be altered using the ALTER TABLE command, as long as the retention period is not reduced. At the time of writing this doesn't seem to work for tables that were initially created with NO DROP UNTIL 0 DAYS IDLE, as all values of days return an error. We currently have a retention period of 0 days for the table. In the following example we try to change it to 100 days, which gives an error. The command is syntactically correct, so I assume this is a bug in this release update.

alter table bct_t1 no drop until 100 days idle;

Error report -

ORA-05732: retention value cannot be lowered

SQL>

This command will work on tables created with NO DROP UNTIL 1 DAYS IDLE or higher.

Regardless of the current drop delay setting, an attempt to switch to the maximum value of NO DROP causes an ORA-00600 error.

alter table bct_t1 no drop;

Error starting at line : 1 in command -

alter table bct_t1 no drop

Error report -

ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

This is a problem, as I would expect most people to want to play it safe by starting with a zero day delay, then upping the value later once they are happy with their setup. Starting on day one with a NO DROP seems very risky, as the only way to remove the table is to drop the whole schema.

Assuming it was not defined as locked, the blockchain row retention clause can be modified using the ALTER TABLE command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error.

-- Increase to 32 days.

alter table bct_t1 no delete until 32 days after insert;

Table BCT_T1 altered.

SQL>

-- Decrease to 16 days (fail).

alter table bct_t1 no delete until 16 days after insert;

Error report -

ORA-05732: retention value cannot be lowered

SQL>

In the current release, attempting to set the row retention to NO DELETE, which is an increase in the retention period, results in an ORA-00600 error. I assume this is a bug in the current release update.

alter table bct_t1 no delete;

Error report -

ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

◉ Blocked DML and DDL Operations

As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for a blockchain table.

The following example shows a successful insert, then some unsuccessful DML statements.

-- INSERT

insert into bct_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);

1 row inserted.

SQL> commit;

Commit complete.

SQL>

-- UPDATE

update bct_t1 set quantity = 10 where id = 1;

Error report -

SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>

-- DELETE

delete from bct_t1 where id = 1;

Error report -

SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>

Some DDL statement that could alter the contents of the data are also prevented. Here is an example of the TRUNCATE statement.

truncate table bct_t1;

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

Extending existing columns is fine, but adding new columns or dropping existing columns is not allowed.

-- Extend column.

alter table bct_t1 modify (fruit varchar2(25));

Table BCT_T1 altered.

SQL>

-- Add column

alter table bct_t1 add (additional_info varchar2(50));

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

-- Drop column.

alter table bct_t1 drop column quantity;

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

◉ DBMS_BLOCKCHAIN_TABLE Package

The DBMS_BLOCKCHAIN_TABLE package is used for maintenance of blockchain tables.

The DELETE_EXPIRED_ROWS procedure removes any rows that are beyond the retention period. They can't be removed using a normal DELETE statement.

set serveroutput on

declare

  l_rows  number;

begin

  dbms_blockchain_table.delete_expired_rows(

    schema_name            => 'admin',

    table_name             => 'bct_t1',

    before_timestamp       => null,

    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);

end;

/

Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

The VERIFY_ROWS procedure checks the rows in the table have a consistent hash, and signature if used.

set serveroutput on

declare

  l_rows      number;

  l_verified  number;

begin

  select count(*)

  into   l_rows

  from   admin.bct_t1;

  dbms_blockchain_table.verify_rows(

    schema_name             => 'admin',

    table_name              => 'bct_t1',

    number_of_rows_verified => l_verified);

  dbms_output.put_line('Rows=' || l_rows || '  Verified Rows=' || l_verified);

end;

/

Rows=1  Verified Rows=1

PL/SQL procedure successfully completed.

SQL>

◉ Considerations

There are a number of things to consider when using blockchain tables.

◉ My overall feeling regarding the 21.1 and 21.2 releases is that the blockchain implementation is kind-of buggy at this point. There are some features that don't work as documented, resulting in error messages that are inaccurate, or aren't trapped properly. There are also some features I've not included in this article as they appear not to work at all. In one case resulting in memory failures that can only be resolved by restarting the instance. I'll revisit these over the next few release updates to see if they get resolved, and update this article accordingly.

◉ Blockchain tables are slower than conventional tables, due to the extra work associated with them.

◉ Blockchain tables can be indexed and partitioned in the normal manner.

◉ There are some restrictions associated with data pump against blockchain tables, described here.

◉ There are a number of general restrictions associated with blockchain tables, described here.

◉ Oracle recommend saving the current hash and the corresponding sequence number for each chain in the instance somewhere outside of the database. This allows you to compare your recorded values to those in the table for extra assurance.

◉ In data guard environments, Oracle recommend maximum protection mode or maximum availability mode when working with blockchain tables.

◉ User certificates can be added to the database using the ADD_CERTIFICATE procedure in the DBMS_USER_CERTS package, and applied to existing rows using the SIGN_ROW procedure in the DBMS_BLOCKCHAIN_TABLE package. This functionality does not appear to work at this point.

I guess the main question should be, why would you use a blockchain table?

◉ If you need an insert-only tamper proof table in your application generally, this could be the solution.

◉ If you want to take advantage of the trust associated with blockchain in a centralized manner, rather than having multiple client applications having to manage blockchains individually, using a blockchain table allows you to centralise that trust.

◉ You can add the trust associated with blockchain to existing applications without having to worry about recoding them.

Continue reading