Skip to main content
H
HK_109852431-Visitor
1-Visitor
April 22, 2024
Solved

Oracle Database import question

  • April 22, 2024
  • 1 reply
  • 1220 views
I am using Windchill PDMLink Release 11.1 and Datecode with CPS M020-CPS27

Can we import database using the following command: (This command has exclude=statistics)
impdp system/manager@wind directory=export dumpfile=EXPDP_acumedprd_19032023_01.dmp,EXPDP_acumedprd_19032023_02.dmp,EXPDP_acumedprd_19032023_03.dmp,EXPDP_acumedprd_19032023_04.dmp exclude=statistics LOGFILE=import_acumedprd.log schemas=acumedprd
And then run the following command as per article https://www.ptc.com/en/support/article/CS129969 to gather statistics manually:
execute dbms_stats.gather_schema_stats('DBUSER', DBMS_STATS.AUTO_SAMPLE_SIZE , CASCADE =>TRUE);
    Best answer by BenLoosli

    Here is what I do:

    a. Export the current database with the expdp command and Windchill shutdown

    expdp System/@wind schemas=PDM directory=expdp_full dumpfile=full_exp.dmp exclude=statistics logfile=full_expdp.log

    b. Import the Oracle backup into the PDM user space

    Impdp System/<password>@wind  schemas=PDM directory=expdp_full dumpfile=full_exp.dmp logfile=full_imp.txt

    c. Gather DB Statistics

    SQLplus PDM/<password>@wind

    Execute dbms_stats.gather_schema_stats(‘PDM’,DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE =>TRUE

     

    I do not see any reason your method should not work since in both cases the statistics are not part of the imported DB.

    1 reply

    B
    23-Emerald III
    BenLoosli23-Emerald IIIAnswer
    23-Emerald III
    April 23, 2024

    Here is what I do:

    a. Export the current database with the expdp command and Windchill shutdown

    expdp System/@wind schemas=PDM directory=expdp_full dumpfile=full_exp.dmp exclude=statistics logfile=full_expdp.log

    b. Import the Oracle backup into the PDM user space

    Impdp System/<password>@wind  schemas=PDM directory=expdp_full dumpfile=full_exp.dmp logfile=full_imp.txt

    c. Gather DB Statistics

    SQLplus PDM/<password>@wind

    Execute dbms_stats.gather_schema_stats(‘PDM’,DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE =>TRUE

     

    I do not see any reason your method should not work since in both cases the statistics are not part of the imported DB.

      C
      CHanna225-Regular Member
      5-Regular Member
      June 6, 2024

      Are you still dropping WCUSER before importing the new dumpfile?

      B
      23-Emerald III
      BenLoosli23-Emerald III
      23-Emerald III
      June 6, 2024

      Yes

       Between step a and b, I do the following:

      Drop user <pdm_username>

      @create_user.sql - this will ask for 3 pieces of infomeation

      PDM username

      Temp Tablespace name - TEMP

      Userdata Tablesapce name - USERS

       

       

      Terms & ConditionsAccessibility statement