Re: Using fixit Job

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Fri, 23 May 2003 17:28:55 GMT
Message-ID: <MPG.1937fa3425b44912989793@news.la.sbcglobal.net>

neokimia_at_hotmail.com said...
> Hi,
>
> Thanks for answer, but I don't have any script yet. I already know the
> command alter...tablespace...resize, but could you tell how I can get the
> current size and how to add 5MB to that tablespace? Do I need to use PL/SQL
> procedure? I'm new user with Oracle database, and I don't know all SQLPLUS
> commands.
>
> Thanks again!
>
> Regards,
>
> -----------
>
> Yanick Quirion
>
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.1936dce28b65384c989791_at_news.la.sbcglobal.net...
> > neokimia_at_hotmail.com said...
> > > Hi all,
> > >
> > > I want to use a fixit job to automatically add space to a tablespace
> when it
> > > reach the warning level. What script I can right into the fixit job to
> only
> > > add, for example 5MB to a tablespace, without adding a datafiles nor use
> the
> > > autoextend option for the tablespace. I want to be notified when a
> > > tablespace excess 90% of space use, and if I use the autoextend, I will
> not
> > > be notified.
> > >
> > > Thanks
> > >
> > > Regards,
> > > -Yanick
> > >
> > It's hard to tell from your post if you already have a fixit script
> > written, or if you want someone to suggest such a script. If the former,
> > see below; if the latter, it depends on your language of choice.
> >
> > But you ask what you need to write "into" (implying an existing script)
> > the fixit job to add 5MB to a tablespace. After finding the datafile
> > name and its current size - eg, from the dba_data_files view), the
> > command to do that is:
> >
> > alter database datafile '/path/to/datafile.dbf' resize nnnM;
> >
> > where nnn = target size (previous size in MB plus 5 MB) and M is, of
> > course, the symbol that tells Oracle you're specifying MB.
> > --
> > /Karsten
> > DBA > retired > DBA
>

Personally, I would use PL/SQL. You can find searchable documentation at http://tahiti.oracle.com. Folloing oversimplified snippits might get you started:

Variables are defined in the DECLARE section like this:

  declare
    new_mb number;
  begin
    ...[ code goes here ]...
  end;

You can get the free MB by tablespace with something like the following cursor loop (which assumes one data file per tablespace):

  for x in
    (

      select nvl(fs.bytes/1024/1024,0) mb_free,
             df.file_name
      from   dba_free_space fs, dba_data_files df
      where  fs.tablespace_name not in ('SYSTEM','TEMP','RBS')
      and    df.file_id = fs.file_id
      group by fs.tablespace_name

    )
  loop
    ...[ x.mb_free equals free MB ]...
  end loop;

You add 5MB like this:

    new_mb := x.mb_free + 5;

You resize the data file with something like this:

    execute immediate 'alter database datafile ' || x.file_name       || ' resize ' || to_char(new_mb) || 'm';

-- 
/Karsten
DBA > retired > DBA

Received on Fri May 23 2003 - 12:28:55 CDT