9 Replies Latest reply on Dec 22, 2016 9:10 PM by rp0428

    [4.2 EA] Resource Plan shows no SQL

    dherzhau

      In the DBA Panel I can open a Resource Plan  an can see the details and so on but no SQL is displayed. If I choose create similar I get the DDL.

       

      A small additional on is if DDL is generated it shows up DLL is generated.

       

      Best regards

       

      Dirk

        • 1. Re: [4.2 EA] Resource Plan shows no SQL
          thatJeffSmith-Oracle

          Is this where you're seeing the issue?

           

          plan-sql.png

          • 3. Re: [4.2 EA] Resource Plan shows no SQL
            thatJeffSmith-Oracle

            we use dbms_metadata to get the DDL, could be a privilege issue with your logon user

             

            view > log > statements - test those queries we use

             

            here's the main one:

             

            SELECT DBMS_METADATA.GET_DDL('RMGR_PLAN',:NAME) FROM DUAL

            • 4. Re: [4.2 EA] Resource Plan shows no SQL
              dherzhau

              If I use the SYS account I could see thee DDL. But If I use my dba account I could not see the DDL. I have the impression that SQLDEVELOPER ist not using the schema parameter, could it be?

              if I execute the following SQLS from SQLDEVELOPER you get:

              SQL> select plan_id, plan from DBA_RSRC_PLANS where plan ='DSS_PLAN';

               

                 PLAN_ID PLAN                                                                                                                           

              ---------- --------------------------------------------------------------------------------------------------------------------------------

                   17314 DSS_PLAN                                                                                                                       

                   17314 DSS_PLAN                                                                                                                       

               

              SQL>

              SQL> SELECT DBMS_METADATA.GET_DDL('RMGR_PLAN','DSS_PLAN') FROM DUAL;

               

              Fehler beim Start in Zeile: 4 in Befehl -

              SELECT DBMS_METADATA.GET_DDL('RMGR_PLAN','DSS_PLAN') FROM DUAL

              Fehlerbericht -

              ORA-31603: Objekt "DSS_PLAN" vom Typ RMGR_PLAN  in Schema "DIRK" nicht gefunden

              ORA-06512: in "SYS.DBMS_METADATA", Zeile 6069

              ORA-06512: in "SYS.DBMS_METADATA", Zeile 8666

              ORA-06512: in Zeile 1

               

              SO Iget the DDL if specify the SCHEMA SYS:

              SQL> SELECT DBMS_METADATA.GET_DDL('RMGR_PLAN','DSS_PLAN','SYS') FROM DUAL;

               

              DBMS_METADATA.GET_DDL('RMGR_PLAN','DSS_PLAN','SYS')                            

              --------------------------------------------------------------------------------

               

                 BEGIN

                 DBMS_RESOURCE_MANAGER.CREATE_PLAN(

                   plan       =>'DSS_PLAN',

               

              Manny Thanks for your support

              • 5. Re: [4.2 EA] Resource Plan shows no SQL
                dherzhau

                same Issue in 4.2 EA2

                • 6. Re: [4.2 EA] Resource Plan shows no SQL
                  thatJeffSmith-Oracle

                  no 'issue' has been identified on our side yet - I'm guessing it's a privilege issue on your side

                  • 7. Re: [4.2 EA] Resource Plan shows no SQL

                    Jeff Smith Sqldev Pm-Oracle wrote:

                     

                    we use dbms_metadata to get the DDL, could be a privilege issue with your logon user

                     

                    view > log > statements - test those queries we use

                     

                    here's the main one:

                     

                    SELECT DBMS_METADATA.GET_DDL('RMGR_PLAN',:NAME) FROM DUAL

                    I haven't experimented with plans or tested this but OPs problem could be that query does NOT provide the schema and the default schema is current_user.

                     

                    All resource manager plans are schema objects, are owned by SYS and the only dictionary view is DBA_RSRC_PLANS.

                     

                    There is no equivalent ALL_... view. So I don't know if there is a specific privilege that could be granted.

                     

                    I will try to find some time this weekend to research. Could be something that got overlooked or could be a special case is needed for sys owned objects.

                    • 8. Re: [4.2 EA] Resource Plan shows no SQL
                      thatJeffSmith-Oracle

                      you have to grant some privs to get ddl on those views, but the details on that are on a MOS doc - so I suggest you open an SR with MOS

                      • 9. Re: [4.2 EA] Resource Plan shows no SQL

                        you have to grant some privs to get ddl on those views, but the details on that are on a MOS doc - so I suggest you open an SR with MOS

                        The access to the views isn't the issue. The issue appears to be:

                         

                        1. the DBMS_METADATA method will default to the current user if the owner parameter isn't specified.

                        2. Sql dev, based on the query you posted, does NOT provided the owner parameter

                        3. the object is owned by sys so does not exist if the metadata query is for the current user and that user isn't sys.