Oracle 18c - Automated Self Driven Database - Future of DBA Jobs ?

Larry Ellison gives sleepless nights not only to Amazon  but also for Oracle DBAs.  Yes, there is a reason.  He declared that the upcoming Oracle database 18c version as an "Autonomous Database".

Here in this blog, we will discuss about the future of Oracle DBAs. Lets see.. whether this announcement would be a TREAT? or THREAT?

Before this, please have a look at my previous blog about Oracle 18c - Announcements by Larry Ellison. Below is the link. 

Whooping Announcements by Larry Ellison - Oracle 18c

What it means - Autonomous Database ?  And What are the Key Highlights of Oracle 18c ?     

• Automated Cyber Security System
• Autonomous Database
• Self Driven - Eliminates Human labor and Human error
• Fully Automated  Performance tuning
• Automated Upgrade while running
• Automated Patch while running
• Zero Labor Cost for Database Maintenance and Management
• Consumes less time 
• Consumes less memory storage
• 99.995% availability - 30 min max down time per year covering planned and unplanned downtime.
• Guaranteed half the price of Amazon. Targets 80% cost cut. Much cheaper than Amazon.
• Activated Data guard for recovery
• 5x faster RAC
• Autonomous at all levels - DWH, OLTP DB, Express, Private Cloud, Public Cloud, On premise
• And more..

Oracle Vs Amazon :-

So, it is a big challenge for Amazon now to handle this big announcement from Oracle.  No doubt about it. 

Larry simply asked "When we have automated DB for half the price of Amazon which is not automated, what is your choice? to customers. Now Amazon customers will simply turn and ask the same question to Amazon Sales guys. 

Amazon already lashes out at Oracle Announcements commenting " No facts, wild claims, and lots of bluster ". Eagerly waiting for Amazon's next step. 

Okay, that is pretty business stuffs.  The next million dollar question is the below one. 

What is the future of Oracle DBAs ? Automated Oracle DB is a TREAT or THREAT? 

Who can answer better than all others. Yes, Larry Ellison talked about the future of Oracle DBAs skill sets in the same announcements.  

Below are his views. I captured the same slide what Larry had presented ( Pl refer the below image )

Now :-

Till 12c, Oracle DBAs do a lot of day-to-day database monitoring and administration tasks,  Routine Patch updates, Taking Backups, Scheduling and Monitoring Jobs,  Manual Performance tuning etc.,  These skill sets will not be much required in Oracle 18c as they are automated now.

In Future :-

Then, what will be the much required skill set to adopt for Oracle DBAs.  He answered for the same. 

From 18c onwards, DBAs will be required much for the below tasks.. 

•  Database Design
•  Schema Design
•  Data Migration
•  Data Policies -  Authorization of who can view data or not 
•  Data Analytics using Machine Learning
•  Understanding Data Patterns

In Summary.. 

As quick as DBAs adopt to Machine Learning, Data Analytics, Data statistics, Data Patterns skill setS,  the latest announcement would be a TREAT for DBAs, else it would be a THREAT. 

In our next blog, we will discuss about what about the existing projects which is in Oracle 11i and 12c and how DBA roles will be slightly changing for existing projects and the upcoming new projects.

Stay Connected !
Sathish Chandran

Oracle 18c - Whoophing announcements by Larry Ellison

When there is an automated aircrafts..  When there is an automated cars like Tesla... Then, why cant we have an automated database ?  that too being an Oracle ?  

These questions from Larry Ellison - Chairman and CTO of  Oracle Corporation made him to declare a big announcement today - 2nd Oct, 2017 at Oracle Open World Conference held on San Francisco, USA. 

Yes, Oracle 18c is on the way very soon.   Here we will the snippets of Larry's big announcement today. 

Is it a war ?

Larry literally declares war against human work and also he targets his biggest counterpart Amazon in his announcements. 

Key Features :-

Solution for Security Threat :   “If you eliminate human labor, you eliminate human error. It’s embarrassing for me to admit it, Ellison said today. If their is no human intervention, we can avoid security threat from bad boys.

Automated Patch Update :-  Oracle 18c will immediately patch itself the moment a threat is detected, since it “does not need a human being to schedule downtime” for maintenance, Ellison highlighted this point very clearly. 

Nearly Zero downtime :-  18C needs less than 30 minutes a year of planned downtime. This is a big deal; no one else does it, Ellison targets Amazon here by declaring this is far better than Amazon.

•   Gurantees 99.995% of system availablity
•   Guaranteed 100x better availability than Amazon

