BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  What is an Oracle schema's          BorlandTalk.com Forum Index -> Delphi Non-Technical View previous topic :: View next topic   Author Message ptc Guest Posted: Sat Jul 30, 2005 5:16 pm    Post subject: What is an Oracle schema's My understanding is that a schema is a blueprint on how to work with the tables in the database. In my travels though, I hear that a schema is a set of tables into itself. So in a database with multiple schemas you would have multiple little databases within the original database. To me this sounds all wrong. ie. If you have 10 schemas you would then end up with 10 copies of the same tables in 1 database. Can anyone clarify this for me? Thank you. ptc Back to top Kurt Bilde Guest Posted: Sat Jul 30, 2005 5:58 pm    Post subject: Re: What is an Oracle schema's Hi ptc! ptc wrote: Quote: To me this sounds all wrong. ie. If you have 10 schemas you would then end up with 10 copies of the same tables in 1 database. Yes, the struture of the database is called the schema. If you need a table in different schemas you just grant different rights for the table across schemas (eg. select, insert, delete ect.) or you create a view depending on your needs. -Kurt Back to top Jarle Stabell Guest Posted: Sat Jul 30, 2005 6:06 pm    Post subject: Re: What is an Oracle schema's ptc wrote: Quote: My understanding is that a schema is a blueprint on how to work with the tables in the database. In my travels though, I hear that a schema is a set of tables into itself. Yes, natural language tend to overload terms quite a bit. Quite often the different levels of abstraction isn't that important to state/grasp, in order to deal with daily problems. 'Table' and 'database' are also used across different abstraction levels in daily speak. It's only when you need to be very exact that you start using terms like 'relation variable' etc, because else people quickly start loosing interest in listening to you. :-) Quote: So in a database with multiple schemas you would have multiple little databases within the original database. Yes. You can think of an Oracle schema as a "little" database. Quote: To me this sounds all wrong. ie. If you have 10 schemas you would then end up with 10 copies of the same tables in 1 database. There's no one forcing you to have 10 equal schemas, and 10 equal schemas makes sense if you are hosting data (different content, but same structure) for 10 different customers who want "their own" "database". (They may be using the same application). It's not more wrong than having 10 different MS Access (or whatever) databases on the same machine. (I don't know how easy it is to make queries across multiple Access databases, it might very well make *much* more sense to have 10 Oracle schemas in an Oracle database instance, than 10 Access databases on a single machine) Cheers, Jarle Back to top Martin Brekhof Guest Posted: Sat Jul 30, 2005 7:14 pm    Post subject: Re: What is an Oracle schema's Quote: Can anyone clarify this for me? Basics: Every user (!) that is created in Oracle get's his own schema named after that user. Within that schema he/she/it basically can do and like as he wishes (create/delete/alter tables/views etc., update those tables etc.) and he (or the sys/system users) can grant those right, or parts there of to other users. Here is how we use it: step 1 create two users: MyApp and MyAppDBA step 2 create all objects (tables etc.) used in your application in the MyAppDBA schema step 3 grant the user MyApp the necessary rights to do update/insert/deletes on the tables in the MyAppDBA schema step 4 let your application login using the MyApp credentials and use the objects in MyAppDBA You can make this better/nicer/<whatever> by creating views and granting rights only on the views and not upon the underlying tables, granting only specific rights for specific tables/views hth Martin Back to top ptc Guest Posted: Sat Jul 30, 2005 7:23 pm    Post subject: Re: What is an Oracle schema's So...if you have a database with 3 tables; tbl1, tbl2 and tbl3 you can create a schema that requires a tbl4. Does the the schema create the table or do you have to create the table in the database in order for the schema to access it? Quote: Yes. You can think of an Oracle schema as a "little" database. But is it actually that way (mini databases) or is it one set of tables and the schema tells oracle how to work with the tables? "little database" is kind of abstract. Does it actually make multiple copies of the tables for each schema? I am stuck on the idea that a schema is some kind of keyed indexing for oracle. Quote: Cheers, Jarle Back to top ptc Guest Posted: Sat Jul 30, 2005 8:12 pm    Post subject: Re: What is an Oracle schema's This sounds more like what I am thinking...I think. A schema is a schematic, in essence a road map to the data. MyApp uses the same objects(tables) as MyAppDBA but MyApp can only see the data based on MyApp schema. If we create another user <MyApp2>. There is not 2 distinct copies of MyAppDBA objects but rather 2 distinct ways to address MyAppDBA objects(tables) to retrieve the data from the same objects? "Martin Brekhof" <martin (AT) brekhof (DOT) nl> wrote Quote: Can anyone clarify this for me? Basics: Every user (!) that is created in Oracle get's his own schema named after that user. Within that schema he/she/it basically can do and like as he wishes (create/delete/alter tables/views etc., update those tables etc.) and he (or the sys/system users) can grant those right, or parts there of to other users. Here is how we use it: step 1 create two users: MyApp and MyAppDBA step 2 create all objects (tables etc.) used in your application in the MyAppDBA schema step 3 grant the user MyApp the necessary rights to do update/insert/deletes on the tables in the MyAppDBA schema step 4 let your application login using the MyApp credentials and use the objects in MyAppDBA You can make this better/nicer/<whatever> by creating views and granting rights only on the views and not upon the underlying tables, granting only specific rights for specific tables/views hth Martin Back to top Jarle Stabell Guest Posted: Sat Jul 30, 2005 8:41 pm    Post subject: Re: What is an Oracle schema's ptc wrote: Quote: So...if you have a database with 3 tables; tbl1, tbl2 and tbl3 you can create a schema that requires a tbl4. Does the the schema create the table or do you have to create the table in the database in order for the schema to access it? A schema is a namespace/"access point" for tables, like mySchema.MyTable1, myOtherSchema.MyTable3. In queries, you can prefix the table names with the schema names, to access tables (views etc) outside of the given schema. (To add somewhat to the confusion, Oracle blurs the distinction between schemas and users, so JoeUser.MyTable1 and MaryUser.MyTable1 might or might not alias the same physical table!) Perhaps it helps if you think of files in a filesystem. Within a given directory ("schema"), MyFile.txt uniquely identifies a given file ("table"), but if you are standing on root, you need a fully qualified name. Also there can be various shortcuts/links to the file, so a given file might have multiple names. Quote: Yes. You can think of an Oracle schema as a "little" database. But is it actually that way (mini databases) No, that's only a simplification. Quote: or is it one set of tables and the schema tells oracle how to work with the tables? I guess it is best to think of it as one set of tables. Schemas are just a way to organize stuff, on a logical level. In one way it is very simple, on the other hand I can see that it is confusing, especially the unification of schema names and user names. :-) Cheers, Jarle Back to top tony Guest Posted: Sun Jul 31, 2005 1:12 am    Post subject: Re: What is an Oracle schema's A schema in Oracle and Postgresql for that matter is like having a database in a database, for example: you create two schemas called: admin and annual, you can then have seperate tables,functions etc per schema, even with the same names because you must prefix the objects with the schema name: admin.demographics annual.demographics There is also usually a public schema, everything in the public schema does not need to be prefixed, so you would just use demographics for a table in the public schema. Does this make sense? Tony http://www.amsoftwaredesign.com home of PG Lightning Admin for Postgresql 8.x Schemas are very handy and allow you to neatly organize your database objects. ptc wrote: Quote: My understanding is that a schema is a blueprint on how to work with the tables in the database. In my travels though, I hear that a schema is a set of tables into itself. So in a database with multiple schemas you would have multiple little databases within the original database. To me this sounds all wrong. ie. If you have 10 schemas you would then end up with 10 copies of the same tables in 1 database. Can anyone clarify this for me? Thank you. ptc Back to top tony Guest Posted: Sun Jul 31, 2005 1:18 am    Post subject: Re: What is an Oracle schema's Well, there can be two tables with the same name and they are seperate entities. myschema1.mytable myschema2.mytable The are just namespaces, there are actually two copies, each one is it's own table just prefixed with the schema name. Postgresql also uses schemas for temporary tables. ptc wrote: Quote: This sounds more like what I am thinking...I think. A schema is a schematic, in essence a road map to the data. MyApp uses the same objects(tables) as MyAppDBA but MyApp can only see the data based on MyApp schema. If we create another user <MyApp2>. There is not 2 distinct copies of MyAppDBA objects but rather 2 distinct ways to address MyAppDBA objects(tables) to retrieve the data from the same objects? Back to top Martin Brekhof Guest Posted: Sun Jul 31, 2005 5:04 am    Post subject: Re: What is an Oracle schema's "ptc" <PCardwell (AT) metegrity (DOT) com> wrote Quote: A schema is a schematic, in essence a road map to the data. MyApp uses the same objects(tables) as MyAppDBA but MyApp can only see the data based on MyApp schema. if you mean 'but MyApp can only see the data based on the rights granted to MyApp' then you are right Quote: If we create another user <MyApp2>. There is not 2 distinct copies of MyAppDBA objects but rather 2 distinct ways to address MyAppDBA objects(tables) to retrieve the data from the same objects? Yes. However, depending on your needs, most of the time you do not use/want the overhead of more users. We always use a table 'users' (or something similar) in which we define each user (including loginname and password) and the specific rights he/she has in the application. hth, Martin Back to top Mike Mader Guest Posted: Mon Aug 01, 2005 1:24 pm    Post subject: Re: What is an Oracle schema's Think of a schema as folder in a file system that stores your tables, view, etc. The schema itself has no bearing on the design of the database tables within. Schemas are a way to separate database tables for various applications. Thanks, Mike -- "ptc" <PCardwell (AT) metegrity (DOT) com> wrote Quote: My understanding is that a schema is a blueprint on how to work with the tables in the database. In my travels though, I hear that a schema is a set of tables into itself. So in a database with multiple schemas you would have multiple little databases within the original database. To me this sounds all wrong. ie. If you have 10 schemas you would then end up with 10 copies of the same tables in 1 database. Can anyone clarify this for me? Thank you. ptc Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Non-Technical All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.