OraExplorer

Mar 07 2010

Book Review: Oracle SQL Developer 2.1 by Sue Harper

Category: APEX, Books, SQL Developer, Tool — ittichai @ 6:31 pm

I must admit that I rarely use the Oracle SQL Developer because I’m using SQL*Plus, Toad from Quest and PL/SQL Developer from Allround Automations as primary tools for database administration and PL/SQL programming. They’re already providing all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it, but I always find lame excuses not to. One of the excuses is that I thought this is just another new tool doing the same things other tools already could do. We’ll find out at the end of this post that I was wrong. Please read on.

Few weeks ago, I’ve received a non-compensated book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this opportunity to explore this tool by reading through this book. No more procrastination! I’m approaching this book as an eager learner who would like to know more the tool and plan for a long-term use.

Let’s get started.

This book is very well organized introducing Oracle SQL Developer from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concepts.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on the installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to connect and make queries to database. It ends with walking you through the navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e.g., data highlighting or filtering are interesting. It goes on explaining some of Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with demonstrating how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in detail with tool’s main interface, SQL Worksheet. It starts with comparing Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features such as drag-and-drop query builder, formatting code, code completion, etc., though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But if you look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries thus saving your time. Samples are as follow:

Table with Unindexes Foreign Keys (under Quality Assurance)
Search Source Code (under Data Dictionary Reports > PLSQL)
ASH and AWR Reports (under Data Dictionary Reports)
Grants and Privileges (under Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL code. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working with other tools before. But the book explains this topic very well for those you’ve not experienced working with them before. The feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very cool. The chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard database tuning tools such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler feature which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files
This is one of strengths of Oracle SQL Developer over others because it integrates with open source version control system. I’m really glad that author covers this topic in detail. The version control integration is very nice to have if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work in team development with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer can perform data export and import in various formats including CSV, Fixed, HTML, DML statements, and SQL Loader. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered to be topic-specific. This means that you can choose to read only what interests you.

Chapter 9 - Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because Oracle SQL Developer can also connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendously beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into detail of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler within SQL Developer.

(More information about Oracle SQL Developer Data Modeler can be found on its site.)

Chapter 11 – Extending SQL Developer
Personally I think this feature sets this tool apart from others. With capability of supporting XML and Java extensions, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 - Working with Application Express
As an APEX developer, the integration with APEX in Oracle SQL developer is a hidden gem. Now all APEX objects can be easily viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains clearly on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning application’s SQL and PL/SQL codes which are visible through the tool becomes much easier. One of the nice features is an ease of refracting PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing us how to manage applications including importing and exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of this tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for data migration. This includes migration repository preparation and database connection.

Summary
After reading through this book and following most of samples, I’m impressed with varieties of unique features (such as integration with APEX, version control, Data Modeler, etc.) this tool offers. As a matter of fact, I’m already using it in our current APEX development project, and plan to extend its usage more in other areas. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. The book covers some basic concepts filling up the gap for those who are new to accessing Oracle database. But in general, I agree with author that those who are already familiar with Oracle database concepts will get more out of this book.

Thanks to Packt Publishing for book review offer.

References:

Oracle SQL Developer 2.1 by Sue Harper on Packt Publishing site

Oracle SQL Developer page on Oracle site

I must admit that I rarely use the Oracle SQL Developer because the primary tools – Quest Toad and Automation PL/SQL Developer, I’m using for database administration and PL/SQL programming seem to provide all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it out. But I always find lame excuses not to start.

A month ago, I’ve received a book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this great opportunity to explore this tool by reading through this book. I’m approaching this book as an eager learner who would like to know the tool better for a long-term use.

Let’s get started.

This book is very well organized introducing the tool from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concept.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to make the connection and making queries to database. It ends with walking you through navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e,g, data highlighting or filtering are interesting. It goes on explaining some Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with showing how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in details with tool’s main interface, SQL Worksheet. It starts with compare Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features, for example, drag-and-drop query builder, formatting code, code completion, etc. even though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But you can look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries, thus saving your time. Sample are as follow:

Table with Unindexes Foreign Keys (Quality Assurance)

Search Source Code (from Data Dictionary Reports > PLSQL)

ASH and AWR Reports (from Data Dictionary Reports)

Grants and Privileges (from Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL codes. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working in other tools before. But the book explains well for those you’ve not experienced working with those objects before. Feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very interesting. This chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard tuning tool such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files

This is one of strengths of the tools over others because it integrates with open source version control system. I’m really glad the author covers this in details. This version control integration is very nice if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work as a team with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer provides many ways to perform data export and import in a various formats including CSV, Fixed, HTML, DML statements, SQL Loader, etc. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered topic-specific. This means that you can choose to read only what interests you.

Chapter 9 - Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because it also can connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendous beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into details of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler with SQL Developer.

(more information about SQL Developer Data Modeler at http://www.oracle.com/technology/software/products/sql/datamodeler.html)

Chapter 11 – Extending SQL Developer
Personally I feel this feature might set this tool apart from others. With capability of supporting XML and Java extension, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 - Working with Application Express
As a APEX developer, the integration with APEX in SQL developer is a gem. Now all APEX objects can be viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains well on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning SQL and PL/SQ which are visible through the tool becomes a lot easier. One of the nice features is an ease to refract PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing to manage applications including importing/exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of the tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for migration including migration repository preparation and database connection.

After reading through this book and following most of samples, I’m impressed with varieties of features (APEX integration, version control, Data Modeler, etc.) this tool offers. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. Even though some basic concept sections are not brief, those who are new to accessing Oracle database will benefit from those sections. In general, I agree with author that you will get more out of this book if you’re already familiar with Oracle database concepts.

Detail about Oracle SQL Developer 2.1 by Sue Harper

http://www.packtpub.com/oracle-sql-developer-2-1/book

Oracle SQL Developer page on Oracle OTN site http://www.oracle.com/technology/software/products/sql/index.html

Thanks to Packt Publishing for book review offer.

www.PacktPub.com

===========================================================

http://www.oracleapplicationexpress.com/tutorials/65-jquery-datepicker

I remembered not quite a long time ago, in order to find out any technical information, it seems like you can to go through a bunch of technical books.

You must subscribe to

But now many technical blog sites pop

This is to programmatically generate a CSV file when a button is clicked
The custom export to CSV

http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html

Session timeout in release 3.1

http://andrew.tulley.co.uk/?p=50

jQuery Calendar Date Picker

http://jqueryui.com/demos/datepicker/

Good document on its configuration

http://docs.jquery.com/UI/Datepicker

How to replace the default APEX calendar with jQuery calendar…

http://roelhartman.blogspot.com/2008/12/how-to-replace-default-apex-calendar.html

jQuery Masked Input
This is a masked input plugin for the jQuery javascript library. It allows a user to more easily enter fixed width input where you would like them to enter the data in a certain format (dates,phone numbers, etc). I

http://digitalbush.com/projects/masked-input-plugin/

jQuery Impromptu is an extention to help provide a more pleasant way to spontaneously prompt a user for input. More or less this is a great replacement for an alert, prompt, and confirm. Not only does it replace these but it also allows for creating forms within these controls. This is not intended to be a modal replacement, just a quick tool to prompt user input in a fashionable way.

jQuery Impromptu

http://trentrichardson.com/Impromptu/index.php

—————————-

Oracle Universal Installer (OUI) did not start when installing OAS 10.1.3.1 on Windows 2003

Last week we installed the OAS 10.1.3.1 on multiple Windows 2003 servers on the VM farm. All went well except the last one. When double-clicking on the setup.exe file, the system verification window popped up. Next I expected the Oracle Universal Installer (OUI) window, but nothing happened. The same installation files and procedure have been used on different Windows systems and have worked fine before. The installation log doesn’t seem to be much help either. We’ve tried all tricks including using a local administrative account, rebooting, etc., but none worked.

After looking through the Oracle support site (using Internet Explorer because the some contents don’t display properly on Fire Fox 3.6), I found this solution in the document ID 308705.1 – OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 which says that the root cause is because the Application Experience Lookup” Service is not started. Once started, the installation proceeds without any issues. The strange thing is that those servers we’ve previously installed it successfully did not have this service up either.

(

OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 [ID 308705.1]

	Modified 04-JUN-2009 Type PROBLEM Status PUBLISHED

In this Document
Symptoms
Cause
Solution

Applies to:

Oracle Application Server 10g Enterprise Edition – Version: 9.0.4.0.0 to 10.1.2.3.0
Microsoft Windows Server 2003
Microsoft Windows Server 2003 R2 (32-bit)
Checked for relevance on 04-Jun-2009

Symptoms

When attempting to install Oracle Application Server 10g on Windows 2003, the setup.exe appears to quit even before launching the Oracle Universal Installer (OUI).

Cause

The Oracle Universal Installer (OUI) is not able to start because the “Application Experience Lookup” Service is not started.

Solution

Start the “Application Experience Lookup Service” and then relaunch the Oracle Universal Installer (OUI)

<< OLE Object: Picture (Device Independent Bitmap) >> Related

Products

· Middleware > Application Servers > Oracle Application Server > Oracle Fusion Middleware

Keywords

OAS; ORACLE UNIVERSAL INSTALLER; SETUP.EXE; OUI

Tags: Book, developer, oracle, Review, sql, SQL Developer

Comments (0)

Feb 21 2010

Use PHP5 to Solve the WordPress Automatic Upgrade Failure for 1and1

Category: WordPress — ittichai @ 6:46 pm

I’ve been having issue with WordPress automatic upgrade for quite awhile. It never make any progress after download completes. This upgrade issue applies to both WordPress itself and its plug-ins. But at that time I’ve never bothered trying to find a solution for it. My WordPress version 2.6 was so far behind.

After searching on internet, the issue seems to relate to the PHP version. The service provider I using, 1and1, provides both PHP4 and PHP5. And WordPress seems to be confused when both are present. With a quick modification in the .htaccess file as shown below to make it use PHP5, the automatic upgrade for both WordPress and plug-ins are working again. Now I’m on the latest version.

AddType x-mapp-php5 .php AddHandler x-mapp-php5 .php

Reference: http://paheli.net/blog/2009/07/22/solved-wordpress-automatic-upgrade-problem/

Tags: .Automatica, PHP, PHP4, PHP5, Upgrade, WordPress, WP

Comments (0)

Feb 20 2010

Oracle 11g Network Access Denied by Access Control List (ACL) when using UTL_INADDR

Category: 11g, Database, Network — ittichai @ 12:10 pm

I wrote in my previous post about the Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g. However, it did not cover another PL/SQL network utility package named UTL_INADDR which retrieves host names and IP addresses of local and remote hosts.

You can read some usage samples of the UTL_INADDR from Eddie Awad’s blog.

Similar to those UTL_ packages, in 11g, you will be required to configure the access control list in order to use the UTL_INADDR. Otherwise, by default, you will receive errors as follows:

TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual;
SELECT utl_inaddr.get_host_name FROM dual
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

Two simple steps to configure are:

1. Create an access control list and its privilege definition.

SQL> connect / as sysdba

begin
dbms_network_acl_admin.create_acl (
acl             => 'Resolve_Access.xml',      -- Name of the access control list XML file
description     => 'Resolve Network Access using UTL_INADDR',  -- Brief description
principal       => 'TEST_USER',               -- First user account or role being granted or denied permission
                                              --   this is case sensitive,
                                              --   but typically user names and roles are stored in upper-case letters
is_grant        => TRUE,                      -- TRUE = granted, FALSE = denied
privilege       => 'resolve',                 -- connect or resolve, this setting is case sensitive,
                                              --   so always enter it in lowercase
                                              --    connect if user uses the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL
                                              --    resolve if user uses the UTL_INADDR
start_date      => null,                      -- optional, null is the default
                                              --   in format of timestamp_with_time_zone (YYYY-MM-DD HH:MI:SS.FF TZR)
                                              --   for example, '2008-02-28 06:30:00.00 US/Pacific'
end_date        => null                       -- optional, null is the default
);

commit;
end;
/

Note that the privilege used for UTL_INADDR is resolve in lowecase.

You can add more users or roles using DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.

To verify a newly-created ACL.

SQL> SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/Resolve%.xml';

ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/Resolve_Access.xml

2. Assign the the access control list to one or more network hosts.

begin
dbms_network_acl_admin.assign_acl (
acl           => 'Resolve_Access.xml', -- Name of the access control list XML file to be modified
host          => '*',                   -- Network host to which this access control list will be assigned
                                        -- This a host name or IP address or wild card name
lower_port    => null,                  -- (optional)
upper_port    => null);                 -- (optional)

commit;
end;
/

TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual;

GET_HOST_NAME
--------------------------------------------------------------------------------
hostname1

Reference: Oracle document on Managing Fine-Grained Access to External Network Services

Tags: 11g, ACL, Network Service, Utility, UTL_HTTP, UTL_INADDR, UTL_SMTP, UTL_TCP

Comments (0)

Feb 06 2010

Oracle Universal Installer (OUI) did not start when installing OAS 10.1.3.1 on Windows 2003

Category: Installation — ittichai @ 7:53 pm

After looking through the Oracle support site (using Internet Explorer because the some contents don’t display properly on Fire Fox 3.6 ), I found this solution in the document ID 308705.1: OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003, which says that the root cause is because the Application Experience Lookup Service is not started. Once started, the installation proceeds without any issues. The strange thing is that those servers we’ve previously installed it successfully did not have this service up either.

Tags: Installation, OAS, OUI, Windows

Comments (0)

Nov 12 2009

11.2.0.1 on Solaris is now available

Category: 11g, Database — ittichai @ 10:35 am

Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris (SPARC) (64-bit) platform is now available for download.

11gr2 solaris

Tags: 11gR2, download, solaris

Comments (0)

Nov 10 2009

11gR2 New Feature – Alter Database Link to Change Password

Category: 11g, Database — ittichai @ 7:26 pm

Our organization requires a regular password change on some database accounts for security compliance. If this account is used in the database link in other database, that database link has to be dropped and recreated with an updated password.

This changes in 11gR2 because it now offers the alter database link to change password. No more drop and recreate database link!

Sample here is on the database where database link is located:

The password of the database link’s account has just been changed.

db11gr2 SQL> select count(*) from tb_test@DL_TEST;
select count(*) from tb_test@DL_TEST
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DL_TEST

db11gr2 SQL> alter database link DL_TEST connect to dblink_test identified by dblink_test;

Database link altered.

db11gr2 SQL> select count(1) from tb_test@DL_TEST;

COUNT(1)
----------
6304

This option is not available in the pre-11gR2.

db11gr1 SQL > alter database link DL_TEST connect to dblink_test identified by dblink_test;
alter database link DL_TEST connect to dblink_test identified by dblink_test
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

Tags: 11g, 11gR2, Database Link, Password

Comments (2)

Oct 22 2009

Oracle Application Express (APEX) Certification Exam

Category: APEX — ittichai @ 7:24 am

Oracle is working toward having the first certification exam for Oracle APEX. This “Oracle Application Express Developer Certified Expert” pilot program is intended for intermediate and advanced APEX developers. You will have to indicate the relevant APEX trainings you’ve completed, years of APEX experiences and, of course, the agreement to provide feedback in order to participate. You can see details about this from Joel Kallman’s blog.

I think this is a good thing for APEX community because it shows Oracle’s commitment to this tool.

BTW, I’m very curious about how the tests will be conducted. Personally I think there should be more of actual development hands-on works rather than doing multiple choices.

Tags: APEX, Certification, Exam, Pilot

Comments (1)

Oct 19 2009

11gR2 New Feature – File Watchers

Category: 11g, Database — ittichai @ 9:19 pm

The File Watcher is a scheduler object that starts a job whenever files whose attributes met the defined criteria arrived on a system. These criteria include the name, location, and other properties of a file. When the file watcher detects the arrival of the designated file, it raises a file-arrival event. The event message, which has all information on the newly-arrived file, can then be used to process the file.

This new feature simplifies the configurations of the most common triggering event in the data load/batch processing which is to detect the arrival of files.

File Watcher configuration

Setup a new database account to manage the file watcher.

SQL> create user watcher_user identified by watcher_pwd
quota unlimited on users;
User created

SQL> grant connect to watcher_user;
Grant succeeded.

SQL> grant EXECUTE on SYS.SCHEDULER_FILEWATCHER_RESULT to watcher_user;
Grant succeeded.

Other grants needed to complete the tests:

grant create table, create procedure, create job to watcher_user;
grant execute on dbms_lock to watcher_user;
grant execute on dbms_system to watcher_user;
grant manage scheduler to watcher_user;

SQL> create or replace directory STAGING_DIR as '/home/oracle/staging';
Directory created.

SQL> grant read, write on directory staging_dir to watcher_user;
Grant succeeded.

Now as a new watcher_user, we will configure the File Watcher.

1. Create a credential using the OS privilege for file access.

begin
  dbms_scheduler.create_credential(
  credential_name => 'watch_credential',
  username => 'oracle',
  password => 'oracle');
end;
/

2. Create a table to store data processed from file.

create table t_staging_files(
  upload_timestamp  timestamp,
  file_name         varchar2(100),
  file_size         number,
  contents          clob
);

3. The procedure will process file data and put into a database table.

create or replace procedure process_files
(payload IN sys.scheduler_filewatcher_result)
is
  l_clob clob;
  l_bfile bfile;

  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  src_csid     NUMBER  := NLS_CHARSET_ID ('AL32UTF8');
  lang_context INTEGER := dbms_lob.default_lang_ctx;
  warning      INTEGER;
begin
  insert into t_staging_files (
    upload_timestamp , file_name, file_size, contents)
  values(
    payload.file_timestamp,
    payload.directory_path || '/' || payload.actual_file_name,
    payload.file_size,
    empty_clob()
  ) returning contents into l_clob;

  l_bfile := bfilename('STAGING_DIR', payload.actual_file_name);
  dbms_lob.fileopen(l_bfile);
  dbms_lob.loadclobfromfile (
    l_clob,
    l_bfile,
    dbms_lob.getlength(l_bfile),
    dest_offset,
    src_offset,
    src_csid,
    lang_context,
    warning
  );
  dbms_lob.fileclose(l_bfile);
end;
/

4. Create a Program object with a Metadata argument.

begin
  dbms_scheduler.create_program (
    program_name        => 'file_watcher',
    program_type        => 'stored_procedure',
    program_action      => 'process_files',
    number_of_arguments => 1,
    enabled             => false);

  dbms_scheduler.define_metadata_argument (
    program_name        => 'file_watcher',
    metadata_attribute  => 'event_message',
    argument_position   => 1);

  dbms_scheduler.enable('file_watcher');

end;
/
PL/SQL procedure successfully completed.

5. Create a File Watcher

begin
  dbms_scheduler.create_file_watcher(
    file_watcher_name => 'my_file_watcher',
    directory_path    => '/home/oracle/staging',
    file_name         => '*',
    credential_name   => 'watch_credential',
    destination       => null,
    enabled           => false);
end;
/
PL/SQL procedure successfully completed.

6. Create an Event-Based Job that references the File Watcher.

begin
  dbms_scheduler.create_job(
    job_name        => 'staging_file_job',
    program_name    => 'file_watcher',
    event_condition => 'tab.user_data.file_size > 10',
    queue_spec      => 'my_file_watcher',
    auto_drop       => false,
    enabled         => false);

    dbms_scheduler.set_attribute('staging_file_job','parallel_instances',true);
end;
/

7. Enable all objects

begin
  dbms_scheduler.enable('my_file_watcher,staging_file_job');
end;
/

8. Perform validation

$ echo "Hello World Hello World" > /home/oracle/staging/test_file.txt

After waiting for about 10-15 minutes,

col UPLOAD_TIMESTAMP format a20
col FILE_NAME format a20
col CONTENTS format a20

select * from t_staging_files;

UPLOAD_TIMESTAMP     FILE_NAME             FILE_SIZE CONTENTS
-------------------- -------------------- ---------- -----------------------

13-OCT-09 01.42.04.0 /home/oracle/staging         23 Hello World Hello World
00000 PM             /test_file.txt

By default, the file watcher checks for the arrival of files every 10 minutes. You can adjust this interval as follows:

as SYS user

begin
 DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL','FREQ=MINUTELY;INTERVAL=2');
end;
/

You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.

col FILE_WATCHER_NAME format a20
col DIRECTORY_PATH format a20
col FILE_NAME format a5
col CREDENTIAL_NAME format a17

SELECT file_watcher_name, directory_path, file_name, credential_name
FROM dba_scheduler_file_watchers;

FILE_WATCHER_NAME    DIRECTORY_PATH       FILE_ CREDENTIAL_NAME
-------------------- -------------------- ----- -----------------
MY_FILE_WATCHER      /home/oracle/staging *     WATCH_CREDENTIAL

References:

Oracle 11gR2 document: Starting a Job When a File Arrives on a System

Starting a Job When a File Arrives on a System

Tags: 11g, 11gR2, Batch, File, New Feature, Watcher

Comments (5)

Oct 18 2009

11gR2 New Feature – DBFS Database File System

Category: 11g, Database — ittichai @ 8:15 pm

The DBFS (Database File System), one of the new 11gR2 features, takes advantage of the SecureFiles feature (which is new in 11gR1). The SecureFiles provide powerful file storage features (including de-duplication, compression, etc.) which removes performance barrier to storing files in the database. This stands in contrast to LOB (now called the BasicFiles). Not only does the DBFS provide the standard file system interface (path names, directories and links) to store and access files in the database, its benefits also include security and performance from using SecureFiles.

DBFS configuration

1. Follow the DBFS prerequisites and installation instructions here.

2. Install the FUSE package.

Since oracle user will do sudo, add it into /etc/sudoers.

$ tar -xzvf fuse-2.7.3.tar.gz
$ cd fuse-2.7.3
$ ./configure --prefix=/usr/src/kernels/`uname -r`-`uname -p`
$ make
$ sudo su
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules

3. Create a database user and a tablespace to store data.

SQL> create user dbfs_admin identified by dbfs_admin;

SQL> grant create session, resource, create view to dbfs_admin;

SQL> grant dbfs_role to dbfs_admin;

SQL> create tablespace tbs_dbfs datafile '/u1/oradata/DB11LNX/dbfs.dbs' size 100m;

The tablespace must be the Automatic Segment Space Management (ASSM) in order to use the SecureFiles. This is the default setting in 11g.

3. Create a file system using dbfs_create_filesystem.sql (located at $ORACLE_HOME/rdbms/admin).

The dbfs_create_filesystem.sql creates a partitioned file system which stores data in the multiple physical segments. The files will be distributed randomly in these partitions. This way it gives the best performance and scalability.

$ cd $ORACLE_HOME/rdbms/admin

SQL> conn dbfs_admin/dbfs_admin

SQL> @dbfs_create_filesystem.sql tbs_dbfs staging_area

Note that the last argument (e.g., staging_area) will be visible as the name of the file system.

When creating a new file system, a new partitioned table will be created having its name from the file system’s name with T_ prefix.

SQL> select table_name, partition_name from user_tab_partitions
where table_name like '%STAGING_AREA';

TABLE_NAME                    PARTITION_NAME
----------------------------- ------------------------------
T_STAGING_AREA                SYS_P141
T_STAGING_AREA                SYS_P142
T_STAGING_AREA                SYS_P143
T_STAGING_AREA                SYS_P144
T_STAGING_AREA                SYS_P145
T_STAGING_AREA                SYS_P146
T_STAGING_AREA                SYS_P147
T_STAGING_AREA                SYS_P148
T_STAGING_AREA                SYS_P149
T_STAGING_AREA                SYS_P150
T_STAGING_AREA                SYS_P151
T_STAGING_AREA                SYS_P152
T_STAGING_AREA                SYS_P153
T_STAGING_AREA                SYS_P154
T_STAGING_AREA                SYS_P155
T_STAGING_AREA                SYS_P156

16 rows selected.

4. Verify by copying files into the exposed file system.

SQL> conn dbfs_admin/dbfs_admin
SQL> !cat ~/dbfs_show_content.sql
col pathname format a40
col pathtype format a10
col contents format a20

select pathname, pathtype,
utl_raw.cast_to_varchar2(filedata) as contents
from dbfs_content
order by std_creation_time;

This shows the default directories currently in database.

SQL> @~/dbfs_show_content.sql

PATHNAME                       PATHTYPE   CONTENTS
------------------------------ ---------- ------------------------------
/staging_area                  directory
/staging_area/.sfs             directory
/staging_area/.sfs/RECYCLE     directory
/staging_area/.sfs/attributes  directory
/staging_area/.sfs/content     directory
/staging_area/.sfs/snapshots   directory
/staging_area/.sfs/tools       directory

7 rows selected.

Let’s create a directory named test_dir. Please note that base directory name staing_area is from the file system created previously. The dbfs_client can be executed from any systems which meet the prerequisite requirements mentioned in the step 1.

{client}$ dbfs_client dbfs_admin@DB11LNX --command mkdir dbfs:/staging_area/test_dir
Password: dbfs_admin

A new directory test_dir is now visible as a new record.

SQL> @~/dbfs_show_content.sql

PATHNAME                       PATHTYPE   CONTENTS
------------------------------ ---------- ------------------------------
/staging_area                  directory
/staging_area/.sfs             directory
/staging_area/.sfs/RECYCLE     directory
/staging_area/.sfs/attributes  directory
/staging_area/.sfs/content     directory
/staging_area/.sfs/snapshots   directory
/staging_area/.sfs/tools       directory
/staging_area/test_dir         directory

8 rows selected.

Copy a file into it.

{client}$ echo "hello world" > /tmp/dbfs_file
{client}$ dbfs_client dbfs_admin@DB11LNX --command cp /tmp/dbfs_file dbfs:/staging_area/test_dir
Password: dbfs_admin
/tmp/dbfs_file -> dbfs:/staging_area/test_dir/dbfs_file

SQL> @~/dbfs_show_content.sql
PATHNAME                                 PATHTYPE   CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area                            directory
/staging_area/.sfs                       directory
/staging_area/.sfs/attributes            directory
/staging_area/.sfs/tools                 directory
/staging_area/.sfs/snapshots             directory
/staging_area/.sfs/RECYCLE               directory
/staging_area/.sfs/content               directory
/staging_area/test_dir                   directory
/staging_area/test_dir/dbfs_file         file       hello world

9 rows selected.

Optionally you can also mount this file system on the client, so the file operations can be done without invoking dbfs_client every time.

Create a mount point. (*)

{client}# mkdir /mnt/dbfs
{client}# chown oracle:dba /mnt/dbfs
{client}# chmod 755 /mnt/dbfs

$ dbfs_client dbfs_admin@DB11LNX /mnt/dbfs
password: dbfs_admin
:

Somehow on my test system, the prompt never returned even though the file system is mounted successfully. So in order to test it, I left this window open, and execute the remaining of commands in a new window.

The prompt will never return using above syntax. However, the following example will read the password from a file, mount a file system, and then free the terminal. (Thanks Simon for pointing this out.)

$ nohup dbfs_client dfs_admin@DB11LNX /mnt/dbfs  < passwordfile.f &

Note that now I can perform all standard Unix file/directory syntaxes to this mount point.

{client}$ echo "hello world 2" > /tmp/dbfs_file2
{client}$ cp /tmp/dbfs_file2 /mnt/dbfs/staging_area/test_dir

SQL> @~/dbfs_show_content.sql
PATHNAME                                 PATHTYPE   CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area                            directory
/staging_area/.sfs                       directory
/staging_area/.sfs/attributes            directory
/staging_area/.sfs/tools                 directory
/staging_area/.sfs/snapshots             directory
/staging_area/.sfs/RECYCLE               directory
/staging_area/.sfs/content               directory
/staging_area/test_dir                   directory
/staging_area/test_dir/dbfs_file         file       hello world
/staging_area/test_dir/dbfs_file2        file       hello world2

10 rows selected.

There are a lot of administrative options you can do from here including using Oracle wallet so no password will be prompted when mounting a DBFS store. You can also mount DBFS through the fstab. See instructions in the Oracle 11gR2’s DBFS File System Client.

One of the claimed benefits of using DBFS is I/O throughput performance in a range of 5-7 GB/sec. I plan to perform performance tests and report the results in the next post. Stay tune!.

(*) Initially, I received this error when trying to run dbfs_client.

dbfs_client: error while loading shared libraries: libfuse.so.2: cannot open shared object file: No such file or directory

By creating a softlink to the /lib folder, it’s solved the problem.

# ln -s /usr/src/kernels/2.6.18-92.el5-i686/lib/libfuse.so.2 /lib/libfuse.so.2

Tags: 11gR2, Database File System, DBFS, New Feature

Comments (3)

Oct 17 2009

OOW09 Summary and Rookie Mistake

Category: Events, OOW — ittichai @ 10:12 am

It is good to be back home. The OOW09 trip was exceedingly beyond expectation especially since this was my first OOW. In general, the technical sessions were very good. Most of the time, however, their contents were very basic, I suppose, to accommodate a large audience of diverse skill levels and interests. Besides networking with colleagues and vendors, I would say that the informal settings of the Unconference, and interactions with product managers and developers of DEMOgrounds are what I enjoyed the most about the conference. I made a rookie mistake of registering too many sessions with only short breaks between them. Because of that, at the end of each day, I felt very exhausted. I will definitely plan better for the next one.

Here are a couple of highlights from the conference:

One of the pictures I took at the event was used in the Oracle OpenWorld Blog’s Wrapping Things Up.

Attendees walking to the keynote

SF is decidedly an iPhone town.

In almost every conference I’ve been to, there are always groups of people dressing weird and shouting out something no one really understands or cares about. (You have to ask them in person if you want to know what they really want.) This one is no exception. I still have no idea what this group was doing on the street in front of the Moscone West.

????

I’m a pen-addict. I’ve not realized this until I found, as I was packing and getting ready to return home, almost 40 pens collected during the visits to vendor exhibits throughout the week. It is my habit to grab pens when stopping by and talking to the vendors. Please help.

A long line attracts people even if they have no clue what it is for. I’ve encountered this a couple of times when people came behind me as I was standing in line. After 5 minutes of waiting, they would ask me, “What are we waiting for?”

Tags: OOW, OOW09, OOW2009, Oracle Open World

Comments (2)