Automated DBA :- The Oracle 18c does three major things automatically while running  a) Tuning   b)  Upgrade  and c) Patch Update. 

Cost Effective :- The Oracle 18c cuts major human labor cost, Maintenance cost and error fixing cost. This will be half the cost when compare to Amazon. Again Ellison digs Amazon here. 

Release Dates :-  Data warehouse :Autonomous databases for warehouses will be released by Dec 2017

OLTP :  The autonomous OLTP Oracle 18c will be released by June 2018. 

Cloud :  Autonomous No SQL Cloud Oracle 18c  will be released by CY 2018.

In Summary, Oracle eyes on 18c Release with the below amazing features. 

  a) Solution to human security threat

  b) Autonomous DBA

  c) Automated Patch, Upgrade and Tuning

  d) 99.995% availability

  e) Cost Effective

  f) Error less

  g) Faster Access

In our next blogs, we will talk about the below... 

• Is this is treat  or  threat for Oracle working professionals ?
• How to upgrade our skills to align with Oracle 18c ?
• How 18c will hit existing projects and its cost?
• And more..   Stay connected.

Regards,

Sathish Chandran

SQL Injection - Which may Destroy Your Database

SQL Injection :-

It is a Code Injection technique which might destroy your database knowingly or Unknowingly. We should all very careful about it. 

Here in this blog, we will see how Injection a) might destroy your database and how it b) will bypass security as well. Yes, lets explore with examples. 




Scenario 1  ( How to break Security Login )

Here, it is to check the User login with Username and Password. Getting the inputs and passed in a query. If it matches with the User List in the User Table, then it will allow that user to login. 

Input Values  - Case 1

User id   =  1001
Password = pass

The above given inputs will frame the SQL as below. 

Select * from User_table  where User id = 101  and Password = 'pass';

The above query will check Username and Password in the User table and allow if the Username and Password is perfectly matched with any of the records.

Here, there is no SQL Injection. Great. 


Input Values - Case 2

User id = 1001 OR 1=1 
Password = pass

The above said Input values  will make SQL statement as below. 

Select * from User_table  where User id = 101  or 1=1  and Password = 'pass';

This will select records always even though if any one enters wrong username and password. Then the application or database is in User's hand to do whatever he want. 

This is one way of doing SQL Injection. Be careful about it. 




Scenario 2  ( How to Inject to destroy Database by dropping key tables )

Here, it is to pass input value to frame  a SQL statement to Execute. 

Delete from transaction where transaction_no = "Input Value"

Input Value - Case 1:

Transaction Number =  100001

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001

Here, it is perfect and there is no SQL injection takes place. Perfect One. But.,  lets see in other two cases below. 


Input Value - Case 2:

Transaction Number =  100001  or  1=1 ;

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001 or  1=1;

We all know that, the above statement will not delete only the transaction 100001 but all the transactions in the transaction table, which is the crisis to the business. 



Input Value - Case 3:

Transaction Number =  100001; drop table USER_LOGIN ;

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001; drop table USER_LOGIN;

Think about it, after deleting transaction what will happen. The key table USER_LOGIN will be dropped which may lead to DB Login Crash. 


Solution :-
1. Be very very careful about Input Parameters
2. Include this SQL injection scenarios in all your test cases during Testing.  

Avoid SQL Injection and Keep our database safe always.


Thanks for all your support. We will connect in our next blog with different topic

Regards,
Sathish

Data Scientist - The SEXIEST job in 21st Century

Why DATA SCIENTIST is the most sexiest job in 21st Century?

When I was started my research on what is the ultimate reach of being an Oracle SQL Expert, it went through many areas, but my (re)search was ended with Data Scientists. I always look for proof, which i got amazing information from Naukri and LinkedIn. 

Just read the below fact which will say more about it. 

India requires over 200,000 data scientists by 2018 and the pay that data scientists get is huge when compared to engineers and chartered accountants. With the right skill sets and experience, the pay gets higher. US and Worldwide requirement by 2020 is over 2 Million Data Scientists are needed. 

Good thing is, there are more and more upcoming hot jobs related to DATA, which are  

•    Data Analysts, 
•    Data Architects, 
•    Data Engineers, 
•    Data Statisticians

Skills set required to become DATA Scientists.

“A data scientist is someone who is better at statistics than any software engineer and better at software engineering than any statistician.”

Complete Skill set required to become DATA Scientists are following:-

