Avatar billede fredand Forsker
31. august 2011 - 12:55 Der er 6 kommentarer og
1 løsning

Why do I get systemtables from Oracle?

Hello!

I'm playing around with some code that retrive metedata from an oracle.

I have created a tablespace and a user like:
DROP TABLESPACE testing INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE testing
  LOGGING
  DATAFILE 'C:\testing.DBF'
  SIZE 32M
  AUTOEXTEND ON
  NEXT 32M MAXSIZE 2048M
  EXTENT MANAGEMENT LOCAL;


CREATE USER testing_user IDENTIFIED BY testing_password DEFAULT TABLESPACE testing;

GRANT CREATE SESSION TO testing_user;
GRANT CREATE SEQUENCE TO testing_user;
GRANT CREATE PROCEDURE TO testing_user;
GRANT CREATE TABLE TO testing_user;
GRANT CREATE VIEW TO testing_user;
GRANT UNLIMITED TABLESPACE TO testing_user;

In this database I got a table like:

DROP TABLE Countries CASCADE CONSTRAINTS;
CREATE TABLE Countries
(
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) TABLESPACE testing;

When I try to fetch the tables like:
  String[] types = {"TABLE"};
  ResultSet resultSet = databaseMetaData.getTables(null, null, "%", types);

  ArrayList<String> tableList = new ArrayList<String>();
    while( resultSet.next() )
    {
        String tableName = resultSet.getString(3);
        System.out.println(tableName);
}

I get the table Countries but I also get tables like:
SDO_COORD_AXES

I guess my user got to much privileges?

How to set him so he only get to see tables I create?

Best regards
Fredrik
Avatar billede arne_v Ekspert
31. august 2011 - 16:32 #1
Maybe resultSet.getString(4) can be used to filter.
Avatar billede fredand Forsker
31. august 2011 - 20:32 #2
Hello Arne!

That was a good idea, that I really thought would work but it seems like they are typed table as well.

A snippet from a output:

BIN$q7nQ6nRsHFngQAB/AQAxfw==$0 TABLE
BIN$q7nQ6nRxHFngQAB/AQAxfw==$0 TABLE
COUNTRIES TABLE
SDO_COORD_SYS TABLE
SDO_CS_SRS TABLE
SDO_DATUMS TABLE
SDO_DATUMS_OLD_SNAPSHOT TABLE

The code:
while( resultSet.next() )
{
  String tableName = resultSet.getString(3);
  String tableType = resultSet.getString(4);
  System.out.println(tableName + " " + tableType);

To me it seems like these tables should be more like system_tables.

Btw I when I run my set up scripts, see snippets in first post I do it like:
    sqlplus -s sys/testing_password@testing as sysdba @drop_user.sql
   
sqlplus -s testing_user/testing_password@testing @init.sql

At least in the first line I do it like sys (creating the user) could this be something? (But correct me if I'm wrong when I say that I hardly can not create the user since I do not got any other user to use?)

In the second command I use the newly created user to set up the tables.

If you got any ideas just let me know.

Best regards
Fredrik
Avatar billede arne_v Ekspert
31. august 2011 - 20:45 #3
Anything useful in column 1 or 2 (catalog and schema).
Avatar billede arne_v Ekspert
15. oktober 2011 - 00:02 #4
Any progress?
Avatar billede fredand Forsker
15. oktober 2011 - 09:58 #5
Hello Arne!

No actually no progress at all.

The idea is that should be a general solution between different types of databases.

If I do progress I will get back with new status.

Best regards
Fredrik
Avatar billede fredand Forsker
13. januar 2012 - 07:37 #6
Hello Arne!

After some search and talk at Oracle I found out that the call should be:

String[] types = {"TABLE"};
  ResultSet resultSet = databaseMetaData.getTables(null, USERNAME, "%", types);

And that USERNAME must be in same upper and lower case as when the user got created.

If I get it right I must use "" around the name "Fredrik" when created else Oracle turn it into uppercase.

CREATE USER Fredrik...
I think will be a user with name FREDRIK

CREATE USER "Fredrik"...

I think will be a user with name Fredrik

How ever as you said there was something really helpfull in parameter 2.

So please give a svar so I can reward you Arne!

Best regards and thanks alot!
/Fredrik
Avatar billede arne_v Ekspert
14. januar 2012 - 02:06 #7
ok
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester