ZAHIR MOHIDEEN

Five Productivity Habits

2024-02-28T20:37:00.000-05:00

So, how many weeks do we have in our lifetime?

Guess …

Before you squeeze your brain too long or ask Google for help, that average number isn’t a million or even tens of thousands... assuming you live a long, healthy life, you can expect after near 90 orbits around the sun to have enjoyed roughly 4,680 weeks on Earth.

Breaking that down a bit, by the time you are age 25, you will have used 1,300 of those weeks. Many of us are in the middle of our careers and lives. We have family, commitments, professions, and hobbies with more weeks under our collective belts than you did at 25 years... and there is no guarantee you will get to 90, as much as many of us hope to get there safely, securely and happily.

From the perspective of limited time, how do we make each weeklong snapshot of our story mean the most to us? How do we make those individual periods the most productive they can be, which in turn allows us to feel engaged and happy in our day-to-day moments.

In this digital era, we need to change our mindset since we are the only ones we can ultimately manage or control. We can use technology as a tool, instead of being a tool to the technology. We have become digiphernia, which is lost to digital information overload, splitting ourselves too much into too many different roles. In this mode, we cannot enjoy any one experience as we look and worry about the next. We have lost focus and priority for even simple tasks at hand, ultimately making ourselves feel burned out rather than accomplished.

As I have gone through my journey, as a son, father, DBA, man of faith, man of habit, I have found these few habits can enhance productivity and they allow me to feel more focused in my daily routines.

#1 Batch Processing of Emails.

Most of us keep our emails open all the time. We have anxiety to process them and look to each individual interaction to achieve minor tasks with often minimal reward. According to a study done at the university of British Columbia checking email less frequently on a scheduled basis reduces stress and anxiety.

https://www.sciencedirect.com/science/article/abs/pii/S0747563214005810?via%3Dihub

Email has become a business focused tool, running a wide array of major project updates, critical tasks, but more often smaller, rapid things that can pull from the bigger picture of your day. By creating scheduled email response periods, where that is your sole focus, you allow yourself the freedom to work on major projects and issues, which in turn could be part of your various electronic responses. Additionally, we still have other tools for urgent issues such as Teams, Slack, mobile phones and tried and true in-person conversation. Not all emails need to be responded to urgently, and the reality is we should all break the habit of urgent communication going over email.

Putting a schedule around this type of activity could also have the auxiliary benefit of being more engaged with your colleagues, because the reality is sometimes a quick conversation can negate misunderstandings or even long, drawn out threads where people are often being added without really understanding what their purpose to the discussion is... so thinking of new ways to engage email, and in fact the way you communicate overall, could return valuable time and focus.

#2 Write it down.

When you are coming up with a plan or a need for next steps, sit down with a pen and paper and start to write.

The purpose isn’t to create a list of tasks to do, or even next steps. You may find it may end up that way, but the real goal at the beginning is to allow your mind to freely form ideas that otherwise could be lost. One idea can beget another, and freeflowing thought you’ve jotted down allows you to come back later, which can sometimes open doors to ideas or opportunities you may not have thought of at the time.

#3 Turn off Notifications.

Distraction is #1 killer for productivity. We may be working through an issue or writing down stream of conscious thought... and suddenly Facebook wants your attention. We can easily turn off or pause notifications in our smart phone or computers. Airplane mode is your friend for this step of the habit.

Those dings and beeps from that device send a signal to the rewards center in our hypothalamus and consequently create a dopamine rush that leads to less favorable workplace productivity, and from there being less productive overall. See the previous step of the journey regarding in-person or other styles of communication for urgent needs. This is often the same effect; the notification is often a service or non-urgent event triggering you to engage it. You have the power to take control back, realizing if there is something urgent that involves your insight or presence, the sender shouldn’t expect your attention to solely be attainable through a cell phone ding.

Prof.Robert Lustig has summarized the difference between Dopamine and Serotonin in his book “The hacking of American Mind". I have learned a lot from Dr.Lustig ( https://robertlustig.com/).

#4 Defensive Calendaring

By blocking certain segments of your calendar, you can plan and focus on an activity (NOTE: Singular Activity!). Human beings for all our abilities and talents and self-assessment, and truly not very good at multitasking, although many of us think we are. Context switching from task-to-task results in less productivity because we often rush to reach the end goal, doing each task as quickly as possible to go on to the next. On average, it takes 52 seconds to refocus from one task to the next, not counting what you may have eventually done with the original train of thought.

As the saying from John Beckley goes “People don’t plan to fail; but they fail to plan”. Like corralling email into specific blocks of time, you can extend the concept to other aspects of your daily routines. Do you want to exercise? Then every day, you plan to at 5pm, or 5am. The time isn’t as relevant as the commitment to the schedule. That allows you to be clear and authentic with yourself as well as your colleagues that at specific periods or times, you may have commitments that matter to you, and you are both ensuring the time is available to meet your obligations. At the same time, guarding against other infringements that may creep randomly into your day.

#5 Automate repetitive tasks.

If there are any repetitive tasks you do throughout your week, they are the most likely automation candidates. This allows you to set routines throughout your day and even your week that repeat reliable, consistent results.

This is where technology and habit can synthesize into a powerhouse of productivity for you. As an example, if you are responsible for generating a summary report for event registrations, and you can predict the timing of when the data is available, you can then use tools at your disposal such as Power Query to automate the output if the output is always the same. Additionally, this can extend out more even if the output needs superficially change, allowing the bulk of the processing to be done and then you simply modify the output. This is something AI will ultimately be beneficial and assistive with as we march into the future, but for the time being it is important to take stock of things you often repeat and do over with similar outcomes each time. Not only will you maybe save some time for your week-to-week schedule, but you’ll also have a predetermined candidate list of concepts and tasks that will be ready when AI becomes more ubiquitous around us.

New and Niche function in SQL Server 2022. Close cousin in DAX ( Power BI )

2023-10-14T19:53:00.006-04:00

SQL Server 2022 introduced the new function GENERATE_SERIES.

With this , we can generate series of values with the appropriate stop interval.

For this function , we need to be on SQL 2022.

Here is the link to the documentation

https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16

1> select @@version

2> go

Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)

Aug 24 2023 02:40:55

Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: ) (Hypervisor)

Here , in this example , we will use to generate dates between two dates.

With the following CTE , we are generating the list of dates between 10/14/2023 and 10/22/2023.

We are omitting the step size , as the default is 1.

1> WITH

2> v_param AS

3> ( SELECT

4> '2023-10-14' AS StartDate ,

5> '2023-10-22' AS EndDate ,

6> DATEDIFF( DAY , '2023-10-14' , '2023-10-22') AS Days

7> )

8> SELECT

9> DATEADD ( DAY , gs.value ,

10> ( SELECT

11> startdate

12> FROM

13> v_param ) ) AS Dates

14> FROM

15> GENERATE_SERIES( 0 ,

16> ( SELECT

17> Days

18> FROM

19> v_param )) gs;

20>

21> go

Dates

-----

2023-10-14 00:00:00.000

2023-10-15 00:00:00.000

2023-10-16 00:00:00.000

2023-10-17 00:00:00.000

2023-10-18 00:00:00.000

2023-10-19 00:00:00.000

2023-10-20 00:00:00.000

2023-10-21 00:00:00.000

2023-10-22 00:00:00.000

Usually, I would like to wrap the functionality in a stored procedure ( in SQL Server / MariaDB ) and in a package ( in Oracle).

Here is the equivalent of the above CTE in a stored procedure.

1> CREATE PROCEDURE p_gen_dates

2> @p_start_date DATE , @p_end_date DATE AS

3> BEGIN

4> SET NOCOUNT ON;

5> SELECT

6> DATEADD ( DAY , gs.value , @p_start_date ) AS Dates

7> FROM

8> GENERATE_SERIES( 0 , DATEDIFF( DAY , @p_start_date , @p_end_date) ) gs;