• SQL databases and database querying languages
• Maths Knowledge (e.g. linear algebra, calculus and probability)
• Statistics (e.g. hypothesis testing and summary statistics)
• Machine learning tools and techniques (e.g. k-nearest neighbors, random forests, ensemble methods, etc.)
• Software engineering skills (e.g. distributed computing, algorithms and data structures)
• Data mining
• Data cleaning and munging
• Data visualization (e.g. ggplot and d3.js) and reporting techniques
• Unstructured data techniques
• R and/or SAS languages
• Python (most common), C/C++ Java, Perl
• Big data platforms like Hadoop, Hive & Pig
• Cloud tools like Amazon S3

Top 6 Technical Skills required to become DATA Scientists are following:- 

       

• SQL databases and database querying languages
• Data visualization (tableau is market leader ) and reporting techniques
• R and/or SAS languages & Python
• Big data platforms like Hadoop, Hive & Pig
• Machine learning 
• Unstructured data techniques ( Image & Video data reading etc )

Roles of Data Scientists and other Data related hot jobs :-

Data Scientist : Data scientists are big data wranglers. They take an enormous mass of messy data points (unstructured and structured) and use their formidable skills in math, statistics and programming to clean, massage and organize them. They are master in uncover hidden solutions to business challenges and save huge money, reduce risks and to take brilliant business decisions. 

Data Analyst: Data analysts collect, process and perform statistical analyses of data

Data Architect : Data architects create blueprints for data management systems. They are the access providers to access organized data and reports to CEOs to Employees.

Data Engineer : Data engineers build massive reservoirs for big data. They develop, construct, test and maintain architectures such as databases and large-scale data processing systems. They are the data providers for data scientists, architects and analysts.

Data Statistician : Statisticians apply statistical theories and methods to collect, analyze and interpret quantitative data. They involves themselves with global statistics data to compare with organization data.

Salary Range for Data Scientists and other Data related hot jobs :-
( data proof taken from Glass door & Pay scale in US )

Data Scientists :       $158,000  per year
Data Architects :      $143,000  per year
Data Engineers :       $117,000  per year
Data Analysts :         $117,000  per year
Data Statisticians :     $95,000  per year


We will see more information about Data Scientists in upcoming blogs.

You are welcome to reach out to me only on Whatsapp +91-9751282520 for more clarifications related to Data Scientists. 

With Thanks,
Sathish Chandran

Partition Pruning in Oracle

I write this article about "Partition Pruning" based on requests from the regular readers ( Vicky, Neil & Chateswar ) of this blog page. This article is specially for them and the ones who is eager to know about Partition Pruning.


Before we go for "Partition Pruning", it is better to go through couple of articles related to Partitioning in Oracle.  Below are the links related to them . 

Table Partitioning in Oracle

Interval Partitioning in Oracle - 11g New Features

What is Partition Pruning?  

In simple English, Pruning means Eliminating or Ignoring or Filtering.

Partition Pruning is just an activity which happened when SQL statement perform to retrieve the data for a partitioned table. 

Pruning allows the database to access only the relevant partitions and ignore all partitions that are not irrelevant or necessary for the SQL statement.

  

In other words, partition pruning is the act of eliminating, or ignoring the partitions that are irrelevant to the SQL statement's selection criteria.

Benefits of Partition Pruning :-

Pruning is DBA's trump card to enhance performance tuning. Partition pruning significantly reduces the amount of data retrieved from disk and reduces the SQL processing and execution time. 

This improves query performance and optimizing resource utilization.

Static Pruning  Vs  Dynamic Pruning :-

Optimizer decides either Static Pruning or Dynamic Pruning based on the way the SQL statement detects the data of the partitioned object. 

Static Pruning occurs at Compile time of the SQL execution.

Dynamic Pruning occurs at Run time of the SQL execution.

Static Pruning happens when the WHERE condition directly filters the data belonging to particular partition. 

Dynamic Pruning happens when the WHERE condition has any functions or complex literals or Joined with another table.

In other words, Static Pruning happens if a SQL statement containing a WHERE condition with the value passed directly on the partition key column. Otherwise, Dynamic Pruning will happen.

Simply, Static Pruning happens if it comes to know the exact partition before the run time. Dynamic Pruning happens if it comes to know the exact partition after the run time.

Performance wise;  Static Pruning is very faster when compare to dynamic Pruning because it filtered out most of the unwanted resources and data at first step. 

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this. 

Interval Partitioning in Oracle - 11g new feature

As we are aware of the concept of Partitioning in Oracle which helps in three ways  a) Performance  b) Manageability  and  c) Availability.

Having basic idea about partition ( especially range partition ) will help you here to understand about interval partitioning easily. 

