Application Development - Database Technologies

Database Technologies

Large Centralized Repository - Data Lake and Big Data

Non Relational Database - NoSQL

Oracle Database, Tools and Technology

Oracle 19c     Oracle 12c     Oracle 11g     Oracle Functions     v$ Views

Systems Architect, DBA Tips

Database General Information

Create Database   Oracle DB Info.   DB Info - 2   DB Info - 3   DB Info - 4   Datafile

Oracle DBA - Tips   DBA Scripts

Compile Objects   - Schema

SYS_CONTEXT   Describe Table   Object Dependencies

Database Login   Audit   Manage User   Privileges   Column, Role, Table

All_Tab_Columns   Insert Into   All_Objects

Oracle DB Memory/CPU    Two    Process    Session    AWR/IOPS

Buffer Cache   Hit Ratio

Library Cache   RowCache

Locked Objects   Longops Analysis

DBMS_SCHEDULER Jobs - One   Two

Segments and Tablespace Queries

Database Size   Object Size - Table, MVW   Segments   Range Partition   TableSpace   Temp Space

Parallel Processing   PGA   SGA   UGA

DDL Reference   Scripts   Oracle AWS-RDS

Manage Index   Manage Sequence

Oracle Errors and Resolution

00603   01555   06508

IOT   External Table   Temp Table   Nested Table   Object/Relational   Streams Table

Oracle Data Warehousing   Materialized Views

SQLLDR (csv)   - Fixed Width   - Skip/ContinueIF   - Streaming   - CLOB 1   - CLOB 2   XML Data

Developer Tips

Dual Table Queries   String Reverse   Virtual Data   SQL Reference

Hextoraw, Date   Count WeekDay   Data Load   Number Series

DateDiff   Calendar Creation   Quarters   Bi-Yearly   Range Query

Week Data Setup   Week Analysis   Week Day

Year-Month Analysis   Year-Month Two   Month Range

WHERE Clause Switching by DECODE

Hierarchy Query -> One   Two   Three   Four   NoCycle   30004

Oracle Developer -> One    Two    Three    Four    ANSI Join    ANSI Outer Join

Input Parameter As A String To Oracle Stored Procedure

Grouping_ID, Grouping Sets    Group_ID/Rollup    CUBE    Bit Vector    3-Column    4-Column    Grouping_ID/Rollup

ERP PO Summary

MERGE (CRUD)

Oracle Analytic Functions

First/Last    Row_Number, Rank,..    Rownum, Rank,..    TopN Query    FIRST_Value

LAG    LEAD    Non-Linear    Sequence Gap    Date Overlap

Calendar Summary    Year Based    GL Calendar

Business Day - Current Month    N-th Day

Dynamic Annual Calendar    Compute Holidays    Last Monday

Ratio_To_Report

Data grouping using Count(*) Over (PARTITION BY ...)

Oracle Analytical Functions    Dense_Rank

Cummulative Totals - Sequential    Null Value    All Data    Yearly

Count(*) Over()   Comma Separated String   Sum() Over()   Compute Percent

Annual Sales    YTD-1    YTD-2    Year Over Year Analysis    Year Over Year - DW    Duplicates    Delete Duplicates

Rolling Average - 3Month

Oracle SQL Model Clause

Iterations    Factorial    Cummulative Sum    Compute ROI

Hemachandra/Fibonacci Sequence

Sales Analysis    Pivot Query

Sum() Over()    Upsert Sequential..    Current Value (CV)

Oracle - Java Development

Oracle - Java    Data Management    Execute Host Command

Oracle - XML Features

XML Functions

XMLElement    XMLElement With Namespace    XMLElement User-Defined Data-Type

XMLConcat    XMLForest    SYS_XMLGen    DBMS_XMLGen 1    DBMS_XMLGen 2

XMLTable (Query XML)    XMLTransform    XMLAGG    XMLDOM

DBMS_Metadata.Get_XML

XMLType Table    XMLSerialize

extractValue    getClobVal    getStringVal    XMLSequence    Split String

XMLTable - Split String

Oracle - PL/SQL

Recursive Call    Autonomous Transactions    Bind Variable    Dynamic SQL    Execute Immediate    Pivot Query/Report

Reverse LOOP   VARRAY   ROWTYPE   Bubble Sort

Associative Array

Auto Numbering    Data Audit    Block Delete

Conditional Compilation    Exception Handling    Error Handling

Encryption, Decryption    Data Reseting

DBMS_RANDOM - Numeric Array    String Generation    Prime Numbers    Evaluate String

Random Seed

Oracle - PL/SQL Nested Table

Multiset - Union    Distinct    Except    Intersect

ANSI Set Operations    Iteration    Sorting    Trim    Delete

Oracle - PL/SQL Advance Features For ETL

Bulk Collect   EXISTS   Forall .. Indices Of   Bulk Collect - LIMIT

Data Output by REF CURSOR   Function With Sys_Refcursor Input

Table Function - Factorials    Prime Numbers    Pipelined and Parallel Table Function

Complex Datatypes

DBMS_LOB   Append   WriteAppend BLOB   Insert BLOB Data   BFILE

WriteAppend CLOB   BLOB To CLOB   CLOB To Varchar   Read-Write BLOB

LONG to Varchar2

SYS.ANYDATA

PL/SQL Performance Analysis and Tuning

DBMS_PROFILER   DBMS_SHARED_POOL

Oracle - PL/SQL Utility Features

DBMS_LOCK   UTL_FILE   UTL_MAIL   UTL_SMTP   Globalization   PL/SQL Wrap   Comma_To_Table   Error Logging

UTL_HTTP - Request   Request_Pieces

Security - SQL Injection Prevention

Oracle - Data Validation

REGEXP   - LIKE   - REPLACE   - SUBSTR   - combinations   - strings, numbers

Oracle - Statistical Functions

Correlation   Covariance   Regression   Standard Deviation   Variance

Oracle - Miscellaneous Functions

BitAND   Bin_To_Num   CAST/MULTISET   Width_Bucket   CEIL, FLOOR, MOD   WM_Concat

CASE   CASE and DECODE   NVL2

Execute SQL Script

Oracle - Miscellaneous PL/SQL

Unused Variables    String Output    isNumeric    Print Refcursor OP

Web Technologies:

Dependent Drop Down

1. Data Warehouse    ETL Process    Master Data Management    ACID    Slowly Changing Dimension    Operational Data Store

2. Storage - Backup Strategies    RAID    Storage Area Network

3. Some Useful Linux/Unix - Commands

General Technology Reference Information Links:

1. Data Life Cycle Management

2. Data (memory) - Designation

3. Java Technology - Overview