Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 26, 2007

Weekly mail magazine dedicated to Oracle users, E-Magazine

Filed under: Oracle Other — H.Tonguç Yılmaz @ 4:42 pm

Always documentation is priority one, but what I like with these kind of resources is that they support the beginners with simple examples and they are free accessible :)

http://www.insight-tec.com/en/mailmagazine/index.html

Also today with a question of one of my colleague I searched and learned about dbms_utility.get_dependency function, I love this package :)


CREATE TABLE testtab (testcol VARCHAR2(20));

CREATE VIEW testview AS SELECT * FROM testtab;

CREATE TRIGGER testtrig
BEFORE INSERT
ON testtab
BEGIN
  NULL;
END testtrig;
/

CREATE OR REPLACE PROCEDURE testproc IS
 i PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM testtab;

  dbms_output.put_line(TO_CHAR(i));
END testproc;
/

set serveroutput on
exec dbms_utility.get_dependency('TABLE', 'HR', 'TESTTAB');

DEPENDENCIES ON HR.TESTTAB
------------------------------------------------------------------
*TABLE HR.TESTTAB()
*   VIEW HR.TESTVIEW()
*   TRIGGER HR.TESTTRIG()
*   PROCEDURE HR.TESTPROC()

PL/SQL procedure successfully completed

And the last words are for this interesting presentation which I was warned about by one of my colleagues, Block Level Tuning by Rich Niemiec, I guess you will also enjoy it as much as I did - http://www.nyoug.org/Presentations/2005/20050929tuningoracle.pdf

What is the easiest(or best) way to export and deploy to another Oracle 10gR2 instance only the views,triggers,procedures,packages and functions?

Filed under: Oracle How To — H.Tonguç Yılmaz @ 12:30 pm

One of my friends, with nick name FENERBAHCE at OTN forums, asked on the easiest way to export and deploy to another Oracle only the views,triggers,procedures,packages and functions; http://forums.oracle.com/forums/thread.jspa?threadID=599890

His purpose is briefly to deploy a schema’s “create or replace” type of Oracle objects from development to test on 10g Release 2. And two options were discussed on the thread dbms_metadata and expdp/impdp with 10g. Below steps I tried and suggested for his purpose using expdp/impdp;


1- $ mkdir /tmp/dp

2- $ sqlplus dest_user/dest_passwd

SQL> create or replace directory ext_tab_dir as '/tmp/dp';

SQL> create database link dev_dblink connect to dev_user identified by dev_passwd using 'dev_tns' ;

SQL> select * from global_name@dev_dblink ;

3- $ expdp dest_user/dest_passwd NETWORK_LINK=dev_dblink DIRECTORY=ext_tab_dir EXCLUDE=USER,TABLESPACE_QUOTA,JAVA_SOURCE,JAVA_CLASS,TABLE,INDEX,SEQUENCE,TYPE,DB_LINK DUMPFILE=dev_export.dmp LOGFILE=dev_export.log

4- $ impdp dest_user/dest_passwd DIRECTORY=ext_tab_dir DUMPFILE=dev_export.dmp LOGFILE=test_import.log SQLFILE=ext_tab_dir:dev_export_ddl.sql

5- $ vi /tmp/dp/dev_export_ddl.sql

To find and replace correctly you may use this one in the sql ddl file produced by impdp;


:1,$s/CREATE PROCEDURE /CREATE or replace PROCEDURE /g
:1,$s/CREATE FORCE VIEW /CREATE or replace VIEW /g
:1,$s/CREATE FUNCTION /CREATE or replace FUNCTION /g
:1,$s/CREATE PACKAGE BODY /CREATE or replace PACKAGE BODY /g
:1,$s/CREATE PACKAGE /CREATE or replace PACKAGE /g

Since with expdp when you exclude table, triggers are also excluded below steps are also needed for just the triggers.


6- $ expdp dest_user/dest_passwd NETWORK_LINK=dev_dblink DIRECTORY=ext_tab_dir INCLUDE=TRIGGER DUMPFILE=dev_export_trigger.dmp LOGFILE=dev_export_trigger.log

7- $ impdp dest_user/dest_passwd DIRECTORY=ext_tab_dir DUMPFILE=dev_export_trigger.dmp LOGFILE=test_import_trigger.log SQLFILE=ext_tab_dir:dev_export_trigger_ddl.sql

8- vi /tmp/dp/dev_export_trigger_ddl.sql

:1,$s/CREATE TRIGGER /CREATE or replace TRIGGER /g

