Some terminology to help: - instance: an installation of oracle (can be multiple instances on 1 host, one instance on a host, etc.) - schema: a user that owns objects (schema name matches username); can be many schemas in a given instance, but certain system instances are always present. - tablespace: a storage container (defines data files and it's own set of storage characteristics) - user: an account to connect to oracle
Each user has a default tablespace (new objects go here unless explicity defined otherwise) A database link lets a user on one instance connect to some remote instance. Assume you are logged into oracle as aradmin. If you want to retrieve data from a table in a different schema, arsexternal, your statement would look like this: select * from arsexternal.table Axton Grams On Fri, Sep 7, 2012 at 2:36 PM, Jason Miller <[email protected]> wrote: > ** Thanks Joe, Rick and Axton! > > Axton pretty much summed up the reasons why I have been considering this > and described our ARSystem DB at the same time :) > > I have only scratched the surface when working with Oracle had have never > completely understood the difference between a user's schema and a > different DB. I guess since the schema falls within the DB the > files, maintenance and configuration are the same where as a completely > different DB would have different file and configuration properties? > > I think the issue mentioned not being able to alter the AR maintained > views is a little different and doesn't change in the proposed setup. We > already know to keep a script handy to reset the grants after the > drop/create and running the script is part of our move to production > procedure. It really depends on what we are doing if we use the T table or > the view. > > Jason > > On Fri, Sep 7, 2012 at 11:22 AM, Axton <[email protected]> wrote: > >> ** I've been doing this for years. I've traditionally worked in Oracle >> shops, where we create a separate schema on the same instance. The data >> never goes over the network. All custom db objects go into this schema and >> not the AR owned schema. Each schema has a schema owner (schema name = >> account name), so there are some grants that have to be given to allow >> cross-talk, but the maintenance is minimal. The downside to using the >> views is that views can not be altered, only created and dropped. What >> this means is that if you apply a grant to a view that AR owns (a db view >> for a form), and you add a field to that form, the grants to that view are >> gone. For this reason, I use the T tables when constructing views. >> Tables can be altered, and this is how Remedy does it, so the grants >> persist through form modifications. >> >> As to the statement about lob/index storage, etc.; this is typically done >> using separate tablespaces. Tablespaces are like separate storage >> containers; probably the easiest way to think about them is that they are >> separate files that the db uses to store stuff. There are some >> characteristics that you can define at the tablespace level (extent size, >> lob characteristics, etc.) that make it advantageous to use separate >> tablespaces for different objects. >> >> The reason I separate these things out is for ease of maintenance. Have >> you ever walked into a shop that didn't do this and had an active Remedy >> system that was 6 years old and under constant development? It get's messy >> in the db. DB objects pile up; sometimes they are no longer used, and in >> the end, you have a pile of db objects and you don't have a good idea if it >> is something Remedy created or a person created. It's a hell of a ball of >> yarn to untangle. >> >> Axton Grams >> >> >> On Fri, Sep 7, 2012 at 1:02 PM, Rick Cook <[email protected]> wrote: >> >>> ** >>> Ah, that makes more sense now. I know that some folks move their AR >>> indices off to a separate DB, under the control of the DBMS, so I don't >>> know why you couldn't move these things, as long as the appropriate AR >>> System calls knew where to find them. >>> >>> In the few instances where you're moving things that interact a lot with >>> the AR System DB from the AR System DB, I wonder if the increased network >>> traffic during those tasks would be a greater cost than the benefit of >>> having a "clean" system. Perhaps if they were on the same DB Server/SAN, >>> but under a different DB, that would provide benefit without cost. >>> >>> Rick >>> On Fri, Sep 7, 2012 at 1:54 PM, Jason Miller <[email protected]>wrote: >>> >>>> ** Now that is a bit tricky... To stay "out of the box" we need to >>>> think "inside the box" :) >>>> >>>> You are absolutely correct. All AR maintained objects will utilize >>>> overlays and best practices. What I am talking about are custom DB objects >>>> not maintained by the AR System. Most of (if not all of) our custom DB >>>> objects are for integration purposes >>>> >>>> Here are some examples: >>>> >>>> - The Views that View Forms are created against >>>> - Tables that cache data from other systems that View Forms are >>>> created against. >>>> - In the case of our HR and Financial systems creating View >>>> Forms over a Linked DB server were too slow for use in UI workflow >>>> (Cost >>>> Center and Sub Account Menus/Validation, HR/Finance Locations, etc.) >>>> - Stored procedures to populate the cache tables mentioned above >>>> - Functions converting to/from epoch time >>>> - Function for INITCAP (we're MS SQL so we had to build it) >>>> - Stored procedures that other systems use to access Remedy data >>>> (yeah we are trying to move most of these to web services when >>>> possible). >>>> - Views that other systems use to access Remedy data (yeah we are >>>> trying to move most of these to web services when possible). >>>> - Stored procedures to truncate import Forms (T and H tables) >>>> >>>> Currently all of these things are in our ARSystem DB. Moving these out >>>> would make the ARSystem DB more portable and "pure." >>>> >>>> Jason >>>> >>>> On Thu, Sep 6, 2012 at 5:40 PM, Easter, David <[email protected]>wrote: >>>> >>>>> ** >>>>> >>>>> Just thinking somewhat inside the box – but wouldn’t overlays do the >>>>> majority of this for you? Any custom objects you create would be well >>>>> known/documented as would any modifications to BMC base objects. The base >>>>> BMC objects would remain on the system unchanged. If you really then >>>>> wanted to transfer only your extensions and modifications to a new box, >>>>> you’d just transfer the custom and overlaid objects.**** >>>>> >>>>> ** ** >>>>> >>>>> -David J. Easter**** >>>>> >>>>> Manager of Product Management, AR System**** >>>>> >>>>> BSM & Atrium Solutions Management**** >>>>> >>>>> BMC Software, Inc.**** >>>>> >>>>> **** >>>>> >>>>> The opinions, statements, and/or suggested courses of action expressed >>>>> in this E-mail do not necessarily reflect those of BMC Software, Inc. My >>>>> voluntary participation in this forum is not intended to convey a role as >>>>> a >>>>> spokesperson, liaison or public relations representative for BMC Software, >>>>> Inc.**** >>>>> >>>>> ** ** >>>>> >>>>> *From:* Action Request System discussion list(ARSList) [mailto: >>>>> [email protected]] *On Behalf Of *Jason Miller >>>>> *Sent:* Wednesday, September 05, 2012 3:33 PM >>>>> *To:* [email protected] >>>>> *Subject:* Thoughts regarding a separate DB for custom DB objects**** >>>>> >>>>> ** ** >>>>> >>>>> ** **** >>>>> >>>>> I would like to get some input on something I have been thinking about >>>>> recently. I am considering creating a new DB on the same DB server as the >>>>> ARSystem DB. This DB would be right next to the ARSystem DB but would >>>>> house all of the custom DB work we do. My thought is to have all custom >>>>> tables, views, stored procedures, functions, etc. in new DB which would >>>>> ideally leave everything in the ARSystem DB created, maintained and >>>>> modified via the AR System API or BMC installers. Basically I am >>>>> picturing >>>>> a layer of abstraction or a library of custom objects if you will. Maybe >>>>> more like a different namespace or data set for those CMDB gurus out >>>>> there. >>>>> **** >>>>> >>>>> ** ** >>>>> >>>>> As it is right now in our very customized 7.5 ARS DB all custom >>>>> tables, views, etc. are all intermixed with the ARS maintained objects. We >>>>> try to keep our objects lumped together by naming convention but just when >>>>> my team starts using the same convention a different DBA (for example) >>>>> comes along and uses their favorite convention. If we all agree to only >>>>> make changes to the custom DB then naming convention is not as important >>>>> (DBAs usually ask which DB do you need something not so much “what is your >>>>> naming convention?”).**** >>>>> >>>>> ** ** >>>>> >>>>> Another considerations is portability of the ARSystem DB. Right now >>>>> when we refresh our production DB to another environment there are things >>>>> like different linked server references that need to be updated, object >>>>> owners, etc. All objects in the custom DB would have the same names >>>>> between environments (Dev, QA, Prod). With all of the custom objects in >>>>> their own DB we can move ARSystem DBs between environments and as long as >>>>> the custom DB is configured correctly for the environment no changes are >>>>> needed to any of ARSystem DB objects.**** >>>>> >>>>> ** ** >>>>> >>>>> Our new “out of the box” ITSM 7.6.04 system does not have any custom >>>>> DB work done yet but I know it is only a matter of time before the need >>>>> arises as we transition between old and new systems. If there are >>>>> advantages and no major negative results to this architecture I would like >>>>> to set this up while we are still out of the box.**** >>>>> >>>>> ** ** >>>>> >>>>> Can anybody think of any negative (or positive) effects of this >>>>> approach? I am figuring because it still on the same DB server that there >>>>> should not be much performance impact.**** >>>>> >>>>> ** ** >>>>> >>>>> Thanks!**** >>>>> >>>>> Jason **** >>>>> >>>>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ **** >>>>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >>>> >>>> >>>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >>> >>> >>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >>> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