9> END

10> GO

1> EXEC [dbo].[p_gen_dates] '2023-10-14' , '2023-10-16'

2> GO

Dates

-----

2023-10-14

2023-10-15

2023-10-16

This new function is niche and simple to use.

Looks like , this function is borrowed from DAX.

In the Power BI Desktop , navigate to the "Table View" and click on the "New Table" .

That opens up a new window to create a DAX measure . Here we create a calculated table using "GENERATESERIES" function . In the example below , we created a table of 10 records , starting with 1 with a step size of 2 ( 1 is the default for step size)

Comments Welcome!

Scalar SQL Macro and DUAL in 23c

2023-05-01T09:51:00.004-04:00

In the last blog post on SQL Macros , ( http://mfzahirdba.blogspot.com/2023/03/sql-macros.html) , I was exploring SQL macros.

In Oracle 23c , scalar macro is ported from 21c .

BTW , I am testing this feature on 23c Free - Developer's Release ( https://www.oracle.com/database/free/)

Here is the test case from the previous post.

BANNER

___________________________________________________________________

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> CREATE OR replace FUNCTION calculate_pies (

2 p_arg1 NUMBER

3 ) RETURN VARCHAR2 sql_macro ( scalar ) is

4 begin

5 return q'{

6 p_arg1 * 3.14

7 }';

8 end;

9* /

Function CALCULATE_PIES compiled

SQL> SELECT

2 level,

3 calculate_pies(level)

4 FROM

5 dual

6 CONNECT BY

7* level <= 5;

LEVEL CALCULATE_PIES(LEVEL) ________ ________________________ 1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

Another new feature is that we don't need DUAL to get the results.Albeit this feature is tiny , it goes parallel with all other RDBMSes

SQL> SELECT

2 level,

3 calculate_pies(level)

4 CONNECT BY

5* level <= 5;

LEVEL CALCULATE_PIES(LEVEL)

________ ________________________

1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

More to come on 23c New Features.

Stay Tuned.

SQL Macros

2023-03-02T17:22:00.005-05:00

SQL Macros was introduced in Oracle 21c. There are two types of SQL Macros in 21c ( Scalar and Table). These enhancements make it easy to implement a view , where we can pass in the parameter to get the desired results.

Prior to this , we can achieve this by using parameterized views and contexts. It is a bit cumbersome. This feature does not involve context switching.

This feature is backported to 19c. But only "Table type SQL Macro".

As 21c release is the innovation release , Oracle recommends 19c to be the production release.

We create a function , and indicate that it returns TABLE for Macro Type.

Then we can use this , FROM clause of the SELECT statement as shown below.

SQL Macro - Table

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

SQL> set linesize 1000

SQL> select banner from v$version;

BANNER

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

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

SQL> create table t as select * from all_objects;

Table created.

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 ) RETURN VARCHAR2

4 SQL_MACRO( TABLE ) is begin RETURN q'{

5 Select object_type , count(*) as Count_Type from t

6 Where owner = count_objects_table.obj_owner_name

7 group by object_type

8 }'

9 ;

10 END;

11 /

Function created.

SQL> set pagesize 1000

SQL> SELECT

2 *

3 FROM

4 count_objects_table ( 'SYS' );

OBJECT_TYPE COUNT_TYPE

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

INDEX 1623

TABLE 1750

CLUSTER 10

EDITION 1

SEQUENCE 3

DIRECTORY 12

PACKAGE 424

FUNCTION 103

SYNONYM 16

PROCEDURE 25

TYPE 1149

OPERATOR 7

TABLE PARTITION 466

INDEX PARTITION 276

TABLE SUBPARTITION 32

CONSUMER GROUP 18

JOB CLASS 3

DESTINATION 2

SCHEDULE 4

WINDOW 9

SCHEDULER GROUP 4

EVALUATION CONTEXT 1

JAVA CLASS 35259

JAVA RESOURCE 1658

25 rows selected.

SQL Macro - Scalar

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

Here , we will create a function that returns scalar macro.

And then use it in SELECT clause , as shown below.

SQL> CREATE OR replace FUNCTION calculate_pies (

2 p_arg1 NUMBER

3 ) RETURN VARCHAR2 sql_macro ( scalar ) is

4 begin

5 return q'{

6 p_arg1 * 3.14

7 }';

8 end;

9 /

Function created.

SQL> SELECT

2 level,

3 calculate_pies(level)

4 FROM

5 dual

6 CONNECT BY

7 level <= 5;

LEVEL CALCULATE_PIES(LEVEL)

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

1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

In 19c

------

As the "table" SQL macro is only the type supported in 19c , the following scipt produces error.

By removing the table type , the function is valid and usable.

SQL> select banner from v$version;

BANNER

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 ) RETURN VARCHAR2

4 SQL_MACRO( TABLE ) is begin RETURN q'{

5 Select object_type , count(*) as Count_Type from all_objects

6 Where owner = count_objects_table.obj_owner_name

7 group by object_type

8 }'

9 ;

10 END;

11 /

Warning: Function created with compilation errors.

SQL> show errors

Errors for FUNCTION COUNT_OBJECTS_TABLE:

LINE/COL ERROR

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

4/10 PLS-00103: Encountered the symbol "(" when expecting one of the

following:

; is default authid as cluster order using external

deterministic parallel_enable pipelined aggregate

result_cache accessible rewrite

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 )

4 return VARCHAR2

5 sql_macro

6 IS

7 BEGIN

8 RETURN q'{

9 Select object_type , count(*) as Count_Type from all_objects

10 Where owner = count_objects_table.obj_owner_name

11 group by object_type

12 }'

13 ;

14 END;

15 /

Function created.

SQL> set pagesize 100

SQL> Select * From count_objects_table('SYS');

OBJECT_TYPE COUNT_TYPE

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

SEQUENCE 4

PROCEDURE 20

TABLE SUBPARTITION 32

FUNCTION 101

INDEX PARTITION 130

WINDOW 9

JAVA RESOURCE 1640

JOB CLASS 2

EVALUATION CONTEXT 1

JAVA CLASS 34395

PACKAGE 261

TABLE 1575

EDITION 1

SYNONYM 16

TYPE 1104

SCHEDULE 4

SCHEDULER GROUP 4

CLUSTER 10

OPERATOR 7

TABLE PARTITION 326

INDEX 1454

CONSUMER GROUP 18

DESTINATION 2

24 rows selected.

SQL Macro is a welcome addition to Oracle. It eases some of the complexity.

Similarly , I hope , Oracle adds "Filtered Index" ( as it exists in SQL Server) to its feature list in the coming releases.

Comments Welcome.

Strange issue with Informatica Secure Agent Installation

2023-02-22T15:05:00.000-05:00

Most of the time , copy and paste is a life saver. Sometimes , this could get tricky.

Recently , we were installing Informatica Secure Agent ( IICS) in one of our servers.

We followed the instructions in Informatica Documentation.

