Results 1 to 9 of 9
  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: How to use a data pump

    Hi,
    I am using oracle 11g..I am trying to export an empty tabe to a new database and then run a script to add the contents (data) into it since I cannot create the table in the new database..It is taking the query to run for too long.

    I have decided to use the data pump to export data into the new table I created but the problem is I don't know how to specify the parameter file..

    Please help me suggest a solution..

    Many thanks!,
    Delisha

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Answers to most of your questions can be found by reading the documentation; I'd suggest you to bookmark that page.

    This is the data pump documentation. Do some reading - I believe you'll manage to do what you plan to do. If not, copy/paste your session so that we could see what you are doing and how.

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    jus this would do.

    expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y

    Read documentation for more info.
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  4. #4
    Join Date
    May 2011
    Posts
    19

    How to use a data pump

    Thank you Little foot and Gethmathan for the very good reply that makes me understand how to use a data pump. I did readings on the links which you sent..I understand that I need to create directory objects and then invoke the data pump export.I will be using the table export mode to export the fields but the table itself is an empty table. I will have to run a query that will get the data and load it into the fields in that table that I exported..The query for the data to be loaded in is this
    SELECT B.SUBS_KEYVALUE,
    B.ANUMBER,
    TMIS_UTILS.get_call_number_loc_zone (B.ANUMBER) AS ANAME,
    TMIS_UTILS.GET_NUMBER_REGION (B.ANUMBER) AS AREGION,
    B.BNUMBER,
    TMIS_UTILS.get_call_number_loc_zone (B.BNUMBER) AS BNAME,
    TMIS_UTILS.GET_NUMBER_REGION (B.BNUMBER) AS BREGION,
    TMIS_UTILS.get_call_type_name (B.ANUMBER, B.BNUMBER) AS CALL_TYPE,
    FROM_ZONE,
    TO_ZONE,
    total_units,
    free_units,
    B.info17 AS TIMEBAND,
    --ROUND ( (B.BILLABLE_DURATION / 60), 2) AS
    BILLABLE_DURATION,
    B.CALL_PRICE AS REVENUE,
    -- ROUND ( (B.BILLABLE_DURATION / 60) * (B.CALL_PRICE), 2) AS REVENUE,
    (B.calldate) AS CALLDATE,
    tmis_utils.is_to_fixed_line (B.ANUMBER, B.BNUMBER) FIXED_2_FIXED,
    B.CUST_KEYVALUE
    FROM ABS.ALLCALLS@ABS_SYSTEM.TELIKOMPNG.COM.PG B
    where calldate between TRUNC(TO_DATE('01-MAY-2011')) AND TRUNC(TO_DATE('31-MAY-2011'))


    The table I created as fields
    SUBS_KEYVALUE,
    ANUMBER,
    ANAME,
    AREGION,
    BNUMBER,
    BNAME,
    BREGION,
    CALL_TYPE,
    FROM_ZONE,
    TO_ZONE,
    total_units,
    free_units,
    TIMEBAND,
    BILLABLE_DURATION,
    REVENUE,
    CALLDATE,
    FIXED_2_FIXED,
    CUST_KEYVALUE..

    My question is this..if I had to use the data pump how would it cater for these two separate jobs?..

    Thank you so much in advance...

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >..It is taking the query to run for too long.
    In your original post you stated line above.

    how does the SELECT in most recent post differ from the long running query in original post
    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.

  6. #6
    Join Date
    May 2011
    Posts
    19

    How to use a data pump

    Anacedent thanks for replying.these two query differs as follows, if I run it to create a table as in
    create table Call_dispersion
    as
    select.....................................
    ............................................
    .............................................

    it would take too long but if I just run the
    select.......................................
    ...............................................
    ................................................

    it would'nt take up long to give me the output,it would be less than a minute.
    Therefore I was hoping to try use the data pump for two separate jobs..Do you think it will work or you have any other suggestions..

    Thank you for your time..

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it would'nt take up long to give me the output,it would be less than a minute.
    I suspect you confused returning FIRST ROWS with returning ALL ROWS.
    Moving the rows across the network induces more delay to complete the loading.
    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.

  8. #8
    Join Date
    May 2011
    Posts
    19
    Thanks I'll check it out> I remember having specifiying rownum < 2 in my functions created.. Thanks again..

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If the select is fast, but the CTAS is slow simply do the following.

    1) add and 1=2 at the end of the select in the CTAS. That will make the table and leave immediately.

    2) do an insert into tabale using the select without the and 1=2
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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