-
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:
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB')
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:
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(table_name => 'PROFILE_STGTAB',
staging_schema_owner=>'schema_name')
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.
-
I think syntax should be like...
Code:
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
replace => TRUE,
staging_table_name => 'PROFILE_STGTAB';
END;
/
Last edited by PAVB; 02-17-2011 at 05: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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|