9- $ sqlplus dest_user/dest_passwd 

spool /tmp/dp/dev_export_ddl_sqlplus.log
@/tmp/dp/dev_export_ddl.sql
spool off

spool /tmp/dp/dev_export_trigger_ddl_sqlplus.log
@/tmp/dp/dev_export_trigger_ddl.sql
spool off

It is best to compile all invalid sources as a last step.


10- $ sqlplus dest_user/dest_passwd 

spool /tmp/dp/utlrp.log
@?/rdbms/admin/utlrp.sql
spool off

Some additional hints are;

a. to see briefly what object types are inside ddl file you can search in the with "/-- new object type path is" in vi editor,
b. to be careful with "&" signed comments inside your codes during running the ddl sql file, or search and replace them with ":1,$s/&/ and /g" in vi editor,

Finally as I already mentioned on the OTN thread I really suspect that this one is the easiest or best method to deploy all and only the sql and pl/sql based database objects, so I thought to blog about it to catch some comments and share them.

December 20, 2007

Apex vs. ADF study

Filed under: Oracle Express Edition(XE) and Application Express — H.Tonguç Yılmaz @ 9:23 pm

Apex
http://dgielis.shellprompt.net/AE_APEX-ADF.pdf

versus

ADF
http://www.amis.nl//files/technology/AMIS_QUERY_APEX_AND_ADF_17dec2007.pdf

and the winner is ..

there can be no “winner”, every each of us has our own tastes, for me I am totally in love with Apex and how easily it enabled a database developer to build web applications, it feels like the old cave man can fly to the moon now :)

For details of round 1 check Dimitri’s post.

December 19, 2007

Alejandro Vargas Oracle Maximum Availability Architecture Seminar Notes

Filed under: Oracle 10g New Features,Oracle Best Practices,Oracle Seminars — H.Tonguç Yılmaz @ 8:51 pm

Oracle Maximum Availability Architecture(MAA) is a set of high availability best practices which targets to minimize the complexity in designing the optimal high availability for critical production databases. I do not work as an infrastructure(operational) dba for years. Within last years especially with 10g Release 2 Oracle’s availability options evolved a lot. I try to catch up with these on my laptop, thanks to vmware, and I really admire how everything is as is now with Oracle.

When I think about the recent years I may easily give my job description like this; database developer, part-time fire-fighter and part-time myth-buster. I always loved to develop database applications, I am working with application developers as an old Oracle dba for the last 4 years. Mostly database modeling, physical design, Oracle’s optimizer and testing for performance is what I think I am useful. Also if there is an Apex project I never miss the chance, developing Apex applications is always fun for me.

I used myth-buster on purpose, because lots of my time is dedicated to exorcism of some evil believes which mostly depend on rumors or some n years aged wrong and/or undocumented test results; “never use Oracle xyz feature it is buggy” or “this optimizer you call in fact is an abuser, I never get what I need without hints” kind of very common behavior. Usually it feels like being Van Helsing; read the documentation which is available to anyone and easy to understand, not rocket science at the end, and kill the beast with a small test case which is most probably adapted from Asktom or the Oracle Users’ Co-operative FAQ kind of resource. In the beginning this is a delicious rant but after years it becomes boring, disappointing and dissatisfying. What is the rationality behind not reading and trying to understand the technology that you are using to make money, please advice me? These days everybody is after some quick and dirty solutions.

I used fire-fighter on purpose, because I am called for aid when something really goes crazy or unexpected on production after a long development and testing period. These cases are the worst ones since with my experience it is usually too late and changes needed will cause delays and angry customers. Still it is a fire and I do whatever I can, in most cases interestingly even this amount is enough. But this is Oracle’s success of course, there is always some options guide you directly to the root cause of the problem and then a workaround related. Like Apex I am also a fan of 10g Grid Control, I never miss the chance to use it when there is a performance problem especially.

During Mr.Alejandro Vargas three days seminar I strongly felt that I have to leave this mystique world of mine and return back to the world where checklists, metalink notes and documents rule. If you do not follow them you even do not get to the point where you ruin everything. How unbelievable it is that anybody can develop some piece of code, google and copy&paste are the major enabler technologies here. When it comes to Oracle database, how ironic also that it works very satisfactory with its defaults and let people think they are “developer”s or “dba”s. I believe even after 10g’s self management capabilities and Grid Control heaven, today database market need experienced people more than ever. Do you think anybody can implement RAC or Data Guard options without reading and testing carefully? And I seriously bet that if all Oracle users as a prerequisite only read the concept guide all around the world, if you consider Oracle’s dominant leadership in the market, we should easily expect a technological shift, of course my share should be less otn forums type of questions, fires and myths.

