Oracle Cheatsheet: January 2019

1. In Google API Console, search for Google Analytics and enable it

2. Create credential, pick service account key, pick project owner and use json key type. Google will download the JSON private key file to your computer

Sample JSON private key file

{
  "type": "service_account",
  "project_id": "penweb",
  "private_key_id": "3ec4cfc55497e888886099ded8c0c4321be4034b",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCv6/lV5nqGEDSb\nwTqoQxedjtChDsfSnX8QqAxSbWJK4z6foQ/DxjYdL+uH557eA+ftN1iQ+nf9RfvM\nxo0pbSj7hF/9MRZucsHsPTs5/AlegqiBBwQLKhPao1fUvoENLoSe9Vm/sohCm7eR\nuJiU5QBJLDuOk4msGtVi/CmVZQkmiTJpkh312345GQsMLRhh9brG5jAoYFFVgQoV\n6Pf8gjLkbC0Y10IICnhV/YSB3ygGsQc1WxZFfyBQVmTK4USghm5WBoci3MBavPaS\nGsjrNtg3DbY1H+UVrmc9GQ6JVLMpP6K5smIbVEJremSM1gMbHoGap/qpH2pHtSgj\nDVP0Fv3dAgMBAAECggEAJtLpw5CaDSCh75bBCnXGDi5Asi6YIE3ER0mC8KT1uoe0\noBd0KnVBp4bWELfn6mDLzCD5AYPZO7K4a3yNFi2iTAGuimrNI8JdNDTeL4GFEs+8\nfr8s4Abg+1EtlsSVJ/LSb18/t4vfwO1rIXRu7fss6TkjCqfaS/pfCdmTFsaFsrRa\nBIzAJufy6MHc3e692kOruavxD0kZi4aESsNgjL1LL7TxltGvTPj0sYXrDXxYfVRL\nHbGDcmWpxsarimKY+sTxNUPRj778uT9RWtZqw2WAgBXMmhQo6kNCsmxKKJzJC9ez\nWO1KATt23bJ7A3Ak09876a6G8mqeHAeGin85lfN2+QKBgQDm4Zn61ybijeqAZqkm\nSwWYlPWu7Zdw+WOeGr1aGeSjPLwa32r17Fh17BYzl9e2FBiBZDOahn/z65HyT7Gl\nSjSqry1k9W0kuIR2L/Ovx/k7E49vyaQqo24IH643wpN3eZzgSbbYAgTyj7bywELa\nZzeH1LCc/3kRGYJ0JYh3aIOKGQKBgQDDD6qQIzIw7aIe3+otDqHCDSEZvy2XtBHZ\nZdULdapqyAup4wY6Wi5HRqpOoM5L66J5vKoF57/IN4kvsY28YqK0+GewLwcyLDfh\nVAKQREA/n5d8R17gmsE/BqM7+B3k+cyJfoHpE61lDSdYHikPth+Hve3nJhENc+O5\na11391HSZQKBgQDEO796Gd/s8x37IEZoS59n/kOK+FmdJfpraOLDb/q54321aHMq\nrkTJhW2hUE+BbDcf0qChl+usz+3t+5pLmHSHFfkJNCgd6855/WurXdYRRiDFrKah\nruJYeUTEE9bu1yhk4YJMufqmh0vBq3Om2c+y3S13YxPtWrcsLLJ+exBCEQKBgGFX\ncxWlCsoR/IKN2W4MzDprlOYlWppHGVU1FUE3wgixtehcy+HY2RlmsVy1sN2ARL+d\nn/d8dF91H3f2kjW9v1ayVAQ/I8cs++Htq63U8X8OpGk3sKhDckaCiHlH05tN8X13\n7VqkIDa6frThsDoP9+IkrRFaFM5y0+TXKHjSqjllAoGATMQScip6IwgPoXRvMPvZ\nB6CDEZc2+xxrpCLqVVc9hj3Qx1X01UhpoZ6POS9ALHDTiMxp4+O8gjqz/lQ8GVIt\nSq8zYlhJjBiXGkdtNiyFHqQO7HgAWGPEVgBxB3yznOw0ASHlvK6SmAq+nqBM8K1v\nYAPDle9crXVN+SelID+xUs8=\n-----END PRIVATE KEY-----\n",
  "client_email": "apex-39@penweb.iam.gserviceaccount.com",
  "client_id": "116219189999913805651",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/apex-39%40penweb.iam.gserviceaccount.com"
}

3. Add a new user to the Google Analytics Dashboard. When ask for email, use the client_email from the JSON file. Give the new user permission for read.

4. Build the JWT file for authentication. I use https://jwt.io
Algorithm : RS256
Payload Data, change iat to current epoch time, exp to 1 hour later, https://www.epochconverter.com/

{
  "iss":"apex-39@penweb.iam.gserviceaccount.com",
  "scope":"https://www.googleapis.com/auth/analytics.readonly",
  "aud":"https://www.googleapis.com/oauth2/v4/token",
  "exp":1548635913,
  "iat":1548632524
}

5. Generate Signature
You should find two text boxes. Paste the private key in the JSON private key file into the private key box without the \n

It will say invalid signature because we didn't provide the public key, but all we need is the encoded signature.

6. Server to Server Authentication
Put the whole encoded signature from jwt.io in the assertion parameter.