( https://docs.informatica.com/integration-cloud/cloud-data-integration/current-version/getting-started/installing-secure-agents/secure-agent-installation-on-linux/downloading-and-installing-the-secure-agent-on-linux.html)

Copied the command in step5 , as below.

We ran into this issue.

[usr@agentserver]$ ./agent64_install_ng_ext.6425.bin –i console

Preparing to install

Extracting the JRE from the installer archive...

Unpacking the JRE...

Extracting the installation resources from the installer archive...

Configuring the installer for this system's environment...

Launching installer...

Usage: agent64_install_ng_ext.6425 [-f <path_to_installer_properties_file> | -options]

(to execute the installer)

where options include:

-? show this help text

-h show this help text

-help show this help text

--help show this help text

-i [gui | console | silent]

specify the user interface mode for the installer

-D<name>=<value>

specify installer properties

-r <path_to_generate_response_file>

Generates response file.

JVM heap size options are only applicable to Installers

-jvmxms <size>

Specify JVM initial heap size.

-jvmxmx <size>

Specify JVM maximum heap size.

The options field may also include the following in case of uninstaller

if it is enabled for Maintenance Mode

-add <feature_name_1> [<feature_name_2 ...]

Add Specified Features

-remove <feature_name_1> [<feature_name_2 ...]

Remove Specified Features

-repair

Repair Installation

-uninstall

Uninstall

notes:

1. the path to the installer properties file may be either absolute,

or relative to the directory in which the installer resides.

2. if an installer properties file is specified and exists, all other

command line options will be ignored.

3. if a properties file named either 'installer.properties' or

<NameOfInstaller>.properties resides in the same directory as the

installer, it will automatically be used, overriding all other command

line options, unless the '-f' option is used to point to another valid

properties file.

4. if an installer properties file is specified but does not exist, the

default properties file, if present, will be used. Otherwise, any

supplied command line options will be used, or if no additional

options were specified, the installer will be run using the default

settings.

Apparently , this is an issue , when we copy/paste the command.

When we keyed in the same command , the install was successful.

[usr@agentserver]$ ./agent64_install_ng_ext.6425.bin -i console

Preparing to install

Extracting the JRE from the installer archive...

Unpacking the JRE...

Extracting the installation resources from the installer archive...

Configuring the installer for this system's environment...

Launching installer...

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

Informatica Cloud Secure Agent (created with InstallAnywhere)

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

Preparing CONSOLE Mode Installation...

....

Installation Complete

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

Congratulations. Informatica Cloud Secure Agent has been successfully

I checked the command for non printable characters when copied it. Nothing.

Everything seemed to be fine.

Anyways , sharing this post , if it is going to be helpful to others.

Tableau LOD Calculations Explained with Ranking / Window functions

2021-08-03T14:07:00.001-04:00

I started my learning journey with Tableau few months ago.

So far , it is pretty good. However , I found LOD ( level of detail) expressions are interestingly complex

at the first encounter. Reading thru few examples and watching few videos to get a better understanding of these concepts.

Being I come from database background and SQL knowledge , I am hoping , I can provide my understanding of LOD thru ranking/ analytical

functions.

Please see my blog posts on ranking / analytical functions here

https://mfzahirdba.blogspot.com/2009/10/analytics-20.html

https://mfzahirdba.blogspot.com/2009/11/analytics-20-part-ii.html

https://mfzahirdba.blogspot.com/2018/10/windowing-clause-why-it-matters.html

https://mfzahirdba.blogspot.com/2013/08/oracles-analytical-functions-vs-sql.html

https://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html

There are three LOD keywords ( FIXED , INCLUDE , EXCLUDE) in Tableau.

FIXED LOD calculations does not depend on the visuals ( aka viz) , where as visulation impacts

how INCLUDE and EXCLUDE are calculated.

In my example , I have created a table in SQL server and populated few records.

To follow along the example , feel free to use the script below.

Create table CourseEnrollment

( StudentName varchar(50) ,

Department varchar(50) ,

Subject varchar(50) ,

EnrollmentDate Date

) ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' , 'Maths' , 'Pre Calculus' , '1988-03-01') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' , 'Maths' , 'Multivariate Calculus' , '2010-05-16') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' , 'Statistics' , 'Statistics 101' , '2020-07-25') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' , 'Maths' , 'Pre Calculus' , '1991-03-01') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' , 'Physics' , 'Phy101' , '2020-07-25') ;

I have connected Tableau to my underlying table .

Here , I want to know the very first enrollment of the student no matter what department / course he enrolled into . Also , this value does not change , when the viz is changed.

In the "sheet" tab , I create a calculated field for the FIXED LOD , by clicking on "Create Calculated Field " as below.

Similarly , create LOD calculations for INCLUDE and EXCLUDE.

Let us bring in these LOD calculations into the sheet .

The value for First Enrollment Date - Fixed is the minimum enrollment date per student , no matter what the dimensions are available in the viz. FIXED by Student Name .

The value for First Enrollment Date - INCLUDE is the minimum enrollment date per student and all other available dimension in the viz . In our case , Department . So , in essence , first enrollment date per student / department.

The value for First Enrollment Date - EXCLUDE is the minimum enrollment date per student and all other available dimension in the viz , except student name as specified in the LOD . In our case , Department . So , in essence , first enrollment date per department.

These results can be derived from SQL using analytical / window functions as

When we remove the department / Student , the FIXED LOD remains the same ; there is a change in Include and Exclude.

Hope , this simple example provides the LOD in the eyes of SQL developer.

I offer support for database maintenance , performance tuning , database development. Please contact for any database related activities.

Renaming PDB

2021-04-19T18:56:00.001-04:00

Renaming the PDBS is very easy in the CDB / PDB architure.

With 18c onwards , we can have 3 pdbs with the regular oracle license.

Please refer to the documentation for further details.

In my example , I have 3 pdbs. We will be renaming one of the pdbs ( from PDB1 to pdb_lx).

Here are my list of PBS

SQL> select name from v$pdbs;

NAME

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

PDB$SEED

PDB1

PDB2

PDB3

Let us confirm if we are in the correct container.

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_id

CON_ID

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

SQL> show con_name

CON_NAME

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

PDB1

PDB needs to be restricted state for it to be renamed.

So , we will close and reopen in the restricted mode.

SQL> alter pluggable database rename global_name to pdb_lx;

alter pluggable database rename global_name to pdb_lx

ERROR at line 1:

ORA-65045: pluggable database not in a restricted mode

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open restricted;

Pluggable database altered.

SQL> alter pluggable database rename global_name to pdb_lx;

Pluggable database altered.

Just to confirm , we will select the old name for the PDB.

As expected , this will fail.

SQL> alter session set container=PDB1;

ERROR:

ORA-65011: Pluggable database PDB1 does not exist.

Even though the renaming of the PDB is successful , the underlying file structure still has pdb1.

$ ls -lrt /ora01/oradata/CDBLX/pdb1

total 640088

-rw-r-----. 1 oracle oinstall 37756928 Apr 19 11:32 temp01.dbf

-rw-r-----. 1 oracle oinstall 5251072 Apr 19 11:39 users01.dbf

-rw-r-----. 1 oracle oinstall 104865792 Apr 19 11:39 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Apr 19 11:39 system01.dbf

-rw-r-----. 1 oracle oinstall 262152192 Apr 19 11:39 sysaux01.dbf

We will create directory and move the datafiles ONLINE ( hey , we are in 19c) .

mkdir -p / /ora01/oradata/CDBLX/pdb_lx

$ ls -lrt /ora01/oradata/CDBLX/pdb_lx/

total 0

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 19 11:47:46 2021

Version 19.3.0.0.0

SQL> alter session set container=pdb_lx;

Session altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/undotbs01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/undotbs01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/sysaux01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/sysaux01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/system01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/system01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/users01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/users01.dbf';

Database altered.

We will drop the temp tablespace datafile from the old directory and recreate it in the new directory.

SQL> alter database tempfile '/ora01/oradata/CDBLX/pdb1/temp01.dbf' drop including datafiles;

Database altered.

SQL> alter tablespace TEMP add tempfile '/ora01/oradata/CDBLX/pdb_lx/temp01.dbf' size 1G reuse;

Tablespace altered.

Let us confirm the contents of the directory structure

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb1/

total 0

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb_lx

total 641056

-rw-r-----. 1 oracle oinstall 104865792 Apr 19 11:48 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 262152192 Apr 19 11:49 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Apr 19 11:49 system01.dbf

-rw-r-----. 1 oracle oinstall 5251072 Apr 19 11:49 users01.dbf

-rw-r-----. 1 oracle oinstall 1073750016 Apr 19 11:49 temp01.dbf

Let us say close the PDB and open it in the normal mode.