When I contacted Alejandro with the content of this MAA seminar my primary focus was to motivate our senior dba staff in using these features. We have unlimited kind of Oracle licensing, so avoiding these options we already purchased is both a threat and causing inefficiency for the company. Alejandro was the right person and luckily he was very interested with the content and as a result for the last three days was successful. But this was only the beginning, there is a big challenge waiting. Any expert who is motivated to implement these new MAA features with 10g Release 2 will be facing another very common behavior; “if it works never take a risk and change it” Of course managers need to avoid risks, but if this is the only priority you kill the creativity in your organization, how may a dba can take risk for the sake of performance of a system if you do not support him as his manager? The answer is simple, no dba would do that and you live with a release xx years old forever. So I plan the next step as to implement and document a MAA solution’s success, this will be a reference point I hope. This is my new priority and motivation for the coming months, so I really suspect that I will be ending up as being an operational Oracle dba again, but if something unexpected happens my b plan is already in pending status :)

I hope you enjoy my three days seminar notes below, much more is already published at Alejandro’s blog, he also may be publishing a post related to this seminar with our questions’ answers and his İstanbul experiences I guess.

10gR2 Automatic Storage Management(ASM)

10gR2 Real Application Clusters(RAC)

10gR2 Data Guard

10gR2 Recovery Manager(RMAN)

10gR2 Flashback

10gR2 Enterprise Manager Grid Control

December 11, 2007

Planning and Testing the 9iR2 to 10gR2 migration of our XXX Tb DWH

Filed under: Oracle Business Intelligence — H.Tonguç Yılmaz @ 9:52 pm

I hope following months will be my best days ever with Oracle, a lot of data waiting for a complete Oracle version and model change during our datawarehouse re-engineering project, it will be a very valuable experience for me.

I had a startup meeting today with the responsible dba team, we agreed on testing primarily;
- Automatic Storage Management(ASM),
- optimizer strategy; dbms_stats system statistics and dictionary/fixed table statistics collection,
- materialized views, bitmap indexes, table compression features and
- new backup/recovery strategy based on Flash Recovery Area solution

Please share your comments, questions and experiences on this tool or such a XXX Tb 10g Release 2 datawarehouse migration, I will be sharing mines during following months.

I am really lucky, great timing for Alejandro Vargas three days seminar on Oracle Database Maximum Availability Architecture(MAA) Best Practices next week. I will be sharing my seminar notes as usual :)

December 9, 2007

After UKOUG 2007 – A summary

Filed under: Oracle Events — H.Tonguç Yılmaz @ 11:45 am

After UKOUG, I left Birmingham for a small London trip. I met Coşkan there, chatting on working and living conditions in London really motivated me, I have the strong feeling that I should try this for some time. With New York, London was the nearest city which gave me this feeling compared to İstanbul. As Coşkan mentioned of 33 days holiday a year, no over time forcing, the quality of living in London and how much an experienced Oracle/Unix expert may be satisfied there, lots of my friends will sure also be interested I guess. One more advantage of London is that it is just 3,5 hours away from my family and friend. So I am back in my home town, and there is no place like home for me still ;)

Below you will find a brief summary of my best Oracle event experience up to now in my life, UKOUG 2007;

December 3, 2007, UKOUG DAY 1

  • Openning and Keynotes by Ian Smith and Tom Kyte
  • Oracle Spatial, An Introduction by Dominic Giles
  • Playing Russion Roulette with Silver Bullets by Jonathan Lewis
  • TimesTen: Anatomy of an In-Memory Database by Chris Jenkins
  • Partitioning from start to database 11g by Ian Abramson
  • Oracle 10g RAC Tuning Tips by Joel Goodman
  • Flashback Logging by Julian Dyke
  • 11g New Features for DBAs by Thomas Kyte
  • Oracle Bloggers Meetup

December 4, 2007, UKOUG DAY 2

  • What’s new with Oracle Data Guard in 11g by Larry Carpenter
  • Performance Metrics using Statspack and Analytical SQL by Daniel Fink
  • 11g new features for Developers by Thomas Kyte
  • Oracle Security Tools by Pete Finnigan
  • Understanding Statspack by Jonathan Lewis
  • Scalability with Oracle RAC for DWH Applications by Christian Antognini
  • Query Transformations by Joze Senegacnik