We have an article already posted in this site in explaining about Partitioning, types and benefits. Here we go with the link 

Article about Oracle Partitioning

Assuming you have gone through the above link already; here we start to know about Interval Partitioning. 

Why Interval Partition?  How it works ?

Interval Partition is one of the key new features of Oracle 11i.  We can highlight interval partition as extended version of Range partition. 

For example:  we have created partitions in a table for the below range criteria of data belongs to a column of a table. 

Partition 1    for the data lesser than Jan-2016

Partition 2    for the data between  Jan-2016 to Jun 2016

Partition 3    for the data between  Jul-2016  to Dec 2016

Partition 4    for the data between  Jan-2017 to Jun 2017

Inserting data lesser than Jun-2017  will be placed into its right partition; but when we insert a data greater than Jun 2017; for example .    15-Aug-2017;  we will end up with an error message saying there is no partition available. 

To fix this growing data scenario, we will have to create or add new partitions as a maintenance activity month on month.  So, Interval Partition helps here.  Interval Partition is automatically created new partitions whenever required as per the new data is getting inserted.

Example code  to create Interval Partitioned table :-

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('30-Apr-2017','DD-MON-YYYY'))
);

Adding the yellow marked line specially for interval partitioning orders Oracle to create new partitions for every new month data greater than 30-Apr-2017. 

For example;  if we insert into a column "created date"  with the data lesser than 30-Apr-2017 it will be a part of Partition part_01.  If we inserted a data greater than 30-Apr-2017, it will create new partition and then insert the new data. 

The new partitions will be created for  the new months of May-2017, Jun-2017, Jul-2017 etc.,  and so on..  So there is no maintenance activity required.  This will be managed automatically. 

List down all the partitions created for a particular table:-

Below query will provide the details about the partitions created by user and interval partitions created by Oracle automatically.

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = <table_name>;

How to add interval partition into already partitioned table?

We are in a scenario of a table which was partitioned already, but we would like to add interval partitioning to avoid further maintenance activities. We can go with an ALTER command. 

ALTER TABLE TABLE_PARTITIONED1 SET INTERVAL(NUMTOMYINTERVAL (1, 'MONTH'));

As we have possibility of creating Composite partitioning  such as ( Range-Hash Partitioning, Range-List Partitioning, Range -Range, List-List and so on.. );

We can have Composite Partitioning with Interval Partitions as well as mentioned below.

• Interval-Hash
• Interval-List
• Interval-Range

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this. 

Table Partitioning in Oracle

Partitioning is nothing but the concept of "Divide & Rule"   or   "Categorize them to handle easily".

In order to handle data in huge tables in a easy and quick way,
Oracle has an effective concept " Partitioning Tables"  and  " Partitioned Indexes"

Lets start knowing about what it is all about, how it works and benefits out of it.


Say.. "Hai" to Partitioning  


Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.

Each Partition has its own name. To retrieve the data, it can be retrieved by calling a table or by calling its partition unit. 


Pictorial view of Partitioned Table and Non Partitioned Table




When actually we can partition a table ?

Huge tables which has data more than 2 GB size can go for partitioning.

Table which has years of data, but mostly we deal with recent months data and old data in a table are just to read.

Table which has data can be categorized into types like Department wise, Country wise, etc.,  So that we can deal with those particular category data alone by naming them as partition.


Types of Partitioning :-

The three types of  partitioning are List, Range and Hash.





Below are the lines to support in explaining the above pictorial view of different partitioning types. 


List Partitioning :-

List partitioning segregates data by its data  which falls into different categories. 

For example:-
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

A query to retrieve data only from Florida can deal with "East Sales Region" Partition alone.

Example code to create list partition of table 

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Range Partitioning :-

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.  

Example Dates between 2000 to 2012, 2012 to 2014 , 2012 to 2016 and greater than 2016.  

Example: Revenue less than 1 million, less than 2 million, less than 4 million and Default ( other values will fall in default partition always )


Example Code :-

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Hash Partitioning:-

No criteria involved. Just spread the data among partitions in equal size. 

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. 

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example code :- 

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

Composite Partitioning:-

We can have composite partitioning with the combination of  different partitions at different levels. 

Region wise(List) .. within region, year wise. (Range). within year  equal distribution ( hash ) partition..







Benefits of Partitioning :- 

There are three key benefits and they are 

Performance :- By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. It is like taking data from 1 million records rather than 1 billion records is always quicker

Manageability:- With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table

Availability :- if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table.



Hope this helps.. if yes, then please share this post and comment your feedback.