Home » RDBMS Server » Server Utilities » ARGUMENT segment taken more space out of total system space (11gR2, HP Unix)
ARGUMENT segment taken more space out of total system space [message #557412] Tue, 12 June 2012 09:23 Go to next message
sap_arul
Messages: 27
Registered: November 2005
Location: BANGALORE
Junior Member
Hi,

After importing my dump, i have noticed that ARGUMENT$ segment taken more than 9 GB out of my total SYSTEM table space.

I belive ARGUMENT$ table is used only to store procedure/package parameter details. But I am not sure Why it has taken more space.

Is there any way we can reduce the SYSTEM table space?

Can you please any one help on this, using with the below details?

Import Details:
--------------
1) Imported using IMP DP. List of parameters used are userid, logfile, dumpfile, directory, job_name and remap_schema.
2) Dump file size is 3GB
3) The below list will be no. of objects imported using my dump.
OBJECT_TYPE           COUNT(1)
------------------- ----------
DATABASE LINK                1
FUNCTION                   246
INDEX                     4742
JAVA CLASS                  11
JAVA RESOURCE                1
JAVA SOURCE                  6
LIBRARY                      1
LOB                        117
PACKAGE                   8006
PACKAGE BODY              7951
PROCEDURE                  492
SEQUENCE                  3245
SYNONYM                  13831
TABLE                     5526
TABLE PARTITION             18
TRIGGER                    419
TYPE                        41
VIEW                      2436
Total                     47090


4) The below list will be amount of space occupied by the segments in the SYSTEM.
col owner form a5  word wrap
col segment_name form a15 word wrap 
col segment_type form a15 word wrap 
select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_m
 from dba_segments
 where tablespace_name = 'SYSTEM'
 and    bytes/(1024*1024) >80
 order by size_m desc


OWNER SEGMENT_NAME    SEGMENT_TYPE        SIZE_M
----- --------------- --------------- ----------
SYS   ARGUMENT$       TABLE                10827
SYS   I_ARGUMENT1     INDEX                 6993
SYS   I_ARGUMENT2     INDEX                 4571
SYS   IDL_UB1$        TABLE                 1635
SYS   SOURCE$         TABLE                 1897
SYS   I_SOURCE1       INDEX                  408
SYS   IDL_UB2$        TABLE                  552
SYS   IDL_CHAR$       TABLE                  140
SYS   C_OBJ#          CLUSTER                 96
SYS   IDL_SB4$        TABLE                   88
SYS   DEPENDENCY$     TABLE                   88


Regards,
Arul.
Re: ARGUMENT segment taken more space out of total system space [message #557416 is a reply to message #557412] Tue, 12 June 2012 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ARGUMENT$ table is used only to store procedure/package parameter details.


SQL> desc sys.argument$
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OBJ#                             NOT NULL NUMBER
 PROCEDURE$                                VARCHAR2(30)
 OVERLOAD#                        NOT NULL NUMBER
 PROCEDURE#                                NUMBER
 POSITION#                        NOT NULL NUMBER
 SEQUENCE#                        NOT NULL NUMBER
 LEVEL#                           NOT NULL NUMBER
 ARGUMENT                                  VARCHAR2(30)
 TYPE#                            NOT NULL NUMBER
 CHARSETID                                 NUMBER
 CHARSETFORM                               NUMBER
 DEFAULT#                                  NUMBER
 IN_OUT                                    NUMBER
 PROPERTIES                                NUMBER
 LENGTH                                    NUMBER
 PRECISION#                                NUMBER
 SCALE                                     NUMBER
 RADIX                                     NUMBER
 DEFLENGTH                                 NUMBER
 DEFAULT$                                  LONG
 TYPE_OWNER                                VARCHAR2(30)
 TYPE_NAME                                 VARCHAR2(30)
 TYPE_SUBNAME                              VARCHAR2(30)
 TYPE_LINKNAME                             VARCHAR2(128)
 PLS_TYPE                                  VARCHAR2(30)

So many stuff about all arguments in it.

Quote:
Is there any way we can reduce the SYSTEM table space?


Not really.

Regards
Michel
Re: ARGUMENT segment taken more space out of total system space [message #557512 is a reply to message #557416] Wed, 13 June 2012 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
9G does seem absurdly high?
How many objects are there in the DB?
Did you do the export from a different DB? If so how big is argument# in that?
Re: ARGUMENT segment taken more space out of total system space [message #557622 is a reply to message #557512] Thu, 14 June 2012 05:44 Go to previous messageGo to next message
sap_arul
Messages: 27
Registered: November 2005
Location: BANGALORE
Junior Member
Hi,

Export & Import i am doing in the same version. (11R2).

Below is object count list.

OBJECT_TYPE           COUNT(1)
------------------- ----------
DATABASE LINK                1
FUNCTION                   246
INDEX                     4742
JAVA CLASS                  11
JAVA RESOURCE                1
JAVA SOURCE                  6
LIBRARY                      1
LOB                        117
PACKAGE                   8006
PACKAGE BODY              7951
PROCEDURE                  492
SEQUENCE                  3245
SYNONYM                  13831
TABLE                     5526
TABLE PARTITION             18
TRIGGER                    419
TYPE                        41
VIEW                      2436
Total                     47090


Regards,
Arul.
Re: ARGUMENT segment taken more space out of total system space [message #559215 is a reply to message #557622] Fri, 29 June 2012 16:48 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Export and import will not permit you to resize the system datafile smaller because other system objects that regullarly extend will extend beyond the high watermark. The only 2 ways that I know of fixing this is:
1) create a new database with a new system datafile and export/import all the objects into the new database.
or
2) create a new database with a new system datafile and use transportable tablespaces to reconnect the datafiles to the new database. This will also require an export/import of procedural objects because the transportable tablespaces only have table and index objects in them.

I have a similar but much smaller problem with the aud$ table. Here are the steps.

1) Display the size of the system tablspace.
ENWEBPD > @da
AUT FILE_ID  INC_MEG MAXMEG    MEG STATUS    FILE_NAME
--- ------- -------- ------ ------ --------- ---------------------------------------
YES       1  1000.00  32768   1024 AVAILABLE /u02/oradata/NWEBPD/system01.dbf

ENWEBPD > list
  1  select autoextensible,file_id,increment_by*8192/1024/1024 inc_meg,
  2  maxbytes/1024/1024 maxmeg,bytes/1024/1024 meg,status,file_name
  3  from dba_data_files where file_id=1
  4* order by autoextensible,file_id

2) Resize down to the highwater mark
ENWEBPD > @mapperi 1


OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
SYS TABLE     AUD$.                 1      97920       1024
SYS TABLE     AUD$.                 1      98944       1024
SYS INDEX     I_COL3.               1      99968        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100096        128
SYS INDEX     I_H_OBJ#_COL#.        1     100224        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100352        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100480        128
SYS TABLE     AUD$.                 1     100608       1024
free space                          1     101632      29440

ENWEBPD > list
  1  select     /*+ Rule */     'free space' owner      /*"owner" of free space*/
  2  ,          '         ' object              /*blank object name*/
  3  ,          file_id                         /*file id for the extent header*/
  4  ,          block_id                        /*block id for the extent header*/
  5  ,          blocks                          /*length of the extent, in blocks*/
  6  from               dba_free_space
  7  where              file_id=&1
  8  union
  9  select     /*+ Rule */ substr(owner,1,20)||' '||substr(segment_type,1,9)  
 10  ,          substr(segment_name,1,32)||'.'||partition_name  /*segment name*/
 11  ,          file_id                         /*file id for the extent header*/
 12  ,          block_id                        /*block id for the extent header*/
 13  ,          blocks                          /*length of the extent, in blocks*/
 14  from               dba_extents
 15  where              file_id=&1
 16* order by   3,4

Since the last extent starts at 101632 I can resize down to 794 megabytes.

ENWEBPD > select 101632*8192/1024/1024 from dual;

101632*8192/1024/1024
---------------------
                  794

ENWEBPD > alter database datafile '/u02/oradata/NWEBPD/system01.dbf' resize 794m;

Database altered.

3) Reclaim space at the highwater mark

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
SYS TABLE     AUD$.                 1      97920       1024
SYS TABLE     AUD$.                 1      98944       1024
SYS INDEX     I_COL3.               1      99968        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100096        128
SYS INDEX     I_H_OBJ#_COL#.        1     100224        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100352        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100480        128
SYS TABLE     AUD$.                 1     100608       1024

ENWEBPD > select bytes/1024/1024 megabytes from dba_segments where segment_name='AUD$';

 MEGABYTES
----------
        88

connect as sys and truncate the sys.aud$ table 
(This is OK according to Oracle Documentation and should not be done on other sys tables).

SYS AS SYSDBA> truncate table sys.aud$;

Table truncated.

ENWEBPD > select bytes/1024/1024 megabytes from dba_segments where segment_name='AUD$';

 MEGABYTES
----------
     .0625

ENWEBPD > @mapperi 1

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          1      92288       7680
SYS INDEX     I_COL3.               1      99968        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100096        128
SYS INDEX     I_H_OBJ#_COL#.        1     100224        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100352        128
SYS CLUSTER   C_OBJ#_INTCOL#.       1     100480        128
free space                          1     100608       1024

4) Resize the system datafile down to the new highwater mark.

Notice the holes where sys.aud$ extents were freed up but I will only be able to 
resize down to 100608 because other system objects have extended.

ENWEBPD > select 100608*8192/1024/1024 from dual;

100608*8192/1024/1024
---------------------
                  786

ENWEBPD > alter database datafile '/u02/oradata/NWEBPD/system01.dbf' resize 786m;

Database altered.

ENWEBPD > alter database datafile '/u02/oradata/NWEBPD/system01.dbf' resize 785m;
alter database datafile '/u02/oradata/NWEBPD/system01.dbf' resize 785m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Alan

[Updated on: Sat, 30 June 2012 00:26] by Moderator

Report message to a moderator

Previous Topic: Flat File Reading
Next Topic: How to upload photos in oracle database
Goto Forum:
  


Current Time: Fri Mar 29 05:00:11 CDT 2024