December 5, 2007, UKOUG DAY 3

  • DB Time Performance method by Graham Wood
  • Programming real applications with Application Express by Andrew Woodward
  • Every Performance Problem Is One Of Two Things by James Morle
  • Debugging APEX Applications by John Scott
  • Modelling on the cheap by Andrew Clarke
  • Real World Database Recovery – An Experience by James Hetherington
  • Understanding and Interpreting Deadlocks: What to do with an ORA-00060 by Mark Bobak
  • PL/SQL Tracking and Tracing Instrumentation for Developers by Tim Scott
  • Extending Security with Oracle Database Vault by Joel Goodman
  • Swingbench, When you need a load by Dominic Giles

December 6, 2007, UKOUG DAY 4

  • Database Bad Practices by Niall Litchfield
  • Query Optimizer 11g – What’s new? by Christian Antognini
  • How to Make your Oracle Apex Applications Secure by Peter Lorenzo
  • Practical Best Programming for PL/SQL Developers(Masterclass) by Steven Feuerstein
  • Investigating Oracle(Masterclass) by Julian Dyke

December 7, 2007

UKOUG DAY 4 – The End –

Filed under: Oracle Events — H.Tonguç Yılmaz @ 7:41 am

Today in the morning I saw Steven Feuerstein just before the first sessions started. I introduced myself to Steven, we had several emails lately related to Steven’s two days İstanbul seminar in February 2008, it was a quick meet up and I enjoyed to chat Steven very much. You must see him presenting, you do not understand how time passes, Steven is one of the best speakers I have ever seen in my life.

I wanted to attend Jonathan Lewis ‘s “The beginners guide to CBO” class as my morning session. What ever the topic is if Jonathan is speaking I believe it is a historical moment and not to be missed, also I was wondering how he would be presenting Optimizer concepts to the beginners since I also do similar presentations each summer to our internees. But just at the entrance after I said good morning to Jonathan he didn’t let me in :) He advised me to choose another presentation and mentioned briefly what he will be talking, mostly the first chapter of his two days seminar which I attended in İstanbul. It was an interesting moment really, but at the end of the day I still felt unhappy that I left and didn’t listen to his presentation :)

Database Bad Practices by Niall Litchfield

And here comes my third great presentation, with very well studied demos he prepared Christian with each slide in his presentation introduced new concepts and questions for me, I enjoyed every second of this session thank you Christian.
Query Optimizer 11g – What’s new? by Christian Antognini

How to Make your Oracle Apex Applications Secure by Peter Lorenzo

Practical Best Programming for PL/SQL Developers(Masterclass) by Steven Feuerstein

Investigating Oracle(Masterclass) by Julian Dyke

So Ukoug 2007 is over, after the event we met Jonathan at the market place near the museum coincidently, I felt lucky to have the chance to thank him personally for such a great independent user group event. I hope some day in Türkiye we also manage to have a user group Troug, just a junior one compared to Ukoug of course :)

For the next year I want and plan to come back of course, but this time I hope as a presenter, with at least one or two case studies I experienced in my company, which soon be Europe’s largest Telco in one country, with this kind of an operation we are in and me working like a fire fighter lately within the company it won’t be a big challenge to come up with some interesting stories I guess ;)

I hope you enjoyed my notes since it took at least an hour for me to prepare them :) And for now this is The End for my Ukoug 2007 adventure.

December 6, 2007

UKOUG DAY 3

Filed under: Oracle Events — H.Tonguç Yılmaz @ 8:31 am

Up to now both Birmingham and Ukoug conference were very satisfactory, I enjoyed the first three days very much. After a 30-45 minutes random walk we found at the end of the New Street the St Martin’s Church, a beautiful scene since it is Christmas time, and two books stores near the Church which I will be mining tonight after Ukoug ends. Also as they told me, “Once pubed, never stopped”, this is a reality in fact, and can easily ruin the conference :)

DB Time Performance method by Graham Wood

Programming real applications with Application Express by Andrew Woodward

Every Performance Problem Is One Of Two Things by James Morle

And here comes my second great presentation at Ukoug, John was prepared well and he was excellent during the presentation. Debugging APEX Applications by John Scott

Modelling on the cheap by Andrew Clarke

Real World Database Recovery – An Experience by James Hetherington

Understanding and Interpreting Deadlocks: What to do with an ORA-00060 by Mark Bobak

PL/SQL Tracking and Tracing Instrumentation for Developers by Tim Scott

Extending Security with Oracle Database Vault by Joel Goodman

