Posts Tagged ‘Oracle’

Oracle University Official Study Notes

January 26, 2011 Leave a comment

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

Categories: Oracle Tags: ,

Working with Flat File Data – Data Warehousing

January 21, 2011 Leave a comment

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

Categories: Belajar, Oracle Tags:

Oracle Warehouse Builder

January 21, 2011 Leave a comment

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
Categories: Belajar, Oracle Tags:

Infomation Lifecycle Management Oracle

January 21, 2011 Leave a comment

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



Categories: Belajar, Oracle Tags:

Exploring Cluster Database

January 21, 2011 Leave a comment

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


Categories: Belajar, Oracle Tags:

Virtual Private Database

January 21, 2011 Leave a comment

What is Virtual Private Database or VPD?

  • VPD provides row level access control
  • VPD enables access control with assurance of physical data control


Benefits of VPD

What are the benefits of VPD?

VPD works by:

  • Associating one or more security policies with tables of views
  • These policies enforce access condition which are appended to user’s SQL query statements
  • VPD can be implemented using stored procedures

Benefits of VPD

  • More robust security mechanism
  • Security enforced by the database
  • Lower cost of application development
  • Lower cost of application deployment


Categories: Belajar, Oracle Tags:

Fine Grained Auditing Oracle

January 21, 2011 Leave a comment

What is Fine Grained Auditing or FGA?

  • Standar auditing tools allow for observing and tracking activities within a database’s tables, sessions, and objects
  • Fine-Grained Auditing (FGA) capability in Oracle 10g allows implementation of auditing at extremely low level of granularity against any table in the database through implementation of FGA policy.

FGA – improvements over standar audit tools

  • Security audit is done using triggers to log updates to sensitive data
  • Fine-grained auditing is a superior tool for detecting security violations by tracking unauthorized access and changes within database tables and views.
  • Can track execution of SELECT statements for which triggers are not available
  • Can be used to check access to specific rows, column, or any other specific datasets
  • Significantly better and high performance compared to triggers
  • Can be used in combination with existing audit tools and trails
  • Can trigger an action such as sending email when an audit violation occurs
Categories: Belajar, Oracle Tags:

Get every new post delivered to your Inbox.