Request

curl --request POST \
  --url https://www.googleapis.com/oauth2/v4/token \
  --header 'Content-Type: application/x-www-form-urlencoded' \
  --header 'cache-control: no-cache' \
  --data 'grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJhcGV4LTM5QHBlbndlYi5pYW0uZ3NlcnZpY2VhY2NvdW50LmNvbSIsInNjb3BlIjoiaHR0cHM6Ly93d3cuZ29vZ2xlYXBpcy5jb20vYXV0aC9hbmFseXRpY3MucmVhZG9ubHkiLCJhdWQiOiJodHRwczovL3d3dy5nb29nbGVhcGlzLmNvbS9vYXV0aDIvdjQvdG9rZW4iLCJleHAiOjE1NDg2MzU5MTMsImlhdCI6MTU0ODYzMjUyNH0.JtE0Q8oC6uaOqCvn59z_UKZPq7qHSLAVqJUFRDDP5nD22S_XL27HgA3mIEZJ9wsxrKngreeXfDqWIyVasdYVPdqowkK5ZB2r3BbAyyOrYnRpLEDiNLW82vYYtdzWKNpJFyhUO46CbuFy53Ntgov03jacnar3aMrd49NYNPZqP4Aiajk1Fivv4164CWbkWcZAB-YfEq1iCgbO_ZESkJaHP5ga8emmgErr3EU4DeiucrFgpqffwpxdr906VnImMlgcbhYcPhw8YmmJK1NhKxmChjjRBmodLncW-iy8H8AL2qeC6eX3MQMzOGEkJcd4wh8RDf7sW57_pW-zTF1Y8NvL5A'

Response

{
    "access_token": "ya29.c.ElmfBt9mjZ4nQRqRC92Z4uNMXgL9PugkbtKocv5DR-lAN-SNOOQwsTBkM5AUEP0GJGwUud6cBTk6Vfmmw0L0zmYslrogWVHAQdn1wPTwI4FZc9sL-2d7zD3c5A",
    "expires_in": 3600,
    "token_type": "Bearer"
}

7. Try to get some data from Google Analytics
You can find the ids from Google Analytics dashboard, admin->view settings (far right)->View ID (187243477)

Request

curl --request GET \
  --url 'https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&start-date=30daysAgo&end-date=yesterday&metrics=ga%3Ausers&dimensions=ga%3AuserType' \
  --header 'Authorization: Bearer ya29.c.ElmfBt9mjZ4nQRqRC92Z4uNMXgL9PugkbtKocv5DR-lAN-SNOOQwsTBkM5AUEP0GJGwUud6cBTk6Vfmmw0L0zmYslrogWVHAQdn1wPTwI4FZc9sL-2d7zD3c5A' \
  --header 'cache-control: no-cache'

Response

{
    "kind": "analytics#gaData",
    "id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:userType&metrics=ga:users&start-date=30daysAgo&end-date=yesterday",
    "query": {
        "start-date": "30daysAgo",
        "end-date": "yesterday",
        "ids": "ga:187243477",
        "dimensions": "ga:userType",
        "metrics": [
            "ga:users"
        ],
        "start-index": 1,
        "max-results": 1000
    },
    "itemsPerPage": 1000,
    "totalResults": 2,
    "selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:userType&metrics=ga:users&start-date=30daysAgo&end-date=yesterday",
    "profileInfo": {
        "profileId": "187243477",
        "accountId": "131677935",
        "webPropertyId": "UA-131677935-1",
        "internalWebPropertyId": "191281733",
        "profileName": "All Web Site Data",
        "tableId": "ga:187243477"
    },
    "containsSampledData": false,
    "columnHeaders": [
        {
            "name": "ga:userType",
            "columnType": "DIMENSION",
            "dataType": "STRING"
        },
        {
            "name": "ga:users",
            "columnType": "METRIC",
            "dataType": "INTEGER"
        }
    ],
    "totalsForAllResults": {
        "ga:users": "55"
    },
    "rows": [
        [
            "New Visitor",
            "32"
        ],
        [
            "Returning Visitor",
            "23"
        ]
    ]
}

8. The access token is good for one hour. After that, we need to start from step #4-6 again

Check the current retention value.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

For alter this retention period to 90 days

execute dbms_stats.alter_stats_history_retention(90);

For check the old record present in database (upto you restore the stats)

select dbms_stats.get_stats_history_availability from dual;

Check out the statistics history

SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner='OWNER_APP'
AND table_name='ORDERS'
ORDER BY stats_update_time;

Check out the differences

select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

Restore the previous statistics for the whole database

execute dbms_stats.restore_database_stats(sysdate-1);

Restore the previous statistics for a schema

execute dbms_stats.restore_schema_stats (ownname=>'OWNER_APP',AS_OF_TIMESTAMP=>sysdate-1);

Restore the previous statistics for a table

execute dbms_stats.restore_table_stats (ownname=>'OWNER_APP', tabname=>'ORDERS', AS_OF_TIMESTAMP=>sysdate-1);

Flush the shared pool to see the new execute plan after restore

alter system flush shared_pool

Oracle Cheatsheet

Sunday, January 27, 2019

Google Analytics authentication using OAuth 2.0 for Server to Server Applications

Thursday, January 3, 2019

Oracle Statistics History