| Create a table automatically at the start of the month and q [message #4381] |
Wed, 04 December 2002 04:56  |
Mark
Messages: 284
Registered: July 1998
|
Senior Member |
|
|
I know how to create a table, but I don't know how to create a table automatically at the start of the month. A way of doing this maybe to try and say create table blah where sysdate = to the first of the month.
The reason being they get approxitmetly 70,000 records in the order table a month, we want to be able to create a table automatically at the start of the month. The current month will be stored in a tablespace called current, the tables for the previous three months will be stored in a seperate tablespace called previous and the ones before that in a tablespace called archieve, which archieve will be offline. At the end of every months we want to move the tables into the correct tablespace, e.g. so the current moves to previous. A new current is created. The oldest table in previous is moved to the archieve tablespace.
The tables may be called for example, SOrderHdr1102 for november and so on, incrementing for each month. We then want to be able to do queries on these tables. This is going to be difficult as I don't know if you can have a variable as a tablename in a query. One way I have thought of doing it is trying to query for any table that has the SOrderHdr in part of the table name in the from clause? Obviously the user will define what months they want to query, this will be done through a web page.
Have you any ideas?
Thank for your help in advance!
Mark
|
|
|
|
| Re: Create a table automatically at the start of the month and q [message #4385 is a reply to message #4381] |
Wed, 04 December 2002 08:32  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
you have to use the dbms_job and execute immediate
to create table dynamically every month
This code ( please modify according to your need)
will submit a job which will create table today and same day next month at 4.00 AM.
var jobno number;
begin
dbms_job.submit(:jobno,
'declare
x number;
t_name varchar2(20);
begin
select to_char(sysdate, ''mmyy'') into x from dual;
t_name := ''SOrderHdr'' || x;
execute immediate ''create table ''|| t_name ||'' ( col1 varchar2, col2 number)'';
end;',
trunc(sysdate)+4/24,
'trunc(add_months(sysdate, 1))+4/24' );
end;
/
Have you explored the option of partitioning the table by date range, instead of creating separate tables?.
Bala.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