SQL> alter pluggable database pdb_lx close;

Pluggable database altered.

SQL> alter pluggable database pdb_lx open;

Pluggable database altered.

We are back in business with the new name for the PDB.

For the databases in non-CDB architecture , we will be using DBNEWID (nid) utility.

Cleanup of Audit Trail in Oracle

2020-12-11T10:30:00.002-05:00

Its been a while, since I blogged. Lot of things are going on like most of us.

Hopefully , in the coming weeks and 2021 onwards, things go smooth

It is recommended that we move objects related to audit trail ( Unified Audit Trail / FGA Audit Trail , ... ) to a different tablespace rather than default tablespace. We can use dbms_audit_mgmt package to perform the move.

In the following example , we moved the audit related objects to a different tablespace TS_AUD_TRAIL

SQL> Select table_name , tablespace_name

2 from dba_tables where table_name in ( 'AUD$' , 'FGA_LOG$')

3 ;

TABLE_NAME TABLESPACE_NAME

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

FGA_LOG$ SYSAUX

AUD$ SYSAUX

SQL> begin

2 dbms_audit_mgmt.set_audit_trail_location( audit_trail_type =>dbms_audit_mgmt.audit_trail_aud_std ,audit_trail_location_value => 'TS_AUD_TRAIL' ) ;

3 end ;

4 /

PL/SQL procedure successfully completed.

SQL> begin

dbms_audit_mgmt.set_audit_trail_location( audit_trail_type =>dbms_audit_mgmt.audit_trail_fga_std ,audit_trail_location_value => 'TS_AUD_TRAIL' ) ;

end;

PL/SQL procedure successfully completed.

SQL> Select table_name , tablespace_name

from dba_tables where table_name in ( 'AUD$' , 'FGA_LOG$')

TABLE_NAME TABLESPACE_NAME

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

FGA_LOG$ TS_AUD_TRAIL

AUD$ TS_AUD_TRAIL

Also , it is better to do a clean up of these records as it is expected to grow. This is important , if we don't move these tables from the default tablespace. There are few other bugs that also causes SYSAUX to grow.

Of course , we need to make sure that we are in compliance with the corporate's retention policy .

Management of audit trail can be either be manual or automatic.

To manually clear the audit trail , we can follow the following steps.

We will first check , when was the last time , we did manual cleanup.

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

no rows selected

DBA_AUDIT_MGMT_LAST_ARCH_TS shows that there was no previous cleanup.

Now , I want to purge any records before Jan 1st 2020. For this , I set the date in DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and then perform the clean up.

SQL> BEGIN

2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (

3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

4 LAST_ARCHIVE_TIME => '01-JAN-2020');

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_AUDIT_MGMT.clean_audit_trail(

3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

4 use_last_arch_timestamp => TRUE);

5 END;

6 /

PL/SQL procedure successfully completed.

After the clean up , we re run the query to list the contents from DBA_AUDIT_MGMT_LAST_ARCH_TS. Now , it has the entry .

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL RAC_INSTANCE

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

LAST_ARCHIVE_TS

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

DATABASE_ID CONTAINER_GUID

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

UNIFIED AUDIT TRAIL 0

01-JAN-20 12.00.00.000000 AM +00:00

5553077069 733F2AB490104389843DFGFADFDS

SQL> BEGIN

2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (

3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

4 LAST_ARCHIVE_TIME => '01-JAN-2020');

5 END;

6 /

BEGIN

ERROR at line 1:

ORA-46258: Cleanup not initialized for the audit trail

ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 177

ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 941

ORA-06512: at line 2

Elapsed: 00:00:00.01

SQL> SELECT JOB_NAME, JOB_STATUS, AUDIT_TRAIL from DBA_AUDIT_MGMT_CLEANUP_JOBS;

no rows selected

Elapsed: 00:00:00.00

SQL> BEGIN

2 DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 24);

3 END;

4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.32

SQL> BEGIN

2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (

3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

4 LAST_ARCHIVE_TIME => '01-JAN-2020');

5 END;

6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> BEGIN

2 DBMS_AUDIT_MGMT.clean_audit_trail(

3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

4 use_last_arch_timestamp => TRUE);

5 END;

6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.70

SQL>

By performing clean up of these audit trail , we can keep the tablespace's storage under control.

Comments welcome.

Stored procedure cannot be the input to the Python script in MS SQL Server

2020-05-13T14:24:00.002-04:00

I am in the journey of using python in MS SQL Stored procedures.

I am big fan of stored procedures since my earlier days in Sybase in 1993 up till now.

The same for Oracle packages and procedures.

With stored procedures , we can encapsulate all the business and data logic.

Being the stored procedure is inside the database , the data and logic is secure , highly performant and so on.

In essence , it is an API that can be called from any languages / tools.

MS SQL server started supporting embedding python scripts in T-SQL.

Unfortunately , the input for the python cannot be stored procedure.

Hopefully , it is changed in the coming releases.

Here we go

a) We will create a table emp with 2 records in it.

C:\>sqlcmd -W -S DBSER\SQL2019DEV -d rptsdb

2> select * from emp;

3> go

id fname lname

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

1 Jane Doe

1 John Doe

(2 rows affected)

b) Create python external script with simple select as the input.

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'SELECT fname , lname FROM emp ORDER BY 1;'

7> WITH RESULT SETS (( fname nvarchar(50), lname nvarchar(50) ));

8> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

c) Create stored procedure to list the records from the table 'emp'

1> create procedure p_emp_list

2> as

3> begin

4> SELECT fname , lname

5> FROM emp

6> end;

7> go

1> exec p_emp_list;

2> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

d) Let us use the above stored procedure as the input to Python script

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'exec p_emp_list;'

7> WITH RESULT SETS (( fname nvarchar(50), lname nvarchar(50) ));

8> go

Msg 39001, Level 16, State 2, Server DBSER\SQL2019DEV, Line 1

Only SELECT statement is supported for input data query to 'sp_execute_external_script' stored procedure.

Msg 11536, Level 16, State 1, Server DBSER\SQL2019DEV, Line 1

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Accessing Python Libraries in SQL Server

2020-05-04T15:05:00.001-04:00

I have been exploring to setting up a contextualized search on one of my datasets. For this experiment, I was planning on using Machine Learning Services ( MLS) in SQL Server 2019.
And , the library I was word2vec . This library is not part of the Standard libraries that came with SQL Server MLS .

Took sometime , to get this installed in my SQL Server environment. I was hoping , this blog post will be helpful , if anyone is trying to install python libraries in SQL Server .

First , we need to identify the system path , where Python is installed.

C:\>sqlcmd -W -S SERNJ\SQL2019DB
1>
2> EXEC sp_execute_external_script
3> @language =N'Python',
4> @script=N'import sys; print("\n".join(sys.path))'
5> go
STDOUT message(s) from external script:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\python37.zip
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\DLLs
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\win32
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\win32\lib
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\lib\site-packages\Pythonwin
C:\ProgramData\SQL2019DEV\Temp-PY\Appcontainer1\3E21F1D4-0032-473A-A2EB-D6763111F7F9\rxLibs
C:\ProgramData\SQL2019DEV\Temp-PY\Appcontainer1\7AD5E2EF-A002-4B2E-8ED1-4BFC71DBDB57

Then navigate to the python services scripts ( in my case , C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts ) directory and install gensim using pip.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts>pip install gensim

pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.

Collecting gensim

Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/gensim/

Could not fetch URL https://pypi.org/simple/gensim/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/gensim/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping

Could not find a version that satisfies the requirement gensim (from versions: )

No matching distribution found for gensim

pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.

Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping

For the above command to work , we need to add these directories to the path.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Lib\site-packages\conda

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Library\bin

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Scripts

Now , try the pip again to install gensim package.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\PYTHON_SERVICES\Library>pip install gensim

Collecting gensim

