Oracle which instance am i connected to

Close ; conn. Dispose ; Response. Next Recommended Reading. Windows 10 Vs Windows Visual Studio Vs Visual Studio Understanding Matplotlib With Examples. Understanding Numpy With Examples. C Evolution. Everything That Every. March 27, - am UTC. No, I'd need clarification from you. What is "it". There are lots of "its" on this page. Implement "what"? The reason so many of us ask about putting a standby database on the same server as the primary database, is that it is homework for Oracle DB Admin courses, which require the creation of a standby DB, and most of us only have access to a single system.

Not easy. Not there yet. There is a document on your site about doing so on a Unix system, but doesn't quite get me there on my Windows System. Thank you very much!

November 17, - am UTC. And -- unless you've actually set it up on two different machines -- in my mind -- you haven't done your homework as yet. Tom: 1. It seems that usually it is the same as the instance name.

I think it is database. THank you,. November 17, - pm UTC. You would need to read the networking guides in order to understand the subtle difference. HOST really has nothing to do with any of these questions. Great explanation! On windows do we have an option using DBCA to create only the database and not a new instance so that I could use the existing instance to mount either of the databases.

Thanks very much indeed. January 20, - pm UTC. DBCA doesn't create an instance. So, DBCA isn't creating an instance, it creates a database.

The instance is fleeting, do a shutdown and goodbye instance. So, just don't start the service which is just a definition -- the service itself consumes no resources and you'll have no instance. Follow up question. You also said "i can have two instances in different homes on a single machine with the same sid".

If it is possible what will the output of "ps -ef grep pmon" Thanks Sai. Something more.. Hi Tom, No, I don't understand. Please bear with me. So, I have got 2 services running each for the database. Now when I shutdown one of the services that doesn't mean that the other database will also be shutdown.

Am I right? If yes, then how come you say "just don't start the service and you'll have no instance" , as stopping one of the services does not mean that the instance is gone since the other database is still operational. Thanks for your patience. You'll always have TWO databases unless you erase them. You'll have as many instances as you want at any point in time by starting and stopping these services.

I'm not understanding your confusion. Each instance is separate and distinct, you start them independent of eachother. They have nothing whatsoever to do with eachother. When you stop the service -- goodbye instance. Database -- oh, that is still there, instance -- gone. So -- what exactly is the question? Tom, This is the confusion. In your earlier reply you said that the DBCA does not create an instance but only database. That's fine. Secondly, if I shutdown one of the services it means that there is 1 instance and 2 database.

Now, this means that I can shutdown the database for which I service is currently running and startup the other database for which I just shutdown the service. Is this correct? Thanks again for your patience. I appreciate it. I was responding to your comment: So, I have got 2 services running each for the database.

The services are running you said. Hence you have two instances going. You do not shutdown a database -- you shutdown an instance, but anyway I don't know how to say it. An instance is just a process running. For each database you want accessible on your machine, you will have an instance running. If you do not want to have access to that datbase -- just stop that instance, easiest way on windows, stop the service. Don't let the service confuse you. It is nothing, a windows artifact, not meaningful.

Tom, I totally understand what you are saying about an instance and the database but this Windows services has confused me a lot. Hopefully this will clear the confusion. I said "So, I have got 2 services running each for the database" because when I create a database using a DBCA it by default creates a service for the new database. I infer this from you following statement. Do you see where I am coming from? I would be very thankful to you for clarifying this confusion. As they say in the wizard of oz "ignore that man behind the curtain".

They are meaningless -- they are just things windows needs to start these things up in the background. A service is a windoze thing - it is not anything to do with the database other than on that platform we need them in order to get started up in the background Checking oracle sid and database name Ask Question.

Asked 10 years, 5 months ago. Active 21 days ago. Viewed k times. I want to check SID and current database name.

Any idea for above two problems? Adnan Adnan 4, 13 13 gold badges 42 42 silver badges 53 53 bronze badges. Add a comment. Active Oldest Votes. Stefan van den Akker 5, 7 7 gold badges 41 41 silver badges 58 58 bronze badges. V4Vendetta V4Vendetta 35k 7 7 gold badges 73 73 silver badges 81 81 bronze badges. Thanks for quick response. Are instance and oracle SID same thing? Adnan They need not be the same since there coould be multiple instances of the DB running on a single machine they are identified by SID — V4Vendetta.

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. The user can provide only the name of the database host to establish a connection.

Eliminates the need to create and maintain a local names configuration file tnsnames. Disadvantage : Available only in a limited environment, as indicated in the Recommended for column.

Advantage : Enables administrators to load Oracle net service name into their native name service using tools and utilities with which they are already familiar. Disadvantage : Requires a third-party naming services that cannot be administered using Oracle Net products.

After the network components are started you should be able to make a connection across the network. How you make a connection depends upon the naming method , and the tool used for the connection.

The connect string takes the following format:. On most operating systems, you can define a default connect identifier. When using the default, a connect identifier does not need to be specified in the connect string. Connect identifiers used in a connect string cannot contain spaces, unless enclosed within single quotation marks ' or double quotation marks ".

In the following examples, a connect identifier and a connect descriptor that contain spaces are enclosed within single quotation marks:.