Mostly Making oracle easy, mostly:
Search for:

Ask A Question

Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  1. I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
  2. I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
  3. I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.

Go!

7,793 Comments

  1. Aswin Narayanan Reply

    Hello Jeff! I just downloaded the SQL Developer on my macbook as I wanted to start learning, however as I open the application, it starts to hang a bit and then freeze. Is there a reason for this that you are aware of? I have the Java 8 downloaded as well, however it never works whenever I try to open it.

  2. Andrew Leary Reply

    Hi Jeff,

    Really appreciate your time and patience sharing your knowledge with all of us .

    Thank you, also, to everyone else who contributes to the knowledge pool.

    Is there a way in SqlDeveloper to move from one SQL statement to the next, in a single script, similar to “a commercial competitor” with a CTRL+PGDN

    Regards

    • Andrew

      Hi All,

      Found another solution

      execute a find (F3) for the regular expression:
      ^(select|update|delete)

      This will find the next DML where the line starts with the keywords. Subqueries will be ignored if indented.

      Andrew

  3. Steve K Reply

    Hi Jeff,

    Your blog is terrific. My question is a simple one, but I can’t find the answer in the official oracle documentation for the SQL Developer tool.

    How do I change the numeric format in the result query grid? I can change the display of a date field by setting the appropriate NLS_Date format, but I cannot format the output to the result grid for a number. Example: I would like to see numbers lined up by the decimal point: 1.20, 1.22 etc. I can’t seem to get the result query grid to add the trailing 0. I can modify the query to to_char(my_number, ‘99.90’), but by doing so changes the data type of the field in the view. I would like the field to remain as a number.

    select 1, to_char(1, ‘99.99’) as t1, to_char(‘1’, ‘00.00’) t2, to_number(to_char(‘1’, ‘00.00’)) as t3 from dual;

    Thanks

  4. Jennifer Hughes Crager Reply

    It cuts it by a third during our testing. Changing ide.conf really deflated performance and this seemed to be the better solution without hurting performance.

    • thatjeffsmith Post Author

      Memory or CPU or something else? Also, do you have physical machines or Citrix/virtualized desktops?

  5. Jennifer Hughes Crager Reply

    Hi – I’m want to apply Windows Look and Feel for all users due to sqldeveloper pegging memory. Where do I go about changing that setting non-GUI. We are looking to apply across board via GPO. Thanks for your time.

    • thatjeffsmith Post Author

      Have you profiled the resource requirement differences between native and Oracle Look and Feels?

      I’m curious to see what you’re findings were.

      All of the preferences or Windows ‘installs’ can be found in the AppData, Roaming Profiles, sqldeveloper folders.

    • Jennifer Hughes Crager

      is it Windows Look and feel set in a .conf file – we did testing on version 17.2 and it made a difference so we just want to set this for every one.

  6. Matt Sauter Reply

    Hello, Jeff. Love your forum and love your attitude (esp. your line about wanting your customers to be happy with the Oracle products).” Was Oracle consultant for 10 years myself. Have worked with Oracle since version 7 as a DBA/DB architect. At one time, was a big user/administrator of the Oracle Designer tool. Currently using SQL Developer Data Modeler version 18.2.0.79 in our factory. Before I state my problems, I need to clarify that I have consulted teh oracle.docs documentation and your forum for these compare/synchronization issues.

    I have imported a data model (*.dmd) from Server X and successfully put it on the Data Modeler on Server Y. This model is named “baseline.dmd”. Also on Machine Y is our operational DB that I’ll call OPS. My primary objective here is to compare all tables in a single schema called “NET” in the OPS DB to the model called “baseline.” At the moment, the only Design one can see in Data Modeler is the one called “baseline” and, under it, is the Relational Model ALSO called “baseline.” I just want to see if our model and our OPS DB match at the table (column/index/PK/FK) level for the “NET” schema.

    So, I read that One way to do this is to use the “Synchronize Model with Data Dictionary” option from the main tool bar (a ‘left arrow’ button). I select this and notice that the available source connections are old ones that came across from when I moved the original Baseline.dmd (they all show with strikethroughs and am not sure how to delete them or add my new connection). However, I DO have a good connection to the OPS DB that I created earlier and I see it listed under the dropdown for “Redirect Connection” (it is named “E05” and I have successfully tested it) so I pick it. I have selected the 3 options under “Database Synchronization” of “Use Source Connection as Filter,” “Use Source Schema,”, and “Synchronize the Whole Schema.” I then hit the button to generate the design for this compare and it runs for 30 minutes and ALWAYS errors with a java error stating ‘ERROR Design – Error Opening Storage Design followed by “java.lang.RuntimeException Error in segment name.” The rest of the 8-9 lines are just the usual cryptic java errors.

    So, then I decided to just import the OPS Data Dictionary into Data Modeler for just the TABLES for the NET schema. I did get this to work, but it comes in as a NEW DESIGN called OPS with a relational model called “Relational_1.” at this point, I decide to then compare the 2 relational models called “Baseline” and “Relational_1. So I go under Tool s and pick “Compare/Merge Models” and I pick the Design to compare as “Baseline.dmd” and then select “Baseline” as the Relational Model under SOURCE MODEL and “Relational_1” as the Target Model under “Target Design OPS.” Under RDBMS Sites, I select “Oracle Database 12c” as the RDBMS Site. I then hit “ok” to kick it all off. The little turning gears appear very shortly, but it never comes back and I quickly see teh error log contencts which show:

    ERROR ModelChooserDiag – Error in Compare Status followed by (again) “java.lang.RuntimeException Error in segment name.” I can include the java babble if necessary but it matches the output from teh syncronization run I first did and, obviously, I am not trying to exactly do the same thing in both the sync and compare actions.

    Again, I am well-versed in data modeling as well as using teh long-departed Oracle Designer as well as SQL Developer. However, I cannot get anything to work that will allow me to compare my OPS DB to my baselined data model which, as I said, both reside on the same Server – “Y.” I have been at this for 7 or so days now when not working other issues. One additional issue/question I have is if you have to have BOTH relational models in teh same Design to be able to compare them or can they be in different Designs as I am attempting here.

    Outta gas and outta ideas. So any help is greatly appreciated.

    Matt

    • thatjeffsmith Post Author

      Yeah, I’ll need to entire java error stack – but I suggest you post the scenario here, and one of our developers can take a look.

      >>ne additional issue/question I have is if you have to have BOTH relational models in teh same Design to be able to compare them or can they be in different Designs as I am attempting here.
      No, you just have to have both designs open.

      What you’re trying to do, should work.

      Extract system a to design a, system b to design b – compare designs.

      I don’t anything about your version of Data Modeler or your databases though.

    • Matt Sauter

      Jeff – great news. I was finally able to get the compare between my 2 relational models to work. My current question is how can I capture this ENORMOUS compare (we have a LOT of deltas) in a report format…if I can do that at all? Thanks.

      Matt

    • Matt Sauter

      Jeff – follow up from a few weeks ago. I am still doing data model compares, but recently, when doing them, I get the split screen for both models being compared (as expected), but it only shows Data Types Model with no tables listed to compare on either side. In my case, I only need to see table/column type differences, but I no longer get this when running the Compare/Merge Models option under Tools. Is there some preference I need to set or something else I am doing wrong? I looked under Tools–>Preferences, but felt like I had all of the correct options selected. And I have gotten this to run and show the tables differences before. Thank you.

    • Matt Sauter

      One additional ‘tack on’ if I may. I CAN get the compares to work when the models being compared are under the SAME DESIGN. Not so if the models are in two different DESIGNS. If you want to compare 2 models, do they have to be under the SAME DESIGN?

  7. Carlos Reply

    Hi Jeff,

    I have a student that is having a very strange issue when trying to run SQL queries that include the WITH clause.

    He’s using SQL Developer version 18.2.0.183 (on Win 10) and is connecting to an Express Edition Release 11.2.0.2.0 local database, and SQL Developer is not using an Oracle client.

    When he tries to run even a very simple statement like this one:

    WITH empl AS
    (
    SELECT *
    FROM employee
    )
    SELECT *
    FROM empl;

    He gets:

    ORA-00933: SQL command not properly ended
    00933. 00000 – “SQL command not properly ended”
    *Cause:
    *Action:
    Error at Line: 6 Column: 1

    In the Script Output panel, it shows:

    Error starting at line : 1 in command –
    WITH empl AS
    Error report –
    Unknown Command

    >>Query Run In:Query Result

    It appears that for some reason SQL Developer were ignoring the line where the WITH clause is used, since the log shows this as the SQL statement begin tried:

    (
    SELECT *
    FROM employee
    )
    SELECT *
    FROM empl

    I’m trying to help him remotely, via a text messaging system, but I’m not able to reproduce his issue, and I’ve been looking for some setting that could produce this kind of behavior, but I have not found anything.

    Do you have any idea of what could be happening there? Maybe you already saw something similar in the past…

    Thanks!

    • thatjeffsmith Post Author

      Sorry, I don’t have a good idea here. Same query works for me, on 12cR2 database, windows 10, and SQL Dev 18.2.

      Does F5 work for him?

      What if he ‘forces’ it to run everything by selecting all the text of the query and then hit ctrl+enter to execute?

    • Amin Adatia

      Check if there a query that is not terminated with a ‘;’ before the WITH query begins

    • Carlos

      Thanks for replying, Jeff.

      Forcing it to run everything by selecting the whole command doesn’t make a difference. It fails too. It’s absolutely weird.

      Not sure about running it as a script, but I will confirm it with him.

      Thanks again.

    • thatjeffsmith Post Author

      Have your student show you (screenshot!) of the entire editor. I think Amin’s guess below has the best chance of explaining this.

    • Carlos

      Thanks for the suggestion, Amin.

      Any query including the WITH clause fails, even when it is the only statement in the worksheet. That was one of my first thoughts too.

      It’s not a real error in the statement, definitely.

      Thanks!

    • Carlos

      I did ask him for a screenshot from the very beginning, because that is what I thought it was too.

      He sent me one with the command that was originally failing, which is slightly more complex, and after that I asked him to try a simpler one, which he said produced the same result.

      Here is the screenshot, if anyone is interested: http://www.image-share.com/ipng-3828-81.html

      (ugly formatting, but not the cause of a problem)

    • thatjeffsmith Post Author

      It looks fine to me other than all that white space – use the formatter!

      I wrote out the query verbatim and it seems fine.

      Ask him to also type it back out verbatim – if it was copied and pasted from the internet, perhaps there’s some characters in there screwing something up.

    • Carlos

      Thanks Jeff,

      Yes, it was copied from a website, but he said he already typed it in, and also tried simpler queries written manually, but the same thing happens. I copied it from the same site and ran it without issues.

      He had an odd rendering performance issue with SQL Developer a few weeks ago, which he solved by changing the Look and Feel from ‘Oracle’ to ‘Windows’, but he found that out after importing a whole bunch of settings from another machine.

      I’ve asked him to start over with a clean installation, and we’ll see what happens.

    • Amin Adatia

      Check the character set for the look-and-feel.

  8. Fleischer Zoltán Reply

    Hi Jeff,

    In 18.2 release there is a new feature:

    “Drag and Drop text from a grid will now give you quoted strings intended to aid with WHERE IN (‘x’, ‘y’, ‘z’, …) lists.
    This happens for ALL grids, including DESC popup dialog for tables”.
    But I want to drag my column names without quotes into the SELECT part of my query. Is there a way that I can switch off this new feature?

    Until that is not possible I reinstall previous release where that worked perfectly.

    Best regards,
    Zoltan Fleischer

  9. Nadir Doctor Reply

    Hi Jeff,

    When using Version 17.4.1.054 Build 054.0712 and providing a statement with joins involving (+), get message ORA-00920: invalid relational operator

    Is there some setting one can specify in the preferences to use non-ANSI syntax?

    Thank you

    • thatjeffsmith Post Author

      The ORA- message comes from the database, not from us. I think your query is wrong.

      This works no problem.

      What are you doing, exactly?

      SELECT * FROM employees, departments
