Oracle service name where to find

Active 2 years ago. Viewed k times. I do not have access to the server and have no local tnsnames. Anders Jakobsen Anders Jakobsen 1 1 gold badge 10 10 silver badges 15 15 bronze badges. AndersJakobsen:you dint have access to the data dictionary views ,ask your DBA to give access or send you the service name. And may be sql developer support the sqlplus commands ,as toad also support that F5 is the command in toad to run such commands. Show 1 more comment. Active Oldest Votes.

Community Bot 1 1 1 silver badge. Goufalite Goufalite 2, 3 3 gold badges 16 16 silver badges 28 28 bronze badges. Although in some instances they could happen to be have the same value.

Add a comment. Connect to the server as "system" using SID. The full service name didn't work for me: jdbc:oracle:thin: localhostorcl. Marmite Bomber Marmite Bomber I gathered, but the fact it doesn't work when running on the same machine makes this answer much less useful than others. It is important to distinct the connected service and the available services.

Carl Bosch Carl Bosch 1, 16 16 silver badges 14 14 bronze badges. Have an upvote. Dhyan Mohandas Dhyan Mohandas 10 10 silver badges 12 12 bronze badges. Moiz Sajid Moiz Sajid 9 9 silver badges 18 18 bronze badges. Rahul Balu Rahul Balu 1. Andrea Diggelmann Andrea Diggelmann 25 7 7 bronze badges. Sign up or log in Sign up using Google.

Sign up using Facebook. A connect descriptor is comprised of one or more protocol addresses of the listener and the connect information for the destination service in the tnsnames. Example shows a connect descriptor mapped to the sales database. As shown in Example , the connect descriptor contains the following parameters:. HOST parameter, which identifies the host name.

The host is sales-server. PORT parameter, which identifies the port. The port is , the default port number. The SID is sales. The destination service name is a database service named sales. In the example, sales. The instance name is optional. End-to-end connectivity using IPv6 in Oracle Database 11 g requires the following configuration:.

The database instance configured for Oracle Net Listener must listen for connection requests on IPv6 endpoints. For a given host name, Oracle Net attempts to connect to all IP addresses returned by Domain Name System DNS name resolution until a successful connection is established or all addresses have been attempted.

Suppose that in Example the sales-server host is an IPv4-only host that is accepting client connections. DNS maps sales-server to the following IP addresses:. In this example sales-server does not support IPv6 connectivity, so this attempt fails. Oracle Net proceeds to connect to the IPv4 address, which succeeds.

The address portion of the connect descriptor is the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server.

The listener receives incoming client connection requests and sends these requests to the database server. After the connection is established, the client and database server communicate directly. The listener is configured to accept requests from clients at a protocol address. This address defines the protocol the listener is listening on and any other protocol-specific information.

For example, the listener could be configured to listen at the following protocol address:. Client connect descriptors configured with this same protocol address can send connection requests to this listener.

The connect descriptor specifies the database service name with which clients seek to establish a connection. The listener knows which services can handle connection requests because an Oracle database dynamically registers this information with the listener. This process of registration is called service registration. Registration also provides the listener with information about the database instances and the service handlers available for each instance. A service handler can be a dispatcher or dedicated server.

This feature can be useful if you have an Oracle Real Application Clusters configuration. For example, the following connect descriptor specifies the instance name sales1 that is associated with sales. Clients that always want to use a particular service handler type can use a connect descriptor to specify the service handler type.

When the listener receives the client request, it selects one of the registered service handlers. Depending on the type of handler selected, the communication protocol used, and the operating system of the database server, the listener performs one of the following actions:. Sends a redirect message back to the client with the location of the dispatcher or dedicated server process. The client then connects directly to the dispatcher or dedicated server process.

Spawns a dedicated server process and passes the client connection to the dedicated server process. After the listener has completed the connection operation for the client, the client communicates directly with the Oracle database without the listener's involvement. The listener resumes listening for incoming network sessions. However, the client will use a dedicated server if no dispatchers are available.

If database resident connection pooling is not enabled on the server, then the client request is rejected, and the user receives an error message. Oracle Call Interface Programmer's Guide and Oracle Database Administrator's Guide for additional information about enabling and configuring database resident connection pooling.

Service handlers act as connection points to an Oracle database. A service handler can be a shared server process or a dedicated server process, or pooled.

