Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31

    Unhappy Unanswered: listener.ora, sqlnet.ora

    Hi, I'm very new to Oracle and I've just installed Oracle Server on my computer and also set up a database.

    I'm trying to set up Oracle such that I can link tables from Access or access it using ADODB. I'm not sure how to set up/create services on the listener so that I can do this....I have tried to change the listener.ora and the sqlnet.ora files, but it still doesn't work.

    I was wondering what values in the files do I have to change to set up a new service?

    in my tnsnames.ora file, I can access my database in SQL*Plus through this entry (made automatically when connecting through DBAStudio):

    Code:
    DEVDB_LOCALHOST =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA = (SID = devdb)(SERVER = DEDICATED))
      )
    When I put in a SERVICE_NAME in the CONNECT_DATE line, I can't connect to the db anymore.

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    No Operating System (OS) mentioned & no OS version. :-(
    No exact version of Oracle specified. :-(

    >it still doesn't work.
    Do you have any idea how many different ways "doesn't work" happens?
    LOTS!
    No specific error message(s). :-(
    No actual commands shown which fail. :-(

    My crystal ball is broken.
    You're On Your Own (YOYO)!

  3. #3
    Join Date
    Jan 2004
    Posts
    99
    below is a sample of the tnsnames.ora and listener.ora file, make sure you start the listener by doing the following. You may want to setup a ODBC connection if using excel.

    1) cmd
    2) lsnrctl
    3) start

    Sample TNSNAMES.ora
    ********************
    zdev.world =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MyWin2K)(PORT = 1521)) ) (CONNECT_DATA = (SID = ZDEV) ) )

    Sample LISTENER.ora
    *********************
    SID_LIST_ZDEV =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = ZDEV)
    )
    )

    ZDEV =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MyWin2k)(PORT = 1521))
    )

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    Thanks zaki_mtk, I will try your suggestion :)


    The OS is Windows 2000, and I'm using Oracle 8.1.

    "Doesn't work" means that the no connection can be made to the database (when linking from Access).

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Also for your ODBC driver.... If you are using Oracle 8.1.7.0 or .1
    there was a problem with it... You need to download Oracle ODBC 8.1.7.4 or .5 .... The problems were fixed there... Go to METALINK
    and get the download ... They are a little over a meg zipped.... Unzip the file and run the exe ... It will place files so that you can run the Oracle installer again and load the new ODBC Drivers...

    HTH
    Gregg

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    I'm using 8.1.7.0, Thanks gbrabham!

    I changed my listener.ora and I am able to access the db in Excel and Access. But the tables that I want are missing from the list. The ones that are in it are $AURORA$JIS$UTILITY$.SNS$PERMISSIONS$, PUBLIC.SYN, etc...

    From DBA Studio, I can see my tables under the Schema/Table/SYSTEM.

    Is there something I should configure so I can see my tables in the list?

    Thanks

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    I just tried added the DataSource in the user, file, and system DSN, but all doesn't show the tables that I have imported into the database. It only show those that are there by default.

    Are there settings that specify which users can see what tables?

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Did you install the "newer" ODBC drivers ??? If so, delete all DSN's and create 1 under system using 8.1.7.4 or better ODBC drivers.

    Under the ODBC setup ... Are you using the same username/password
    that the tables are created under (same schema name)??? If not,
    then on the Oracle side, grant SELECT to ODBC_USERNAME on
    your tables ...

    HTH
    Gregg

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    I just downloaded Oracle Driver 8.1.7.5.0 file.
    When I unzip the file, it saves onto my temp file....is there where it is supposed to go?

    Also, I have to install Oracle again after this?

  10. #10
    Join Date
    Jan 2004
    Posts
    99
    your not men't to be loading tables into the system or sys tablespace, I recommend you do the following.

    1) create a users tablespace
    2) create a new user and assign this as the default tablespace of the new user.

    the Aurora tables are java related tables, which you dont really want to play around with.

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Run the executable and put the files into another directory ... ie: c:\1\...

    Run the Oracle Installer and point to the above location for the "installable products" ... This will only find the installation for ODBC drivers and Oracle will take care of the rest for you ...

    Gregg

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    Hi,

    I am installing the updated Oracle Driver, but I have an error "Error in writing to file C:\oracle\ora81\BIN\SQORA32.dll".

    I don't seem to have this file installed on my computer. Did I forget to install something?

    btw, thanks for all your help, I really appreciate it

  13. #13
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    Actually, I found it.....sorry about that.....but for some reason, I can't install the new driver. The file isn't read-only...

  14. #14
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31
    Hi, I am able to access the tables under a new user. (I still can't install an updated ODBC driver though :( )

    Thanks sooo much to all of you!!

Posting Permissions

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