Downloading https://files.pythonhosted.org/packages/0b/66/04faeedb98bfa5f241d0399d0102456886179cabac0355475f23a2978847/gensim-3.8.3-cp37-cp37m-win_amd64.whl (24.2MB)

100% |████████████████████████████████| 24.2MB 1.3MB/s

Collecting Cython==0.29.14 (from gensim)

Downloading https://files.pythonhosted.org/packages/1f/be/b14be5c3ad1ff73096b518be1538282f053ec34faaca60a8753d975d7e93/Cython-0.29.14-cp37-cp37m-win_amd64.whl (1.7MB)

100% |████████████████████████████████| 1.7MB 6.5MB/s

Collecting smart-open>=1.8.1 (from gensim)

Downloading https://files.pythonhosted.org/packages/74/77/744c79da6e66691e3500b6dffff29bdd787015eae817d594791edc7b719b/smart_open-2.0.0.tar.gz (103kB)

100% |████████████████████████████████| 112kB 3.3MB/s

Requirement already satisfied: scipy>=0.18.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.1.0)

Requirement already satisfied: six>=1.5.0 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.12.0)

Requirement already satisfied: numpy>=1.11.3 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from gensim) (1.15.4)

Requirement already satisfied: requests in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from smart-open>=1.8.1->gensim) (2.21.0)

Collecting boto (from smart-open>=1.8.1->gensim)

Downloading https://files.pythonhosted.org/packages/23/10/c0b78c27298029e4454a472a1919bde20cb182dab1662cec7f2ca1dcc523/boto-2.49.0-py2.py3-none-any.whl (1.4MB)

100% |████████████████████████████████| 1.4MB 6.5MB/s

Collecting boto3 (from smart-open>=1.8.1->gensim)

Downloading https://files.pythonhosted.org/packages/c8/1e/587abcd94e8f6dbd42df730f40eb5f7313b6fd7255f5ef5a0db53d116999/boto3-1.13.1-py2.py3-none-any.whl (128kB)

100% |████████████████████████████████| 133kB 6.5MB/s

Requirement already satisfied: idna<2 .9="">=2.5 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (2.8)

Requirement already satisfied: urllib3<1 .25="">=1.21.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (1.24.1)

Requirement already satisfied: certifi>=2017.4.17 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (2019.3.9)

Requirement already satisfied: chardet<3 .1.0="">=3.0.2 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from requests->smart-open>=1.8.1->gensim) (3.0.4)

Collecting s3transfer<0 .4.0="">=0.3.0 (from boto3->smart-open>=1.8.1->gensim)

Downloading https://files.pythonhosted.org/packages/69/79/e6afb3d8b0b4e96cefbdc690f741d7dd24547ff1f94240c997a26fa908d3/s3transfer-0.3.3-py2.py3-none-any.whl (69kB)

100% |████████████████████████████████| 71kB 4.6MB/s

Collecting botocore<1 .17.0="">=1.16.1 (from boto3->smart-open>=1.8.1->gensim)

Downloading https://files.pythonhosted.org/packages/46/b8/588f44ac91f280beabd0d5ce192a65f50e32e39ebb2a4193590ccb3afff2/botocore-1.16.1-py2.py3-none-any.whl (6.2MB)

100% |████████████████████████████████| 6.2MB 6.7MB/s

Collecting jmespath<1 .0.0="">=0.7.1 (from boto3->smart-open>=1.8.1->gensim)

Downloading https://files.pythonhosted.org/packages/a3/43/1e939e1fcd87b827fe192d0c9fc25b48c5b3368902bfb913de7754b0dc03/jmespath-0.9.5-py2.py3-none-any.whl

Requirement already satisfied: docutils<0 .16="">=0.10 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from botocore<1 .17.0="">=1.16.1->boto3->smart-open>=1.8.1->gensim) (0.14)

Requirement already satisfied: python-dateutil<3 .0.0="">=2.1 in c:\program files\microsoft sql server\mssql15.sql2019dev\python_services\lib\site-packages (from botocore<1 .17.0="">=1.16.1->boto3->smart-open>=1.8.1->gensim) (2.8.0)

Building wheels for collected packages: smart-open

Running setup.py bdist_wheel for smart-open ... done

Stored in directory: C:\Users\zahir\AppData\Local\pip\Cache\wheels\27\65\38\8d7f5fe8d7afb4e4566587b2d1933cec185fba19257836c943

Successfully built smart-open

Installing collected packages: Cython, boto, jmespath, botocore, s3transfer, boto3, smart-open, gensim

Found existing installation: Cython 0.29.2

Uninstalling Cython-0.29.2:

Successfully uninstalled Cython-0.29.2

Successfully installed Cython-0.29.14 boto-2.49.0 boto3-1.13.1 botocore-1.16.1 gensim-3.8.3 jmespath-0.9.5 s3transfer-0.3.3 smart-open-2.0.0

Now , we should be able to use gensim in SQL Server .

More to come on this.

Stay tuned.

Inline External table in Oracle

2020-04-21T17:25:00.001-04:00

It is good to be back. Hope everyone is safe.
Let us pray for everyone whose lives have been impacted by #covid19.

Oracle has been enhancing "external table" features in each release .

Here is one of my post describing the support for wildcard characters in the file location.
http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

There were few external table enhancements in Oracle 18c.
One of them is that you do not need to create table first and then reference that external table in your query.

In Oracle 18c and later , I can define the external table definition inline and access the files without defining the external table.

Let us use the same example files in http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

Here is an example .

Sign on and create directory and grant the access to regular user ( in our case , zahir).

SQL> create directory EX_TAB_DIR as 'c:\datadumps\';

Directory created.

SQL> grant read , write , execute on directory EX_TAB_DIR to zahir;

Grant succeeded.

Sign on to the database with the user account.

SQL> show user
USER is "ZAHIR"

SQL> ho dir c:\datadumps
Volume in drive C has no label.
Volume Serial Number is E247-41EF

Directory of c:\datadumps

04/21/2020 05:09 PM
.
04/21/2020 05:09 PM
..
04/21/2020 04:52 PM 28 emp1.txt
04/21/2020 04:52 PM 25 emp2.txt
04/21/2020 04:53 PM 50 emp500.txt

SQL> ho type c:\datadumps\emp1.txt type c:\datadumps\emp2.txt type c:\datadumps\emp500.txt

c:\datadumps\emp1.txt

101,zahir,F
102,mohamed,R
The system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp2.txt

201,Hameed,A
252,Abdul,RThe system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp500.txt

511,Balasubramaniam,A
577,Yesudas,A
501,Janaki,A

SQL> col emp_id format a5
SQL> col emp_name format a20
SQL> col emp_type format a5
SQL> SELECT
2 emp_id,
3 emp_name,
4 emp_type
5 FROM
6 external
7 ( ( emp_id VARCHAR2(1000),
8 emp_name VARCHAR2(1000),
9 emp_type VARCHAR2(1000)
10 )
11 TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"
12 ACCESS PARAMETERS
13 ( debug = 3
14 RECORDS DELIMITED BY NEWLINE
15 CHARACTERSET we8mswin1252
16 BADFILE ex_tab_dir : 'emp_ext.bad_xt'
17 LOGFILE 'emp_ext.log_xt'
18 FIELDS TERMINATED BY "," LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
19 )
20 LOCATION ( 'emp*.txt' ) reject limit unlimited )
21 ;

EMP_I EMP_NAME EMP_T
----- -------------------- -----
101 zahir F
102 mohamed R
201 Hameed A
252 Abdul R
511 Balasubramaniam A
577 Yesudas A
501 Janaki A

7 rows selected.

In the program listing , the external table is defined inline between the lines 7 and 20.With this method, it is easier to load the data from flat files into the database without defining the table first.

Comments Welcome.

GROUP BY behavior in MySQL / MariaDB

2018-12-18T15:37:00.002-05:00

