Re: help: grant problem

med10054_at_leonis.nus.sg (NORHISHAM BIN MAIN) wrote:

>Hi,
 

>Currently, I cannot create any grants in my database, The following
>command will fail as follows:
 

>SQL> grant select, insert, delete on MY_TABLE to SCOTT;
 

>ERROR at line 1:
>ORA-00604: error encountered at recursive level 1
>ORA-01547: failed to allocate extent of size 131490 in tablespace 'SYSTEM'

>What is the units of the size mentioned above ? Is it in blocks or
>bytes ? I have about 46M free space in my SYSTEM tablespace. Would
>appreciate any help....

The units are bytes. The problem is probably free space fragmentation in the system tablespace. You've got enough total space to do what you want, but no individual free spaces are big enough. Try adding another datafile to the tablespace. That new space is guaranteed to be contiguous.

With any other tablespace you can export all of the objects contained in it (use the COMPRESS=Y option), drop them, recreate the tablespace, and then import them. This should compress free space as well as segments. With the SYSTEM tablespace however, I don't know of any easy way to do this short of creating a new database. If someone else does, I'd like to hear about it. Perhaps ORACLE will include a defragmenter in a future release.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!