user9358016 wrote:... they are simple and just to create/rebuild indexes ...
Don't do that.
You shouldn't be rebuilding indexes unless you have SCIENTIFIC PROOF that it will majorly speed up things.
Most likely, you really just want to gather statistics on the table (and index).
On 11g, that seems to be automated for me; I don't know the command right off the top of my head by I do know that the 'ANALYZE' command is deprecated.
Oh, and if you have table names like something_a_date ...
You may need to look into PARTITIONED TABLES.
While I was doing my research these days, I did find that "analyze" is better then "rebuild". And I will change it ASAP. Thanks again!
One more thing, we have clients are using Oracle 10g/11i Standard Edition which do not include "PARTITION". On the other hand, I have to create something compatible for both 10g/11i Standard Edition.
Appreciated you help again!
Use DBMS_STATS package instead of 'analyze'. 'ANALYZE' is deprecated and may not exist in future releases.
Again - what you are trying to do (build tables/indexes on the fly) is extremely wrong.
Add a DATE column and keep everything in one table. You can then just add the PARTITION clause for EE instances.
(against my better judgement)
Two methods exist to run dynamically generated DDL statements:
DBMS_SQL -- I'm not 100% sure on this one. read the manual.
If you are dynamically generating the SQL, help limit SQL Injection by using DBMS_ASSERT on any user provided input.