From oracle-l-bounce@freelists.org Fri Apr 30 17:18:02 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3UMHlL30054 for ; Fri, 30 Apr 2004 17:17:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3UMHb630036 for ; Fri, 30 Apr 2004 17:17:47 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 40B7A72CBB4; Fri, 30 Apr 2004 17:08:21 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08938-71; Fri, 30 Apr 2004 17:08:21 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B75572CA4B; Fri, 30 Apr 2004 17:08:20 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 30 Apr 2004 17:07:07 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 132C972C9FA for ; Fri, 30 Apr 2004 17:07:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08938-52 for ; Fri, 30 Apr 2004 17:07:06 -0500 (EST) Received: from web50605.mail.yahoo.com (web50605.mail.yahoo.com [206.190.38.92]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id BCF8E72C8C9 for ; Fri, 30 Apr 2004 17:07:06 -0500 (EST) Message-ID: <20040430222010.97549.qmail@web50605.mail.yahoo.com> Received: from [24.148.161.169] by web50605.mail.yahoo.com via HTTP; Fri, 30 Apr 2004 15:20:10 PDT Date: Fri, 30 Apr 2004 15:20:10 -0700 (PDT) From: Michael Thomas Subject: Re: Using dbms_metadata to extract a full schema To: oracle-l@freelists.org In-Reply-To: <4092A10D.10771CE0@sun.com> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4144 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mhthomas@yahoo.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi, I found some problems using the DBMS_METADATA package and procedures, especially with large DDL statements. We were using v9.2.0.1 DB, UTF-8 and mixed bag of column types, e.g. some tables with column_value01 VARCHAR2( 4000 CHAR ) and others with column_value02 VARCHAR2( 4000 BYTE ) (from memory so syntax errors are my own). I am attempting to highlight the meaninglessness (word?) of VARCHAR2( 4000 ) in UTF-8. Following the examples/docs/experience/whatever, its quite complicated to get every schema object and type, even just a complete list of objects. I mean it usually takes about four or more procedure calls just to setup the correct 'environment' to format your extraction. What I finally did was generate two passes. First was a complete list of the commands to do the extraction which at least included one command for every object (this list was good to check when objects were added or dropped). Second pass was to attempt to run the commands in a 'reasonable' time frame while capturing the spooled output. Good luck, you can waste some time if you attempt to reverse a large schema like Siebel. :-( I hated when there were extra 'random' CR-LF characters inserted in large table creation DDL. I attempted to 'very clever' and parse the DDL so I could successfully format the result with a tool like PLFormatter. Looking back I think it was more trouble than it was worth, but I was 'very clever' and wasted more than two days debugging that junk. Extracting packages, procedures and the like (triggers, etc) are a complete waste of time with DBMS_METADATA (triggers had bugs). In summary, others have posted suggestions here about tools that can reverse entire schema's DDL. I would try those first. :-) But, I'm sure complete Oracle education classes could be taught around DBMS_METADATA... (okay its Friday and I'm drifting...). Regards, Mike Thomas PS: My work was on some early versions of 9.2 and its remotely possible Oracle has fixed all the problems I've reported above. In that case, disregard. ;-) > call. Of course, this would make sense and the > documentation > SAYS you can do it, but it looks not to be the case. > > Regards, > Daniel > __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------