WHERE employees.department_id = departments.department_id (+);
    • Nadir Doctor

      Thanks, Jeff – seems an extra space character somehow crept in a column name before an underscore character after obtaining that lengthy sql from an AWR report.

  11. Peter Reply

    Hi there,

    I just upgraded to SqlDeveloper version 18.2, but i recognized that the rendering of the gui is …, well, looks bad. Downgraded to version 18.1, everything is fine again. Is someone having the same effect ? Or any hints how to fix the rendering in version 18.2 ?

    Many thanks in advance,
    Peter

    • thatjeffsmith Post Author

      Without more details from you, I’m left to guess…THIS

      Some High DPI displays have scaling issues with Java FX in Java 9. If you are seeing scaling or resolution issues, try using Java 8 JDK instead.

    • Peter

      Oh, my mistake, didn’t see that. Using jdk 8 helps, thanks a lot !

      Regards

  12. Paul Reply

    This has been a longstanding problem. Using ‘DESC’ reports that the table object does not exist. However, a count of records is available. Why is that?

    C:\Oracle\sqldeveloper-17.3.1.279.0537-no-jre\sqldeveloper\sqldeveloper\bin\sql.bat

    SQLcl: Release 17.3.0 Production on Wed Aug 22 15:37:08 2018

    Copyright (c) 1982, 2018, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    DATAWH @ ODB01TEST2 >desc DIM_FCTS_CMC_CLHP_CLHI_CLHO
    ERROR:
    ——————————————————–
    ERROR: object DIM_FCTS_CMC_CLHP_CLHI_CLHO does not exist
    DATAWH @ ODB01TEST2 >select count(*) from DIM_FCTS_CMC_CLHP_CLHI_CLHO;

    COUNT(*)
    ———-
    11731031

    • thatjeffsmith Post Author

      What is DIM_FCTS_CMC_CLHP_CLHI_CLHO?

      We query the ALL_OBJECTS view looking for something that resolves to that when doing a DESC.

    • Paul

      SUBSTR(OWN OBJECT_NAME SUBSTR(OBJ
      ———- —————————— ———-
      DATAWH DIM_FCTS_CMC_CLHP_CLHI_CLHO TABLE
      DATAWH DIM_FCTS_CMC_CLHP_CLHI_CLHO_V VIEW
      SASUSER DIM_FCTS_CMC_CLHP_CLHI_CLHO SYNONYM
      DATAWH DIM_FCTS_CMC_CLHP_CLHI_CLHO DIMENSION

  13. Paul Muller Reply

    Perhaps this is more of a Windows 10 question than a SQL*Developer question; but I hope you can point me to an answer.
    I installed v18.2 on my new windows 10 laptop and then navigated to its location in windows explorer and pinned sqldeveloper.exe to the taskbar. I can start SQL*Developer by clicking on the taskbar icon; but when SQL*Developer starts up, windows puts a second SQL*Developer icon on the taskbar as well. This wouldn’t be a problem except a couple of times a day I click the first icon, trying to switch back to SQL*Developer from some other application and instead I start up a new instance!
    Do you know how I can prevent that second icon from appearing?

    • thatjeffsmith Post Author

      One solution: Instead of pinning it to the task bar, pin it to the start menu.

      It’s a ‘problem’ because SQL Developer let’s you launch as many instances of the application as you want…I think. The other thing I see is the shortcut I pin to the task bar doesn’t have a label, just the icon. So click on the item in the task bar with the ‘Oracle S…’ text after the logo.

  14. Daniel Looby Reply

    Installed 18.2 and did not bring over previous preferences (i.e.: clean slate). Went to disable some features to ‘improve performance and user experience’. After the restart the Welcome Page no longer displays. How do I restore the Welcome Page?

  15. Gustavo Molleri Reply

    Jeff,

    I have an ORDS problem that my group couldn’t figure it out.
    At our development environment we can create webservices within 3 ways using SQL Developer:
    1 – SQLDeveloper > connections > specific schema >”REST Data Services”;
    2 – SQLDeveloper > View > “REST Data Services” > Development > REST Development;
    3 – Executing sql script like “BEGIN ORDS.DEFINE_TEMPLATE(…”;

    But when we instaled ords at our production the first option wasn’t enabled anymore.
    Both schemas have the same grants.
    Is this a instalation configuration problem or a specific database configuration or grant that wasn’t set.

    Thank you

    • GustavoMolleri

      I have already tried that.
      It looks like my production user doesn’t have the right grant to the ords services.

  16. Bob Reply

    Hi Jeff,
    I am using Oracle SQL Developer 18.2 and using the spool syntax to export my data. My code is as follows:

    spool c:\Testdata.txt
    set sqlformat delimited |

    When the data is exported I have double quotes around my data. How can I have no quotes around the data, i.e; no text enclosures?

  17. Cyrill Reply

    Hi Jeff
    I’ve about >100 connections setup in sql developer, now the navigation is quite hard, is there a way that I can have a filter for just the open connection (~10-20) in the navigation for easier browsing over the objects? or any other great tips?
    thanks!

    • Cyrill

      Thanks for your response. Yes I’m using connection folders. That is also at the moment my workaround, I change the folder manually to “connected” and reimport all connection at sqldeveloper start to the folders before.

  18. Mukundhan Reply

    I connect to different Oracle databases in SQL Developer and query data in the SQL Worksheet. I rarely go to the objects tree under the database. When I make any new connection, SQL Developer takes a while to build the objects tree and after a while it opens the SQL Worksheet.

    Is there a way to customize, so that the SQL Worksheet opens immediately? I’m fine having to wait later if there is a need for me to go to the objects tree.

    • thatjeffsmith Post Author

      There’s no way to change the order of connection steps. But, you might want to take a look at the View > Log > Statements panel and work with your DBA on why it’s taking so long for one or more of those queries to run (not sure how long ‘a while’ is.)

    • Mukundhan

      Ok, little disappointing, but there are other several great features. Thanks Jeff for the quick response.

    • thatjeffsmith Post Author

      Do you understand my reply though? Instead of avoiding the problem on our end, it may be possible to FIX the problem on your end.

  19. Manju Chugani Reply

    Do we have an option of creating flow chart for PL/SQL packages?

  20. Daniel Stein Reply

    Hi Jeff,

    we finally migrated from 4.1 to 18.1 We love the enhancements, but there are two changes that we don’t like:

    1. In 4.1 we could cancel a running script (F5) during the execution and the fetch phase of an statement. In 18.1 cancelling of a scriptrun only works while the execution of an statement is still running. We tested Thin and Thick driver as you described here: https://www.thatjeffsmith.com/archive/2013/04/die-or-how-to-cancel-queries-in-oracle-sql-developer/
    It makes no difference
    The behaviour in 4.1 was nice because we didn’t have to limit “max rows to print” in the preference and yet had full control over the execution of the statement and the fetch….

    2. Setting linesize in “connection startup script” isn’t recognized anymore.

    Quote from manual:
    “Every time you click the Run Script icon, the linesize value is reset to the system default, which is the width of the Script Output pane. If you want to ensure a specific linesize is in effect, use the set linesize command (for example, set linesize=80) for each Run Script occurrence.” https://docs.oracle.com/database/sql-developer-18.1/RPTUG/sql-developer-concepts-usage.htm#f1_sqlviewer_html

    And we can’t even control the default.
    We liked the old behaviour better, because we set the pagesize, numwidth, linesize etc. in the “startup script” and were done…

    • thatjeffsmith Post Author

      1 is a bug (with the jdbc driver???), but I need to track that down and get you a better answer.

      2 can you tell me why a dynamic linesize setting is causing problems for you? the idea is we know how wide your screen is and we set the lineize for you.

    • Daniel Stein

      Hi Jeff,

      thanks for your answer.

      1. perfect. The bug is quite annoying

      2. We get results like the one in the following screenshot. The example query is “Select * from user_tables”. The line size doesn’t match the screen width. It is much larger (horizontal scrollbar).
      https://i.imgur.com/2S3HwS4.png

      But that is okay. The problem is, that results get truncated at the right side (see next screenshot, same sample query )
      https://i.imgur.com/xSUYiMf.png

  21. Andrew Merton Reply

    Hi Jeff –

    I have an Excel spreadsheet (.xlsx) (downloaded from Google Sheets) with ~150 rows (so not a big one). When I open it in the Import Data wizard it shows me the column headers, but no data, and I can’t progress through the wizard (the Next button does nothing).

    When I save it as a csv, I see the data in the wizard and all is well.

    I have tried saving a copy in a clean spreadsheet – no difference.

    Any suggestions as to what might be wrong?

    • thatjeffsmith Post Author

      Send me your file and I’ll take a look. Note that I’m on holiday until Thursday this week.

    • Andrew Merton

      Thanks Jeff – I just found that if I close the import dialog and look in the Logging Page, I see this:

      Error evaluating cell Students!A2 at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:347)

      There is a user defined function there, which I assume is either not working or invalid in WorkbookEvaluator. If I replace all the formulae using Paste/Values, SD then imports it cleanly.

      So I suppose I should log an enhancement request to get some indication of the error on the Import Dialog, rather than a silent failure and/or needing to go to the Logging page…

  22. Daniel Looby Reply

    Jeff,

    Co-worker says that in Toad (that other definitely more expensive program from [pick vendor of the week] she use to be able to get a list of all invalid packages/procedures and then push one button to attempt to compile them all. Yes, we see that we can compile them one by one, but is there a compile all option?

  23. Jeff Lamb Reply

    Hi Jeff,

    I love your site! I’ve been using SQL Developer for a long time to write procedures, functions and to do database but never really explored the other features. Reading through your site I’m learning a lot.

    Question: I’ve been playing with reports and I’ve got a few where I need/want to pass in multiple character values as the bind. It’s simple enough in a query but I must not be doing something right with quotes around each value since I’m not getting any data returned.

    Using a simple query to test with in a worksheet this works perfectly:
    Select first_name, last_name, phone_number from users where last_name in ( ‘Smith’,’Jones’,’Doe’);

    In my report I have this as the query:
    Select first_name, last_name, phone_number from users where last_name in ( :last_arg_in )

    Going with what I had in the query from the worksheet this is what I’m entering as the bind value when I run the report:
    ‘Smith’,’Jones’,’Doe’

    I’ve tried with quotes, without quotes, single and double quotes, and several combinations of multiple quotes but it doesn’t return any values. How can I enter multiple values in the bind?

    Thanks for any help you can provide

    • thatjeffsmith Post Author

      You need to this THIS.

      Also:
      I love your site! I’ve been using SQL Developer for a long time to write procedures, functions and to do database but never really explored the other features. Reading through your site I’m learning a lot.

      Thanks for the feedback! Really appreciate that. Nice way to kick off the weekend.

    • Jeff Lamb

      I’m learning all kinds of new stuff today, that worked perfectly!!

      Thank you!!

      Have a great weekend

  24. Mark Reply

    Hi Jeff,

    Quick question, if “UserA” has defined a PL/SQL package (spec & body) and grants “Execute” privileges to “UserB”. I believe it should be possible in SQL Developer for “UserB” to access the source of the package specification defined by “UserA”. When I do this in 18.2, the object appears but the source does not.
    Similarly granting “Debug” privilege on the package to “UserB” allows the package body to accessed in SQL developer, but again, no source code.

    Has this feature been removed in recent versions of the SQL Developer product?

    Many thanks,
    Mark.

    • thatjeffsmith Post Author

      No changes have been made here as far as I know.

      Here is the code we run as UserA to get the code for an object in UserB

      WITH src AS (
    SELECT ROWNUM,
           line,
           text,
           origin_con_id
      FROM sys.dba_source
     WHERE TYPE =:TYPE
       AND owner =:owner
       AND name =:name
) SELECT text
    FROM src,
         (
    SELECT MAX(origin_con_id) max_orig
      FROM src
)
   WHERE origin_con_id = max_orig
 ORDER BY line

      If you can’t get to DBA_ views, we default to ALL_ views. So it’s a matter of the ALL_SOURCE views figuring whether or not UserA can peak into UserB’s text.

    • Mark

      Hi Jeff,

      Thanks for the quick response. I have run the queries that you have provided in SQL*Plus and the result is 0 rows returned. I am now starting to believe that this is a more fundamental issue with the ALL_USER_SOURCE view. Is this something you can help with or is this a support issue?

      Many thanks,
      Mark.

    • thatjeffsmith Post Author

      No need to open a SR with Support.

      You need to: grant execute on the procedure in SchemaB to User A, and then ALL_SOURCE will have the code for that procedure when they open it.

      Or, grant ‘god’ powers to User A via SELECT ANY DICTIONARY priv or the DBA role.

      Now, if you actually DID grant execute privs and it’s not showing in ALL_SOURCE, then go open a bug, it’s a database problem.

      From the Docs “ALL_SOURCE describes the text source of the stored objects accessible to the current use” – accessible I think translates to ‘executable.’

      It might matter if you did the grant to the user or via a ROLE.

    • Mark

      Hi Jeff,

      Thanks again for the quick reply.

      I can confirm that I granted the EXECUTE privilege to “UserB” when this issue occurred. I did however forget to mention that I was using editions (EBR) in my test case. As well as ALL_SOURCE, I also tried ALL_SOURCE_AE but with the same results. This error occurs in SQL Developer and the data dictionary views.

      Attempting to replicate without using editions does show the package source for the package specification only as documented. I guess this is a bug with EBR and I will raise an SR for this unless you have any more ideas to try.

      Many thanks in advance,
      Mark.

  25. Amin Adatia Reply

    v18.2
    Oracle Exadata with 8 Nodes (11.2.0.3)

    I have Worksheets open for 4 Nodes and some have the Unshared Worksheets.
    I tried to open Real Time SQL Monitor — got the Window message about formatting the display/editor window. This was taking too long for my liking. Closed the Window. Every tab is now frozen while at the bottom there is a furiously moving bar with a blob moving left to right and back about Loading Real Time SQL Monitor. So I cannot do anything at all any more.

    1. If I kill the Application using Windows Task Manager then will my currently running Jobs — procedure calls (which take a long while to complete) still continue or will these jobs go into never land?
    2. Is there a limit as to how many Node Worksheets can be opened — I am trying to use a specific node to run this specific procedure with 6 different parameter sets
    3. Is it possible via right mouse click, from Monitor Sessions Tab, to go directly to the Real Time SQL Monitor row for the Query/Session

    Regards

    • thatjeffsmith Post Author

      You lost me here

      got the Window message about formatting the display/editor window

      I’m not familiar with this message.

      There are two ways to open the RTSM page, tools menu and on the DBA panel.

    • Amin Adatia

      The message is
      Waiting for Editor to initialize with a square dotted box with the dots moving around. It is now about a minute since I launched the Tool. In the status windows at the bottom the blue bar is moving left to right

Write A Comment