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:

Oracle Label Security

January 21, 2011 Leave a comment

Oracle Label Security – OLS

What is Oracle Label Security or OLS?

  • Offers a powerful implementation of row-based security for restricting user access to specific data
  • Useful especially in a data warehousing environment.
  • Makes use of and expands the scope of Virtual Private Database (VPD) technology

How does OLS work?

  • Security policies are established to identify how the data needs to be secured.
  • User labels are used to define row-level security policies
  • Access mediation process used to determine
    • Permissions required to access the row
    • Actions permitted on the row once accessed.
Categories: Belajar, Oracle Tags:

Transparent Data Encryption Oracle 10g

January 21, 2011 Leave a comment

Transparent Data Encryption

What is TDE?

  • A mechanism to encrypt sensitive information in the database.
  • Encryption is done by Oracle before writing to the disk.
  • Individual applications are not burdened with encryption if TDE facility is used.

What are the advantages of TDE?

  • Complexity of application program is reduced.
  • Applications can run as is without modification after encryption.
  • Encryption management can be done across the enterprise with common policy enforced by Oracle.

How to set up TDE?

  • Prepare the Database for encryption by editing the sqlnet.ora file to include an entry


  • Connect to the database as SYSDBA and create the master encryption key by executing the command

alter system set key identified by “username”

  • For later sessions the wallet needs to be open, This is done by executing the command

alter system set wallet open identified by “username”

What are the applications of TDE?

  • Creating Tables with encrypted columns
  • Creating Index on encrypted data
  • Making changes to the encrypted data
Categories: Belajar, Oracle Tags:

Recovery From Error Using Flashback

January 21, 2011 Leave a comment

Oracle memiliki fitur yang dinamakan Flashback recovery yang digunakan untuk mengembalikan table yang telah kita delete. Table yang telah kita delete oleh oracle disimpan didalam recycle bin untuk ditampung sementara sebelum dibersihkan.

  1. Saya membuat satu table di schema HR yang diberi nama TESTLAH yang disimpan didalam tablespace USERS. Lalu saya akan mendelete table tersebut dengan mengklik button Delete with options
  2. Tampilan sesudah button delete with options ditekan. Pilih delete yang paling atas, yang akan mendelete semua isi table.
  3. Pesan yang menunjukkan table telah berhasil didelete
  4. Table sudah terhapus dan selanjutnya klik button recycle  bin
  5. Pilih Schema dan tekan button Flashback drop untuk mengembalikan table yang telah didelete
  6. Show SQL
  7. Hasilnya
Categories: Belajar, Oracle Tags:

Get every new post delivered to your Inbox.