Re: newbie plsql

First off, Data Definition Language (DDL) like creating and droping indexes are not transactional. i.e. They do not need to be commited [as] and they cannot be rolled back. They do not take up any rollback segment space.

As far as the failure to re-create the index, it may be that there is not sufficent contiguous space to allocate the first extent. You may need to defragment the tablespace[s], not just the index.

Nick
nwhitehead_at_rocketmail.com

>Hi,
>
>i´m tring to write a block in plsql . The ideia is to drop an index and
>re-create the same index again ( desfragmentation ) .
>But i have a problem after i drop the index , i don´t know how to
>commit, and the create index fails because it thas not have space ( the
>create index allocates the same extents that the index had ) .
>
>begin
>....
>dbms_sql.parse(cursor,'drop index ISALES',0);
>i:=dbms_sql.execute(cursor);
>dbms_sql.close_cursor(cursor);
>commit;
>dbms_sql.parse(cursor,'create index ISALES on SALES (......) ... ',0);
>i:=dbms_sql.execute(cursor);
>dbms_sql.close_cursor(cursor);
>...
>end;
>
>Have you ever had the same problem ?
>
>Nuno
>PORTUGAL
>
Received on Tue Oct 07 1997 - 00:00:00 CDT