It is better to unlearn and relearn the SQL , if we need to work on different DBMS. I was surprised by the "GROUP BY" behavior in MySQL / MariaDB.

Typically , when we are using GROUP BY , all the columns except the aggregated columns mentioned in the SELECT clause has to be defined in the GROUP BY clause. If any columns are missed , then the prompt produces an error . However , in MySQL / MariaDB , this produces the result ( sum / avg ) . This may be a side effect ,which may not be the desirable result , we are looking for.

Let us create a simple table in Oracle , MS SQL Server , PostgreSQL , MySQL and MariaDB.When I issue the GROUP BY without mentioning other columns , all the DBMSes except MariaDB and MySQL produced an error.

Here is an example

ORACLE

SQL> Create table emp_hours
2 ( empid int ,
3 workweek int ,
4 hrstype varchar(10) ,
5 hrs number(5,2)
6 ) ;

Table created.

SQL> insert into emp_hours values ( 1 , 20 , 'REG' , 20 ) ;
1 row created.

SQL> insert into emp_hours values( 1 , 20 , 'OT' , 4.5 ) ;
1 row created.

SQL> insert into emp_hours values ( 2 , 20 , 'REG' , 40 ) ;
1 row created.

SQL> insert into emp_hours values( 2 , 20 , 'REG' , 35 ) ;
1 row created.

SQL> insert into emp_hours values ( 3 , 20 , 'OT' , 4.5 ) ;
1 row created.

SQL> commit;
Commit complete.

SQL> select empid , hrstype , sum(hrs) from emp_hours;
select empid , hrstype , sum(hrs) from emp_hours
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;

EMPID HRSTYPE SUM(HRS)
---------- ---------- ----------
3 OT 4.5
1 REG 20
2 REG 75
1 OT 4.5

MS SQL Server

1> Create table emp_hours
2> ( empid int ,
3> workweek int ,
4> hrstype varchar(10) ,
5> hrs numeric(5,2)
6> ) ;
7> go
1> insert into emp_hours values ( 1 , 20 , 'REG' , 20 ) ;
2> insert into emp_hours values( 1 , 20 , 'OT' , 4.5 ) ;
3> insert into emp_hours values ( 2 , 20 , 'REG' , 40 ) ;
4> insert into emp_hours values( 2 , 20 , 'REG' , 35 ) ;
5> insert into emp_hours values ( 3 , 20 , 'OT' , 4.5 ) ;
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

1> select empid , hrstype , sum(hrs) from emp_hours;
2> go
Msg 8120, Level 16, State 1, Server AUSTIN02, Line 1
Column 'emp_hours.empid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

1> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;
2> go
empid hrstype
----- ------- -
1 OT 4.50
3 OT 4.50
1 REG 20.00
2 REG 75.00

(4 rows affected)

PostgreSQL

Similarly , we get the error as shown below in Postgres for the above test case.

ERROR: column "emp_hours.empid" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803

Character: 8

MySQL

mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.5.49-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select empid , hrstype , sum(hrs) from emp_hours;
+-------+---------+----------+
| empid | hrstype | sum(hrs) |
+-------+---------+----------+
| 1 | REG | 104.00 |
+-------+---------+----------+
1 row in set (0.00 sec)

Instead of a error , the above SQL summed up the hours and produced one record.

Personally , I would prefer the above SQL to error out , instead of giving this answer.

mysql> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;
+-------+---------+----------+
| empid | hrstype | sum(hrs) |
+-------+---------+----------+
| 1 | OT | 4.50 |
| 1 | REG | 20.00 |
| 2 | REG | 75.00 |
| 3 | OT | 4.50 |
+-------+---------+----------+
4 rows in set (0.05 sec)

MariaDB

Windowing Clause ... Why it matters.

2018-10-23T16:47:00.006-04:00

Windowing clause in Analytical ( Oracle ) / Windows functions ( SQL Server ) plays an important role of how the results are rendered. This can be better explained with the following use case.

Here is an use case.

A employee may have multiple email addresses in a table. The requirement was to display the first non null email address .

Create table tab_t
( id int ,
lname varchar2(20) ,
fname varchar2(20) ,
start_dt date ,
end_dt date ,
sal number(10,2) ,
email varchar2(20)
) ;

insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('10-01-1992' , 'MM-DD-YYYY') , null , 1000.00 , 'fn1@email.com');
insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY') , 7.25 , null );
insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('05-16-1999' , 'MM-DD-YYYY') , to_date('11-15-2000' , 'MM-DD-YYYY') , 5.25 , null );
insert into tab_t values ( 2 , 'LA2' , 'FI2' , to_date('01-01-1993' , 'MM-DD-YYYY') , to_date('01-01-1995' , 'MM-DD-YYYY'), 75000.00 , 'fn2@email.com');
insert into tab_t values ( 2 , 'LA2' , 'FI2' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY') , 7.25 , 'fn2@newemail.com' );
insert into tab_t values ( 3 , 'LA3' , 'FI3' , to_date('07-02-1970' , 'MM-DD-YYYY') , to_date('02-04-1980' , 'MM-DD-YYYY') , 3.25 , null );
insert into tab_t values ( 3 , 'LA3' , 'FI3' , to_date('02-05-1980' , 'MM-DD-YYYY') , to_date('02-04-1980' , 'MM-DD-YYYY') , 10250.00 , 'myfirst@email.com' );
insert into tab_t values ( 4 , 'LA4' , 'FI4' , to_date('11-01-2009' , 'MM-DD-YYYY') , null , 90000 , 'mywork@email.com' );

SQL> Select * from tab_t;

Here is the SQL to list the first non null email address and we will filter the results where rn =1 in the second image below .

Listing 2:

SELECT

t.*,

FIRST_VALUE(email) IGNORE NULLS OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as fv_email,

ROW_NUMBER() OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as rn

FROM

tab_t t

Listing 3:

Select * from

(

SELECT

t.*,

FIRST_VALUE(email) IGNORE NULLS OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as fv_email,

ROW_NUMBER() OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as rn

FROM

tab_t t

)

Where rn = 1

Looks like , the SQL works fine.

Let us add few more records , where the first active record has null email address.

insert into tab_t values ( 5 , 'LA5' , 'FI5' , to_date('10-01-1992' , 'MM-DD-YYYY') , null , 1000.00 , null);

insert into tab_t values ( 5 , 'LA5' , 'FI5' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY') , 7.25 , 'email.com' );

insert into tab_t values ( 5 , 'LA5' , 'FI5' , to_date('05-16-1999' , 'MM-DD-YYYY') , to_date('11-15-2000' , 'MM-DD-YYYY') , 5.25 , null );

Let us execute the SQL in Listing 2 and see the results.

Here is the result after executing the SQL in Listing 3.

Obviously , our SQL did not address all the scenarios . We need the email.com as the first non null email address for Employee 5. This is because , how the "window" is defined . By default , the window is from "unbounded rows preceding" to the "CURRENT ROW" . Let us change the "Window" to "UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" as seen below.

Listing 3:

SELECT

t.*,

FIRST_VALUE(email) IGNORE NULLS OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as fv_email,

ROW_NUMBER() OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as rn

FROM

tab_t t

Listing 4:

SQL with filter only the first record.

Select * from

(

SELECT

t.*,

FIRST_VALUE(email) IGNORE NULLS OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as fv_email,

ROW_NUMBER() OVER(

PARTITION BY t.id

ORDER BY

coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC

) as rn

FROM

tab_t t

)

Where rn = 1

Now , with changing the "window " , we are able to get the correct results.

Comments welcome.

Schema only user accounts in Oracle

2018-09-17T10:49:00.001-04:00

Prior to Oracle 18c , there is no difference between user and schema . It has been used interchangeably .

SQL Server has introduced the concept of "SCHEMA" only accounts quite a while ago.
Now , Oracle 18c ( aka 12c R2) has this new feature .

We can create a schema only account with the clause " no authentication". Once this schema gets created , the objects can be accessed by either proxy users , other users .

Prior to this , typically , I create a user with no "create session" privilege ; store only the persistent information in that user . Then there will be another user that does the data processing and the logic is exposed as an API.

With 18c and " no authentication" clause , it is easier from management/administration perspective.

Example shown below

SQL> create user sales no authentication ;

User created.

SQL> alter user sales quota unlimited on users;

User altered.

SQL> alter user sales default tablespace users;

User altered.

SQL> create table sales.employees ( empname char(50)) ;

Table created.

SQL> create user webappuser identified by Pa55w0rd ;

User created.

SQL> grant select , insert , update , delete on sales.employees to webappuser;

Grant succeeded.

SQL> grant create session to webappuser ;

Grant succeeded.

SQL> alter user webappuser default tablespace users;

User altered.

SQL> alter user webappuser quota unlimited on users;

User altered.

Now , login as webappuser .

SQL> insert into sales.employees values ('Zahir');

1 row created.

SQL> commit;

Commit complete.

SQL> show user
USER is "WEBAPPUSER"
SQL>

Case Sensitiveness - Part 2

2018-04-26T15:31:00.000-04:00

Oracle maintains the case sensitiveness by default.

Prior to Oracle 12c Release 2 , we can achieve the nls settings at the session level.
Please see the blog post here ( http://mfzahirdba.blogspot.com/2010/04/case-sensitiveness.html)

With Oracle 12c Release 2 , we can set it at the column level , table level and of course at the session level.Column level settings takes precedence over other settings .

Here is the example .

Let us create the table in a traditional way .

SQL> CREATE TABLE empl_reg (
2 firstname VARCHAR2(30) ,
3 lastname VARCHAR2(30) ,
4 sal INT
5 );

Table created.

SQL> insert into empl_reg values ( 'Zahir','mohideen', 1000 ) ;

1 row created.

SQL> insert into empl_reg values ( 'zahir','mohideen', 2000 ) ;

1 row created.

SQL> insert into empl_reg values ( 'zAhir','mohIdEen', 5000 ) ;

1 row created.

SQL> insert into empl_reg values ( 'Abdul','MohIdEen', 50000 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> Select * from empl_reg where firstname ='zahir';

FIRSTNA LASTNAME SAL
------- ------------ ----------
zahir mohideen 2000

In this case , to get all records with First name as 'Zahir' , as a workaround , we typically use lower/upper function. Sometimes , we will use Function Based Index on this functions to boost the performance.

SQL> Select * from empl_reg where lower(firstname) ='zahir';

FIRSTNA LASTNAME SAL
------- ------------ ----------
Zahir mohideen 1000
zahir mohideen 2000
zAhir mohIdEen 5000

Now , let us create the table with columns - collation case insensitive ( ci) way .

SQL> Create table emp
2 ( firstname varchar2(30) collate binary_ci ,
3 lastname varchar2(30) collate binary_ci ,
4 sal int
5 ) ;

Create table emp
*
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

You would need to set the parameter to extended at the CDB , PDB SEED and PDBs and bounce the instance , as this is a static parameter.

This is a bit tedious workflow . Hopefully , in the next release , this process is made simpler .

SQL> CREATE TABLE empl (
2 firstname VARCHAR2(30) COLLATE binary_ci,
3 lastname VARCHAR2(30) COLLATE binary_ci,
4 sal INT
5 );

Table created.

SQL> insert into empl values ( 'Zahir','mohideen', 1000 ) ;

1 row created.

SQL> insert into empl values ( 'zahir','mohideen', 2000 ) ;

1 row created.

SQL> insert into empl values ( 'zAhir','mohIdEen', 5000 ) ;

1 row created.

SQL> insert into empl values ( 'Abdul','MohIdEen', 50000 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> Select * from empl where firstname ='zahir';

FIRSTNA LASTNAME SAL
------- ------------ ----------
Zahir mohideen 1000
zahir mohideen 2000
zAhir mohIdEen 5000

Now , we can get all the three records no matter what the case is .
As we can see, the explain plan does nlsssort .

SQL> explain plan for Select * from empl where firstname ='zahir';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3277272027

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPL | 1 | 47 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter(NLSSORT("FIRSTNAME",'nls_sort=''BINARY_CI''')=HEXTORAW('7A
6168697200'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

SQL> explain plan for Select * from empl_reg where firstname ='zahir';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040152178

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPL_REG | 1 | 47 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("FIRSTNAME"='zahir')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

Comments Welcome!

APPROX_PERCENTILE in Oracle 12cR2

2018-04-25T14:24:00.005-04:00

In my one of my previous blog post (http://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html) , we discussed about the PERCENTILE_CONT , PERCENTILE_DISC and median .

Beginning in 12c Release , we can get the approximate value of PERCENTILE_CONT and these related functions .

The adavcantage is that this is MUCH faster than regular PERCENTILE_CONT functions . This is very much useful , when we don't need the exact value for the results . Examples could be analyzing the website visits , surveys , etc.

As it can be seen from the example below , the execution time differs from the PERCENTILE_CONT / APPROX_PERCENTILE. The difference in the execution time could be significant , if the underlying data object is huge .

SQL> CREATE
2 TABLE t AS
3 SELECT
4 'TEST' item ,
5 'E' AS region ,
6 level AS wk ,
7 ROUND(dbms_random.value(1,500)) forecastqty
8 FROM
9 dual
10 CONNECT BY level <= 3000 ;

Table created.

Execution #1:

SQL> SELECT
2 item,
3 region,
4 wk,
5 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY forecastqty) AS fqty
6 FROM
7 t
8 GROUP BY
9 item,
10 region,
11 wk

.....
.....
......

ITEM R WK FQTY
---- - ---------- ----------
TEST E 29986 228
TEST E 29987 329
TEST E 29988 8
TEST E 29989 465
TEST E 29990 411
TEST E 29991 484
TEST E 29992 232
TEST E 29993 416
TEST E 29994 443
TEST E 29995 95
TEST E 29996 48

ITEM R WK FQTY
---- - ---------- ----------
TEST E 29997 385
TEST E 29998 367
TEST E 29999 433

30000 rows selected.

Elapsed: 00:00:11.70

Execution #2:

SQL> SELECT
2 item,
3 region,
4 wk,
5 APPROX_PERCENTILE(0.5) WITHIN GROUP(
6 ORDER BY
7 forecastqty
8 ) as approx_fqty
9 FROM
10 t
11 GROUP BY
12 item,
13 region,
14 wk
15/

......
....
......

ITEM R WK FQTY
---- - ---------- ----------
TEST E 29986 228
TEST E 29987 329
TEST E 29988 8
TEST E 29989 465
TEST E 29990 411
TEST E 29991 484
TEST E 29992 232
TEST E 29993 416
TEST E 29994 443
TEST E 29995 95
TEST E 29996 48

ITEM R WK FQTY
---- - ---------- ----------
TEST E 29997 385
TEST E 29998 367
TEST E 29999 433

30000 rows selected.

Elapsed: 00:00:09.23

In Oracle 12c Release 2, there are few other new functions for approximation.

Also , on a similar note ....

In Oracle 12c Release 1, APPROX_COUNT_DISTINCT was introduced to cater to these situations .

Please see this link for the deails

http://mfzahirdba.blogspot.com/2015/01/approxcountdistinct-new-oracle-12c.html

In Oracle 12c Release 2 , we have another parameter (APPROX_FOR_COUNT_DISTINCT) that can be set at the session level / system level to override the "approxmiation" behavior . Once we set this , the legacy COUNT functions in our legacy code will go for approximation . There is no need to revisit all our code to change it to 'APPROX_COUNT_DISTINCT' Again , this can be changed , only if the business use case allows it .

BTW , this is my 100th blog post. Comments welcome !

CSV output from Oracle

2018-01-17T14:04:00.003-05:00

Prior to 12c R2 , producing CSV ouptut from a table required bit of work .
With 12c R2 , the process is quick and easy. The good old SQLPlus has got the long awaited option .

With the SET option , we should be spooled to a file quicker.

Here is an example.

SQL> select * from emp;

ENAME ID
-------------------- ----------
Zahir 10
Hameed 20
Farook 30
Basheer 30

SQL> set markup csv on

SQL> select * from emp;

"ENAME","ID"
"Zahir",10
"Hameed",20
"Farook",30
"Basheer",30

By default , the delimiter for the character values will be enclosed in quotes.
We can turn it off by the quote off.

SQL> set markup csv on quote off
SQL> select * from emp;

ENAME,ID
Zahir,10
Hameed,20
Farook,30
Basheer,30

Sometimes , it is beneficial to a different delimiter.
In the following example , we use '|' as the delimiter .

SQL> set markup csv on delim '|' quote on
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30

In this way , the CSV ouput from oracle tables/views is a loooooot easier.

Comments welcome.

Update :
To make the example complete . We need to use spool on/off to produce a file in our directory. In the example below , I have spool the results to a file emp.csv . We can few other options to not to display ( supress ) the header , query . To make it simpler , I have just simply spooled the file .

Thanks very much to my good friend , Soma for pointing out this .

SQL> spool emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30

SQL> spool off
SQL> host dir
Volume in drive D has no label.
Volume Serial Number is AXCA-3871

Directory of D:\test

01/17/2018 03:26 PM .
01/17/2018 03:26 PM ..
01/17/2018 03:27 PM 123 emp.csv
1 File(s) 123 bytes
2 Dir(s) 710,110,654,464 bytes free

SQL> ho type emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30

Auto restart of PDB in multi tenant ( CDB) database

2018-01-11T14:08:00.000-05:00

Most of the time , when we start/restart the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE ) to open the pdbs . In 12c release 2 , the other option was to save the current state , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE).

Here is an example.

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted.

SQL> col name format a20

SQL> select name , open_mode from v$pdbs;

NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY
PDB_SIS READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size 12468584 bytes
Variable Size 2751467160 bytes
Database Buffers 7549747200 bytes
Redo Buffers 21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode from v$pdbs;

NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY

PDB_SIS MOUNTED

Now , let us open the PDB , confirm its status and save the state.
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode from v$pdbs;

NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY
PDB_SIS READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode from v$pdbs;

NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY

PDB_SIS READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME STATE
-------------------- --------------

PDB_SIS OPEN

Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs re-initalized to the saved state.

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1.0335E+10 bytes

Fixed Size 12468584 bytes

Variable Size 2751467160 bytes

Database Buffers 7549747200 bytes

Redo Buffers 21082112 bytes

Database mounted.

Database opened.

SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME STATE

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

PDB_SIS OPEN

SQL> select name , open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB_SIS READ WRITE

Thanks !

R Dataframe in the eyes of SQL developer.

2017-12-27T17:05:00.006-05:00

R has gained lot of momentum in the last few years for Data Science. At first , for a SQL professional , this may be bit daunting ; however there are lot of similarities between the RDBMS concepts and R concepts , that will make the learning curve tad easier. One of the similarity is the data frame.

Data frame is one of the important component in R to capture the data from the external data sources ( aka importing from CSV , loading from RDMS , and so on ) .

It is conceptually same as the a table in a RDBMS system.

In the following , I have created a data frame with 3 elements and 6 rows.
In RDBMS , this is the same as creating a table 'emp' and inserting 6 records.

emp <- data.frame="" span="">
name=c("Zahir","Farook","Hameed","Basheer","Aslam","Suhaib"),
deptno=c(10,20,30,30,20,20),
city=c("Monroe","Trichy","Kilakarai","Kilakarai","Chennai","Chennai"))

When the data frame is referenced at the prompt , it returns the entire data set. This is similar to "SELECT * FROM EMP",

The function "rbind" is used to insert a record into the existing dataset.
This is similar to "INSERT INTO EMP values ('Karady' , 100 , 'Colombo') "

emp <- arady="" data.frame="" deptno="c(100),city=c(" emp="" name="c(" olombo="" rbind="" span="">

The function "nrow" is used to get the record count of the dataset.
This is similar to " SELECT count(*) from EMP".

The function "ncol" is used to get the record count of the columns.
This is similar to " SELECT count(*) from information_schema.columns where table_name =EMP'" .

With the following example , we are filtering the records that have deptno = 30 . This is similar to
"SELECT * FROM EMP WHERE DEPTNO= 30'.

We can add , additional filter with the pipe function . Pipe is used for 'OR' condition.
This is similar to "SELECT * FROM EMP WHERE DEPTNO= 30 ORCITY ='Chennai'.

As we can see , there are lot of similarites in with the concept of Table (tuple) and the dataframe.
This could be a starting point to get familiar with R for a SQL professional .

I understand , I have just scratched the surface on the data frame and its functions.

As of now , Oracle and MS SQL Server has incoprated 'R' into their offerings.

Comments Welcome.

DBSAT in 12c .

2017-11-04T13:48:00.001-04:00

Oracle has provided a new tool to assess the security configuration and advise on it. It is a lightweight utility and the most important thing is that it is FREE ( surprise!). As long as , we have the active support from Oracle , we should be able to download the utility .

For the first release , it is very promising , albeit the findings for Windows Operating Systems is limited. For Windows , it does not look the OS configurations.

a) First , a user needs to be created with minimalist privileges . The documentation has the sample script that could be provisioned .

create user dbsat_user identified by xxxxx;

// If Database Vault is enabled, connect as DV_ACCTMGR to run this command

grant create session to dbsat_user;

grant select_catalog_role to dbsat_user;

grant select on sys.registry$history to dbsat_user;

grant select on sys.dba_users_with_defpwd to dbsat_user; // 11g and 12c

grant select on audsys.aud$unified to dbsat_user; // 12c only

grant audit_viewer to dbsat_user; // 12c

grant capture_admin to dbsat_user;// 12c covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$

// if Database Vault is enabled, connect as DV_OWNER to run this command

grant DV_SECANALYST to dbsat_user;

b) We need to "collect" the configuration by running the commands below. As usual , this will run " as the administrator". At the event of the process , it will ask for the password . Make sure that it is secured as the file has very important information.

D:\dbsat_output>set path=%path%;d:\dbsat

D:\dbsat_output>dbsat collect dbsat_user/xxxxxxxxxx dbsat_output20171024

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

Connecting to the target Oracle database...

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 13:01:05 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Database Security Assessment Tool version 1.0.2 (October 2016)

Setup complete.

SQL queries complete.

OS Commands Skipped.

BEGIN

ERROR at line 1:

ORA-20002: Complete without OS Commands.

ORA-06512: at line 4

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

DBSAT Collector completed successfully.

Calling D:\app\oracle\product\12.1.0\dbhome_1\bin\zip.exe to encryptdbsat_output20171024.json...

Enter password:

Verify password:

adding: dbsat_output20171024.json (164 bytes security) (deflated 88%)

zip completed successfully.

D:\dbsat_output>dir

Volume in drive D is New Volume

Volume Serial Number is 32D6-96CB

Directory of D:\dbsat_output

10/24/2017 01:01 PM .

10/24/2017 01:01 PM ..

10/24/2017 01:01 PM 29,227 dbsat_output20171024.zip

1 File(s) 29,227 bytes

2 Dir(s) 223,632,429,056 bytes free

c) We need to generate a report based on the file , generated from the last step. At the completion of this step , we will have three files ( text file , excel , html ) , that lists the recommendations. Be sure to safeguard this file , as the exposure to the outside world would cause security risks that our database has now.

D:\dbsat_output>dbsat report -a -n dbsat_output20171024