Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2001
    Posts
    5

    Unanswered: Can't select from table after DTS from MSSQL

    I exported a sql database into 'test' users schema with the test user / pass used during DTS. I can browse the schema using toad or schema mgr using the test user's credentials. I can not select from any of the imported tables.

    I can 'CREATE TABLE ...' and create a new table using the test user and then successfully select from that one?

    I have checked the users permissions but can't see anything I am missing.

    These are the permissions I have granted the user
    GRANT CREATE SESSION TO test;
    GRANT CREATE TABLE TO test;
    GRANT CREATE VIEW TO test;
    GRANT CREATE SEQUENCE TO test;
    ALTER USER test QUOTA unlimited ON test;

    Any help would be gladly appreciated, I am sure it is something really basic


    Regards
    Harry

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    If you are logged on with the user that is the owner of the tables, you must be able to select from these tables. You don't need any special privileges for that.

    All tables that a user has created, the user has all privileges on that table.

    Please specify the problem further (error-message?)
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Oct 2001
    Posts
    5
    I get an 'ORA-00942: table or view does not exist' when I try to select from the table.
    I have tried prefixing the table with the owners name (test.tablename) which produces the same results.
    Could it be something that the DTS transfer has done or not set?

    Regards,
    Harry

  4. #4
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Ah, I think i know what the problem is.
    I think the table name is created with Capitals or spaces.

    (CREATE TABLE "This Table" ...)
    if you do SELECT * FROM This Table; it will say ora-942
    SELECT * FROM "This Table"; will work.

    You should try putting "" around the table name.

    SELECT * FROM "This Table".

    (I think it's a bad thing the DTS creates table names like this ... )

    Let me know if this was the problem.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  5. #5
    Join Date
    Oct 2001
    Posts
    5
    Quite right

    Though the table was lower case and had no spaces (acc_fd2), I use underscores instead of spaces. Is this bad practice in Oracle?
    Is uppercase or lowercase the preferred?

    Many thanks
    Harry

  6. #6
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Oracle default will use CAPITALS.

    If you type CREATE TABLE Test

    The tablename will be TEST

    select * from Test and select * from TEST will work.

    If you use "":

    create table "test"

    the tablename will be test

    select * from test and select * from TEST will fail
    you need select * from "test".

    Therefore, best is NOT to use "" when creating a table, so Oracle converts all to UPPERCASE tablenames.

    Greetz

    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •