Archive for August 2015 | Monthly archive page

To read or process the Ashley Madison data is fairly straight forward. The dataset comes with a suite of files. These are:

am_am.dump.gz aminno_member.dump.gz aminno_member_email.dump.gz member_details.dump.gz member_login.dump.gz CreditCardTransactions.7z README

Each of these files come with a PGP signature. You can use gunzip on a Mac (or unix platform) to extract the files. 7z files will require 7-Zip software on a Windows computer.

You will need MySQL software from Oracle to load this data. MySQL Community edition is free.

Top Cities by Users for Ashley Madison

Here’s are the Top 100 cities. It’s interesting that Singapore doesn’t feature on the list at all. The city-state had banned the site in the interest of the family. It looks like the ban worked. Sydney, New York and Toronto looks like a hotbed of infidelity.

São Paulo 374542 New York 268171 Sydney 251813 Toronto 222982 Santiago 218125 Melbourne 213847 Houston 186795 Los Angeles 181918 London 179129 Chicago 162444 Rio de Janeiro 156572 Madrid 135294 Bogotá 123559 Brisbane 118857 Brooklyn 110859 Miami 109505 Calgary 107021 San Antonio 99157 Dallas 97736 Brasília 97096 San Diego 94953 Perth 88754 Las Vegas 87720 Atlanta 86897 Philadelphia 86018 Edmonton 84971 Lima 82279 Phoenix 81913 Belo Horizonte 77834 香港 77561 Austin 77432 Columbus 73377 Montreal 72304 Washington 71779 Jacksonville 70134 Denver 70043 Mississauga 69403 Curitiba 68916 Barcelona 68513 Dublin 65658 Ciudad de México 64516 Orlando 63549 San Francisco 62333 Minneapolis 61403 灣仔 60674 Portland 60672 Charlotte 59686 Ottawa 58463 Seattle 56935 Indianapolis 56741 Buenos Aires 56701 Adelaide 55490 Tampa 55321 Cleveland 55031 Vancouver 52651 Fort Lauderdale 52554 Cincinnati 52055 Springfield 51644 Arlington 51345 Salvador 51069 San Jose 51043 Fort Worth 50976 Medellín 50308 Beverly Hills 49437 Bronx 49067 Boston 47951 Pittsburgh 47815 Kansas City 47793 Louisville 47239 Winnipeg 47202 Porto Alegre 47018 Saint Louis 46547 Richmond 46546 Buffalo 46532 North York 46223 Roma 46000 Johannesburg 45831 Sacramento 45777 Rochester 45216 Columbia 44541 Tucson 43293 Central 41900 Oklahoma City 41809 Salt Lake City 41773 El Paso 40914 Milwaukee 40392 Hamilton 40096 Cali 38847 Colorado Springs 38696 New Delhi 38620 London 38561 Brampton 38446 Madison 37813 Paris 37641 Saint Paul 37412 Cape Town 37001 Fortaleza 36922 Scarborough 35952 Albuquerque 35802 תל אביב יפו 35602

Yes, we have a copy of it. No, we’re not selling it. However, we’ll be putting our data analytics and data quality glasses on to see what lies within.

Several Australian cities featured prominently on the list of AM users. Singapore, which had banned the website was highly represented.

There is nothing special about Ashley Madison’s leak except that the brand attracts alot of negative emotions. They probably stepped on the toes on a capable geek. Reality is that nothing is safe on the internet, and transparency is your only defence.

Should Ashley Madison have done more to protect their data? Yes! The next simple step of basic data encryption should have been done. But it wasn’t.

Bye bye Ashley Madision. You’re not the first, and I am sure you won’t be the last. There is no defence against cheating spouses expect character, honesty, truth and love.


Oracle 12c was released in Jul, 2014. It packs a number of new features which bring its up to date with utilising significant improvements in hardware, virtualisation and storage technology.

1. Pluggable Databases Through Database Consolidation

The cloud has driven Oracle to address the problem of Multi-tenancy in Oracle 12c. The core database architecture has introduced Container Databases also called (CBD) and Pluggable Databases (PDB). Memory and processes are now owned by the Container Database. A container holds the metadata where the PDBs hold the user data. You can create upto 253 PDBs including the seed PDB.

In a larger Oracle setup, it is common to see 20 or 30 different instances running in production environment. This can create a maintenance nightmare as all these instances have to be separately

Upgraded Patched Monitored Tuned RAC Enabled Adjusted Backed up and Data Guarded.

Pluggable Databases feature allows you to do all this in ONE single instance. This is a significant efficiency improvement for DBAs.

2. Redaction Policy

Data Redaction helps you to mask data. You can setup a Data Redaction policy, for example SSN field in a Employee table can be masked. This is called redaction.

From Sql Develop you can do this by going to the table: Employee->Right click on Security Policy->click on New->click on Redaction Policy->Enter SSN. When you do a select * from employee, it will show that the SSN is masked. The new data masking will use a package called DBMS_REDACT. It is the extension to the FGAC and VPD present in earlier versions. By doing this, whoever needs to view the data will be able to see it where as the other users will not be able to view it.

3. Adaptive Query Optimization and Online Stats Gathering:

With this feature, it helps the optimizer to make runtime adjustments to execution plan which leads to better stats. For statements like CTAS (Create Table As Select) and IAS (Insert As Select), the stats is gathered online so that it is available immediately.

4. Restore a Table easily through RMAN:

Earlier if you had to restore a particular table, you had to do all sorts of things like restoring a tablespace and or do Export and Import. The new restore command in RMAN simplifies this task.

5. Size Limit on Varchar2, NVarchar2, Raw Data Types increased:

The previous limit on these data types was 4K. In 12C, it has been increased to 32,767 bytes. Upto 4K, the data is stored inline. I am sure everyone will be happy with this small and cute enhancement.

6. Inline PL/SQL Functions and Procedures:

The in line feature is extended in Oracle 12C. In addition to Views, we can now have PL/SQL Procedures and Functions as in line constructs. The query can be written as if it is calling a real stored procedure, but however the functions do not actually exist in the database. You will not be able to find them in ALL_OBJECTS. I think this will be a very good feature for the developers to explore as there is no code that needs to be compiled.

7. Generated as Identity/Sequence Replacement:

You can now create a col with ‘generated as identity’ clause. Thats it. Doing this is equivalent to creating a separate sequence and doing a sequence.nextval for each row. This is another handy and a neat feature which will help developer community. This is also called No Sequence Auto Increment Primary Key.

8. Multiple Indexes on a Single Column:

Prior to 12C, a column cant be in more than one index. In 12C, you can include a column in B-tree index as well as a Bit Map index. But, please note that only one index is usable at a given time.

9. Online Migration of Table Partition or Sub Partition:

You can very easily migrate a partition or sub partition from one tablespace to another. Similar to how the online migration was achieved for a non-partitioned table in prior releases, a table partition or sub partition can be moved to another tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.

10. In Database Archiving:

This feature enables archiving rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression but are not visible to the application. These records are skipped during FTS (Full Table Scan).

Other Features:

Other Oracle features are:

Advanced Replication and Streams is deprecated. Oracle Golden Gate (a separate product) can provide this functionality. Invisible Columns. You can now have a invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries