Home » RDBMS Server » Server Administration » Tablespace Not re utilize space (Oracle 11g R2)
Tablespace Not re utilize space [message #661955] Fri, 07 April 2017 08:40 Go to next message
sachinonnet
Messages: 5
Registered: June 2008
Location: INDIA
Junior Member

Hello,

We configured Table with BLOB Column with seperate TableSpace.

CREATE TABLE EV_LOG
( "ID" FLOAT(63),
"MESSAGE_ID" VARCHAR2(128 BYTE),
"EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NULL ENABLE,
"CLASSIFIER" VARCHAR2(128 BYTE),
"EVENT_TIME" TIMESTAMP (6) CONSTRAINT "SYS_C0014416" NOT NULL ENABLE,
"EVENT_DATA" BLOB
)
LOB ("EVENT_DATA") STORE AS "EV_LOG_LOB"( TABLESPACE dwh_data04 DISABLE STORAGE IN ROW )
PARTITION BY RANGE ("EVENT_TIME")
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (dwh_data03, dwh_data02)
(
PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')),
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-02-2012', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-03-2012', 'DD-MM-YYYY'))
);


We configured Scheduled Job to update BLOB Column to EMPTY, This Job runs every night and update 30 days old rows.

SET event_data = empty_blob()

Despite this we can see spike in tablespace usage, We have not 3month data and Every day it require 2-3 GB More space for inserting 0.4 to 0.42 millions of rows every day.

RUN_TIME NAME ALLOC_SIZE_GB CURR_USED_SIZE_GB PREV_USED_SIZE_GB VARIANCE
---------- --------------- ------------- ----------------- ----------------- --------------------
2017-03-29 dwh_data04 480 224.07 220.88 3.19
2017-03-30 dwh_data04 480 227.51 224.07 3.44
2017-03-31 dwh_data04 480 230.05 227.51 2.54
2017-04-01 dwh_data04 480 233.26 230.05 3.21
2017-04-02 dwh_data04 480 236.07 233.26 2.81
2017-04-03 dwh_data04 480 239.5 236.07 3.43
2017-04-04 dwh_data04 480 242.75 239.5 3.25
2017-04-05 dwh_data04 480 244.88 242.75 2.13


What could be the possible reason for space consumption, As per my understanding ORACLE should re-utilize empty space.

Regadrs,
Sachin
Re: Tablespace Not re utilize space [message #661957 is a reply to message #661955] Fri, 07 April 2017 09:07 Go to previous messageGo to next message
Flyby
Messages: 181
Registered: March 2011
Location: Belgium
Senior Member
seems you have to compact it manually
Reclaiming Unused LOB Space
reclaiming-lob-space-in-oracle
Re: Tablespace Not re utilize space [message #661959 is a reply to message #661957] Fri, 07 April 2017 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4034643
Re: Tablespace Not re utilize space [message #661960 is a reply to message #661955] Fri, 07 April 2017 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Why didn't you feedback and gave us the solution of your problem in your previous topic?

Re: Tablespace Not re utilize space [message #661963 is a reply to message #661955] Sat, 08 April 2017 07:01 Go to previous messageGo to next message
sachinonnet
Messages: 5
Registered: June 2008
Location: INDIA
Junior Member

sachinonnet wrote on Fri, 07 April 2017 19:10
Hello,

We configured Table with BLOB Column with separate TableSpace.

CREATE TABLE EV_LOG
   (    "ID" FLOAT(63),
        "MESSAGE_ID" VARCHAR2(128 BYTE),
        "EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NULL ENABLE,
        "CLASSIFIER" VARCHAR2(128 BYTE),
        "EVENT_TIME" TIMESTAMP (6) CONSTRAINT "SYS_C0014416" NOT NULL ENABLE,
        "EVENT_DATA" BLOB
   )
     LOB ("EVENT_DATA") STORE AS "EV_LOG_LOB"( TABLESPACE dwh_data04 DISABLE STORAGE IN ROW )
     PARTITION BY RANGE ("EVENT_TIME")
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     STORE IN (dwh_data03, dwh_data02)
  (
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-02-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-03-2012', 'DD-MM-YYYY'))
  );

configured Scheduled Job to update BLOB Column to EMPTY, This Job runs every night and update 30 days old rows.

SET event_data = empty_blob()

Despite this we can see spike in tablespace usage, We have not 3 month data and Every day it require 2-3 GB More space for inserting 0.4 to 0.42 millions of rows every day.
  RUN_TIME   NAME            ALLOC_SIZE_GB CURR_USED_SIZE_GB PREV_USED_SIZE_GB VARIANCE
---------- --------------- ------------- ----------------- ----------------- --------------------
2017-03-29 dwh_data04           480            224.07            220.88 3.19
2017-03-30 dwh_data04           480            227.51            224.07 3.44
2017-03-31 dwh_data04           480            230.05            227.51 2.54
2017-04-01 dwh_data04           480            233.26            230.05 3.21
2017-04-02 dwh_data04           480            236.07            233.26 2.81
2017-04-03 dwh_data04           480             239.5            236.07 3.43
2017-04-04 dwh_data04           480            242.75             239.5 3.25
2017-04-05 dwh_data04           480            244.88            242.75 2.13

What could be the possible reason for space consumption, As per my understanding ORACLE should re-utilize empty space.

Regadrs,
Sachin
Re: Tablespace Not re utilize space [message #661964 is a reply to message #661963] Sat, 08 April 2017 07:02 Go to previous message
sachinonnet
Messages: 5
Registered: June 2008
Location: INDIA
Junior Member

Thanks for the updates, I will update once I apply the logic.

Michel , I will take care of the tags , thanks for all
Previous Topic: Tablespace question, need the answer
Next Topic: Unexpected queries on user_arguments
Goto Forum:
  


Current Time: Wed Dec 13 21:45:21 CST 2017

Total time taken to generate the page: 0.02411 seconds