Home » RDBMS Server » Server Utilities » manage data availability with limited quota (Oracle 10g, 10.0.2.0, Solaris 10, Win2003Server)
manage data availability with limited quota [message #351699] Thu, 02 October 2008 09:42 Go to next message
kowalsky
Messages: 37
Registered: May 2003
Member
hi all,
I have a question about what would it be the simplest/more efficient way to preserve only a portion of the data in some table but be able to access past data if necessary.

What happens is I have a small quota on a database where I need to manage only one table; the table gets around 4 GB of data at the end of each month and that's using the whole quota, so next month I have to store the current month's data somewhere else and load the next 4 GB into my table. In case someone wants to look at the data from 2 months ago, 3 months ago, I need to be able to make it available within reasonable time.

I guess I have no quota on the file system of the client which is a Win2003 server box.
My initial intent was to use export and import so that at the end of each month I export the current contents of the table and store the file resulted on the file system and use import when the need comes to access whatever past data (after exporting the current contents).

Is there any more elegant solutions? I never worked with transportable tables - is it worth to try that?

Thanks,
kowalsky
Re: manage data availability with limited quota [message #351703 is a reply to message #351699] Thu, 02 October 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Partition the table by month.
Use a different tablespace for each partition.
You can then exchange table/partition each month.
You can then take the tablespace offline to archive the file and put them back when needed.

Regards
Michel
Re: manage data availability with limited quota [message #351705 is a reply to message #351699] Thu, 02 October 2008 10:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And you can compress the data within the table.
Re: manage data availability with limited quota [message #351709 is a reply to message #351703] Thu, 02 October 2008 11:12 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
Thank you all for your advice,

Michel,
just want to confirm, the archiving you mention is not an Oracle utility - it is plain archiving with file system utilities (zip, gzip, etc.)

The second reply from Mahesh apparently points to a compressing done within Oracle ...

Thanks again,
kowalsky
Re: manage data availability with limited quota [message #351710 is a reply to message #351709] Thu, 02 October 2008 11:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not exactly.
There are several methods available to "archive" (put the data away for future use) using Oracle methods.

Most efficient method is to "partition" the table,
Make use of some kind of "plug and Play" concept to get the data out and bring it back when needed.

I particularly like this outlined method.
http://www.oracle.com/technology/oramag/oracle/06-jul/o46xml.html

Also, you do NOT want to use Any OS tools to compress again the dump files created by above process.If required You can just "compress" them within the database (to save some space from the 4G limit).

[Updated on: Thu, 02 October 2008 11:37]

Report message to a moderator

Re: manage data availability with limited quota [message #351712 is a reply to message #351709] Thu, 02 October 2008 12:38 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can "archive" using an OS tool or RMAN as a normal backup. Once the tablespace is put read-only is just need to be backed up once.
RMAN backup can also be compressed.

Regards
Michel
Previous Topic: sqlldr inserts null when column data default exists
Next Topic: QUERY TO DETERMINE THE SIZE OF TABLE
Goto Forum:
  


Current Time: Sun Dec 11 08:24:26 CST 2016

Total time taken to generate the page: 0.06393 seconds