Re: stored procedure

The Command will only Deallocate the Space over the Highwater Mark for that Object and not the actual Unused Space. So, Are you sure you want to do this ?
or better Export/Import to Regain the Unused Space. ?

-Thiru

Ben Ryan wrote:

> In article <37DEB9F0.FE51EA5F_at_gsisrl.it>,
> Alessandro Benati <webmaster_at_gsisrl.it> wrote:
> > I need to make a stored procedures that deallocates the unused space
> for
> > all tables of a given schema. I've tried to do something like this,
> but
> > it doesn't work:
> >
> > create procedure proc as
> > tab_name varchar2;
> > beegin
> > declare cursor c1 is
> > select table_name from all_tables where owner='myowner';
> > open c1;
> > loop
> > fetch c1 into tab_name;
> > dbms_sql.execute('ALTER TABLE ' || table_name || ' DEALLOCATE
> UNUSED;');
> >
> > end loop;
> > close c1;
> > end proc;
> >
> > Where is the error?
> > Thank you to anyone will help me.
> > Alessandro Benati
> >
> >
>
> Errors include
> 1) beegin
> 2) how the cursor c1 is declared
> 3) Use of the dbms_sql package, you have to invoke the open and close
> routines
>
> Suggest you post actual error messages returned.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Sep 15 1999 - 12:56:21 CDT