Re: Interpreting AUTOEXTENSION increment in DBA_DATA_FILES and altering it.

Hi!

I have SYSTEM tablespace about 4GB for one of my Apps installations. And you *cant* reduce it's size (at least without hacking). The reasons are seen from below:

SQL> select object_type, count(*) from dba_objects group by object_type order by 2 desc;

OBJECT_TYPE COUNT(*)

------------------ ----------
SYNONYM                 34462
INDEX                   24625
PACKAGE                 22670
PACKAGE BODY            22343
VIEW                    17531
TABLE                   13418
JAVA CLASS              10612
SEQUENCE                 5834
TRIGGER                  1971
TYPE                      406
JAVA DATA                 301
LOB                       261
JAVA RESOURCE             170
QUEUE                      99
INDEX PARTITION            73
TABLE PARTITION            58
LIBRARY                    36
FUNCTION                   34
UNDEFINED                  29
PROCEDURE                  21
TYPE BODY                  19
OPERATOR                   16
CONTEXT                    13
CLUSTER                     9
JAVA SOURCE                 8
CONSUMER GROUP              4
INDEXTYPE                   4
DATABASE LINK               2
RESOURCE PLAN               1

SQL> select segment_name, trunc(bytes/1048576) MB from dba_segments
where tablespace_name = 'SYSTEM'
and bytes > 20000000
order by MB desc;
  2 3 4 5

SEGMENT_NAME                             MB
-------------------------------- ----------
SOURCE$                                1156
IDL_UB1$                                702
I_SOURCE1                               540
IDL_UB2$                                346
ARGUMENT$                               265
I_ARGUMENT1                             182
IDL_SB4$                                 64
C_OBJ#                                   60
I_COL1                                   43
VIEW$                                    38
IDL_CHAR$                                38
DEPENDENCY$                              31
I_DEPENDENCY1                            29
I_COL2                                   26
I_DEPENDENCY2                            25
HIST_HEAD$                               24
I_COL3                                   22
ACCESS$                                  20

Tanel.

> What's the default tablespace of the Apps owner?
> Does the Apps owner have unlimited tablespace privilege?
> If the Apps user defaults tablespace is SYSTEM, couldn't you have
> changed it before installing apps?
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Thu Jul 03 2003 - 18:49:19 CDT