7 Replies Latest reply on Jun 5, 2020 9:49 PM by asterger

    Data not Visible to SYS

    asterger
    asterger May 21, 2020 1:12 AM

      SQL Developer 19.4.  Within Connections > Object Viewer > Views looking at a DBA view (DBA_RSRC_PLANS) and selecting Data in the viewer, see a row count but no data.  What Preference do I need to set to see data.  Yes, logged in as SYS.

       

      Thanks,

       

      -- Alan

      • 179 Views
      • Tags:
        • 1. Re: Data not Visible to SYS
          thatJeffSmith-Oracle
          thatJeffSmith-Oracle May 21, 2020 3:56 PM (in response to asterger)

          SYS sees EVERYTHING...unless you have some data vault / security thing going, which you should already be aware of

           

          • 2. Re: Data not Visible to SYS
            asterger
            asterger May 25, 2020 7:03 PM (in response to thatJeffSmith-Oracle)

            Ah no.  Please reread the path again.  Perhaps I didn't use the best terms in describing the path.  This is what I'm seeing:
            Screenshot from 2020-05-25 14-57-43.png

            • 3. Re: Data not Visible to SYS
              Glen Conway
              Glen Conway May 25, 2020 9:02 PM (in response to asterger)

              Not really certain about this but...

               

              1) If your connection is to a container DB, try DBA_CDB_RSRC_PLANS

              2) If your connection is to a pluggable DB, use DBA_RSRC_PLANS

               

              I see documentation saying the DBA_RSRC_PLANS view shows all the resource plans in the database, but maybe that just means for pluggable databases?  Corrections welcome!

               

              Cheers

               

              Edit:

              But in my test on a CDB for DBA_RSRC_PLANS, I see no data, and no rows.  You, on the other hand, show 11 rows with no data.

              Best to use View > Log > Statements log to see which SQL statements are generated and executed when you click on the Data tab.

              • 4. Re: Data not Visible to SYS
                thatJeffSmith-Oracle
                thatJeffSmith-Oracle May 26, 2020 4:43 PM (in response to asterger)

                Right, we need more info about your environment, because it should be working

                 

                • 5. Re: Data not Visible to SYS
                  BPeaslandDBA
                  BPeaslandDBA May 26, 2020 5:45 PM (in response to asterger)

                  I'm logged into SQL Developer as myself, not SYS. But I do have the DBA role granted to me. And I can see data in that view.

                   

                  Whenever we run into issues like this I always revert back to what Oracle Support would have me do and that is to see how the SQL statement runs in SQL*Plus. So sign on to the database as SYS in SQL*Plus and query that same view. Does it return rows? If not, then it would rule out SQL Dev as being the issue.

                   

                  Also, in SQL Dev you can try to just query the view in the worksheet. See if it returns rows.

                   

                  HTH,

                  Brian

                  • 6. Re: Data not Visible to SYS
                    asterger
                    asterger Jun 4, 2020 8:45 PM (in response to BPeaslandDBA)

                    Database is non-plugable, Oracle 12.1.0.2 SE 64-bit (Linux).  SQLcl renders data on DBA_RSRC_PLANS.  Since there are a number of VPD application tables, was not too concerned initially.  However, when data doesn't render using SYS, that got my attention.  Pertinent View -> Logs below:

                    SELECT /*+ no_parallel("DBA_RSRC_PLANS") */  ROWID "ROWID" FROM "SYS"."DBA_RSRC_PLANS"    null
select   NULLIF((select count(1) from all_updatable_columns where owner = 'SYS' and table_name = 'DBA_RSRC_PLANS' and (insertable = 'YES' or updatable = 'YES')),0),  (select rowid from SYS.DBA_RSRC_PLANS where rownum = 1) from dual    null
select   NULLIF((select count(1) from all_external_tables where owner = 'SYS' and table_name = 'DBA_RSRC_PLANS'),0)from dual    null
select column_name from  Dba_updatable_columns A where TABLE_NAME = :OBJECT_NAME and owner = :OBJECT_OWNER and A.updatable = 'YES'     "OBJECT_NAME"="DBA_RSRC_PLANS", "OBJECT_OWNER"="SYS"
select column_name from  Dba_updatable_columns A where TABLE_NAME = :OBJECT_NAME and owner = :OBJECT_OWNER and A.updatable = 'YES'     "OBJECT_NAME"="DBA_RSRC_PLANS", "OBJECT_OWNER"="SYS"
select   NULLIF((select count(1) from all_updatable_columns where owner = 'SYS' and table_name = 'DBA_RSRC_PLANS' and (insertable = 'YES' or updatable = 'YES')),0),  (select rowid from SYS.DBA_RSRC_PLANS where rownum = 1) from dual    null
select   NULLIF((select count(1) from all_external_tables where owner = 'SYS' and table_name = 'DBA_RSRC_PLANS'),0)from dual    null

                     

                    • 7. Re: Data not Visible to SYS
                      asterger
                      asterger Jun 5, 2020 9:49 PM (in response to asterger)

                      [Solved] I have been rolling SQL Developer preferences forward for quite some time.  There are 8 system* directories in ~/.sqldeveloper.  Renamed system19.4.0.354.1759/ to *.old and restarted SQL Developer.  I now have data in DBA_RSRC_PLANS.  But lost my Connection presets in the process.