DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005

    moving an SQL Profile from one instance to another

    I am working in 10g R2. I have created several SQL profiles in one oracle instance and would like to mmove them to another. Here is what have done so far:
    I created the staging tabe to store the profile using:

    Next I loaded the profile into the staging table:
    EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_0149f3c7edcdc000',
    staging_table_name => 'PROFILE_STGTAB')

    I exported the table and imported it into the other instance using datapump (not sure if this method matters, just want to provide all the facts)

    now I am trying to unpack the profile from the staging table and load it using:

    I get the following error:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'UNPACK_STGTAB_SQLSET'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    the user I am logged in as has the priv. create any sql profile.

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    I think syntax should be like...
    sqlset_name => '%',
    replace => TRUE,
    staging_table_name => 'PROFILE_STGTAB';
    Last edited by PAVB; 02-17-2011 at 04:04 PM. Reason: formatting
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2005
    I tried that and now I get this error:
    ORA-00904: "OWNER": invalid identifier
    ORA-06512: at "SYS.DBMS_SQLTUNE", line 5219
    ORA-06512: at line 2

    I looked at the unpack_stgtab_sqlset proceduer in the dbms_sqltune package and it only has this:
    PROCEDURE unpack_stgtab_sqlset(
    sqlset_name IN VARCHAR2 := '%',
    sqlset_owner IN VARCHAR2 := NULL,
    replace IN BOOLEAN,
    staging_table_name IN VARCHAR2,
    staging_schema_owner IN VARCHAR2 := NULL);

    The documentation says that leaving the sqlset owner blank will apply it to the current shema which is what I want. The staging table does not have a field called owner.

Posting Permissions

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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.