DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: automating the status of tablespace

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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 [url]http://kb.indiana.edu/data/afiz.html[/url]

    A scripting reference is at [url]http://www.oase-shareware.org/shell/[/url]

    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 :)

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    thanks pwoneill,
    for the present i am going to make it through a cron job.
    manjunath

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width