ORACLE DataGrip, schema content completely missing
Hi all,
As in the post title, I created an Oracle (using 11g) connection from the Database tool window and DataGrip finds all underlying object for the available schemas, except for the schema that I created manually, which is of course the one I need. Something must be preventing this schema from being fully accessible, but I have no clue what. I granted all privileges to DEV1, just in case, but it didn't work. Tried various filtering options (in the figure below, All schemas was selected, but DEV1 still has no underlying objects found). Tried checking/unchecking Introspect using JDBC metadata. Tried to forget the schema cache. Tried a couple more things, but to no avail. Also, I can open DEV1 in an editor and I can query DUAL (getting X in column DUMMY), but not table ACCOUNT, which belongs to DEV1 (if I try, I get [42000][942] ORA-00942: table or view does not exist).
Since I'm an IntelliJ (using 2017.2.2) newbie, please explain where I can find stuff when asking me to post more info, like logs, versions, etc.
Any suggestions would be greatly appreciated!
Thanks in advance,
Elio
Please sign in to leave a comment.
Hi,
What is your user permissions for DEV1? What query did you invoke to provide user's rights?
Could you provide any IDE logs?
One can find logs by navigating to Help -> Collect and Show logs in Explorer:
Thank you.
Thanks for answering Vasily!
This is how I create and authorise DEV1. Specifically, after I couldn't see the schema content, I added grant all privileges, but to no avail:
create user dev1 identified by dev1;
grant create session to dev1;
grant all privileges to dev1;
The only relevant lines of the logs that I find after I let DataGrip crawl into the schema (by the way, I do it from within IntelliJ) are in idea.log, as follows:
2017-09-02 02:11:59,034 [272463352] INFO - urce.DatabaseConnectionManager - Connecting as: dev1
2017-09-02 02:11:59,034 [272463352] INFO - urce.DatabaseConnectionManager - Connecting to: jdbc:oracle:thin:@localhost:1521:XE
2017-09-02 02:11:59,530 [272463848] INFO - .introspection.OraIntrospector - Oracle Introspector: initialized for Oracle version 11.2.0.2 driver version 12.1.0.2 using SLOW mode
2017-09-02 02:11:59,541 [272463859] INFO - .introspection.OraIntrospector - Oracle Introspector: initialized for Oracle version 11.2.0.2 driver version 12.1.0.2 using SLOW mode
2017-09-02 02:12:06,307 [272470625] INFO - .diagnostic.PerformanceWatcher - Pushing properties took 5ms; general responsiveness: ok; EDT responsiveness: ok
2017-09-02 02:12:06,336 [272470654] INFO - .diagnostic.PerformanceWatcher - Indexable file iteration took 29ms; general responsiveness: ok; EDT responsiveness: ok
This is settings.txt, but I think it refers to all of IntelliJ:
=====DEPLOYMENT SETTINGS=====
Default Server : null
=====ADDITIONAL SETTINGS=====
Exclude items by name : .svn;.cvs;.idea;.DS_Store;.git;.hg
Operations logging : BRIEF
Stop operation on the first error : false
Overwrite up-to-date files : true
Preserve file timestamps : true
Delete target items when source do not exist : false
Create empty directories : false
Prompt when overwriting or deleting local items : true
Upload changed files automatically : Never
Upload external changes : false
Override default permissions on files : -1
Override default permissions on folders : -1
Warn when uploading over newer file : NONE
Notify about remote changes : false
The other log files have an older timestamp than my latest attempt to crawl into the dev1 schema, so I guess they are not relevant. Correct me if I'm wrong.
Does that tell you anything? Let me know if I can provide more info!
Thanks,
Elio
Ok it seems this is solved. I just had done something wrong with the creation of the tables, I created them as "CREATE TABLE PRODUCT..." from DEV1, supposing DEV1 would automatically be the owner. I recreated them with "CREATE TABLE DEV1.PRODUCT..." and now it seems it's working. I still was convinced that creating them from DEV1 would be right and enough, but maybe something went wrong with DEV1 having too many rights. It also seems I have to improve my understanding of ownerships/rights in Oracle to master such issues.
Thanks for your help!
Elio
Well, more info for the interested ones: I had created a USER, not a SCHEMA... my fault. DataGrip apparently allows you to select from a list of users, but calls these users schemas, which added to the confusion. Would be nice if "Users..." were displayed instead of "Schemas...", given that that is what is actually filtered upon, as far as I know...
I'm getting somewhat confused, but maybe we are going off topic a bit: CREATE USER automatically creates its SCHEMA, but, after I CREATE TABLE DEV1.PRODUCT, when I SELECT * FROM PRODUCT, being logged in as DEV1, I won't get the content of DEV1.PRODUCT, unless I SELECT * FROM DEV1.PRODUCT. This was the root cause of my issues. Now, by creating the table as CREATE TABLE DEV1.PRODUCT I solve my original issue with DataGrip, but it's still beyond me what's causing me not being able to select directly from PRODUCT instead of DEV1.PRODUCT, since it should be a table owned by DEV1 (the user) and created on DEV1 (the SCHEMA).
Any suggestions appreciated, as it would bring more clarity to me and to possible future readers, as well.
Elio