The shared server architecture uses a dispatcher process to direct client connections to a common request queue. An idle shared server process from a shared pool of server processes picks up a request from the common queue. This approach enables a small pool of server processes to serve a large number of clients.

A significant advantage of the shared server model over the dedicated server model is reduced system resources, enabling support of an increased number of users. The listener uses the dispatcher as a type of service handler to which it can direct client requests. When a client request arrives, the listener performs one of the following actions:. Hands the connection request directly to a dispatcher.

Issues a redirect message to the client, containing the protocol address of a dispatcher. The client then terminates the network session to the listener and establishes a network session to the dispatcher, using the network address provided in the redirect message.

The listener uses direct hand off whenever possible. Redirect messages are used, for example, when dispatchers are remote to the listener.

Figure shows the listener handing a connection request directly to a dispatcher. In a dedicated server configuration, the listener starts a separate dedicated server process for each incoming client connection request dedicated to servicing the client. After the session is complete, the dedicated server process terminates.

Because a dedicated server process has to be started for each connection, this configuration may require more system resources than shared server configurations. A dedicated server process is a type of service handler that the listener starts when it receives a client request. The dedicated server informs the listener of its listening protocol address. The listener passes the protocol address to the client in a redirect message and terminates the connection.

The client connects to the dedicated server directly using the protocol address. One of the preceding actions is selected based on the operating system and the transport protocol. If the client and database exist on the same computer, then a client connection can be passed directly to a dedicated server process without going through the listener. This is known as a bequeath protocol. The application initiating the session spawns a dedicated server process for the connection request.

This happens automatically if the application used to start the database is on the same computer as the database. Figure shows the listener passing a client connection request to a dedicated server process. The listener starts a dedicated server process, and the dedicated server inherits the connection request from the listener. Figure shows the role of a dedicated server in a redirected connection.

The listener provides the location of the dedicated server process to the client in a redirect message. The client connects directly to the dedicated server.

Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios in which an application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling pools "dedicated" servers.

A pooled server is the equivalent of a server foreground process and a database session combined. Database resident connection pooling complements middle-tier connection pools that share connections between threads in a middle-tier process. In addition, it enables sharing of database connections across middle-tier processes on the same middle-tier host and even across middle-tier hosts.

This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers. Having a pool of readily available servers has the additional benefit of reducing the cost of creating and closing client connections. Database resident connection pooling provides pooling for dedicated connection s across client applications and processes.

This feature is useful for applications that must maintain persistent connections to the database and optimize server resources such as memory. Clients obtaining connections out of the database resident connection pool are persistently connected to a background process, the connection broker, instead of the dedicated servers.

The connection broker implements the pool functionality and performs the multiplexing of inbound connections from the clients to a pool of dedicated servers with sessions. When a client must perform database work, the connection broker picks up a dedicated server from the pool and assigns it to the client. Subsequently, the client is directly connected to the dedicated server until the request is served.

After the server finishes processing the client request, the server goes back into the pool and the connection from the client is restored to the connection broker. A naming method is a resolution method used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service.

Users initiate a connection request by providing a connect string. A connect string includes a user name and password, along with a connect identifier. A connect identifier can be the connect descriptor or a name that resolves to a connect descriptor. The connect descriptor contains:. A database service name or Oracle system identifier SID.

The following CONNECT command uses a connect string that has a complete connect descriptor as the connect identifier instead of a net service name. The string should be entered on a single line. It is shown on two lines because of page width. One of the most common connect identifiers is a net service name , a simple name for a service. When net service name sales is used, connection processing takes place by first mapping sales to the connect descriptor.

This mapped information is accessed by naming method s. The following naming methods are available:. Selecting the appropriate naming method for mapping names to connect descriptors depends upon the size of the organization. For large organizations with several databases, use directory naming to store names in a centralized directory server. For an Internet network, configure the application Web servers needed to connect to the databases with the local naming method.

Table summarizes the relative advantages and disadvantages of each naming method and provides recommendations for using them in the network. Table Naming Methods: Advantages and Disadvantages. Stores net service names and their connect descriptors in a localized configuration file named tnsnames.

Disadvantage : Requires local configuration of all net service name and address changes. Stores connect identifiers in a centralized LDAP-compliant directory server to access a database service.

Centralizes network names and addresses in a single place, facilitating administration of name changes and updates. This eliminates the need for an administrator to make changes to what potentially could be hundreds or even thousands of clients. Requires minimal user configuration.