Feed aggregator

Partner Webcast, special edition: Big Data Lab Promotion for partners

We are excited to inform all EMEA Oracle Partners of this fantastic opportunity to grow your Big Data & Cloud business through Big Data Lab Promotion packages! We can't tell you more in a...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Manage Azure in PowerShell (RM)

Yann Neuhaus - Fri, 2016-10-14 02:49

Azure offers two deployment models for cloud components: Resource Manager (RM) and Classic deployment model. Newer and more easier to manage, Microsoft recommends to use the Resource Manager.
Even if these two models can exist at the same time in Azure, they are different and managed differently: in PowerShell cmdlets are specific to RM.

In order to be able to communicate with Azure from On-Premises in PowerShell, you need to download and install the Azure PowerShell from WebPI. For more details, please refer to this Microsoft Azure post “How to install and configure Azure PowerShell“.
 
 
Azure PowerShell installs many modules located in C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell:
Get-module -ListAvailable -Name *AzureRm*
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 1.1.3 AzureRM.ApiManagement {Add-AzureRmApiManagementRegion, Get-AzureRmApiManagementSsoToken, New-AzureRmApiManagementHostnam...
Manifest 1.0.11 AzureRM.Automation {Get-AzureRMAutomationHybridWorkerGroup, Get-AzureRmAutomationJobOutputRecord, Import-AzureRmAutom...
Binary 0.9.8 AzureRM.AzureStackAdmin {Get-AzureRMManagedLocation, New-AzureRMManagedLocation, Remove-AzureRMManagedLocation, Set-AzureR...
Manifest 0.9.9 AzureRM.AzureStackStorage {Add-ACSFarm, Get-ACSEvent, Get-ACSEventQuery, Get-ACSFarm...}
Manifest 1.0.11 AzureRM.Backup {Backup-AzureRmBackupItem, Enable-AzureRmBackupContainerReregistration, Get-AzureRmBackupContainer...
Manifest 1.1.3 AzureRM.Batch {Remove-AzureRmBatchAccount, Get-AzureRmBatchAccount, Get-AzureRmBatchAccountKeys, New-AzureRmBatc...
Manifest 1.0.5 AzureRM.Cdn {Get-AzureRmCdnCustomDomain, New-AzureRmCdnCustomDomain, Remove-AzureRmCdnCustomDomain, Get-AzureR...
Manifest 0.1.2 AzureRM.CognitiveServices {Get-AzureRmCognitiveServicesAccount, Get-AzureRmCognitiveServicesAccountKey, Get-AzureRmCognitive...
Manifest 1.3.3 AzureRM.Compute {Remove-AzureRmAvailabilitySet, Get-AzureRmAvailabilitySet, New-AzureRmAvailabilitySet, Get-AzureR...
Manifest 1.0.11 AzureRM.DataFactories {Remove-AzureRmDataFactory, Get-AzureRmDataFactoryRun, Get-AzureRmDataFactorySlice, Save-AzureRmDa...
Manifest 1.1.3 AzureRM.DataLakeAnalytics {Get-AzureRmDataLakeAnalyticsDataSource, Remove-AzureRmDataLakeAnalyticsCatalogSecret, Set-AzureRm...
Manifest 1.0.11 AzureRM.DataLakeStore {Add-AzureRmDataLakeStoreItemContent, Export-AzureRmDataLakeStoreItem, Get-AzureRmDataLakeStoreChi...
Manifest 1.0.2 AzureRM.DevTestLabs {Get-AzureRmDtlAllowedVMSizesPolicy, Get-AzureRmDtlAutoShutdownPolicy, Get-AzureRmDtlAutoStartPoli...
Manifest 1.0.11 AzureRM.Dns {Get-AzureRmDnsRecordSet, New-AzureRmDnsRecordConfig, Remove-AzureRmDnsRecordSet, Set-AzureRmDnsRe...
Manifest 1.1.3 AzureRM.HDInsight {Get-AzureRmHDInsightJob, New-AzureRmHDInsightSqoopJobDefinition, Wait-AzureRmHDInsightJob, New-Az...
Manifest 1.0.11 AzureRM.Insights {Add-AzureRmMetricAlertRule, Add-AzureRmLogAlertRule, Add-AzureRmWebtestAlertRule, Get-AzureRmAler...
Manifest 1.1.10 AzureRM.KeyVault {Get-AzureRmKeyVault, New-AzureRmKeyVault, Remove-AzureRmKeyVault, Remove-AzureRmKeyVaultAccessPol...
Manifest 1.0.7 AzureRM.LogicApp {Get-AzureRmIntegrationAccountAgreement, Get-AzureRmIntegrationAccountCallbackUrl, Get-AzureRmInte...
Manifest 0.9.2 AzureRM.MachineLearning {Export-AzureRmMlWebService, Get-AzureRmMlWebServiceKeys, Import-AzureRmMlWebService, Remove-Azure...
Manifest 1.0.12 AzureRM.Network {Add-AzureRmApplicationGatewayBackendAddressPool, Get-AzureRmApplicationGatewayBackendAddressPool,...
Manifest 1.0.11 AzureRM.NotificationHubs {Get-AzureRmNotificationHubsNamespaceAuthorizationRules, Get-AzureRmNotificationHubsNamespaceListK...
Manifest 1.0.11 AzureRM.OperationalInsights {Get-AzureRmOperationalInsightsSavedSearch, Get-AzureRmOperationalInsightsSavedSearchResults, Get-...
Manifest 1.0.0 AzureRM.PowerBIEmbedded {Remove-AzureRmPowerBIWorkspaceCollection, Get-AzureRmPowerBIWorkspaceCollection, Get-AzureRmPower...
Manifest 1.0.11 AzureRM.Profile {Enable-AzureRmDataCollection, Disable-AzureRmDataCollection, Remove-AzureRmEnvironment, Get-Azure...
Manifest 1.1.3 AzureRM.RecoveryServices {Get-AzureRmRecoveryServicesBackupProperties, Get-AzureRmRecoveryServicesVault, Get-AzureRmRecover...
Manifest 1.0.3 AzureRM.RecoveryServices.Backup {Backup-AzureRmRecoveryServicesBackupItem, Get-AzureRmRecoveryServicesBackupManagementServer, Get-...
Manifest 1.1.9 AzureRM.RedisCache {Reset-AzureRmRedisCache, Export-AzureRmRedisCache, Import-AzureRmRedisCache, Remove-AzureRmRedisC...
Manifest 2.0.2 AzureRM.Resources {Get-AzureRmADApplication, Get-AzureRmADGroupMember, Get-AzureRmADGroup, Get-AzureRmADServicePrinc...
Manifest 1.0.2 AzureRM.ServerManagement {Install-AzureRmServerManagementGatewayProfile, Reset-AzureRmServerManagementGatewayProfile, Save-...
Manifest 1.1.10 AzureRM.SiteRecovery {Stop-AzureRmSiteRecoveryJob, Get-AzureRmSiteRecoveryNetwork, Get-AzureRmSiteRecoveryNetworkMappin...
Manifest 1.0.11 AzureRM.Sql {Get-AzureRmSqlDatabaseImportExportStatus, New-AzureRmSqlDatabaseExport, New-AzureRmSqlDatabaseImp...
Manifest 1.1.3 AzureRM.Storage {Get-AzureRmStorageAccount, Get-AzureRmStorageAccountKey, Get-AzureRmStorageAccountNameAvailabilit...
Manifest 1.0.11 AzureRM.StreamAnalytics {Get-AzureRmStreamAnalyticsFunction, Get-AzureRmStreamAnalyticsDefaultFunctionDefinition, New-Azur...
Manifest 1.0.11 AzureRM.Tags {Remove-AzureRmTag, Get-AzureRmTag, New-AzureRmTag}
Manifest 1.0.11 AzureRM.TrafficManager {Disable-AzureRmTrafficManagerEndpoint, Enable-AzureRmTrafficManagerEndpoint, Set-AzureRmTrafficMa...
Manifest 1.0.11 AzureRM.UsageAggregates Get-UsageAggregates
Manifest 1.1.3 AzureRM.Websites {Get-AzureRmAppServicePlanMetrics, New-AzureRmWebAppDatabaseBackupSetting, Restore-AzureRmWebAppBa...

 
The basic cmdlets to connect and navigate between your different Accounts or Subscriptions are located in “AzureRM.Profile” module:
PS C:\> Get-Command -Module AzureRM.Profile
CommandType Name Version Source
----------- ---- ------- ------
Alias Login-AzureRmAccount 1.0.11 AzureRM.Profile
Alias Select-AzureRmSubscription 1.0.11 AzureRM.Profile
Cmdlet Add-AzureRmAccount 1.0.11 AzureRM.Profile
Cmdlet Add-AzureRmEnvironment 1.0.11 AzureRM.Profile
Cmdlet Disable-AzureRmDataCollection 1.0.11 AzureRM.Profile
Cmdlet Enable-AzureRmDataCollection 1.0.11 AzureRM.Profile
Cmdlet Get-AzureRmContext 1.0.11 AzureRM.Profile
Cmdlet Get-AzureRmEnvironment 1.0.11 AzureRM.Profile
Cmdlet Get-AzureRmSubscription 1.0.11 AzureRM.Profile
Cmdlet Get-AzureRmTenant 1.0.11 AzureRM.Profile
Cmdlet Remove-AzureRmEnvironment 1.0.11 AzureRM.Profile
Cmdlet Save-AzureRmProfile 1.0.11 AzureRM.Profile
Cmdlet Select-AzureRmProfile 1.0.11 AzureRM.Profile
Cmdlet Set-AzureRmContext 1.0.11 AzureRM.Profile
Cmdlet Set-AzureRmEnvironment 1.0.11 AzureRM.Profile

According to the cmdlets present in “AzureRM.Profile” module, you will be able to connect to your Azure Account(enter your credentials):
PS C:\> Login-AzureRmAccount
Environment : AzureCloud
Account : n.courtine@xxxxxx.com
TenantId : a123456b-789b-123c-4de5-67890fg123h4
SubscriptionId : z123456y-789x-123w-4vu5-67890ts123r4
SubscriptionName : ** Subscription Name **
CurrentStorageAccount :

 
You can list your associated Azure Subscriptions:
Get-AzureRmSubscription
SubscriptionName : ** Subscription Name **
SubscriptionId : z123456y-789x-123w-4vu5-67890ts123r4
TenantId : a123456b-789b-123c-4de5-67890fg123h4

 
To switch your Subscription, do as follows:
Select-AzureRmSubscription -SubscriptionId z123456y-789x-123w-4vu5-67890ts123r4
Environment : AzureCloud
Account : n.courtine@xxxxxx.com
TenantId : a123456b-789b-123c-4de5-67890fg123h4
SubscriptionId : z123456y-789x-123w-4vu5-67890ts123r4
SubscriptionName : ** Subscription Name **
CurrentStorageAccount :

 
Or you can take a specific “snapshot” of your current location in Azure. It will help you to easily return to a specific context at the moment you ran the command:
PS C:\> $context = Get-AzureRmContext
Environment : AzureCloud
Account : n.courtine@xxxxxx.com
TenantId : a123456b-789b-123c-4de5-67890fg123h4
SubscriptionId : z123456y-789x-123w-4vu5-67890ts123r4
SubscriptionName : ** Subscription Name **
CurrentStorageAccount :
...
PS C:\> Set-AzureRmContext -Context $context
Environment : AzureCloud
Account : n.courtine@xxxxxx.com
TenantId : a123456b-789b-123c-4de5-67890fg123h4
SubscriptionId : z123456y-789x-123w-4vu5-67890ts123r4
SubscriptionName : ** Subscription Name **
CurrentStorageAccount :

 
It is also possible to list all the available Storage Account associated to your current subscriptions:
PS C:\> Get-AzureRmStorageAccount | Select StorageAccountName, Location
StorageAccountName Location
------------------ --------
semicroustillants259 westeurope
semicroustillants4007 westeurope
semicroustillants8802 westeurope

 
To see the existing blob container in each Storage Account:
PS C:\> Get-AzureRmStorageAccount | Select StorageAccountName, ResourceGroupName, Location
Blob End Point: https://dbimssql.blob.core.windows.net/
Name Uri LastModified
---- --- ------------
bootdiagnostics-t... https://dbimssql.blob.core.windows.net/bootdiagnostics-ta... 30.09.2016 12:36:12 +00:00
demo https://dbimssql.blob.core.windows.net/demo 05.10.2016 14:16:01 +00:00
vhds https://dbimssql.blob.core.windows.net/vhds 30.09.2016 12:36:12 +00:00
Blob End Point: https://semicroustillants259.blob.core.windows.net/
Name Uri LastModified
---- --- ------------
mastervhds https://semicroustillants259.blob.core.windows.net/master... 28.09.2016 13:41:19 +00:00
uploads https://semicroustillants259.blob.core.windows.net/uploads 28.09.2016 13:41:19 +00:00
vhds https://semicroustillants259.blob.core.windows.net/vhds 28.09.2016 13:55:57 +00:00
Blob End Point: https://semicroustillants4007.blob.core.windows.net/
Name Uri LastModified
---- --- ------------
artifacts https://semicroustillants4007.blob.core.windows.net/artif... 28.09.2016 13:59:47 +00:00

Azure infrastructure can be easily managed from On-Premises in PowerShell. In a previous post, I explained how to deploy a Virtual Machine from an Image in Azure PowerShell.
If you have remarks or advises, do not hesitate to share ;-)

 

Cet article Manage Azure in PowerShell (RM) est apparu en premier sur Blog dbi services.

Which Oracle Access Manager Patchsets Can Be Used with EBS?

Steven Chan - Fri, 2016-10-14 02:04

Oracle versions numbers can be very long.  Complicating matters further, different product families within Oracle use the version hierarchy differently.  This can make it confusing to determine whether a specific product update has been certified with Oracle E-Business Suite.

Specifically, a customer recently asked whether it was acceptable to apply the Oracle Access Manager 11.1.2.2.8 patchset to their OAM instance that was integrated with EBS 12.1.3.  They were concerned because our OAM integration documentation for that combination explicitly specified compatibility with OAM 11.1.2.2.0.

In this particular case, the fifth-level digit in the Oracle Access Manager version is not relevant.  In other words, the customer could apply OAM 11.1.2.2.8 because it was covered by the OAM 11.1.2.2.0 certification. 

For example, all of the following OAM patchsets will work with EBS 12.1.3:

  • OAM 11.1.2.2.0 (documented certification)
  • OAM 11.1.2.2.1
  • OAM 11.1.2.2.2
  • OAM 11.1.2.2.3
  • OAM 11.1.2.2.4
  • OAM 11.1.2.2.5
  • OAM 11.1.2.2.6
  • OAM 11.1.2.2.7
  • OAM 11.1.2.2.8

This is sometimes shown in our documentation in either of the following ways:

  • OAM 11.1.2.2
  • OAM 11.1.2.2.x

The absence of a fifth-digit or the presence of an 'X' in the fifth digit's place means any fifth-digit level updates may be applied to an OAM environment integrated with EBS 12.1.3 without requiring a new certification.  This applies to all environments, including test and production.

Related Articles

Categories: APPS Blogs

Documentum story – IAPI login with a DM_TICKET for a specific user

Yann Neuhaus - Fri, 2016-10-14 02:00

During our last project, one of the Application Teams requested our help because they needed to execute some commands in IAPI with a specific user for which they didn’t know the password. They tried to use a DM_TICKET as I suggested them but they weren’t able to do so. Therefore I gave them detailed explanations of how to do that and I thought I should do the same in this blog because I was thinking that maybe a lot of people don’t know how to do that.

 

So let’s begin! The first thing to do is obviously to obtain a DM_TICKET… For that purpose, you can log in to the Content Server and use the local trust to login to the docbase with the Installation Owner (I will use “dmadmin” below). As said just before, there is a local trust in the Content Server and therefore you can put any password, the login will always work for the Installation Owner (if the docbroker and docbase are up of course…):

[dmadmin@content_server_01 ~]$ iapi DOCBASE -Udmadmin -Pxxx
 
 
        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2015
        All rights reserved.
        Client Library Release 7.2.0050.0084
 
 
Connecting to Server using docbase DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 013f245a8000b7ff started for user dmadmin."
 
 
Connected to Documentum Server running Release 7.2.0050.0214  Linux64.Oracle
Session id is s0

 

Ok so now we have a session with the Installation Owner and we can therefore get a DM_TICKET for the specific user I was talking about before. In this blog, I will use “adtsuser” as the “specific user” (ADTS user used for renditions). Getting a DM_TICKET is really simple in IAPI:

API> getlogin,c,adtsuser
...
DM_TICKET=T0JKIE5VTEwgMAoxMwp2ZXJzaW9uIElOVCBTIDAKMwpmbGFncyBJTlQgUyAwCjEKc2VxdWVuY2VfbnVtIElOVCBTIDAKMTgwCmNyZWF0ZV90aW1lIElOVCBTIDAKMTQ1MDE2NzIzNwpleHBpcmVfdGltZSBJTlQgUyAwCjE0NTAxNjc1MzcKZG9tYWluIElOVCBTIDAKMAp1c2VyX25hbWUgU1RSSU5HIFMgMApBIDggYWR0c3VzZXIKcGFzc3dvcmQgSU5UIFMgMAowCmRvY2Jhc2VfbmFtZSBTVFJJTkcgUyAwCkEgMTEgU3ViV2F5X2RlbW8KaG9zdF9uYW1lIFNUUklORyBTIDAKQSAzMSBQSENIQlMtU0QyMjAwNDYuZXUubm92YXJ0aXMubmV0CnNlcnZlcl9uYW1lIFNUUklORyBTIDAKQSAxMSBTdWJXYXlfZGVtbwpzaWduYXR1cmVfbGVuIElOVCBTIDAKMTEyCnNpZ25hdHVyZSBTVFJJTkcgUyAwCkEgMTEyIEFBQUFFSWlRMHFST1lIZEFrZ2hab3hTUUEySityd2xPdnZVcVdKbFdVdTUrR2lDV3ZtY1dkRzVwZnRwVWRDeVVldE42QjVOMnVxajZwYnI3MEthaVNpdGU5aWdmRk43bDA0cjM0d0JtYlloaUpQWXgK
API> exit
Bye

 

Now we do have a DM_TICKET for the user “adtsuser” so let’s try to use it. You can try to login in the “common” way as I did above but that will just not work because what we got is a DM_TICKET and that’s not a valid password. Therefore you will need to use something else:

[dmadmin@content_server_01 ~]$ iapi -Sapi
Running with non-standard init level: api
API> connect,DOCBASE,adtsuser,DM_TICKET=T0JKIE5VTEwgMAoxMwp2ZXJzaW9uIElOVCBTIDAKMwpmbGFncyBJTlQgUyAwCjEKc2VxdWVuY2VfbnVtIElOVCBTIDAKMTgwCmNyZWF0ZV90aW1lIElOVCBTIDAKMTQ1MDE2NzIzNwpleHBpcmVfdGltZSBJTlQgUyAwCjE0NTAxNjc1MzcKZG9tYWluIElOVCBTIDAKMAp1c2VyX25hbWUgU1RSSU5HIFMgMApBIDggYWR0c3VzZXIKcGFzc3dvcmQgSU5UIFMgMAowCmRvY2Jhc2VfbmFtZSBTVFJJTkcgUyAwCkEgMTEgU3ViV2F5X2RlbW8KaG9zdF9uYW1lIFNUUklORyBTIDAKQSAzMSBQSENIQlMtU0QyMjAwNDYuZXUubm92YXJ0aXMubmV0CnNlcnZlcl9uYW1lIFNUUklORyBTIDAKQSAxMSBTdWJXYXlfZGVtbwpzaWduYXR1cmVfbGVuIElOVCBTIDAKMTEyCnNpZ25hdHVyZSBTVFJJTkcgUyAwCkEgMTEyIEFBQUFFSWlRMHFST1lIZEFrZ2hab3hTUUEySityd2xPdnZVcVdKbFdVdTUrR2lDV3ZtY1dkRzVwZnRwVWRDeVVldE42QjVOMnVxajZwYnI3MEthaVNpdGU5aWdmRk43bDA0cjM0d0JtYlloaUpQWXgK
...
s0

 

Pretty simple, right? So let’s try to use our session like we always do:

API> retrieve,c,dm_server_config
...
3d3f245a80000102
API> dump,c,l
...
USER ATTRIBUTES

  object_name                                   : DOCBASE
  title                                         : 
  ...

 

And that’s it, you have a working session with a specific user without the need to know any password, you just have to obtain a DM_TICKET for this user using the local trust of the Installation Owner!

 

Cet article Documentum story – IAPI login with a DM_TICKET for a specific user est apparu en premier sur Blog dbi services.

Links for 2016-10-13 [del.icio.us]

Categories: DBA Blogs

How to destroy your performance: PL/SQL vs SQL

Yann Neuhaus - Fri, 2016-10-14 00:11

Disclaimer: This is in no way a recommendation to avoid PL/SQL. This post just describes a case I faced at a customer with a specific implementation in PL/SQL the customer (and me) believed is the most efficient way of doing it in PL/SQL. This was a very good example for myself to remind me to check the documentation and to verify if what I believed a feature does is really what the feature is actually doing. When I was doing PL/SQL full time in one my of previous jobs I used the feature heavily without really thinking on what happened in the background. Always keep learning …

Lets start by building the test case. The issue was on 12.1.0.2 on Linux but I think this will be reproducible on any release (although, never be sure :) ).

SQL> create table t1 as select * from dba_objects;
SQL> insert into t1 select * from t1;
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /
SQL commit;
SQL> select count(*) from t1;

  COUNT(*)
----------
   5565632

SQL> create table t2 as select object_id from t1 where mod(object_id,33)=0;
SQL> select count(*) from t2;

  COUNT(*)
----------
    168896

This are my two tables used for the test: t1 contains around 5,5 millions rows and there is t2 which contains 168896 rows. Coming to the issue: There is a procedure which does this:

create or replace procedure test_update
is
  cursor c1 is select object_id from t2;
  type tab is table of t2.object_id%type index by pls_integer;
  ltab tab;
begin
  open c1;
  fetch c1 bulk collect into ltab;
  close c1;
  forall indx in 1..ltab.count
    update t1 set owner = 'AAA' where object_id = ltab(indx);
end test_update;
/

The procedure uses “bulk collect” and “forall” to fetch the keys from t2 in a first step and then uses these keys to update t1 in a second step. Seemed pretty well done: Not a loop over each single row, compare with the list and then do the update when there is a match. I really couldn’t see an issue here. But when you execute this procedure you’ll wait for ages (at least if you are in VM running on a notebook and not on super fast hardware).

The situation at the customer was that I was told that the update, when executed as plain SQL in sqlplus, takes less than a second. And really, when you execute this on the test case from above:

SQL> update t1 set owner = 'AAA' where object_id in ( select object_id from t2 );

168896 rows updated.

Elapsed: 00:00:05.30
SQL> rollback;

Rollback complete.

Elapsed: 00:00:02.44
SQL> update t1 set owner = 'AAA' where object_id in ( select object_id from t2 );

168896 rows updated.

Elapsed: 00:00:06.34
SQL> rollback;

Rollback complete.

Elapsed: 00:00:02.70
SQL>

It is quite fast (between 5 and 6 seconds on my environment). So why is the PL/SQL version so much slower? Aren’t “bulk collect” and “forall” the right methods to boost performance? Lets take a look at the execution plan for the plain SQL version:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |    A-Time     | Buffers | Reads  |  OMem |  1Mem |  O/1/M|
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |      1 |       |       | 24303 (100)|          |       0 |00:00:04.52    |     259K|   9325 |       |       |       |
|   1 |  UPDATE               | T1       |      1 |       |       |            |          |       0 |00:00:04.52    |     259K|   9325 |       |       |       |
|*  2 |   HASH JOIN           |          |      1 |    48 |  4416 | 24303   (1)| 00:00:01 |     168K|00:00:01.76    |   86719 |   9325 |  2293K|  2293K|  1/0/0|
|   3 |    VIEW               | VW_NSO_1 |      1 |   161K|  2044K|    72   (2)| 00:00:01 |    2639 |00:00:00.05    |     261 |     78 |       |       |       |
|   4 |     SORT UNIQUE       |          |      1 |     1 |  2044K|            |          |    2639 |00:00:00.04    |     261 |     78 |   142K|   142K|  1/0/0|
|   5 |      TABLE ACCESS FULL| T2       |      1 |   161K|  2044K|    72   (2)| 00:00:01 |     168K|00:00:00.01    |     261 |     78 |       |       |       |
|   6 |    TABLE ACCESS FULL  | T1       |      1 |  5700K|   429M| 23453   (1)| 00:00:01 |    5566K|00:00:05.88    |   86458 |   9247 |       |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

It is doing a hash join as expected. What about the PL/SQL version? It is doing this:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  4hh65t1u4basp, child number 0
-------------------------------------
UPDATE T1 SET OWNER = 'AAA' WHERE OBJECT_ID = :B1

Plan hash value: 2927627013

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |       |       | 23459 (100)|          |
|   1 |  UPDATE            | T1   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   951 | 75129 | 23459   (1)| 00:00:01 |
---------------------------------------------------------------------------

Uh! Why that? This is what I wasn’t aware of. I always thought when you use “forall” to send PL/SQL’s SQL to the SQL engine Oracle would rewrite the statement to expand the list in the where clause or do other optimizations. But this does not happen. The only optimization that takes place when you use “forall” is that the statements are send in batches to the SQL engine rather then sending each statement after another. What happens here is that you execute 168896 full table scans because the same statement (with a another bind variable value) is executed 168896 times. Can’t be really fast compared to the SQL version.

Of course you could rewrite the procedure to do the same as the SQL but this is not the point here. The point is: When you think what you have implemented in PL/SQL is the same as what you compare to when you run it SQL: Better think twice and even better read the f* manuals, even when you think you are sure what a feature really does :)

 

Cet article How to destroy your performance: PL/SQL vs SQL est apparu en premier sur Blog dbi services.

write data from a table to a csv file and save in it a directory.

Tom Kyte - Thu, 2016-10-13 19:06
I need to write data from a table to a csv file and save in it a directory. I am using below code: create or replace PROCEDURE get_query_result_as_csv_file( in_query IN VARCHAR2, in_filename IN VARCHAR2) IS l_blob BLOB; ...
Categories: DBA Blogs

Double select from same parametrized view

Tom Kyte - Thu, 2016-10-13 19:06
Dear all, I'm trying to use parameterized view, and i need select from some view twice with different values of same parameter vie UNION ALL. Could you say, having considered the above example, is it possible that the both queries will use o...
Categories: DBA Blogs

Is there a way to force DML error logging without adding LOG ERRORS INTO clause to INSERT, UPDATE etc?

Tom Kyte - Thu, 2016-10-13 19:06
For INSERT statements run against a table with an associated DML error log table, I would like to force DML error logging for INSERT statements that do not include a LOG ERRORS INTO clause. For Example: CREATE TABLE "WSB"."TEST" ("CCHAR" VA...
Categories: DBA Blogs

Audit trace for table

Tom Kyte - Thu, 2016-10-13 19:06
Hi Tom, we came across the request from customers. There were some activities on table (let's name it T1), audit was not turned on from the creation of db. Customer raised request to provide some trace or audit for T1- all activities for pas...
Categories: DBA Blogs

IMPDP STATS TABLES

Tom Kyte - Thu, 2016-10-13 19:06
Dear Tom, Please help me with the below 1) I did full dump restoration from prod to test environment 2) Now I'm trying to import the prod stats to test environment 3) Created STAT_TABLE in the newly imported test environment like below beg...
Categories: DBA Blogs

How do i insert update and delete using db link

Tom Kyte - Thu, 2016-10-13 19:06
There are two instance on Unix server Instance A and Instance B i want to update one table on Instance B from Instance A What is the best way ?
Categories: DBA Blogs

DBMS_METADATA issues

Tom Kyte - Thu, 2016-10-13 19:06
Hi, I am trying to extract the DDL code for an index. Please have a look at the below code. The same returns BEGIN NULL; END; as the output. Please can you confirm if there is something wrong with the code? drop table a; create ta...
Categories: DBA Blogs

SQL query to find employees earning more than department average

Tom Kyte - Thu, 2016-10-13 19:06
My question.. how to write query to display the employee who are getting more salary then the average salary to the department_number from department table. thanks in advance...
Categories: DBA Blogs

October 26th: Exelon Corporation—Oracle HCM Cloud Customer Forum

Linda Fishman Hoyle - Thu, 2016-10-13 17:24

Join us for an Oracle HCM Cloud Customer Forum call on Wednesday, October 26, 2016.

Tim Hickey, Director of HR Strategic Services, and Duane Akin, Director of IT at Exelon Corporation, will discuss their company’s journey from on-premises Oracle PeopleSoft HR applications to Oracle HCM Cloud. You will hear how they are tracking toward the goal of lowering Excelon’s TCO and improving the efficiency and effectiveness of its HR operations.

Register now to attend the live forum on Wednesday, October 26, 2016, at 9:00 a.m. PT.

Public Facing Citizen Website

WebCenter Team - Thu, 2016-10-13 11:37

Authored by: Mitchell Palski, Oracle Fusion Middleware Specialist

‘Interactivity’ is the name of the game when it comes to your government’s web presence. Guiding citizens in your town, village, or city, to the correct information or tools can be a difficult task. Web experiences have to cater to a VERY wide range of end-users, ranging from the iPhone illiterates to the estranged Androidians. My grandmother bookmarks websites on her home screen and thinks they are all apps. Meanwhile, my neighbor is writing his own mobile app that notifies gamers when new video games are about to be released. 

Then there’s me (a typical millennial) – the self-proclaimed “UI guy” who grew up using Hotmail and AIM. I remember dial-up, but only like a bad dream. I was always the lead Java developer in my Undergraduate group projects, but I’ve never built a mobile app from scratch. I have to Google “Unix commands” almost every time I need to actually use them, but I’ve also never paid a utility bill with an actual check. The point is, I need my city website to work for me, and my grandmother, and my nerdy neighbor.

On the other side of the screen, there’s another persona – a “technical, but not a developer” type who wants to understand their users, who needs a portfolio-bulking project, and who works for my local government IT department. He/she knows graphic design and the English language better than Enterprise Architecture and C#. Now that we know who we’re dealing with, let’s make a deal.

We’re going to build a website that is useful, usable, desirable, findable, accessible, and credible (see https://www.usability.gov). Thousands of people who all live in the same geographic region are all going to log into this website to look up information, fill out forms, pay taxes/fees, reserve public parks, and maybe even market their businesses. To sum it up, we need a solution that is going to:
  1. Improve awareness of City services
  2. Encourage users to interact with Citizen services
  3. Speed-up the process by which those Citizens are engaged
The task can seem daunting, but luckily for you (I assume you’re faced with this task, otherwise you wouldn’t be reading this) it’s 2016 and plenty of people in the world have already started doing the same thing and come up with some pretty damn good best practices for getting it done, Oracle being one of them.
If you aren’t familiar with the term “Platform as a Service” (Paas) or why it matters, check out this article by Carol Hildebrand. If you don’t have the time, here is a summary:
  • Oracle can host your development tools in the Cloud for you.
  • You won’t have to pay for the hardware, the installation, or the maintenance of those tools.
  • You’ll be able to quickly and easily scale your environment.
  • You can focus on the project, rather than the infrastructure.


In Oracle’s portfolio of Cloud products, we’ve lifted our Enterprise grade web solutions into the Cloud so that you can take advantage of tools that help with:

  1. Drag-and-drop, simple, mobile-friendly, 508-compliant web design
  2. Web Content Management and Publishing
  3. Secure customer support – Chat and Knowledge base
Oracle’s Digital Experience Platform allows you to:
  • Quickly and easily define, manage, and update your site navigation
  • Build and organize web pages using drag-and-drop, WYSIWYG user interfaces
  • Integrate seamlessly with eCommerce and customer relationship management (CRM) tools

Oracle RightNow Chat Cloud Service, agents can always guide your customers to the best answers and outcomes. Sessions can be initiated by a customer or proactively based on company-defined rules and triggers. Resolve issues with higher productivity, increase conversion rates and boost satisfaction.

The end result of combining Oracle RightNow Chat Cloud Service with the DX Platform is a complete customer journey. Your solution will not only proactively engage citizens with a complete self-service website, but also allow your staff to quickly interact with citizens directly from the online service that they need assistance with. Intuitive design mixed with an online chat service will streamline the user experience, improve user satisfaction, and reduce operational costs of active call-centers.

Want to learn more about Oracle’s DX platform? Check out these blog posts:
Oracle Products:
Other Helpful Links:

MariaDB: audit plugin

Yann Neuhaus - Thu, 2016-10-13 11:19
Why should you Audit your MySQL Instances?

First to provide you a way to track user accessing sensible data
Secondly to investigate on suspicious queries in all your critical databases
Thirdly to comply with law and industry standards

The MariaDB Audit Plugin can help you to log all or part of the server activity as:
– who was connected and at which time
– which databases and tables were accessed
– which action/event (CONNECT, TABLE,…)
– which queries were run
All of them can be stored in a dedicated audit log file

This Plugin provides auditing not only for MariaDB where it is included by default, but also for Percona Server and
even for Oracle MySQL when using the community version

Installation on MariaDB

The MariaDB Audit Plugin library is shipped with the MariaDB server
Once the server is installed, you need still to locate your plugin directory and install/load it
mysqld7-[MariaDB]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+-----------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------+
| plugin_dir | /u00/app/mysql/product/mariadb-10.1.16-linux-x86_64/lib/plugin/ |
+---------------+-----------------------------------------------------------------+
mysqld7-[MariaDB]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Check then if it has been loaded
mysqld7-[MariaDB]>SELECT * from information_schema.plugins where plugin_name='server_audit'\G
*************************** 1. row ***************************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 3.2
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.11
PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE_PLUS_PERMANENT
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.4.0

Configuration of the important audit system variables

You can either set them manually with SET GLOBAL but I recommend to define them in the option file (my.cnf)
#
## Audit Plugin MariaDB
#
server_audit_events = CONNECT,QUERY,TABLE # specifies the types of events to log
server_audit_logging = ON # Enable logging
server_audit = FORCE_PLUS_PERMANENT # Load the plugin at startup & prevent it from beeing removed
server_audit_file_path = /u00/app/mysql/admin/mysqld8/log/mysqld8-audit.log # Path & log name
server_audit_output_type = FILE # separate log file
server_audit_file_rotate_size = 100000 # Limit of the log size in Bytes before rotation
server_audit_file_rotations = 9 # Number of audit files before the first will be overwritten
server_audit_excl_users = root # User(s) not audited

Restart the server and check the audit system variables
mysqld7-[MariaDB]>show global variables like "server_audit%";
+-------------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------------------------------+
| server_audit_events | CONNECT,QUERY,TABLE |
| server_audit_excl_users | root |
| server_audit_file_path | /u00/app/mysql/admin/mysqld7/log/mysqld7-audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 10000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | sme |
| server_audit_loc_info | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| server_audit_output_type | file |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+----------------------------------------------------+
16 rows in set (0.00 sec)

Audit log file

In the Audit log file directory, you will find one current audit file and 9 archived audit log file as defined by the parameter server_audit_file_rotations
mysql@MariaDB:/u00/app/mysql/admin/mysqld7/log/ [mysqld7] ll
total 344
-rw-rw----. 1 mysql mysql 242 Oct 13 10:35
-rw-rw----. 1 mysql mysql 556 Oct 13 10:35 mysqld7-audit.log
-rw-rw----. 1 mysql mysql 10009 Oct 13 10:35 mysqld7-audit.log.1
-rw-rw----. 1 mysql mysql 10001 Oct 12 14:22 mysqld7-audit.log.2
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:47 mysqld7-audit.log.3
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:41 mysqld7-audit.log.4
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:34 mysqld7-audit.log.5
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:28 mysqld7-audit.log.6
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:22 mysqld7-audit.log.7
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:15 mysqld7-audit.log.8
-rw-rw----. 1 mysql mysql 10033 Oct 12 13:09 mysqld7-audit.log.9

You can check the latest records in the current one
mysqld7-[MariaDB]>tail -f mysqld7-audit.log
20161013 14:22:57,MYSQL,sme,localhost,31,179,QUERY,employees,'create tables tst(name varchar(20))',1064
20161013 14:23:05,MYSQL,sme,localhost,31,180,CREATE,employees,tst,
20161013 14:23:05,MYSQL,sme,localhost,31,180,QUERY,employees,'create table tst(name varchar(20))',0
20161013 14:23:41,MYSQL,sme,localhost,31,181,WRITE,employees,tst,
20161013 14:23:41,MYSQL,sme,localhost,31,181,QUERY,employees,'insert into tst values(\'toto\')',0
20161013 14:24:26,MYSQL,sme,localhost,31,182,WRITE,employees,tst,
20161013 14:24:26,MYSQL,sme,localhost,31,182,QUERY,employees,'update tst set name=\'titi\'',0
20161013 14:24:53,MYSQL,sme,localhost,31,183,QUERY,employees,'delete from tst',1142
20161013 14:48:34,MYSQL,sme,localhost,33,207,READ,employees,tst,
20161013 14:48:34,MYSQL,sme,localhost,33,207,QUERY,employees,'select * from tst',0
20161013 15:35:16,MYSQL,sme,localhost,34,0,FAILED_CONNECT,,,1045
20161013 15:35:16,MYSQL,sme,localhost,34,0,DISCONNECT,,,0
20161013 15:35:56,MYSQL,sme,localhost,35,0,CONNECT,,,0
20161013 15:35:56,MYSQL,sme,localhost,35,210,QUERY,,'select @@version_comment limit 1',0
20161013 15:36:03,MYSQL,sme,localhost,35,0,DISCONNECT,,,0

The audit log file which is a plain-text format contains the following commas separated fields
timestamp : 20161012 10:33:58
serverhost : MYSQL
user : sme
host : localhost
connection id: 9
query id : 77
operation : QUERY
database : employees
object : ‘show databases’ # Executed query if QUERY or table name if TABLE operation.
return code : 0

Installation on Percona or MySQL

It is quite the same as on MariaDB
Once your server is installed, you have to look after your plugin directory
mysqld8-[Percona]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------------------------------+
| plugin_dir | /u00/app/mysql/product/Percona-Server-5.7.14-7-Linux.x86_64.ssl101/lib/mysql/plugin/ |
+---------------+--------------------------------------------------------------------------------------+

mysqld10-[MySQL]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------+
| plugin_dir | /u00/app/mysql/product/mysql-5.6.14-linux-glibc2.5-x86_64/lib/plugin/ |
+---------------+-----------------------------------------------------------------------+

then copy the MariaDB plugin server_audit.so in the Percona/MySQL plugin directory
mysql@Percona:[mysqld8] cp /u00/app/mysql/product/mariadb-10.1.16-linux-x86_64/lib/plugin/server_audit.so
/u00/app/mysql/product/Percona-Server-5.7.14-7-Linux.x86_64.ssl101/lib/mysql/plugin/

mysql@MySQL:[mysqld10] cp /u00/app/mysql/product/mariadb-10.1.16-linux-x86_64/lib/plugin/server_audit.so
/u00/app/mysql/product/mysql-5.6.14-linux-glibc2.5-x86_64/lib/plugin/

Install/load the plugin & check
mysqld8-[(Percona)]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';
mysqld8-[Percona]>SELECT * from information_schema.plugins where plugin_name='server_audit'\G
*************************** 1. row ***************************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE_PLUS_PERMANENT

mysqld10-[MySQL]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';
mysqld10-[MySQL]>SELECT * from information_schema.plugins where plugin_name='server_audit'\G
**************************** 1. row ***************************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 3.2
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE_PLUS_PERMANENT

Configuration of the important audit system variables

You can take exactly the same audit system variables defined for MariaDB

Conclusion

The MariaDB Auditing Plugin is really quick and easy to install
It is a good and cheap auditing solution and can be installed on different distributions
It lets you see exactly what SQL queries are being processed
Auditing information can really help you to track suspicious queries, detect mistakes and overall troubleshoot abnormal activity

 

Cet article MariaDB: audit plugin est apparu en premier sur Blog dbi services.

List with Details on a Single Page in Oracle Application Builder Cloud Service

Shay Shmeltzer - Thu, 2016-10-13 11:11

This question came up a couple of times from users so I figured I'll document how to achieve a layout that shows a list of items and allows you to pick an item from this list to show the details of this item on the same page.

list with details image

The default layout that ABCS creates is a list on one page with the ability to select an item and go see the details or edit that record on another page.

To combine the two into a single page, start from the edit or the details page that ABCS created.

On this page you then add the table or list for the same object, and set the link on a field to do the edit or details - this basically means that you'll do a navigation to the same page.

If you now run the page you'll be able click items in the table and see their details on the same page.

Here is a quick demo of how it is done:

Note that if you want this to be the default view that people see when navigating to your app - just update the navigation menu of your application to reflect this. 

Categories: Development

Partitioning – When data movement is not performed as expected

Yann Neuhaus - Thu, 2016-10-13 07:16

This blog is about an interesting partitioning story and curious data movements during merge operation. I was at my one of my customer uses intensively partitioning for various reasons including archiving and manageability. A couple of days ago, we decided to test the new fresh developed script, which will carry out the automatic archiving stuff against the concerned database in quality environment.

Let’s describe a little bit the context.

We used a range-based data distribution model. Boundary are number-based and are incremented monolithically with identity values.

We defined a partition function with range right values strategy as follows:

CREATE PARTITION FUNCTION pfOrderID (INT) 
AS RANGE RIGHT FOR VALUES(@boundary_archive, @boundary_current)
go

 

The first implementation of the partition scheme consisted in storing all partitioned data inside the same filegroup.

CREATE PARTITION SCHEME psOrderID 
AS PARTITION pfOrderID 
ALL TO ([PRIMARY])
go

Well, the initial configuration was as follows. We applied page compression to the archive partition because it contained cold data which is not supposed to be updated very frequently.

blog 106 - 0 - partitioned table starting point

blog 106 - 1 - partitioned table starting point config

As you may expect, the next step will consist in merging ARCHIVE and CURRENT partitions by using the MERGE command as follows:

ALTER PARTITION FUNCTION pfOrderID() 
MERGE RANGE (@b_archive);

 

Basically, we achieve a merge operation according to Microsoft documentation:

The filegroup that originally held boundary_value is removed from the partition scheme unless it is used by a remaining partition, or is marked with the NEXT USED property.

 

blog 106 - 2 - partitioned table after merge

At this point we may expect SQL Server has moved data from the middle partition to the left partition and according to this other Microsoft pointer

When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.

But the results come as a little surprise because I expected to see a page compression value from my archive partition.

blog 106 - 3 - partitioned table after merge config

At this point, my assumption was either compression value doesn’t inherit correctly as mentioned in the Microsoft documentation or data movement is not performed as expected. The latter may be checked very quickly by looking at the corresponding records inside the transaction log file.

SELECT 
	[AllocUnitName],
	Operation,
	COUNT(*) as nb_ops
FROM ::fn_dblog(NULL,NULL)
WHERE [Transaction ID] = (
     SELECT TOP 1 [Transaction ID]
     FROM ::fn_dblog(NULL,NULL)
     WHERE [Xact ID] = 164670)
GROUP BY [AllocUnitName], Operation
ORDER BY nb_ops DESC
GO

I put only the relevant sample here

blog 106 - 5 - log file after merge config

Referring to what I saw above, I noticed data movement was not performed as expected but rather as shown below:

blog 106 - 4 - partitioned table after merge config 2

So, this strange behavior seems to explain why compression state switched from PAGE to NONE in my case. Another strange thing is when we changed the partition scheme to include an archive filegroup, we returned to normality.

CREATE PARTITION SCHEME psOrderID 
AS PARTITION pfOrderID 
TO ([ARCHIVE], [PRIMARY], [PRIMARY])
go

 

I doubled checked the Microsoft documentation to see if it exists one section to figure out the behavior I experienced in this case but without success. After further investigations I found out an interesting blog post from Sunil Agarwal (Microsoft) about partition merging and some performance improvements shipped with SQL Server 2008 R2. In short, I was in the specific context described in the blog post (same filegroup) and merging optimization came into action transparently because number of rows in the archive partition was lower than the one in the current partition at the moment of the MERGE operation.

Let me introduce another relevant information – the number of lines for each partition – to the following picture:

blog 106 - 6 - partitioned table optimization stuff

Just to be clear, this is an interesting and smart mechanism provided by Microsoft but it may be a little bit confusing when you’re not aware of this optimization. In my case, we finally decided with my customer to dedicate an archiving partition to store cold data that will be rarely accessed in this context and keep up the possibility to store cold data on cheaper storage when archiving partition will grow to a critical size. But in other cases, if storing data is the same filegroup is a still relevant scenario, keep in mind this optimization to not experience unexpected behaviors.

Happy partitioning!

 

 

Cet article Partitioning – When data movement is not performed as expected est apparu en premier sur Blog dbi services.

How can we use Oracle to deduplicate our data

Bar Solutions - Thu, 2016-10-13 05:13

Dear Patrick,

We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?

Ramesh Cunar

Dear Ramesh,

Going through all these records by hand seems like quite a time consuming process. I don’t know the number of records in your tables, but I would definitely not do this by hand. Writing an application to do this makes it possible to run the application multiple times, so you can update the rules making it better every time. Writing an application can be time consuming and running it can be as well.

I think your best shot would be to try and find a solution in either PL/SQL or SQL. Depending on the complexity and size of your data you could try any of the following solutions.

You can write a procedure that loops through your data and saves the records in an Associative array. If the record already exists in the array, or at least the key fields are equal, then it should not be added.

CREATE OR REPLACE PROCEDURE ot_deduplicate_aa IS
  TYPE t_ot_emp_aa IS TABLE OF ot_emp%ROWTYPE INDEX BY PLS_INTEGER;
  CURSOR c_ot_emp IS
    SELECT e.empno
          ,e.ename
          ,e.job
          ,e.mgr
          ,e.hiredate
          ,e.sal
          ,e.comm
          ,e.deptno
      FROM ot_emp e;
  r_ot_emp         ot_emp%ROWTYPE;
  l_unique_ot_emps t_ot_emp_aa;
  FUNCTION record_already_exists(record_in        IN ot_emp%ROWTYPE
                                ,collection_inout IN OUT t_ot_emp_aa) RETURN BOOLEAN IS
    l_indx        PLS_INTEGER := collection_inout.first;
    l_returnvalue BOOLEAN := FALSE;
  BEGIN
    IF l_indx IS NOT NULL THEN
      LOOP
        l_returnvalue := l_returnvalue OR ((record_in.ename = collection_inout(l_indx).ename) AND
                         (record_in.job = collection_inout(l_indx).job));
        l_indx := collection_inout.next(l_indx);
        EXIT WHEN l_returnvalue OR(l_indx IS NULL);
      END LOOP;
    END IF;
    RETURN l_returnvalue;
  END record_already_exists;

BEGIN
  OPEN c_ot_emp;
  LOOP
    FETCH c_ot_emp
      INTO r_ot_emp;
    EXIT WHEN c_ot_emp%NOTFOUND;
    -- check if this record already exists
    IF NOT (record_already_exists(record_in => r_ot_emp, collection_inout => l_unique_ot_emps)) THEN
      l_unique_ot_emps(l_unique_ot_emps.count + 1) := r_ot_emp;
    END IF;
  END LOOP;
  FOR indx IN l_unique_ot_emps.first .. l_unique_ot_emps.last LOOP
    INSERT INTO ot_emp_deduplicated
      (empno
      ,ename
      ,job
      ,mgr
      ,hiredate
      ,sal
      ,comm
      ,deptno)
    VALUES
      (l_unique_ot_emps(indx).empno
      ,l_unique_ot_emps(indx).ename
      ,l_unique_ot_emps(indx).job
      ,l_unique_ot_emps(indx).mgr
      ,l_unique_ot_emps(indx).hiredate
      ,l_unique_ot_emps(indx).sal
      ,l_unique_ot_emps(indx).comm
      ,l_unique_ot_emps(indx).deptno);
  END LOOP;
END ot_deduplicate_aa;
/

You can speed up this process by using bulk processing for both the retrieval and the storing of the data. Watch out that you don’t blow up your memory by retrieving too much data at once. You can use the limit clause to prevent this.

Another way to go at this, is the use of the analytic functions in Oracle. The idea behind using analytics is to rank all the record in their own partition. This rank can be rather arbitrary so we can use ROW_NUMBER as a ranking mechanism. In the PARTITION BY clause we can add all the columns we need to check for duplicates on. And to define which record to keep we add the (obligatory) order by clause. Then, using this new column, we can state that we are just interested in the records where the ROW_NUMBER equals 1.

WITH emp_all_rows AS
 (SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno
       ,row_number() OVER (PARTITION BY e.ename, e.job ORDER BY e.empno ASC) rn
    FROM ot_emp e) 
INSERT
  INTO ot_emp_deduplicated(empno, ename, job, mgr, hiredate, sal, comm, deptno)
(SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
   FROM emp_all_rows
  WHERE rn = 1)
/

You can of course use this same technique to delete the duplicate rows directly from the source table, but then you cannot use the query factoring (WITH clause) because that isn’t supported in SQL.

DELETE FROM ot_emp e
 WHERE e.rowid IN 
(SELECT ear.rowid
   FROM (SELECT e.rowid
               ,row_number() over(PARTITION BY e.ename ORDER BY e.empno ASC) rn
           FROM ot_emp e) ear --<-- EmpAllRows
  WHERE rn > 1)
/

It is probably faster to insert the de-duplicated rows into a new table, then drop the original table and rename the new table. Be aware of any triggers, constraints, indexes etc. that may exist and need to be recreated.

You can check out the full demo code at
https://livesql.oracle.com/apex/livesql/s/ckqg3dkqmvj5omqttapzmrhtg
Or you can send me an email and I will send you the demo script.

Hope this helps in your efforts to de-duplicate your data.

Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Winter 2015.

Pages

Subscribe to Oracle FAQ aggregator