Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: How do i extract the selected existing DB objects from a Schema?

    Hi All,

    I have searched a lot for the help in all the related Oracle forums. The below script i got from ASKTOM forums...
    --------------------------------------------------------
    ---------------------------------------------------------
    set termout off
    set heading off
    set feedback off
    set linesize 50
    spool xtmpx.sql
    select '@getcode ' || object_name
    from user_objects
    where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
    /
    spool off
    spool getallcode_INSTALL
    select '@' || object_name
    from user_objects
    where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
    /
    spool off
    set heading on
    set feedback on
    set linesize 130
    set termout on
    @xtmpx.sql


    is what I use. creates a script that calls the getcode script one by one to get the code out where
    getcode is:


    set feedback off
    set heading off
    set termout off
    set linesize 1000
    set trimspool on
    set verify off
    spool &1..sql
    prompt set define off
    select decode( type||'-'||to_char(line,'fm99999'),
    'PACKAGE BODY-1', '/'||chr(10),
    null) ||
    decode(line,1,'create or replace ', '' ) ||
    text text
    from user_source
    where name = upper('&&1')
    order by type, line;
    prompt /
    prompt set define on
    spool off
    set feedback on
    set heading on
    set termout on
    set linesize 100
    -------------------------------------------------------
    -------------------------------------------------------

    In my case i have only few (around 5000 packages/ Procedures) to be extracted. If i am using the above query it will obviously generate much more packages.
    I would like any suggestions whether i can either create a custom table and put all the required DB objects to be extracted inthat table and use that table in the above query rather than user_objects.

    Please help me in this

    Aditya Garg

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you consider data pump (or even export and import utilities)?

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    I have no other option due to Project constraints...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.orafaq.com/forum/t/143472/136107/

    alternatively use DBMS_METADATA
    Last edited by anacedent; 04-10-09 at 11:06.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Cop out?

    Quote Originally Posted by aditya.gargs
    I have no other option due to Project constraints...
    Project constraints? Seems just a cop out.

    With SQL*Developer (or TOAD) you can select the objects you want to export the DDL.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2009
    Posts
    4
    Sorry to say LKBRWN_DBA... I could have done the same work which you have suggested if the DB objects would have been in the range of 100- 500, but here i am talking about 5000+ DB objects from 50,000 objects.

    Hope you can understand i am just working here to reduce the manual work and trying to do the work automatically ..

    hope you can understand the problem and give your suggestion.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I agree, use the right tool for the job...

    If you need the DDL, use expdp/impdp. You can spool the contents to a file using SQLFILE, and specify the objects that you want to include. You have to export (data pump) them first, and then use impdp to get the file.

    EXPORT
    2 Data Pump Export
    IMPORT
    3 Data Pump Import

    While I haven't tested your specific situation out before, I have gathered DDL from a *.dmp file. Here's what I think you'd need to do:

    Code:
    [oracle@ora12 tmp]$ vi expdp_20090313.par
    schemas=scott
    directory=DATA_FILE_DIR
    dumpfile=expdp_20090313.dmp
    logfile=expdp_20090313.log
    include=FUNCTION
    include=PROCEDURE
    include=PACKAGE
    
    [oracle@ora12 tmp]$ expdp scott/tiger@dev12 parfile=expdp_20090313.par
    ... <output from expdp>
    
    [oracle@ora12 tmp]$ vi impdp_20090313.par
    schemas=scott
    directory=DATA_FILE_DIR
    dumpfile=expdp_20090313.dmp
    logfile=impdp_20090313.log
    sqlfile=impdp_20090313.sql
    
    [oracle@ora12 tmp]$ impdp scott/tiger@dev12 parfile=impdp_20090313.par
    ... <output from impdp>
    When you include the SQLFILE parm, the objects/data are not actually imported, only the DDL are written to a file. I think that you should be able to do the same with import, I'm just more familiar with import data pump.

    --=Chuck

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Unfortunately, Chuck, he has already declined use of exporting tool (see messages #2 & #3).

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    I saw that, but it doesn't make sense, since I didn't get the reason for the push-back. I was hoping that if I showed them how easy it was, that they would convince management to take that route.

  10. #10
    Join Date
    Apr 2009
    Posts
    4
    Hi Chuck, Thanks a lot for providing this information ... i will surely work with this code and try to come with suitable output... hope i can make my manager understand if this works out...

    Thanks all

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking expdp

    With datapump you can limit the number of packages/procedures/functions using the "LIKE" functionality (if possible):
    Code:
    SCHEMAS=MYSCHEMA
    DUMPFILE=expprocs.dmp
    DIRECTORY=datapump_dir1
    LOGFILE=expprocs.log
    INCLUDE=PROCEDURE:"LIKE 'XPRC_%'"
    INCLUDE=FUNCTION:"LIKE 'YFNC_%'"
    -- Etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •