Posted in Oracle

Oracle University Official Study Notes

1.   Oracle_10g_2_Day_DBA_for_windows

2.   Oracle_10g_Administration_I

3.   Oracle_10g_Administration_II

4.   Oracle_10g_Advanced_PLSQL

5.   Oracle_10g_AS_Administration_I_and_II

6.   Oracle_10g_Backup_and_Recovery

7.   Oracle_10g_Build_J2EE_Apps

8.   Oracle_10g_Dataguard

9.   Oracle_10g_Develop_PL-SQL_Program_Units

10. Oracle_10g_Enterprise_Manager_Grid_Control

11.  Oracle_10g_Forms_Developer_Build_Internet_Applications

12.  Oracle_10g_Implement_and_Administor_Data_Warehouse

13.  Oracle_10g_Java_Programming

14.  Oracle_10g_Managing_oracle_on_Linux

15.  Oracle_10g_Migrating_from_SQL_Server

16.  Oracle_10g_New_Features

17.  Oracle_10g_Performance_Tuning

18.  Oracle_10g_PL-SQL_Fundamentals

19.  Oracle_10g_Quick_Start_for_DBA_s

20. Oracle_10g_Rac_Basic_Concepts_and_Architecture

21.  Oracle_10g_Real_Application_Clusters

22.  Oracle_10g_Reports_Developer

23.  Oracle_10g_SQL_Fundamentals_I

24.  Oracle_10g_SQL_Fundamentals_II

25.  Oracle_10g_SQL_Tuning

26.  Oracle_10g_Streams

27.  Oracle_10g_Using_OLAP

28.  Oracle_10g_Warehouse_Builder

29.  Oracle_10g_Warehouse_implementation_I

30.  Oracle_10g_Warehouse_implementation_II

31.   Oracle_10g_Xml_Fundamentals

32.  Oracle_10g__Reports_Developer

33.  Oracle_11g_Administration_I

34.  Oracle_11g_Administration_II

35.  Oracle_11g_New_Features_for_Administrators

36.  Oracle_11g_PLSQL_Fundamentals

37.  Oracle_11g_RAC_Administration

38.  Oracle_11g_SQL_Fundamentals_II

39.  Oracle_11g_SQL_Fundaments_I

40.  Oracle_11g_Sql_Plsql_New_Features1.     Oracle_10g_2_Day_DBA_for_windows

Posted in Belajar, Oracle

Working with Flat File Data – Data Warehousing

Creating Source Module from flat files :

  • First create a flat file source module
    • Use Create Module Wizard
  • Create Flat file definitions by importing Metadata from the flat files
    • Use import metadata wizard to import flat files
    • Use flat file sample wizard to sample flat files

Source and Target Modules

  • Modules are storage objects that logically group source and target object definitions
  • Source modules are for data warehouse center or partner application to read data from.
  • Target modules are for the Data Warehouse center or partner application to write data to.

Defining a Target Warehouse Module

  • Create Target User, Target Module and Target schema
  • Creating external tables
  • Designing dimensions
  • Designing cubes

ETL (Extract, Transform, Load) Data Flow Mappings

Steps :

  • Examine an existing map
  • Use of mapping editor tool in warehouse builder
  • Design mapping between external table and dimensions
  • Design mapping between external table and cubes
  • And finally generate code for these mapping using mapping editor

Deploying Objects in Target Warehouse

Deployment : Final step in creating physical instance of the data warehouse target modules :

  • Deployment process generates scripts for creating data objects such as tables
  • Deployment process also generated scripts that load data into these objects

Use Control center Manager provided in Oracle Warehouse builder to

  • Deploy the external table
  • Deploy the cube and the referenced dimensions
  • Deploy and execute the ETL mappings

Check Metadata for Dependency

Inter Row Calculations using SQL model Clause

  • SQL model clause is a Business Intelligence tool
  • Query ouputs can be processed with inter row calculations
  • Reduces the need for importing database query outputs to spreadsheets to get business results
  • Increases performance, scalability and manageability of enterprise data warehousing applications

Filling Gaps in Sparse Data

  • Often a row may not have data for a particular column or dimension
    • Example : daily sales data with no sales on a particular day
  • This is known as sparse data
  • Difficult to run business analytics
  • Partitioned Outer join is an oracle feature that fills these gaps in sparse data
    • This is called densification of sparse data
  • This makes it easier to apply data warehousing applications to sparse data

High speed data loading adapted to data warehousing applications

  • Load flat flies as external tables
  • Loading in parallel is allowed
  • Faster than the corresponding SQL *Loader available in previous Oracle Versions

Rolling windows operations using oracle partition

  • Segregate past data using oracle partition
  • Use rolling window to
    • Add new data to new partition
    • Remove old partition
  • Reduces cost by
    • Improved disk utilization
    • Reduced impact on end user

Oracle ETL Infrastructure features

Oracle Application Express

Posted in Belajar, Oracle

Oracle Warehouse Builder

Oracle Warehouse builder

  • Enterprise business intelligence integration design tool.
  • Manages the full life-cycle of data and metadata for the Oracle database
  • Provides an easy to use, graphical environmetn to rapidly desgitn, deploy, and manage business intelligence systems.
  • Formerly called Oracle Business Ingelligence Warehouse Builder
Posted in Belajar, Oracle

Infomation Lifecycle Management Oracle

What is ILM ?

  • ILM is the process of managing data form its creation until the time it can be disposed of. It involves understanding :
    • How the data evolves
    • How it grows in size
    • How its usage changes over time, and
    • How long it is required to be kept
  • ILM is required in order to satisfy compliance with legal and regulatory frameworks


ILM in Oracle

Oracle is an ideal platform for ILM because it has :

  • Fine grained auditing
  • Enforceable compliance policies
  • Transparent data classification, and
  • Ability to store various kinds of data in one architectural framework


ILM Implementation and Management

  • ILM can be implemented using Oracle Database by following these steps :
    • Define the data classes
    • Create storage tiers for the data classes
    • Create data access and migration policies
    • Define and enforce compliance policies
  • Oracle ILM assistant – a GUI based tool for managing ILM environment



Posted in Belajar, Oracle

Exploring Cluster Database

What is a cluster database

  • Oracle RAC is a software option to Oracle database
  • Multiple servers running Oracle software simultaneously and still accessing a single database
  • RAC harnesses the power of multiple low-cost computers to server as a single large computer for database processing

RAC vs non RAC


  • Single database
  • Single instance on single server


  • Single database
  • Multiple servers running an instance in each

Ease of RAC operation

  • Cluster database home page provides a unified and simple way to view
    • All the targets in the clusters
    • All the instances of the database
    • The state of interconnects in the cluster
  • Cluster database instance home page displays
    • Current state of an instance