Swingbench, When you need a load by Dominic Giles After this presentation since I am a PL/SQL dinasour :) I thought this can be done easily within Oracle, why not an open source project based on DBMS_SCHEDULER and APEX technology for inputs of load and reports. Hüsnü has already a loading Oracle series, and maybe there is already some.

After surviving 10 sessions on my third day today it will be a child play I guess :) But because of the long way waiting back to home I plan to post the last day’s notes on this Sunday.

December 5, 2007

UKOUG DAY 2

Filed under: Oracle Events — H.Tonguç Yılmaz @ 8:20 am

Today I had the chance to meet and chat with Andrew Clarke(APC@Otn forums) and Dominic Brooks. Also I talked with Julian Dyke and Joze Senegacnik for a while especially on memories from their İstanbul seminars. Ukoug in deed an interesting place to be, when you are walking from one session to another you may see Tom Kyte or Jonathan Lewis walking with you, or you can sit next to Jonathan Lewis in a session and everyone is very opened to meet and chat here.

Different than I planned before, I didn’t attend Cost Based Optimiser Roundtable facilitated by Jonathan Lewis and Harvesting the Advantages of a Database Centric Development Approach by Toon Koppelaars sessions today. It is really tiring to catch up with all sessions, I feel like a Nba All-Star game coach, which one to choose is really an issue. Two short complains I have to make; Hall 1 is the coldest hall I have ever seen, I was afraid of the outside but got cold in Hall 1, ironic :) Also I flew across a continent to see Kyte and Lewis, but during their presentations because of time limitations they cut shortly, so sad, no time constraints for these presenters let them finish please :)

And here are my second day’s sessions’ notes;

What’s new with Oracle Data Guard in 11g by Larry Carpenter

Performance Metrics using Statspack and Analytical SQL by Daniel Fink

11g new features for Developers by Thomas Kyte

Oracle Security Tools by Pete Finnigan

Understanding Statspack by Jonathan Lewis

Scalability with Oracle RAC for DWH Applications by Christian Antognini

Query Transformations by Joze Senegacnik

After the presentations with my colleague Uğur Demirelce, Uğur is one of the two DBAs of Europe’s second largest Telco Oracle datawarehouse, we took a walk to the New Street and bought our train tickets to London for Friday. Unbelievable how this country is expensive, even food and transportation. Since it is Christmas time streets are really fun, we walked through the Library, Museum and Town Hall, photographic scenes are all around.

Wednesday will be another though day, I hope to survive 10 sessions this time, see you tomorrow :)

December 4, 2007

UKOUG DAY 1

Filed under: Oracle Events — H.Tonguç Yılmaz @ 10:54 am

It took 10,5 hours for me to arrive my hotel Hyatt Regency which is near the seminar place ICC at Birningham. 4 hours I waited for the second flight at Amsterdam Schipol Airport, and after all that hours UK customs told me that my first name, last name and birth date exactly matches someone they are looking for so I had to wait another hour. This welcome surprise was little too much for me, thanks to my middle name’s selectivity, they apoligised and let me go after an hour sitting at midnight. After 5-6 hours sleep at most I enjoyed my breakfast, a full English breakfast they say, of course without beans and all that fatty meat. I can not believe someone eating this type of a breakfast and drinking that much for years still keep healthy :) Anyway between my hotel and ICC there was a tunel bridge so it took a 2 minutes walk to the registration place. I like to be early every time, so another surprise was waiting for me, they told me they couldn’t recieve the money for registration, after a little chat they were able to charge and I took a deep breathe again :) There I saw John Scott, an Ace Apex blogger and active Otn forums member, we had a little chat and left for the first timers briefing. So here are my first day notes as I promised, I hope you enjoy them;

Openning and Keynotes by Ian Smith and Tom Kyte

Oracle Spatial, An Introduction by Dominic Giles

Playing Russion Roulette with Silver Bullets by Jonathan Lewis

TimesTen: Anatomy of an In-Memory Database by Chris Jenkins

Partitioning from start to database 11g by Ian Abramson

This was my best presentation of the day. Not because of the topics mentioned, but especially Mr.Goodman’s presentation skills. Oracle 10g RAC Tuning Tips by Joel Goodman

Flashback Logging by Julian Dyke

11g New Features for DBAs by Thomas Kyte

After Tom Kyte‘s presentation I joined to the Oracle Bloggers Meetup organized by Mark Rittman, it was not so cold outside, but after my first drink and eatings I felt completely out of service and left early for a good sleep. Thank you Mark for the organization :)

So see you tomorrow, show just started and more is coming :)

Next Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers