First of all, your help and efforts will be highly appreciated!
I am new to the Oracle world and learning to write PL/SQL with real world problems.
This is what I am facing now:
I have a bunch of SQL scripts need to run several times a year, they are simple and just to create/rebuild indexes on different tables. samples as below
CREATE INDEX ST_CODE_INDX_JAN2013 ON TREE_JAN2013 (CODE) NOLOGGING TABLESPACE ST_LRG_INDX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX ST_ID_INDX_JAN2013 ON RECORDS_JAN2013 (M_ID) NOLOGGING TABLESPACE ST_LRG_INDX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
And in total we probably will need to run around 100 scripts like this every time. Basically what we are doing is to import new tables every time and recreate/rebuild indexes. As you may see in the sample scripts above, all tables keep the same suffix but different table names. So, for now, what we are doing is to manually modify the script to reflex changes then kick it off.
Now, how can I create a procedure/function stored in database and waiting for a EXEC command to pass the suffix and then run automatically?
Once again, appreciated your great help!
Somebody needs to take a class on Data Normalization.
a date like "JAN_2013" should NEVER be part of any object name.
This "design" is amateurish, flawed, and should be trashed, reworked & corrected.
You face this challenge due to this design flaw.
Table should contain a new column of DATE datatype.
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.