-
Hi guys,
I wanted to make a tablespace read-only after 3 days of its creation and drop it altogether along with the datafiles in it after 45 days. The tablespace holds only one table called State.
I can get the information connecting as system/manager using the follwing script.
select a.table_name,a.tablespace_name,b.created
from all_all_tables a,all_objects b
where a.table_name=b.object_name
and a.table_name='STATE';
I guess it requires a script to be written in shell script. Can someone help me? And is there anyway without writing a shell script but making it automated?(i am new for scripting).
I want also to automate the script to run everyday at a particular time.
Thanks in advance
manjunath
-
to automate the script so it runs every day at a specific time, you would use cron. on your system type "man cron", or check out http://kb.indiana.edu/data/afiz.html
A scripting reference is at http://www.oase-shareware.org/shell/
You could also use Perl, or another language if you're more familiar, but I'd stick to some sort of scripting language as opposed to a compiled one like C since they're easier.
You could create a table to use for tracking your tablespaces, let's say "tablespace_state". In it you would want to insert the tablespace name, and it's creation date. You could also throw in datafile but you could just query fort that after you have the tablespace name.
When you create a tablespace, insert a record into your tablespace_state table.
Your cron job would run daily, and compare today's date with the date in your tablespace_state table for all of the tablespaces listed. If it's 3 days later, alter tablespace read only, if it's 45 days later, alter tablespace offline drop, then remove the datafile.
Of course if you only need to do this once for one tablespace, it's probablly easier to do by hand but you wouldn't learn much that way :)
-
thanks pwoneill,
for the present i am going to make it through a cron job.
manjunath
