Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: query

  1. #1
    Join Date
    Feb 2008
    Posts
    6

    Unanswered: query

    How do i check which is my current database?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE    11.1.0.6.0      Production
    TNS for Linux: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Posts
    6
    Thanks for this info. My question however is different. Maybe i could not communicate it correctly. Suppose i have several databases and i have switched databases several times. How do i know which is my current database name?

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Code:
     SELECT name
    FROM v$database

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by barnaby
    Suppose i have several databases and i have switched databases several times. How do i know which is my current database name?
    Are you sure you are talking about a "database"? Several databases is something that is very seldomly used in Oracle.
    My guess would be, that you are talking about users (schemas).
    How exactly are you switching your "database"?

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by shammat
    Are you sure you are talking about a "database"? ...snip...
    My guess would be, that you are talking about users (schemas).
    "?
    Good point.
    Quote Originally Posted by shammat
    Several databases is something that is very seldomly used in Oracle.
    Not such a good point. What about Test, Dev and Live databases? What about DW and OLTP systems? You use the same database for both systems? I hope not

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by pablolee
    Not such a good point. What about Test, Dev and Live databases? What about DW and OLTP systems? You use the same database for both systems? I hope not
    Right, but do you "switch" from dev to prod often ? I hope not
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by RBARAER
    Right, but do you "switch" from dev to prod often ? I hope not
    True, but if I was being really picky, I would say that I wasn't commenting on the 'switching' I was commenting on the
    Several databases is something that is very seldomly used in Oracle
    But then of course you could argue context ...

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pablolee
    Not such a good point. What about Test, Dev and Live databases?
    The name "database" has a very specific meaning in the Oracle world. When talking about Test, Dev and Production this is (most of the time) done using different installations on different servers, not done using different databases (i.e. instances) on the same server.

    From my experience, most of the time when someone talks about a "database" in the Oracle worlds, he/she comes from a SQL Server/MySQL background and indeed means a user (or schema) in Oracle rather than a database.

    So basically you are right that multiple databases/instances are used for multiple environments but usually they reside in different Oracle installation.
    I tend to use the term database only when talking about multiple instances in the same Oracle installation

  10. #10
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by shammat
    The name "database" has a very specific meaning in the Oracle world. ...snip...
    I tend to use the term database only when talking about multiple instances in the same Oracle installation
    You are quite correct to say that Database has a very specific meaning in the Oracle world. Unfortunately you are misusing the term. Database, in the Oracle world actually refers to the physical files associated with the Oracle SERVER. Oracle SERVER is the combination of Instance (Memory structures (the SGA) and background processes) and the database (the physical files). Therefore the term database in the strictest sense of the word [i]in the Oracle world[i] means the datafiles, online redo log files and control files (and NO password files, parameter files and archived redo log files are not included withing the Database umbrella) I am assuming from your post that when you refer to a database, you are actually referrring to the Oracle Server. If I was to hear someone talk about multiple instances in the same installation my immediate thoughts would turn to Real Application Clusters. But once again, I am being picky, picky, picky

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Disclaimer: I'm by no ways an Oracle DBA. All my information is gathered from the manual with very limited hands-on experience.

    Quote Originally Posted by pablolee
    I am assuming from your post that when you refer to a database, you are actually referrring to the Oracle Server.
    I always had the impression that single Oracle Server ("installation") can host more than one instance/database (without having a RAC).
    From a user's point of view this is the SID that I supply when connecting to the database (e.g. using JDBC)

    If I was to hear someone talk about multiple instances in the same installation my immediate thoughts would turn to Real Application Clusters. But once again, I am being picky, picky, picky
    Hmm.
    The Concepts manual says "Multiple instances can run concurrently on the same computer, each accessing its own physical database".
    RAC may come into play when more than one instance accesses the same database: "In large-scale cluster systems, Real Application Clusters enables multiple instances to mount a single database."

    I have seen (actually worked with) installation where exactly this was the case. Same server (IP address), same listener port, different SIDs. This was necessary because the application had the schema name hardcoded and we needed a test and integration installation on the same physical server. The instances could be started and stopped independently from each other. I wasn't involved in setting that up, but I have met this kind of installation already with 8i and 9.2 where RAC was not available (at least with 8i).

    Maybe I'm mistaking this setup as multiple instances where in fact it was something else (see my disclaimer at the top )

  12. #12
    Join Date
    Dec 2007
    Posts
    253
    This was my fault. My meaning when I wrote:
    f I was to hear someone talk about multiple instances in the same installation my immediate thoughts would often turn to Real Application Clusters
    was NOT that when someone mentions multiple instances the are talking about RAC, but rather that this is almost instantly where (usually incorrectly) my mind leads me, just as, when you saw the OP reference multiple databases, you automatically thought of Schemas (in which I believe that you are most likely correct). I had just spent another few minutes writing about completely inconsequential semantic differences between terms and decide to delete them because it'll just confuse matters for the OP, which is pointless (that and I might be wrong )

  13. #13
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pablolee
    I had just spent another few minutes writing about completely inconsequential semantic differences between terms and decide to delete them because it'll just confuse matters for the OP, which is pointless
    You are absolutely right!
    I think Oracle has chosen a very bad terminology for these things. The biggest problem I have with these terms is, that they are not defined clearly at the beginning of e.g. the Concepts manual ("A database is...", "An instance is...").
    One needs to read and fully understand the whole set of books to be able to see the subtle differences as the information is spread across chapters and books.

  14. #14
    Join Date
    Dec 2007
    Posts
    253

    Talking

    Quote Originally Posted by shammat
    You are absolutely right!
    My wife will tell you that that is a very rare occassion . I don't have too much of a problem with the terminology that Oracle has chosen (although there is always arguments to raise of "well, surely calling it ... would make more sense" but that is true of pretty much any system, but I agree that there can be a lot of confusion over terms (this thread being a case in point) However, if it was easy, it wouldn't be any fun!

  15. #15
    Join Date
    Feb 2008
    Posts
    6

    query

    I understand my terminology was not correct when i asked my question, but i see that it has created an interesting discussion. I am new to Oracle (have had some introductory experience with MS SQL and Sybase)and am trying to get the Oracle concepts clear.
    I am confused as to what an instance is and how do i start it? How does it differ from a connection (so commonly used in MS SQL and Sybase)?

Posting Permissions

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