Sometimes moving a small amount of data in Oracle database requires more work than the actual task you need to complete. DBAs always have their tools (PL/SQL Developer Text Importer is my favorite one) to move a small amount of data between databases.
But when it comes to nested tables this might be a bit challenging. USER_SDO_GEOM_METADATA table (for those of you who are not familiar with it, it is the catalog information of spatial layers in an Oracle database) has no exception to that. Here how it is easy to handle it
The structure of USER_SDO_GEOM_METADATA table has the following structure
SQL> desc user_sdo_geom_metadata Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(32) COLUMN_NAME NOT NULL VARCHAR2(1024) DIMINFO MDSYS.SDO_DIM_ARRAY SRID NUMBER
Obviously DIMINFO column is the problematic part since it is in SDO_DIM_ARRAY type. Now create a temporary table by unfolding this nested table at the source database site.
CREATE TABLE temp_metadata AS SELECT t.table_name, t.column_name, d.*, t.srid FROM user_sdo_geom_metadata t, TABLE(t.diminfo) d;
Once you are done check the structure of temp_metadata table which only contains primitive SQL types for the columns
SQL> desc temp_metadata Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME VARCHAR2(32) COLUMN_NAME VARCHAR2(1024) SDO_DIMNAME VARCHAR2(64) SDO_LB NUMBER SDO_UB NUMBER SDO_TOLERANCE NUMBER SRID NUMBER
Choose your favorite tool to move this table to the target database since it contains just a few rows. Then as the final step import this data into USER_SDO_GEOM_METADATA table at the target database (Ensure that there is no other record with the same table_name.column_name key on the target USER_SDO_GEOM_METADATA table otherwise trigger on it will warn you)
insert into user_sdo_geom_metadata SELECT m.table_name, m.column_name, CAST(MULTISET(SELECT sdo_dimname,sdo_lb,SDO_UB,sdo_tolerance FROM temp_metadata n WHERE n.table_name = m.table_name and n.column_name = m.column_name) AS SDO_DIM_ARRAY) as diminfo, m.srid FROM (select distinct table_name,column_name,srid from temp_metadata) m; commit;
You are done! Check everything works fine at the application site.
You can find my joint Solidify RMAN Backup with Oracle Open Storage 7000 presentation with my esteemed friend Orhan Bıyıklıoglu held yesterday in Open World 2010.
I have been in Sweden (in a japanese spa hotel near Stockholm City) between Monday and Wednesday to join ORCAN event. Thanks to Patrik Norlander and his friends, the event was really perfect. I had two presentations and joined presentations of other ACEs and experts.
I spent my time in talking with Dan Morgan on a possible Turkey Oracle User Group Event, with Jose Senegacnik on Oracle and planes, with Dimitri Gielis whether APEX 4.0 is sufficiently mature to grow large scale applicaitons, and finally with Luca Canali about recent Oracle Streams projects in CERN.
it was a perfect time for me
After attending the CDC implementation session of CERN team in UKOUG 2009, new features of Oracle Streams technology introduced with 11g got my attention. While searching for a suitable resource, I came across with this extremely helpful resource.
The problem about many Oracle books is that they either paraphrase Tahiti (or Oracle My Support notes although it is illegal) or they are built on some pseudo examples generated just to create a problem to find a solution.This book is definitely an exception and it is not for my book-shelf but for my briefcase.
Thanks to Ann L. R. McKinnell and Eric Yen start with a few warm up chapter (Chapter 1) explaining the underlying concepts of streaming idea and its proper usage, Oracle CDC components, and a brief introduction to XStreams which will be detailed in Chapter 6.
Chapter 2 is for database architects who are responsible with designing the replication system such that it will work smoothly for their business. There is an invaluable check list including almost everything that should be taken into consideration before starting.
Chapter 3 is a kind of implementation chapter of Chapter 2. In order to satisfy the checklist given in previous chapter, this chapter defines the necessary configuration details.
Chapter 4, Chapter 5, and Chapter 6 explains different ways of replication in detail. Keep in mind that you can read Chapter 5 online.
Chapter 7 and Chapter 8 are my favorite ones and I believe those are the reasons why this book is an excellent reference for all implementors. Chapter 7 is explaining the importance of documentation in a replication environment and explain how you can automatically generate your environment map and how you can gather performance data with Oracle utility packages. Chapter 8 is all about troubleshooting in Oracle Streams environment. I think this is the most important part because people keep on changing what you have implemented. The methodology and toolkit to track,diagnose, and solve a problem in your streaming environment is put very clearly in two sub-chapters and 13 bulletins.
To sum up, Oracle 11g Streams Implementor’s Guide is a really niche reference for not only those try to implement an Oracle CDC environment but also wish to understand essence of replication concepts (almost all are the same with slight changes in terminology and the way they have been implemented).
In this post you will find some tips I have been using throughout my career to become successful as an expert of Oracle RDBMS. Although fine grain details of the post are focused on Oracle RDBMS, it is not very hard to generalize those ideas to any expertise.
With my wishes of those tips to be helpful for young experts …
Fall in Love
I never thought Oracle software as a way of making money in first place. Oracle stuff is simply a natural extension of what I did as a 12 year old kid with QBASIC. Beginning at those days, “IT” has become my whole life. I never thought on returns or feasibility of my effort on it. The more time I spend, the deeper my love becomes and the deeper my love gets, the more time I devote.
One of the most popular questions I always see in youngsters mind is “Is Oracle worth putting effort to have an outstanding income?” To tell the truth, Oracle is may be one of the last resorts to be taken just to make “good money” as you think about bugs, crashes, nightly wakeups, etc. Instead I put my reason as “I am spending time as an Oracle expert because I love to solve problems about very large databases”
It is always a great challenge for me to look for a cost-effective, robust, and scalable solution for any kind of VLDB problem:
Search for the solution, find it, develop/architect it and be in proud with your solution when it lives on production…
Growing from Jedi Youndling to Jedi Grand Master
I think being a good player in enterprise arena or academy is strongly associated with finding an appropriate master for you. When I look back, I take myself as pretty lucky to have great masters at any stage of my career. Those masters will not only technically guide you, but also (and more importantly) they will give the necessary non-technical tips to make you successful in arena.
Obviously next step is to be a master. You might think this as a pay-back to community. But more importantly this is very critical to improve your own skills. It is appreciated that explaining a concept is pretty much different by knowing it in details. After starting to guide Oracle newbies, I have noticed that my presentation skills got improved. My expressive capabilities (reduction, simplification, exemplification, etc) for non technical people (managers, customers, etc) on technical stuff have improved. Finally it is a great motivation for me to keep my knowledge fresh.
As a child of a trader family, not to share our business secrets was always very essential. After choosing IT as my career path, I have understood that in order to become an esteemed expert, only way is to share knowhow instead of hiding it. Today’s web environment makes it almost impossible to hide a piece of information of our domain. Either you spread that piece of information and catch other’s interest or some other expert will do that.
Presenting, blogging, answering questions at user forums, or any other way of sharing your knowhow are very important for you to become a well-known member of community. At this point never underestimate yourself. Everybody, expert or newbie, have words to speak on expertise he/she works.
Put it in a New Form
Never hesitate to taste new things. It is also valid for Oracle Technologies (not just for food or beverage). One of my most important quotes in Oracle Magazine is
“I think to be a successful Oracle DBA, you should always be looking to adopt new database Technologies, but you should always need to be very conservative in testing those Technologies”
When I look back in my career, “new” always accompanies me. Here are a few of them:
- First Oracle Database Beta Testing with 11g Release 2
- First large size RAC project
- Introducing ASM
- First and the only ACED in Turkey
- Youngest ACED and DBA of the Year award winner.
Let first to know, first to try be all your desire.
Ensure that Your “Team” is Ready to Go Anytime
Keep in mind that all projects will have their own problems. You will either face with an unreported bug or stuck at some point on design. The question is how many people can you ignite in case that something fails. My way is to put as many distinct profiles as possible when I face with a problem:
- Experts in my company
- Oracle My Support
- Members of ACS from our Oracle region, EMEA
- Oracle Development Team
- Oracle PM Team
- Esteemed expert of community
As this profile list gets longer, one question might be how to alert that many people as it is required. One problem I always see is that people are trying to alert people as problems start. That is not a practical way that will work.
The way to take is to build up your network before any problem even before starting the project. You can build up this by attending user group meetings, conferences or sending emails to those experts and introducing your projects and yourself. You will have an army of experts ready to go for action if you don’t let them forget you.
DBA or Developer? This is the Question
Yet another popular question from youngsters is “Should I build my career path as a DBA or developer?” I first start working with Oracle as a SQL and PL/SQL developer, after that I managed a software team in a startup company and finally I have been working as a VLDB DBA.
All those stuff has nothing to do with technology and expertise. It is all about companies’ organizational hierarchy. They are simply “titles” to be written on your business cards. Systems like Oracle are so complicated that they don’t like titles. If you wish to have a “good” relationship with Oracle RDBMS, you should be a composition of
- Good DBA
- Good Developer
- Intermediate OS Admin
- Good Hardware Spec Reader & Benchmarker.
That is simply because any failure somewhere within the stack will return as a failure at RDBMS level. As an expert you should prevent this.
Return to Basics
Another question by engineering students during their undergraduate education is “Why do we take that many theoretical courses? What is their use in real life?” To cut it short I can simply say they are extremely important.
I can exemplify it with queuing theory (based on probability theory and statistics). I have seen many people in enterprise interpreting service time, wait time, number of waiting requests, max wait time, etc in different contexts like I/O, network, OWI, etc. erroneously.
Indeed, three basic chapters from any queuing theory book are sufficient to interpret them correctly:
- T = E +W
- Little’s Law
- Markov Chains
Unfortunately, today’s hardware and software is hard to understand by just knowing 4 arithmetic operations. So taking basic probability theory, simulation systems, queuing theory, algorithm analysis, linear/non-linear/stochastic equations has a great effect on me to interpret/solve enterprise level problems.
I am finally back from UKOUG and I am sharing my presentation on backup & recovery of enourmous databases.
In Open World 2009, my second presentation was a joint session with my friend Timothy Chien who works for Oracle as Principal Product Manager of Database HA products. The room was almost full (~900 people).Thanks to all attendees. It seems this topic is still hot :). You can find Tim’s and my part below:
Here is the content of my first presentation in Open World 2009:
As I’ve promised, I publish my presentation I held on Tuesday in Oracle Open World. It was a really challanging experience for me to perform a presentation in such a great event. Moreover one of the Oracle experts, Julian Dyke, I really like to read attented to my presentation. And I am very proud of hearing his positive feedbacks about my presentation. Thanks a lot Julian. Hope to see you in Birmingham…
I also want to thank to Reiner Zimmermann from Oracle, all my collegues and managers in Turkcell, and Hasan Tonguc Yilmaz for their supports and ideas in preparing this presentation.