Ref: https://www.devopsschool.com/blog/oracle-tutorials-what-is-pfile-and-spfile/

7 Auditing Database Activity

This chapter contains:

• About Auditing
• Why Is Auditing Used?
• Where Are Standard Audit Activities Recorded?
• Auditing General Activities Using Standard Auditing
• Tutorial: Creating a Standard Audit Trail
• Guidelines for Auditing
• Initialization Parameters Used for Auditing

Ref: https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50511

https://learn.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?view=sql-server-ver16

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectURL {
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
            String SQL = "SELECT TOP 10 * FROM Person.Contact";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

For Java Platform:

Ref: https://www.jooq.org/

•Hierarchical Queries

•START WITH

•CONNECT BY PRIOR

•CONNECT BY FOLLOWING

•ORDER by level

•Analytics Functions

•Windowing

•PRECEDING, UNBOUNDED_PRECEDING

•ROWS BETWEEN Unbounded Preceding

•CURRENT ROW

•RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND INTERVAL ‘30’ DAY Following

•ROWS BETWENN 1 PRECEDING and 1 FOLLOWING

Other Analytics Functions and Clauses:

•OVER()

•PARTITION OVER

•Over Partition By

•Avg () OVER Partition BY

•RANK()

•DENSE_RANK()

•DENSE_RANK FIRST

•DENSE_RANK LAST

•RANK or DENSE_RANK OVER

•KEEP

•Aggregate by keeping

••KEEP (DENSE_RANK FIRST ORDER BY …)

•KEEP (DENSE_RANK LAST ORDER BY …)

•RANK()

•RANK() OVER

•RANK() OVER PARTITION BY

•DENSE RANK() OVER

•DENSE RANK() OVER PARTITION BY

•PARTITION BY …. ORDER BY

•ROW_NUMBER() OVER (ORDER By …)

•ROWS BETWEEN Unbounded Preceding

•CURRENT ROW

•RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND INTERVAL ‘30’ DAY Following

•ROWS BETWENN 1 PRECEDING and 1 FOLLOWING

oKEEP

–Aggregate by keeping

oDENSE_RANK()

oDENSE_RANK FIRST

oDENSE_RANK LAST

oOver()

oOver Partition By

–Avg () OVER Partition BY

oROW_NUMBER

oRANK()

oDENSE_RANK

oVariations

–RANK() OVER

–RANK() OVER PARTITION BY

–DENSE RANK() OVER

–DENSE RANK() OVER PARTITION BY

–PARTITION BY …. ORDER BY

–(Partition … order by)

REF: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/CREATE-FUNCTION-statement.html

Ref: https://docs.oracle.com/database/121/LNPLS/create_procedure.htm#LNPLS01373

Tools for data integration, ETL (Extract, Transform, Load), and data management.

1. Apache NiFi

2. Pentaho Data Integration (PDI)

3. Apache Spark

4. Microsoft SQL Server Integration Services (SSIS)

5. Informatica PowerCenter

6. Apache Airflow

7. Matillion

8. Fivetran

9. Stitch

10. Apache Camel

11. Luigi

12. SnapLogic

13. DataStage (IBM)

Ref: Internet

From: https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Syntax:

Example:

Write a SQL block that will categorize Customers’ Credit Limit. If the limit is > 5000, show high, when 100 show low otherwise show medium. Utilize CASE statement.

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;

CUST_LAST_NAME       CASECR
-------------------- ------
...
Bogart               Medium
Nolte                Medium
Loren                Medium

Isolation => I in ACID

Supported two types:

• Read Committed
• Serializable

However, there are other levels of ISOLATION such as:

Read Uncommitted

Repeated READ

—

Note: in Read Committed, dirty data is locked until committed.

In Repeated Read, A row (being modified) is locked until committed.

In Serializable, A block/dataset/table (being modified) is locked until Serialization Terminated.

TCL/Transaction Control Language Commands

COMMIT

SET AUTOCOMMIT OFF/ON

ROLLBACK

SAVEPOINT

ROLLBACK TO SAVEPOINT