1 oracle schema support large reques per day , is this safe ?

Question

I 'm java system designer. As we have large project to do tightly,
Those projects are java api without webpage.

I design to create general flow engine to support all project.
This idea use 1 oracle schema , having general transaction table . And others control routing table.

They all nearly complete. But DBA Team concern that he is suffered to maintain very large request to 1 schema.

1 reason is if there are problem is some table. He must offline tablespace to fix. This is problem because all project will be affected.

I try to convince by split data of each table to partition by project_code & "month number to delete" .

Eaxmple partition:

PROJ1_05 PROJ1_06 PROJ1_07
PROJ2_05 PROJ2_06 PROJ2_07

and all transaction table will store on its partition.

So, If there are problem on any part of tablespace then he should offline some partition and another project with use same table should able to service

Transaction per day should around 10Meg Record per day.

Is this a good idea?
If I must use 1 schema, what is strategy to do?
Do you have any comment?

Answer 1

For the most part you should not have any issues with working under a single schema. It really depends on how you want to build your application. If you are trying to duplicate the same type of application across different customers, cases can be made for the same structure replicated across different schemas as well as just putting it in one schema and handling security based on the customer.

Partitioning data by creating separate tables will make the coding much more complicated than it needs to be and works around letting the database do what it does best which is handle sets of data. I would be very wary of that approach. If you have some of the enterprise licensing from Oracle you can do table partitioning to improve performance but separate tables sounds like a bad idea.

Why are you going into your project thinking you are going to be offline-ing tablespaces regularly? If the project is designed well and tested well, there should not be a regular need to take a tablespace offline and work on a table during production hours. Periodic maintenance should be anticipated and scheduled, not done on the fly.

I would go with 1 schema/1 table for each type of data design personally unless there was a really compelling reason to duplicate the structure and segregate out to different schemas (legal or contract requirements).

As far as performance given your data load, you would have to test it to be able to see what your actual results and room for improvement through tuning will be.