System tablespace got big after import 2

[evgeniosc]

I made an import to specific schemas in an oracle db and the system tablespace got bigger.

The three schemas that I imported had their own tablespaces. I do not know why but the system tablespace got bigger.

I checked the dependencies of system tablespace and there were no user objects. I also dropped the imported schemas but no free space was released on the system tablespace.

Is there any sql that I can check the extent size of a tablespace?

 

[LKBrwnDBA]

Query DBA_SEGMENTS



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

evgeniosc

To help resolve your question, I recommend you run the following query both before and after your future imports:

select segment_name, bytes
  from dba_segments
 where tablespace_name = 'SYSTEM'
   AND bytes > 10000000
 order by bytes;

SEGMENT_NAME                                  BYTES
------------------------------ --------------------
PK_O                                     10,485,760
ARGUMENT$                                10,485,760
PK_C0                                    10,485,760
PK_CS                                    10,485,760
PK_CT                                    10,485,760
JAVA$MC$                                 11,534,336
I_COL1                                   11,534,336
I_HH_OBJ#_COL#                           13,631,488
I_HH_OBJ#_INTCOL#                        13,631,488
C_FILE#_BLOCK#                           14,680,064
SYS_LOB0000000494C00003$$                20,971,520
HIST_HEAD$                               24,117,248
C_OBJ#                                   25,165,824
IDL_UB2$                                 25,165,824
C_TOID_VERSION#                          26,214,400
SOURCE$                                  45,088,768
I_H_OBJ#_COL#                           167,772,160
IDL_UB1$                                243,269,632
C_OBJ#_INTCOL#                          301,989,888

19 rows selected.

Notice that in the database against which I ran this query, the largest object, "C_OBJ#_INTCOL#" (which holds Oracle cluster information), is over 300MB. The SYSTEM tablespace objects grow as Oracle needs to track new, additional information. When you import large amounts of information, it follows that Oracle must track additional data-dictionary information about the new data. The data-dictionary tables reside in the SYSTEM tablespace. Therefore you should notice growth in the SYSTEM tablespace despite the imported data being stored in non-SYSTEM tablespaces.

Let us know if this helps answer your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[evgeniosc]

I understand that after the import the system tablespace will get bigger.

But after that I dropped all the imported schemas and the allocated space on system was almost the same as after the import. Shouldn't the additional space be released?

Do you know what is the segment IDL_UB1$?

The biggest segments are the following:
Segment Bytes Megabytes
IDL_CHAR$ 10485760 10
C_FILE#_BLOCK# 11534336 11
I_OBJ2 11534336 11
I_OBJ5 11534336 11
I_SOURCE1 12582912 12
OBJ$ 12582912 12
ARGUMENT$ 12582912 12
I_COL3 13631488 13
I_COL2 14680064 14
JAVA$MC$ 15728640 15
I_HH_OBJ#_COL# 20971520 20
AUD$ 20971520 20
I_HH_OBJ#_INTCOL# 20971520 20
C_TOID_VERSION# 24117248 23
IDL_UB2$ 32505856 31
I_COL1 38797312 37
HIST_HEAD$ 55574528 53
C_OBJ# 59768832 57
SOURCE$ 75497472 72
IDL_UB1$ 260046848 248

 

[SantaMufasa]

Do you know what is the segment IDL_UB1$?

IDL_UB1$ is one of four tables that hold compiled PL/SQL code:
IDL_UB1$
IDL_CHAR$
IDL_UB2$
IDL_SB4$

But after that I dropped all the imported schemas and the allocated space on system was almost the same as after the import. Shouldn't the additional space be released?

To help answer your question, I'll use a comparable situation in an Oracle-application scenario: Let's assume that you have a million-row table that occupies 100MB. Question: If you use the DELETE command to get rid of all 1M rows, how much space does the table occupy? Answer: 100MB. When Oracle deletes rows from a table, Oracle does not return the space to the tablespace for use by other objects; Oracle simply sets a flag on a deleted row that indicates that the row's space is usable by other rows in the same table for expansion.

This is the effect that you are seeing...After you drop objects/schemas, Oracle simply deletes the dropped objects' representative rows in the data dictionary. As we saw, above, deleting rows from a table does not return the rows' space to the data dictionary...the data dictionary shows the same amount of space consumption as before your DROPs, but their space is now open, usable space within the data dictionary object that formerly described the now-dropped object(s).

Does this explain why you are seeing what you are seeing?



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[evgeniosc]

Thanks Mufasa for your answer. I understand what you mean.

I just thought that Oracle EM Console was clever enough to report the space occupied by the dropped objects as free space.

 

[evgeniosc]

Why does the table IDL_UB1$ gets bigger every time i re-import the data.

What I import does not contain PL/SQL at all.

 

[khobar]

Hi

when u import the schema that schema has associated pl/sql packages/procedures which are getting imported probably that might be the reason

khobar