Re: CREATE DATABASE using OCI, is it possible?

in article 6de30668.0203122304.76fbc0c6_at_posting.google.com, rizzo2k_at_hotmail.com at rizzo2k_at_hotmail.com wrote on 3/12/02 11:04 PM:

> Essentially, I'm trying to create a database in which I can
> subsequently create some tables (4 actually) which are linked thru a
> common index. The database "dbname" would represend some general
> category and the tables would contain text and data pertaining to that
> category. So each database would be a mirror or the other except for
> the actual data content.

I'm still not sure what you want to do with these sets of tables that are different - back them up independently from each other (i.e a physical seperation) ?, have a different name resolution for each (i.e a logical seperation), and if so, why ?, or what ? And I'm not sure what you mean by 'linked by a common index'

Anyhow, for different name resolutions, you can set up multiple schema's (aka users), and then use the CREATE SCHEMA command to create a set of tables in each users schema - see the docs on CREATE SCHEMA - http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a76956/general.htm. I'm pretty certain you could do this dynamically from OCI using dynamic SQL from either the client side, or by calling a stored procedure that performs the operation for you - see http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/ a76939/adg09dyn.htm .

So you could have one schema for each of your categories. Note that users wanting to access these sets of tables will need to be given privileges on each schema, which could become a nightmare quickly if you have a large number of categories and a large number of users. And AFAIK, building a single index across all these different tables is not possible.

For a physical seperation, you could use this method and store each "schema's" tables in a different Oracle tablespace (although I don't think you can do this with the CREATE SCHEMA command, you would need to create each table in each schema a single CREATE TABLE command at a time.

Alternatively, you can use partitioned tables where category is your partiton key - but you will probably need to use list partitioning, which is an Oracle9i feature, and part of the partitioning option, which is additional cost - see
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/pa rtiti.htm This approach however would allow you to index all the data in a single common index

Last but not least, I still don't really understand what you are trying to do, so some (or all) of this may be very, very wrong advice indeed. Received on Wed Mar 13 2002 - 16:48:06 CET