| ARGUMENT segment taken more space out of total system space [message #557412] |
Tue, 12 June 2012 09:23  |
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 #557622 is a reply to message #557512] |
Thu, 14 June 2012 05:44  |
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 |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
