| auto create table at start of the month [message #23366] |
Wed, 04 December 2002 06:11  |
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: auto create table at start of the month [message #23368 is a reply to message #23366] |
Wed, 04 December 2002 06:34  |
Ramki
Messages: 26
Registered: September 2002
|
Junior Member |
|
|
Hi,
Sorry to say, u r trying to do bad DB design!!
Use partition table, check oracle syntax for partition tables.
archive offline is also supported by partition table.
pls. read complete feature of partition table,
you can achieve all your requirement with that.
-Thanks
|
|
|
|
| Re: auto create table at start of the month [message #23372 is a reply to message #23366] |
Wed, 04 December 2002 08:59 |
Mark
Messages: 284
Registered: July 1998
|
Senior Member |
|
|
Cheers, I have looked at table partition now. It looks to be the way forward. I have created a partition on the date that the order was created. Do you know if you can create a partition auto. for each month?
Mark
|
|
|
|
| Re: auto create table at start of the month [message #23388 is a reply to message #23372] |
Wed, 04 December 2002 22:41 |
Ramki
Messages: 26
Registered: September 2002
|
Junior Member |
|
|
Hi,
"Create a partition auto" -Don't do this.
Don't do any DDL (create command ) in auto (run time )
Create 12 partition each for a month in the design time itself .
Then u can make offline and archive a single partition,
so that use this for next year also.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
